DAC (Dedicated Administrator Connection) is a well-known function of SQL Server for the case that the server isn’t responding to regular connection for various reasons like heavy-load etc. Having the magic DAC in the pocket can lead administrators to the opinion that they are save in such a case, that…
This article is just a simple demonstration of improvements in scalar functions handling implemented in SQL Server 2019. Prior to this version when the scalar function was used in a query, it was blocking parallel plan generation. You can read more on this topic in this article. One of the…
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()…
The more and more is Power BI used everywhere it’s starting to be really painful to incorporate it in any metadata management solution. This article is a result of 10 hours investigation on how to get metadata from Power BI files. To be honest, a lot of workarounds, community tools,…
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…
One of our customers was facing the issue of how to save responses from Microsoft Forms to Azure SQL database. The initial idea was to go with Google Forms and Zappier but we have recommended the use of Microsoft Forms to stay inside the Azure environment instead of being split…
OdbcPrec() and OdbcScale() functions
14. 10. 2020
I have discovered these two functions accidentally when searching for something else. I was trying to find more information about them but there is nothing in the official documentation and these function can’t be found in any metadata view like sys.all_object etc. It looks like they exist in SQL Server…
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…
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…