Press "Enter" to skip to content

Category: T-SQL

Defending Less-than-Ideal Practices

Published 2023-01-12 by Kevin Feasel

Deborah Melkin has a confession and a defense:

I feel like this is where I should say something like, “Hi, my name is Deborah and I’ve used nolock in production.” I would also have to confess to doing things like using correlated sub queries, not using a foreign key, implemented complicated triggers, etc. I often talk about how the first real SQL script I wrote had cursors running over temp tables in SQL Server 6.5, which I’m fairly certain was one of the first thing I read you were NOT supposed to do. And oh, hello there, denomalized table and dynamic SQL! I’m sure I’ve done more things than this too. These are just the ones I can remember doing, or at least I’m willing to admit in public.

With some of these, the answer is “that’s the best alternative I had at the time.” With correlated sub-queries, I wouldn’t even consider that a bad thing. Granted, I personally prefer a combination of common table expressions and the APPLY operator but that’s usually not for performance reasons.

Comments closed

Finding Skipped Identity Values in a Table

Published 2023-01-09 by Kevin Feasel

Brent Ozar minds the gap:

When someone says, “Find all the rows that have been deleted,” it’s a lot easier when the table has an Id/Identity column. Let’s take the Stack Overflow Users table:

It has Ids -1, 1, 2, 3, 4, 5 … but no 6 or 7. (Or 0.) If someone asks you to find all the Ids that got deleted or skipped, how do we do it?

Click through for two methods, one specific to SQL Server 2022 and one which works for all versions of SQL Server.

Comments closed

Finding Blockers in Azure SQL DB + MI

Published 2022-12-28 by Kevin Feasel

Jose Manuel Jurado Diaz writes a program:

Today, I worked on a service request that our customer is looking for all blocking issues that is happening in their database. We have many articles about it Lesson Learned #22: How to identify blocking issues? – Microsoft Community Hub and in Diagnostics Settings and QDS we can collect this information but all points to that we cannot see the TSQL that is blocking and TSQL command that is blocked in an easy way. In the following script that I share as a script example we could take this one. 

First of all, please, remember that a blocking issue is normal and fundamental for any RDBMS. This script is basically when you need to understand what is happening to improve or reduce this.

I was a little surprised the answer wasn’t to use Extended Events, though this does work if you simply need something to run in ad hoc scenarios.

Comments closed

Finding Rogue Line Feeds

Published 2022-12-23 by Kevin Feasel

Bill Fellows finds out whose line (feed) it was, anyway:

I ran into an issue today that I wanted to write about so that maybe I remember the solution. We ran into a case where the source data in a column had an unprintable character. In this case, it was a line feed character, which is ASCII value 10, and they had 7 instances in this one row. “How did that get in there? Surely that’s an edge case and we can just ignore it,” and dear reader, I’ve been around long enough to know that this is likely a systemic situation.

Click through for two ways of answering this against data already in SQL Server, as well as one additional route explained but sans demonstration.

Comments closed

Comparing Table Records with T-SQL

Published 2022-12-22 by Kevin Feasel

Chad Callihan compares and contrasts:

We recently looked at looked at comparing schemas using Azure Data Studio. What if we need to compare tables by using a query? For this post we’ll compare using EXCEPT, NOT IN, and NOT EXISTS to find differences between two tables.

Our two tables to compare will be Comic and Comic_Copy. Based on counts, we have 48 more records in Comic than we do in Comic_Copy. Let’s find the differences.

In Chad’s specific query, NOT EXISTS works great. Where I like EXCEPT is when you need to see if any of the non-key columns differ. For example, if you also needed to compare titles for rows with the same ID and ensure those titles matched.

Comments closed

The Value (and Cost) of DATETRUNC

Published 2022-12-21 by Kevin Feasel

Brent Ozar points out the ups and downs of DATETRUNC():

The first one, passing in a specific start & end date, gets the best plan, runs the most quickly, and does the least logical reads (4,299.) It’s a winner by every possible measure except ease of writing the query. When SQL Server is handed a specific start date, it can seek to that specific part of the index, and read only the rows that matched.

DATETRUNC and YEAR both produce much less efficient plans. They scan the entire index (19,918 pages), reading every single row in the table, and run the function against every row, burning more CPU.

SQL Server’s thought process is, and has always been, “I have no idea what’s the first date that would produce YEAR(2017). There’s just no way I could possibly guess that. I might as well read every date since the dawn of time.”

Read on for the upshot.

Comments closed

Bit Twiddling in T-SQL

Published 2022-12-05 by Kevin Feasel

Louis Davidson explains how bit operations work in T-SQL:

I expect that 99% of the people reading this looks at this probably would expect there to be a status table that contained the values of status. Seeing that this is a base 2 number, you may be in that 1% that thinks this might be a bitmask. but unless you have and eidetic memory, you probably don’t know what all of the bits mean.

A bitmask is a type of denormalization of values where instead of having a set of columns that have on or off values (no Null values), you encode it like:

Bitmasks make me break out the angry nun ruler. You can almost guarantee you’re doing something wrong if you design a bitmask as a column in a table.

Comments closed

GENERATE_SERIES and Data Types

Published 2022-11-23 by Kevin Feasel

Bill Fellows runs into an issue:

Perfect, now I have a row for each second from midnight to approximately 5.5 hours later. What if my duration need to vary because I’m going to compute these ranges for a number of different scenarios? I should make that 19565 into a variable and let’s overengineer this by making it a bigint.

Things don’t work out quite the way you might have expected there. Read on and see what Bill found and how you can circumvent the problem.

Comments closed

AT TIME ZONE Performance and an Alternative

Published 2022-11-14 by Kevin Feasel

Joe Obbish looks at time zones:

Queries that use the AT TIME ZONE operator may perform worse than desired. For example, Jonathan Kehayias blogged about performance struggles with AT TIME ZONE at sqlskills.com. One key difference between Jonathan’s opinion and my own is that I enjoy writing these kinds of posts.

Read on for the details and check out some work Joe has done around time zone friendly functions as an alternative to AT TIME ZONE.

Comments closed