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…

Read more
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…
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…

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

Specifying FILEGROUP in SELECT * INTO

17. 1. 2019

SQL Server 2017 and SP2 for SQL 2016 bring a small enhancement of SELECT .. INTO clause which offers more possibilities on how to solve some DBA head-scratching situations. It’s possible to specify FILEGROUP where the new table will be created. Till now it was only in the PRIMARY filegroup…

Read more
T-SQL

TRIM() přichází: LTRIM(RTRIM()) v propadlišti dějin

5. 1. 2019
Nová funkce TRIM(), která přichází v SQL Serveru 2017, je opravu jen logickou kombinací dvou funkcí RTRIM() a LTRIM() vnořených do sebe, ale i tak se jedná o milé vylepšení T-SQL jazyka, který se tím kompatibilitou zase o kousek blíží SQL standardům implementovaných v konkurenčních RDBMS. Syntaktické a funkční srovnání…
T-SQL

TRANSLATE() jako alternativa k řetězení REPLACE() funkcí

5. 1. 2019

Další novou zajímavou funkcí pro práci s řetězci v SQL Serveru 2017 je funkce TRANSLATE(), která funguje jako šikovná náhrada více REPLACE() funkcí vnořených do sebe. Funkce má tři vstupní parametry: TRANSLATE ( inputString, characters, translations) inputString je řetězec, v němž chceme provést nahrazení characters jsou znaky, které chceme nahradit…

Read more