Press "Enter" to skip to content

Category: Syntax

Using CONCAT_WS

Published 2018-03-30 by Kevin Feasel

Dave Mason points out another nice addition to the T-SQL toolbelt in SQL Server 2017:

In the last post, I looked at a new T-SQL function for SQL Server 2017. Let’s continue down that path and look at CONCAT_WS(), which is also new for SQL Server 2017. Here’s the definition of the function from Microsoft Docs:

“Concatenates a variable number of arguments with a delimiter specified in the 1st argument. (CONCAT_WS indicates concatenate with separator.)”

Read on for an example using CONCAT_WS.  It’s one of those functions that I haven’t quite committed to memory, but every time I get reminded of it, I remember that I really need to remember it.

Comments closed

Comparing Distinctness

Published 2018-03-28 by Kevin Feasel

Michael J. Swart shows several options for comparing whether an attribute’s value is distinct from a parameter:

Check it:

DECLARE @TeamId bigint = NULL,
    @SubTeamId bigint = NULL;
 
SELECT TOP 1 TaskId
FROM tasks
WHERE assignedTeamId IS NOT DISTINCT FROM @TeamId
  AND assignedSubTeamId IS NOT DISTINCT FROM @SubTeamId

Talk about elegant! That’s what we wanted from the beginning. It’s part of ANSI’s SQL 1999 standard. Paul White tells us it’s implemented internally as part of the query processor, but it’s not part of T-SQL! There’s a connect item for it… err. Or whatever they’re calling it these days. Go read all the comments and then give it a vote. There are lots of examples of problems that this feature would solve.

PROS: Super-elegant!
CONS: Invalid syntax (vote to have it included).

This would be nice to have.  In the meantime, Michael shows several options which are currently valid syntax.

Comments closed

The Secret Power Of TRIM

Published 2018-03-26 by Kevin Feasel

Dave Mason points out something quite useful about TRIM in SQL Server 2017:

Now I can tidy things up and remove both leading and trailing spaces with a single call to TRIM():

SELECT TRIM(b.foo)
FROM dbo.bar b;

On the surface, this may not seem like that big of a deal. And I would tend to agree. But, in this example, it saves a few key strokes and makes the code slightly more readable. And it is nice for T-SQL to finally have a function that has been around in other languages for far longer than I’ve been writing code for a living.

But Wait, There’s More!

Click through for that more.  This makes TRIM a lot more useful, so go check it out.

Comments closed

Window Functions In SQL

Published 2018-03-16 by Kevin Feasel

Eleni Markou explains what window functions are:

What we want is a table with an extra column which will represent the average price of all products belonging to the same category as the one on the current line.

One approach to solve this problem is to calculate the average price per category using an aggregate function and then join the result with the initial table over the Product Type column in order to get a new table looking at which you can easily find out if a product is more expensive than the average of its category.

Although this would definitely do the job, the query would be quite complicated and lengthy and may lack readability. To avoid these, an alternative approach would be to make use of window function where there is no need to mess with subqueries and joins. When using a windowed function, you can retrieve both aggregated and non-aggregated values at the same time while when using GROUP BY you can get only the results grouped into a single output row.

I ask questions about window (or windowing) functions whenever I interview someone for a job.  They are extremely useful things, and I highly recommend Itzik Ben-Gan’s windowing functions book for SQL Server 2012 if you want to learn a lot more.

Comments closed

Using STRING_AGG In SQL Server 2017

Published 2018-03-09 by Kevin Feasel

Derik Hammer talks about one of the nicer T-SQL additions in SQL Server 2017:

Creating comma separated strings from a column, or delimited strings as I like to call it, is a very common problem in SQL. Beginning with SQL Server 2017 and Azure SQL Database, there is now another option to the existing set of solutions, STRING_AGG().

I would like to convince you to use STRING_AGG over the other methods. So, let us begin with the competing solutions.

I completely agree and have been switching code over to use STRING_AGG since upgrading to 2017.  The code is so much clearer as a result compared to STUFF + FOR XML PATH concatenation.

Comments closed

The Power Of Window Functions

Published 2018-02-22 by Kevin Feasel

Ben Richardson has an introduction to the concept of window functions:

This is a much more efficient result. In the first line of the script the id, name and gender columns are retrieved. These columns do not contain any aggregated results.

Next, for the columns that contain aggregated results, we simply specify the aggregated function, followed by the OVER clause and then within the parenthesis we specify the PARTITION BY clause followed by the name of the column that we want our results to be partitioned as shown below.

This post focuses on normal aggregates which accept windows.  Once you understand that, there’s a wide world beyond it, and you’ll quickly see how useful window functions can be.

Comments closed

Visualizing Types Of Joins

Published 2018-02-19 by Kevin Feasel

Daniel Hutmacher has a nice PDF cheat sheet showing different types of joins:

How it works: For each join example, there are two tables, the left and the right table, shown as two columns. For the sake of simplicity, these tables are called “a” and “b” respectively in the code.

You’ll notice that the sheet uses a kind of pseudo-code when it comes to table names and column names.

Click through for the PDF.  I appreciate that he did not use Venn diagrams, as those should be saved for Union/Intersect/Except.

Comments closed

The Joy Of OUTPUT

Published 2018-01-17 by Kevin Feasel

Mark Wilkinson shows off some fun stuff you can do with the OUTPUT clause:

A common command in the Linux world is the tee command. What tee allows you to do is pipe the output of a command to a file as well as the console. This same functionality can be implemented using multiple OUTPUT clauses in a T-SQL statement. In this example we are going to update a few hundred records. When the update statement is run, not only will it update the MyGuid table but it will update a log table and also return the result of the update. This is accomplished by using two OUTPUT clauses.

Check it out.  I don’t use OUTPUT that often, but it can be quite useful when in a pinch or if you want to prevent scanning a table twice.

Comments closed

Using JSON_MODIFY To Modify Existing JSON

Published 2017-12-28 by Kevin Feasel

Jovan Popovic shows off the JSON_MODIFY function in SQL Server:

Recently I found this question on stack overflow. The problem was in appending a new JSON object to the existing JSON array:

UPDATE TheTable
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', N'{"id": 3, "name": "Three"}')
WHERE Condition = 1;

JSON_MODIFY function should take the array value from TheJSON column (the first argument), append the third argument into the first argument, and write the appended array back in TheJSON column.

However, the unexpected results in this case is the fact that JSON_MODIFY didn’t appended a JSON object {"id": 3, "name": "Three"}to the array. Instead, JSON_MODIFY appended a new JSON string literal  "{\"id\": 3, \"name\": \"Three\"}" to the end of the array.

This might be surprising result if you don’t know how JSON_MODIFY function works.

Read on to see how JSON_MODIFY works and why this doesn’t quite do what the poster thought.

Comments closed