Press "Enter" to skip to content

Category: T-SQL

Identifying Troublesome NOLOCK Statements

Published 2021-07-29 by Kevin Feasel

Aaron Bertrand is on a mission:

I’ve warned before about the possible downsides of both NOLOCK in general and, more specifically, when used against the target of an update or delete. While Microsoft claims that corruption errors due to the latter have been fixed in cumulative updates (e.g. see KB #2878968), we’re still seeing an occasional related issue where SQL Server will terminate, producing a stack dump that indicates a DML statement with NOLOCK as the cause. How do I find and correct all these potentially problematic statements?

The contrarian in me says, “You’re using NOLOCK; they’re all trouble.” But Aaron is a lot nicer about it than I am here.

Comments closed

Throwing Exceptions in T-SQL

Published 2021-07-23 by Kevin Feasel

Chad Callihan plays hot potato:

When it comes to error handling or troubleshooting a long stored procedure, RAISERROR is an easy statement to use that gets the job done. Way back in SQL Server 2012, Microsoft wanted to replace RAISERROR with the new (arguably less convenient) THROW statement. I thought it would be worth looking at an example using THROW and what it takes to have custom messaging with a parameter.

THROW can be a little less convenient, but conceptually speaking, I do think it’s better than the alternative. The part which makes it tricky in practice is that so many types of T-SQL errors are non-catchable, so as a developer, you have to keep on your toes about it.

Comments closed

String Padding in T-SQL

Published 2021-07-16 by Kevin Feasel

Kenneth Fisher pads some work:

I’ve been working on converting a piece of DB2 code into T-SQL and one of the functions I had to replace was lpad. It’s a simple enough function that will pad a string on the left with another string. So changing 1234 to 00001234. It’s a common enough task when formatting strings. And both DB2 and Oracle provide both lpad and rpad functions. However, guess what? SQL Server doesn’t. So how do we handle that in T-SQL? It’s a pretty easy pattern.

Click through for the answer.

Comments closed

Buffering Events in SQL Server

Published 2021-07-14 by Kevin Feasel

Eitan Blumin has a technique to reduce expensive upserts:

Do you find yourself facing performance problems and long lock chains caused by very frequent INSERT, UPDATE, or DELETE statements being executed on a table? Check out this neat trick that could help you out and make all the difference in the world.

Okay, I admit that title ended up being quite long. But I wanted something that could be easily found in search engines by people facing similar problems.

I’ve done something similar, though without the partition switch and instead deleting batches into a temp table. This is a good example of something I like to say about scalable processes in T-SQL: many times, the most scalable technique involves a mental pivot (and sometimes a literal pivot, such as using tally tables to work with string data) of the straightforward answer.

1 Comment

Removing Comments from Code with T-SQL

Published 2021-07-13 by Kevin Feasel

Tomaz Kastrun is not pleased with these comments:

This procedure will strip all the comments from your T-SQL query and return only the parts, that are uncommented.

Procedure is able to detect and remove the following type of comments (regardless of the position of the code or comment):

– in-line comment ( — comments )

– multiline comment (Slash star /* comments)

– in-line comment block

– comment within a comment (multiline block)

Read on to learn how.

Comments closed

String Splitting with Line Numbers using CTEs

Published 2021-07-13 by Kevin Feasel

Steve Stedman has an interesting use for common table expressions:

Years ago while working on my CTE presentation for a SQL Saturday I added a blog post called “Using a CTE to Split a String Into Rows“, and since that posting I have used it many times.   But as things go in development, eventually there is a need to do something more.

Doing some complex string building to create files lately I cam across the need to use a CTE to split strings into rows but to also include line numbers.  To accomplish that I first started with the same function that I used last year, but renamed it, and added a second output column called LineNumber, here is what it looked like:

Click through for the script, as well as some additional notes.

Comments closed

Levels of Recursion in CTEs

Published 2021-06-29 by Kevin Feasel

Steve Stedman probes the limits:

When presenting unleashing Common Table Expressions at SQL Saturday a while back, I was asked a couple of great questions that I didn’t know the answer to. So I did the research and tracked it down:

1. How many levels of recursion can you have in a CTE?

2. How many levels of nesting can you have in a CTE?

So I started doing the research and doing some testing to figure it out.

How many levels of recursion can you have in a CTE?

Click through for the two answers.

Comments closed

LAG() in SQL Server

Published 2021-06-25 by Kevin Feasel

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.

Comments closed

GROUP BY ROLLUP

Published 2021-06-24 by Kevin Feasel

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.

Comments closed