A Fine Slice Of SQL Server
Chad Callihan shows off one of the best window functions:
The LAG function in SQL Server allows you to work with a row of data as well as the previous row of data in a data set. When would that ever be useful? If you’re a sports fan, you’re familiar with this concept whether you realize it or not. Let’s look at an example.
LAG() is outstanding for business reports, such as if you want three-month trailing data.
Dinesh Asanka hits on one of the under-utilized grouping operators:
You will see that data is aggregated for the columns provided by the GROUP BY clause. Important to note that the data will not be ordered in the GROUP BY columns and you need to explicitly order them by using the ORDER BY clause as shown in the above query.
In the above query, if you wish to find the total for Australia only, you need to run another GROUP BY with EnglishCountryRegionName and perform a UNION ALL. This will be a very ugly method. By using GROUP BY ROLLUP you can achieve the above-said task as shown in the following query.
If I were to rank grouping operators by how frequently I use them, it’s GROUPING SETS by a country mile, then ROLLUP, and almost never do I use CUBE.
ROWS PRECEDING, FOLLOWING, UNBOUNDED, refers to the current row and those before or after based on preceding or following.
RANGE PRECEDING, FOLLOWING, UNBOUNDED, means all values in the current range and those before or after.
Read on to see how to use these.
Comments closedErik Darling jumps to the heart of the issue:
How many times have you seen something like this?
You can replace “Update” with “Insert” or “Delete”, but the story is the same.
Large modifications are typically not fast modifications, for a number of reasons. To understand why, we need to think about the process as a whole.
Click through for several possible reasons.
Comments closedI see a lot of scripts on the internet that use dynamic SQL, but leave people wide open to SQL injection attacks.
In many cases they’re probably harmless, hitting DMVs, object names, etc. But they set a bad example. From there, people will adapt whatever dynamic SQL worked elsewhere to something they’re currently working on.
Click through for a demonstration of the problem.
Comments closedErik Darling shows how we can use FOR XML PATH on data which includes reserved XML characters:
The purpose of these queries is to show you hot to remove XML elements, and handle XML control characters like &, <, >, etc. All of these results return a single row, just to keep the examples simple.
Read on to learn more. One thing I’ve done in the past, when I know that there are specific reserved characters in use, is to run REPLACE() over the resultant data, changing < to < and so forth. But Erik shows us how to do it the best way.
Shane O’Neill has a new cmdlet for us:
Don’t get me wrong – I’m aware that you don’t need Excel installed on the computer where you’re running these commands from. You still need to save the files somewhere though. The function doesn’t take data from variables.
I can use dbatools and
Write-DbaDbTableData. This function is not dependent on the table having to already exist. It will create the table for you if you tell it to. Thank you-AutoCreateTable; even though I recommend pre-sizing your columns if you want to go with this method.However, I don’t want to have to create the table beforehand.
Click through to check it out and grab a copy for yourself.
Comments closedBrent Ozar takes us through a simple but useful SET command:
When you’re working with T-SQL, you’ll often see SET NOCOUNT ON at the beginning of stored procedures and triggers.
What SET NCOUNT ON does is prevent the “1 row affected” messages from being returned for every operation.
Read on to see why this is useful. Also check out the comments for a few other reasons to use it, such as applications written in such a way that they get confused and fail when NOCOUNT is off.
Kenneth Fisher takes us through CROSS versus OUTER APPLY:
I love CROSS APPLY. I also love OUTER APPLY. What’s the difference though? The same difference as with an INNER vs OUTER JOINs. The CROSS APPLY only includes rows where there is a match, while OUTER APPLY includes all rows even if there isn’t a match. I’ve found over time that I have a lot easier time using an example for this rather than trying to explain in any detail. I’m going to use STRING_SPLIT for my example because it’s easy.
Click through for the example.
Comments closedErik Darling talks about a pet peeve of mine:
I see people writing stored procedures that use local variables either because they read that all functions in a where clause are dangerous, or because they need to perform a calculation on one of the parameters that get passed in.
In nearly all cases, you’re better off… not doing that.
Here are a couple examples of why.
I think a lot of this stems from advice to avoid non-SARGable predicates, but miss the idea that expressions of the sort Erik shows are perfectly cromulent.
Comments closed