Press "Enter" to skip to content

Category: T-SQL

Using FOR XML PATH with Reserved XML Characters

Published 2021-06-17 by Kevin Feasel

Erik 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 &lt; to < and so forth. But Erik shows us how to do it the best way.

Comments closed

ConvertTo-SQLSelect

Published 2021-06-16 by Kevin Feasel

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 closed

What SET NOCOUNT ON Does

Published 2021-06-16 by Kevin Feasel

Brent 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.

Comments closed

CROSS and OUTER APPLY

Published 2021-06-11 by Kevin Feasel

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 closed

When Expressions Beat Local Variables

Published 2021-06-03 by Kevin Feasel

Erik 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

Inlined Financial Functions

Published 2021-06-02 by Kevin Feasel

Erik 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 closed

Understanding the STUFF() Function

Published 2021-05-28 by Kevin Feasel

Chad 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.

Comments closed

Querying Delta Lake Files with T-SQL in Azure Synapse Analytics

Published 2021-05-28 by Kevin Feasel

Jovan Popovic shows off a new feature in public preview for Azure Synapse Analytics serverless SQL pools:

You can use Azure Synapse and Azure Databricks to prepare and modify your Delta Lake data sets placed in the Azure Data Lake storage. Once your data engineers have prepared the data, your data analysts can create reports using the tools such as Power BI.

Using the serverless query endpoint in Azure Synapse, you can create a relational layer on top of your Delta Lake files that directly references the location where Azure Synapse and Azure Databricks are used to modify data. This way, you can get the real-time analytics on top of the Delta Lake data set without any need to wait for a pipeline to copy and prepare data.

Read on to see how this works.

Comments closed

Passing Defaults to Stored Procedures

Published 2021-05-26 by Kevin Feasel

Greg Dodd wants the usual order:

If you’ve done work with stored procedures, you are probably aware that stored procedures have parameters, and that the parameters can be defaulted when you declare them. I was recently caught out due to some application code that checked when a parameter was specified for a stored procedure, if the value for the parameter was NULL then pass in the keyword DEFAULT. The Code assumed that if I had gone to the effort of specifying the parameter but not the value, that I must want the default value of the Stored Procedure. I had expected it would pass in the SQL NULL keyword.

Read on to see what actually happens and how you can use a default value.

Comments closed

Querying the SQL Server Backup History

Published 2021-05-25 by Kevin Feasel

Chad Callihan takes us through our sordid pasts:

What do you do when you want to check the history of your database backups? Do you look at a drive containing your backups? Do you have software like Netbackup for you to open and review? My preference is to run a query in SSMS to get a quick overview.

With a couple tweaks this versatile script can be helpful in a variety of scenarios. You can check a single database, an entire SQL server, or multiple SQL servers. Here is how I use this type of script that queries the backupset table and backupmediafamily table in msdb. I recommend checking out these two tables because there are some good column options that you may want to include when reviewing backups. If you’re feeling dangerous, just modify the scripts below to be SELECT * and see what you get.

Click through for the scripts.

Comments closed