A Fine Slice Of SQL Server
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 closedErik Darling has some functions for us:
At just about every client site, I see a common set of financial functions being used to calculate various things. The code is all the same, too.
Some of it comes from published government guidelines, and some of it comes straight out of accounting 101 books.
The big problem is that all of these functions were written as scalar UDFs, and performance becomes dead.
Recently, one of my clients was nice enough to agree to let me publish my rewrites of their functions as inline table valued functions.
Check them out on Erik’s GitHub repo.
Comments closedChad Callihan explains an important function:
I used to always see the STUFF function in passing when reading blogs and kind of move past it without understanding what it was doing. I would see it used and think it’s doing something with a bunch of stuff and kind of skip over it since it wasn’t entirely relevant to what else I was reading. When I read about what the STUFF function actually does, it made a lot more sense as a name. More than dealing with “a bunch of stuff” the STUFF function is used for stuffing a string into another string. Let’s check out a few examples of stuffing data.
Granted, 99% of its importance is in combination with FOR XML PATH() but that’s still important. And we get to see a few other use cases for it as well.