T-SQL Ninja #38


What do CONCAT and CONCAT_WS do? The CONCAT function is a function from the field of string manipulation. Similar to what you already know from STUFF and STRING_AGG, these functions link strings together. Unlike STRING_AGG and STUFF in combination with SELECT FOR XML, however, CONCAT and CONCAT_WS do not aggregate rows, i.e. the entries to be combined must already be in columns. How to use CONCAT and CONCAT_WS Let’s […]

T-SQL Ninja #37

COALESCE (and a little ISNULL)

What is COALESCE anyway? Suppose you have several columns in your database that may contain all NULL values. Now you want the value of the first column, but if it’s not there, then the value of the second column, if it’s not there, then the value of the third column, and so on… There are voices that […]

T-SQL Ninja #36


What is STRING_AGG? Last week you saw how to use STUFF and SELECT FOR XML to create a comma-separated list from the rows of a table. Since SQL Server 2017, this has become even easier with the introduction of the STRING_AGGfunction. The STRING_AGG function concatenates strings and inserts a separator. What can you do with STRING_AGG? Remember last week when we created […]

T-SQL Ninja #35


What’s STUFF? STUFF is a function for manipulating strings. The function “stuffs” one string into another, hence the rather figurative name. Traditionally, string manipulation is not necessarily one of the great strengths of relational databases and SQL syntax, STUFF is one of the functions from this area that, if mastered, can make your life much easier. What can […]

T-SQL Ninja #34


What does PERCENTILE_CONT do? The last time you worked in the Dojo you used the PERCENTILE_DISC function. This function always returned you the exact value in your table where a certain quantile was reached. That is, it operated on the discrete set of data and picked a specific value from that set, but what if you wanted […]

T-SQL Ninja #33


What is PERCENTILE_DISC? Like the CUME_DIST function introduced last week, PERCENTILE_DISC is a statistical function. It was introduced in SQL Server 2012 and calculates quantiles. If you want to use the 0.5-quantile of a set (or ‘sample’ if we want to stick to the usual terminology used in statistics) you understand the value where half of all values are […]

T-SQL Ninja #32


What is CUME_DIST? Suppose you have a table with certain values and you want to know what percentage of records are below each value. This kind of question can be answered by the CUME_DIST function. CUME_DIST is short for “Cumulative Distribution” and is a statistical function that calculates for a given value and distribution the probability of a record […]

T-SQL Ninja #31


What are LEAD and LAG? Last week you saw how you use FIRST_VALUE and LAST_VALUE to give the first and last rows in a query window. But what do you do if you want to compare a row in the Sales.Order table with the customer’s previous order? Then LEAD and LAG are the means of choice. LEAD returns the next row in the results window, […]

T-SQL Ninja #30


What are FIRST_VALUE and LAST_VALUE? Like the ROW_NUMBER and RANK functions introduced in weeks 12 to 14, FIRST_VALUE and LAST_VALUE are also part of the window functions. This means they are functions that operate on an ordered set of rows in the result set. According to their names, the functions return the first or last row of the window on which they operate. […]

T-SQL Ninja #29


What is DROP IF EXISTS? You’ve seen it over and over again in the past months, and also from the syntax, the functionality of DROP IF EXISTS is relatively clear: Dropping a database object, if it exists. The syntax for this was introduced in SQL Server 2016 and saves us poor ninjas from some rather unpleasant syntax-climbing […]