Press "Enter" to skip to content

Category: T-SQL

Optimizing for Mediocre

Published 2021-10-11 by Kevin Feasel

Erik Darling points out an issue with some approaches to preventing parameter sniffing problems in queries:

Despite the many metric tons of blog posts warning people about this stuff, I still see many local variables and optimize for unknown hints. As a solution to parameter sniffing, it’s probably the best choice 1/1000th of the time. I still end up having to fix the other 999/1000 times, though.

In this post, I want to show you how using either optimize for unknown or local variables makes my job — and the job of anyone trying to fix this stuff — harder than it should be.

Click through for two methods, both of which end up being the wrong answer.

Comments closed

Value Comparisons with Nullable Columns

Published 2021-10-11 by Kevin Feasel

Chad Baldwin wants to check if rows exist before inserting:

I haven’t posted in a while, so I thought I would throw a quick one together to hopefully restart the habit of writing and posting on a regular basis.

One of my first blog posts covered how to only update rows that changed. In that post, I described a popular method that uses EXISTS and EXCEPT to find rows that had changed while also implicitly handling NULL values.

Click through for two types of technique, one for non-nullable data and one which can include NULL.

Comments closed

Foreign Keys and Delete Operations

Published 2021-10-08 by Kevin Feasel

Kenneth Fisher takes us through a case of deleting rows:

Deleting rows from a table is a pretty simple task right? Not always. Foreign keys, while providing a ton of benefits, do make deletes a bit more complicated.

Click through for an example of this, as well as a quick discussion of cascading deletes, which sound really useful until you make a big mistake. The other problem with cascading deletes is, even if you do intend to delete everything noted, the process is a lot slower than what you can do in batches, and you’re liable to increase the size of your transaction log file to boot.

Comments closed

Top with Percent

Published 2021-10-06 by Kevin Feasel

Kevin Wilkie is on the top shelf:

In the last blog post, we went over the extreme basics of using the TOP operator in SQL. We showed how to grab things like the TOP 10 of a certain item.

That ability will get you through a number of criteria that you will be asked to perform. But what if you’re asked to grab the top five percent of performers in your company? Or in a region? It’s kinda hard to do that if you only have what we know so far, right?

Read on for the answer.

Comments closed

UDFs and STRING_AGG

Published 2021-10-05 by Kevin Feasel

Erik Darling has a bone to pick with STRING_AGG():

If you’re like me and you got excited by the induction of STRING_AGG into the T-SQL Lexicon because of all the code odd-balling it would replace, you were likely also promptly disappointed for a few reasons.

Read on for one post which covers all of those reasons. Even with that disappointment, I’m still happy with STRING_AGG() on the whole, myself. There are some extra steps it’d be nice to eliminate in certain circumstances, but 60% of the time, it works every time.

Comments closed

Sharing Short Code Examples

Published 2021-10-05 by Kevin Feasel

John McCormack lays out the parameters for this T-SQL Tuesday:

T-SQL Tuesday this month is going back to basics and its all about code. I’d like to know “What are your go to handy short scripts”?

What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.

Click through for two of John’s.

Comments closed

Using the FIRST_VALUE() Window Function

Published 2021-10-04 by Kevin Feasel

Dave Mason explains what FIRST_VALUE() does:

Last week, I found myself with a T-SQL problem. A customer has a database where multiple “widget” records might exist in a table. End users might enter the duplicate rows, or they might be inserted by other automated processes. My task was to keep the most recent widget record. But, if there were any NULL values, look in the previous “duplicate” rows and use the most recent missing value for each column.

Click through to see it in action. And following up from Dave’s last point, IGNORE NULLS did originate in Azure SQL Edge, though hopefully we see it in the next version of on-premises SQL Server.

Comments closed

Unique Constraints vs Unique Indexes

Published 2021-09-29 by Kevin Feasel

Erik Darling calls out unique key constraints:

I do love appropriately applied uniqueness. It can be helpful not just for keeping bad data out, but also help the optimizer reason about how many rows might qualify when you join or filter on that data.

The thing is, I disagree a little bit with how most people set them up, which is by creating a unique constraint.

Data modeling Kevin wants to use unique key constraints because that’s the correct thing to do. Implementation Kevin uses unique nonclustered indexes for the reasons Erik describes. Not mentioned in Erik’s post but potentially relevant is that operations on unique nonclustered indexes can be done online, whereas unique key constraint operations (creation and alteration via drop+create) are offline.

Comments closed

Using Query Labels in Azure Synapse Analytics

Published 2021-09-27 by Kevin Feasel

Gauri Mahajan shows one of the pieces of functionality in Azure Synapse Analytics dedicated SQL pools that I’d like to see on-premises:

Azure Synapse supports a concept known as “query labels” that allows tagging any DDL or DML queries that are executed on the dedicated SQL pool. These labels can be queried using the dynamic management views (DMVs). One can use these labels to describe the purpose of the query or add any metadata to the query being executed and the same can be used later for instrumenting the queries, specifically to identify the queries that meet the desired search criteria. Let’s walk through a step-by-step exercise to understand this concept practically.

Click through for the process.

Comments closed