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 NULL value stored in DateTime column can be used in…
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…
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…
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…
Having fun with square brackets
11. 9. 2020
This article is just for fun. 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…
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…
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….
Maximum nesting level of CASE statement
1. 7. 2020
Did you know that CASE statement has a maximum nesting level of 10? The same limit exists for IIF() function which is just a syntactical sugar and is internally transformed into multiple CASE statements. I have prepared two samples you…
APPROX_COUNT_DISTINCT()
20. 9. 2019
APPROX_COUNT_DISTINCT() is nice new function announced currently to be in public preview for Azure. Being well known for Oracle users is now joining (like many other things:)) also the Microsoft world. This function is designed to provide aggregations across large…
ROUND() is a well-known function in SQL Server. Most of us know only that two parameters can be used like ROUND(99,95, 2). This will do standard mathematical up/down rounding of numbers in the first parameter for the number of decimal…