Press "Enter" to skip to content

Category: T-SQL

Concatenating Strings and (N)VARCHAR Truncation

Published 2024-05-28 by Kevin Feasel

Vlad Drumea troubleshoots a common problem:

The code in this case is the GetStatsInfoForWholeDB.sql script that’s part of PSBlitz’s resources.
This script is used for, you wouldn’t believe by the name alone, getting statistics information for a specific database.

Due to the fact that it might be ran on Azure or on older versions of SQL Server, as well as on databases with incremental statistics, the best option for it was to use dynamic SQL.

In this case it uses a variable @SQL defined as NVARCHAR(MAX) to store the query that’s built at runtime and execute it via EXEC.

Read on for one of the most common issues you may run into around generating dynamic SQL.

Comments closed

The Joy of the Common Table Expression

Published 2024-05-23 by Kevin Feasel

Kevin Wilkie talks common table expressions:

Most of our coding these days has Common Table Expressions, also known as CTEs. If not, you’re either working on an older version of SQL Server or you haven’t been introduced to this piece of goodness.

CTEs can make reading SQL queries a lot easier if the logic is convoluted. For example, let’s use the following in a CTE.

I’ll admit that I probably over-use common table expressions, but I like them more than sub-queries—I find them easier to read, and if they’re going to perform as well (or poorly) as sub-queries, I’d might as well use the form that makes more intuitive sense to me.

Comments closed

OPTIMIZE FOR vs Forced Plans in SQL Server

Published 2024-05-23 by Kevin Feasel

Erik Darling makes a comparison:

I often see clients using forced plans or plan guides (yes, even still, to this day) to deal with various SQL Server performance problems with plans changing.

There’s usually an execution plan or two floating around that seems to be a good general idea for a given query, and a couple weird high-end and low-end outliers for very specific populations of values.

Read the whole thing, of course.

In defense of plan guides, the company I used to work for had a few—maybe three or four in total—because of really weird data skew problems on database 106 out of 700 (or so)—because there’s always one customer that makes wildly different use of the system than everyone else. And so a query that worked perfectly fine for 699 databases (or so) flops like a fish out of water for this one database with this one customer’s data in it. So the plan guide was a nicer expediency than optimizing for mediocre on all 700 (or so) databases.

1 Comment

The Joy of Partitioned Views

Published 2024-05-16 by Kevin Feasel

Rod Edwards talks partitioned views:

This post came around when I was at a loose end one evening, and just started poking at a local sandpit database, and it got me reminiscing and revisiting / testing a few things. The devil makes work for idle thumbs and all that…

Partitioned Views…do they have a place in society anymore?

Rod does a great job of following Betteridge’s Law of Headlines, as well as saving the ‘Yes’ answer for the post itself. Partitioned views come with their own pains, though one use case Rod did not include is using PolyBase and partitioned views to move “cold” data to slower external storage.

Comments closed

Job Threading and Thread Partitioning in SQL Server

Published 2024-05-16 by Kevin Feasel

Aaron Bertrand continues a series on threading:

In part 2 of this series, I showed an example implementation of distributing a long-running workload in parallel, in order to finish faster. In reality, though, this involves more than just restoring databases. And I have significant skew to deal with: one database that is many times larger than all the rest and has a higher growth rate. So, even though I had spread out my 9-hour job with 400 databases to run faster by having four threads with 100 databases each, one of the threads still took 5 hours, while the others all finished within 1.5 hours.

Read on to learn what Aaron did to make things move faster.

Comments closed

Dynamic Unpivoting of Columns in T-SQL

Published 2024-05-13 by Kevin Feasel

Kristyna Ferris does a bit of twisting:

Picture this, your data ingestion team has created a table that has the sales for each month year split into different columns. At first glance, you may think “what’s the big deal? Should be pretty easy, right? All I need to do is unpivot these columns in Power BI and I’m good to go.” So you go that route, and the report works for one month. Next month, you get an urgent email from your stakeholders saying they can’t see this month’s numbers. That’s when you realize that this table will grow with new columns every month. That means that any report you make needs a schema refresh every single month. Unfortunately, Power BI will not grab new columns from a table once it’s published into the online service. The only way for the Power Query to pivot the new columns is for you to open the report in your desktop, go to Power Query, and refresh the preview to get all the columns in that table.

Which is quite the pain. But Kristyna has a solution using the UNPIVOT operator in T-SQL.

Comments closed

The NOT Keyword in SQL Server

Published 2024-05-08 by Kevin Feasel

Kevin Wilkie gets tied up in NOTs:

Today, I want to talk about the keyword NOT in SQL Server. It can be your friend or your worst enemy depending upon how you use it. Let’s delve into some examples of what I’m talking about and how it’s easy for people to mess it up.

Look, it’s not that I’m not saying you shouldn’t avoid not using NOT here. It’s just that it’s really hard to get the coveted quintuple-negative in natural speech.

Comments closed

Object Dependencies and Referencing Entities

Published 2024-05-08 by Kevin Feasel

Andy Brownsword takes another look:

Last week we looked at identifying object dependencies through performing a wildcard search on the objects such as procedures. I noted another option could be to use the views such as sys.sql_expression_dependencies and Kevin also commented about using the supporting functions such as sys.dm_sql_referencing_entities.

I wanted to briefly look at both of these options and look at how their results can differ. 

Linkception graf achieved.

Comments closed

SQL Server FAST N Query Hint

Published 2024-05-06 by Kevin Feasel

Chad Callihan hits the Turbo button:

How familiar are you with the OPTION (FAST N) query hint? It’s not one that I had ever used, so I decided to experiment with it a bit. Let’s look at what it does and how to apply it to a query.

My recollection is that this query hint highly prioritizes “streaming” operators and de-prioritizes operators that work on the whole dataset as a block, such as sorts. This means, for example, that you’ll get more nested loops joins and natural merge joins, but few sort + merge or hash match joins. It may also make some queries take considerably longer as a result.

Comments closed