T-SQL

Calculating average value of multiple columns

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

Having fun with square brackets

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

Using MERGE to override OUTPUT clause limitations

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

Maximum nesting level of CASE statement

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

APPROX_COUNT_DISTINCT()

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

Removing decimal places from number without rounding

ROUND() is well known function in SQL Server. Most of us knows only that two parameters can be user like ROUND(99,95, 2). This will do standard mathematical up/down rounding of number in first parameter for number of decimal places from…
T-SQL

Concatenating strings in T-SQL

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

Comparing strings with trailing spaces

There is all the time confusion in 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. Few most important things to remember…