Press "Enter" to skip to content

Category: T-SQL

Describing the First Result Set

Published 2020-02-19 by Kevin Feasel

Phil Factor dives into sys.dm_exec_describe_first_result_set_for_object():

I’ve been working on a project unkindly nicknamed ‘The Gloop’ because the code is a bit amorphous. Basically, it is an approach to documenting SQL Server databases, using the facilities provided such as the metadata views and DMFs. Although it is relatively simple to record the result returned by a table-valued function I’d rather neglected the stored procedures because there was no metadata that could produce the first result set produced by a procedure or trigger.

I’d been silly because there is, of course, an Execution system DMF that does it: sys.dm_exec_describe_first_result_set_for_object(). it takes as its parameter the object_id of a procedure or trigger and describes the first result metadata for the module with that ID. It has the same result set definition as sys.dm_exec_describe_first_result_set.

I’m going to have to try this out, but I have problems with sp_describe_first_result_set and how it blows up if you use a temp table in the stored procedure whose result set you’re grabbing.

Comments closed

String Aggregation with T-SQL

Published 2020-02-19 by Kevin Feasel

Andy Levy talks about one of my favorite functions in SQL Server 2017:


This is another in a group of several posts on modernizing T-SQL code with new features and functionality available in SQL Server.

SQL Server 2016 gave us the STRING_SPLIT() function, but what about the reverse – compiling a set of values into one delimited string? We only had to wait 15 months for the release of SQL Server 2017, and the STRING_AGG() function.

I had the STUFF() / FOR XML PATH trick memorized for quite some time, but that was always a solution which felt like it worked on accident. Even if the new solution weren’t faster than the old, I’d still use it.

Comments closed

NULL Handling Features not in T-SQL

Published 2020-02-17 by Kevin Feasel

Itzik Ben-Gan continues a series complexity around NULL:

When using the offset window functions LAG, LEAD, FIRST_VALUE and LAST_VALUE, sometimes you need to control the NULL treatment behavior. By default, these functions return the result of the requested expression in the requested position, irrespective of whether the result of the expression is an actual value or a NULL. However, sometimes you want to continue moving in the relevant direction, (backward for LAG and LAST_VALUE, forward for LEAD and FIRST_VALUE), and return the first non-NULL value if present, and NULL otherwise. The standard gives you control over this behavior using a NULL treatment clause with the following syntax:

offset_function(<expression>) IGNORE_NULLS | RESPECT NULLS OVER(<window specification>)

There are three good examples of functionality around handling NULL which the current implementation of T-SQL is missing.

Comments closed

SELECT * Doesn’t Keep Views up to Date

Published 2020-02-13 by Kevin Feasel

Reitse Eskens busts a myth:

Last week i read a comment or a blog somewhere (i can’t remember it anymore but please let me know so i can credit!), that a select * in a view gets a full definition under the covers and doesn’t get updated when the underlying table is updated.

So, i decided to take that for a test, see what i can reproduce.

Spoilers: it doesn’t. Click through for the proof, as well as what does update a view’s definition.

Comments closed

Handling Azure SQL Database Scale Changes

Published 2020-02-13 by Kevin Feasel

Arun Sirpal shows us how to handle scaling events in Azure SQL Database:

For some reason I have friends / colleagues telling me that when scaling (up and down for this example) that no downtime occurs. Well, not only does Microsoft documentation say differently, I will show it. So let’s test it out. Before the practical test, this is the official stance. “There is a switch over period where connectivity is lost to the database for a short amount of time, which can be mitigated using retry logic”.

Retry logic is an important part of any application. We tend to forget about it with on-prem applications talking to on-prem databases, but that’s a mistake.

Comments closed

Guids in Persisted Calculated Columns

Published 2020-02-13 by Kevin Feasel

Slava Murygin shows us some odd things which occur when you try to make a persisted calculated column out from a UNIQUEIDENFITIER data type:

This post is for you in case you decide to use Uniqueidentifier column in your table and then you think about including it into a Persisted calculated column.
You also might see it useful if you like weird or funny SQL Server behavior.

Slava has an Azure feedback item and it looks like someone tested the behavior in SQL Server 2019 and it works as you’d expect, so this must have been fixed sometime between then and now.

Comments closed

SQL Server Life Hacks

Published 2020-02-12 by Kevin Feasel

Kevin Hill has a few life hacks for us:

Since I am an independent consultant here under the Dallas DBAs name, I am frequently asked to come in and quickly find out why the server is so slow. Sometimes by existing customers, sometimes out of nowhere. Many of these times I cannot make any “permanent” or “lasting” changes, so my go-to is to run sp_whoisactive as a temporary stored procedure (code in link). Works the same, nothing left behind, no corporate policies violated.

Read on for another useful tip.

Comments closed

Solving Groups of Groups Problems with Window Functions

Published 2020-02-12 by Kevin Feasel

James McGillivray shows off some of the power of window functions:

Windowing functions are an underused feature in SQL Server, with myriad uses. The most common problems we solve are running totals, seeing group totals on the same line as individual lines from the group (allowing calculations like subtotal %). I don’t know if there are better solutions, but before Windowing Functions, I used to solve these kind of problems with self joins, or nested queries. The performance of Windowing Functions is significantly better than this approach, and that alone has made my life considerably better.

The more I learn about Windowing Functions, the more often I see use cases where they are useful.

I love talking about, and teaching people to use, Windowing functions to make their lives better, so I’m quite excited to be able to use them as the topic for today’s post.

Specifically, James looks at groups of groups problems and aggregates of aggregates problems.

Comments closed

Finding Foreign Key Relationships in SQL Server

Published 2020-02-04 by Kevin Feasel

John Morehouse shows how you can piece together foreign key relationships in SQL Server:

Recently, I had to purge some parent records from a table.  In this case, the parent table had foreign keys, which itself isn’t an issue.  The fact that there were more than 30 of them was.   While SQL Server will happily tell you that you are violating a foreign key if a child record is present when deleting the parent record, finding all of them can be cumbersome.  This is even more true when you have a larger number of foreign keys.

Thankfully, SQL Server can tell us a lot of information about foreign keys including both the parent and child tables as well as the column used.  From this information, we can dynamically create a SELECT statement that would tell us the number of child records that are tied to the parent ID.

Click through for the solution.

Comments closed

Splitting Strings with T-SQL

Published 2020-02-04 by Kevin Feasel

Andy Levy recommends using STRING_SPLIT():

Last year, you finally retired the last of your SQL Server 2008R2 instances. Congratulations! But are you taking advantage of everything that your new instances have to offer? Unless you did a review of all of the T-SQL in your applications, I’m guessing not.

At one time or another, we all find ourselves having to do some string parsing, especially splitting strings on a delimiter. Nearly all of us have one (or two or a dozen) functions for doing this somewhere on every instance of SQL Server. But since SQL Server 2016, we’ve had an official way to do it – the STRING_SPLIT() function.

Andy’s example involves splitting strings, but there are plenty of functions which come into the T-SQL lexicon. It might be worth doing a quick review of the available system functions just to see if there’s something useful which slipped with a newer version of SQL Server.

Comments closed