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
T-SQL

Maximum nesting level of CASE statement

1. 7. 2020
Did you know that the 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 can play with it. Let’s start with the query…
T-SQL

APPROX_COUNT_DISTINCT()

20. 9. 2019
APPROX_COUNT_DISTINCT() is a 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 data sets where responsiveness is more critical than absolute…
T-SQL

Removing decimal places from a number without rounding

11. 9. 2019

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 places from the second parameter. But there is also an…

Read more
T-SQL

Concatenating strings in T-SQL

1. 4. 2019
It may look like an easy task to concatenate string in T-SQL at the first look. But it still has some hidden points where beginners are failing most of the time. There are a few very important things to remember when working with string and doing concatenation. Without respecting them…
T-SQL

Comparing strings with trailing spaces

20. 2. 2019

There is all the time confusion in the developer’s daily job how exactly is SQL Server handling padding spaces before and after strings in comparison and data persistence. Time to make it more clear now. The few most important things to remember are: SQL Server follows the ANSI/ISO SQL-92 specification…

Read more