A Fine Slice Of SQL Server
Jeet Kainth explains the importance of specifying your window when using LAST_VALUE():
To return the actual last value, add the additional clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This ensures that for each row the LAST_VALUE() function looks at all rows; from the very first row to the very last row.
Click through for the example. Remember that the default is going to the current row, not the entire data set.
Comments closedKevin Chant has some advice when you have to deal with a giant SQL script:
If you have been given a script that is thousands of lines long from a developer, the first thing I would ask is if they can split it up.
I say this because a lot of developers who write long scripts tend to have come from various backgrounds. Hence, some of them are used to developing on other programming languages.
So, they do not always appreciate that SQL is a set-based language. In addition, they do not always appreciate SQL Server is optimised for set based queries.
Sometimes you can break these scripts down, though there are of course good ways and bad ways to do so.
Comments closedBrent Ozar shows why the StartDate + EndDate pattern is not great for filtering:
If all you need to do is look up the memberships for a specific UserId, and you know the UserId, then it’s a piece of cake. You put a nonclustered index on UserId, and call it a day.
But what if you frequently need to pull all of the memberships that were active on a specific date? That’s where performance tuning gets hard: when you don’t know the UserId, and even worse, you can’t predict the date/time you’re looking up, or if it’s always Right Now.
This is where I advocate pivoting to a series of event records, so instead of a start date and end date, you have an event type (started, expired, cancelled, etc.) and a date. There are other alternatives as well, but it’s a good thought exercise.
Comments closedPhil 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.
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 theSTRING_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.
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 closedLast 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 closedArun 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 closedThis 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 closedKevin 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