There isn’t built-in support for working with hierarchies in the SQL Server except for the hierarchyid data type. This means that we should solve most of the hierarchical data processing tasks using the old-school portfolio of standard T-SQL language. One of these tasks is a simple conversion of hierarchical paths…
Objects like tables or views can be referenced using the dot (.) notation based on this pattern: [{server}].[{database}].[{schema}].[{object}], e.g. [MyServer1].[MyDatabase1].[dbo].[MyTable1]. One will expect that this sequence should go from right to left and parts can’t be skipped. But we can have much more fun with it in the reality. When…
Generate Array of Values in FOR JSON
23. 1. 2022
There is quite sustainable support for working with JSON format in SQL Server since the 2016 version. But it’s tricky time-to-time to do more complex things, especially building more complex JSON documents from tabular data format. One of these things is how to build an array of values, e.g. we…
Practical Tally Table to WHILE Comparison (Generating Calendar) The “Tally table” is a well-known concept not only in the SQL Server world but in all set-based (relational) database engines. I don’t want to repeat some basic or go much deep into it. You can do it on your own starting…
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…
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…
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…
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()…
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…
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…