T-SQL

FORMATMESSAGE() maximum output length

26. 12. 2021

FORMATMESSAGE() is a quite useful competitor to RAISERROR() function, mainly in the case we won’t print the message directly but prepare it for further processing. One important limitation is there we must remember: The maximum output character length is limited to 2047 characters. When the final message is longer then…

Read more
T-SQL

OFFSET with FETCH NEXT and Clustered Index Scans

7. 3. 2021
The OFFSET… FETCH NEXT extension to SELECT statement was introduced first in SQL Server 2012. It should basically support simple pagination of query results from the application. For large datasets, it’s really important to understand how it is internally implemented, and based on these findings, I won’t recommend using it…
T-SQL

Updating LOBs using the WRITE() column method

24. 2. 2021
The WRITE (expression,@Offset,@Length) extension of the UPDATE statement can be used to perform updates that insert or append new data in minimally logged mode if the database recovery model is set to bulk-logged or simple. Minimal logging is not used when existing values are updated. This all is valid for…
T-SQL

What is the default data type of NULL?

9. 1. 2021

When a NULL value is stored in a column it’s easy to say what is the data type of it: it’s the data type of the column. This means that the NULL value stored in the DateTime column can be used in all the date & time functions like DATEADD()…

Read more
T-SQL

Using Binary Hash as an Alternate Record Key

17. 12. 2020
It’s a common scenario in data processing solutions that we must import some data for further processing and we can’t dictate source format or data types used. This can complicate things, especially in the case when source data key values are in string format with untrivial length. Character data types…
T-SQL

Using STRING_AGG() as a dynamic T-SQL generator

12. 11. 2020
Concatenating strings is an essential practice in the dynamic T-SQL preparation process. A very common task is to generate multiple commands statements concatenated with UNION ALL, i.e. to calculate the number of rows in every table. The traditional way how to do it is that we will declare the @Stmt…
T-SQL

Calculating average value of multiple columns

13. 10. 2020

It’s an easy task to calculate the average AVG() value for all rows in one or more columns. But it starts to be a little bit more interesting when we should calculate the average value for more columns on the same row. There are basically two options on how to…

Read more
T-SQL

Having fun with square brackets

11. 9. 2020
This article is just for fun. From time to time T-SQL dialect may look like little bit scary. In the example below, we will create such a monster using square brackets. We will create a sample table and remove one square bracket in the column name. Let’s see what will…
T-SQL

Converting string values with thousands separator to numeric value

16. 8. 2020
It’s the common task to import CSV files into a staging area in the database where columns are using some character data type like VARCHAR and conversion to proper numeric type is done later. It can be a little bit confusing if our input data contains numbers stored with different…
T-SQL

Using MERGE to override OUTPUT clause limitations

7. 7. 2020

The OUTPUT clause is a very powerful extension of T-SQL included in SQL Server 2005. But from time to time it needs some tweaks or workarounds to get the expected result. One such example is using OUTPUT with INSERT statements. There is a limitation that only columns from the table…

Read more