Press "Enter" to skip to content

Category: T-SQL

Finding Object Dependencies in SQL Server

Published 2024-05-01 by Kevin Feasel

Andy Brownsword looks for references:

When looking to migrate, consolidate or deprovision parts of a SQL solution it’s key to understand the dependencies on the objects inside.

Identifying dependencies can be challenging and I wanted to demonstrate one way to approach this. We’ll start with some objects across a couple of databases:

Read on for a pair of queries that get you on the way. Reference detection is surprisingly difficult in SQL Server, especially if you have cross-server queries. Even cross-database queries may not work the way you expect.

Another option is to use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities. I wrote a blog post on the topic a long while back and included some of the caveats around these two DMFs.

1 Comment

Writing Conditional JOIN and WHERE Clauses

Published 2024-04-26 by Kevin Feasel

Erik Darling has Blondie stuck in my head:

The OR operator is a perfectly valid one to use in SQL statements. If you use an IN clause, there’s a reasonable chance that the optimizer will convert it to a series of OR statements.

For example, IN(1, 2, 3) could end up being = 1 OR = 2 OR = 3 without you doing a darn thing. Optimizers are funny like that. Funny little bunnies.

The problem generally isn’t when asking for IN or OR for a single column, with a list of literal values, the problem is usually when you:

  • Use OR across multiple where clause columns
  • Use OR in a join clause of any variety
  • Use OR to handle NULL parameters or variables

This is an excellent, detailed article and well worth the read.

Comments closed

Troubleshooting sp_getapplock Blocking

Published 2024-04-25 by Kevin Feasel

Brent Ozar has an epiphany:

I’ll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense:

  • sp_BlitzFirst @SinceStartup = 1 showed very clearly that their top wait, by a long shot, was blocking. Hundreds of hours of it in a week.
  • sp_BlitzIndex showed the “Aggressive Indexes” warning on a single table, but… only tens of minutes of locking, nowhere near the level the database was seeing overall.
  • sp_BlitzCache @SortOrder = ‘duration’ showed a couple queries with the “Long Running, Low CPU” warning, and they did indeed have blocking involved, but … pretty minor stuff. Plus, their plan cache was nearly useless due to a ton of unparameterized queries pouring through constantly, overwhelming Azure SQL DB’s limited plan cache.
  • sp_Blitz wasn’t reporting any deadlocks, either. (sp_BlitzLock doesn’t work in Azure SQL DB at the moment because Microsoft’s no longer running the default system health XE session up there. They turned that off in order to save money on hosting costs, and passed the savings on to… wait… hmm)
  • As a last-ditch hail-Mary, I ran sp_BlitzWho repeatedly, trying to catch the blocking happening in action. No dice – the odds that I’d catch it live weren’t great anyway.

Click through for the story and how all the pieces ultimately fit together.

Comments closed

Searching for a String Position

Published 2024-04-22 by Kevin Feasel

Chad Callihan goes index hunting:

SQL Server has the CHARINDEX() and PATINDEX() functions that can both be used to find the position of a value in a string. They each have some differences that make them unique and more fitting for specific situations.

Let’s look at a few examples of how each can be used.

Read on to see how to use CHARINDEX() and PATINDEX() on two statements that are total falsehoods: Kevin Conroy always will be the number 1 and best Batman.

Comments closed

The Equivalency of Views and Common Table Expressions

Published 2024-04-19 by Kevin Feasel

Erik Darling makes a comparison:

Perhaps one of the most exhausting parts of my job is disabusing developers of the notion that common table expressions hold some weight in gold over any other abstraction layer in SQL Server.

Think of it like this:

  • Views are like a permanent home
  • Common table expressions are like a mobile home

You can put equally terrible queries in either one and expect equally terrible results.

Read the whole thing for additional spicy analogies and similes.

I would say that I certainly do not disdain views, so much as I see them as a yellow flag. It’s really easy to go from helpful views to views nested in views nested in views like malevolent Matryoshka dolls. It’s harder to do that with common table expressions before that reptilian part of your brain kicks in and says that “hey, maybe this isn’t the greatest idea I’ve ever had.” Not that this will stop some people, admittedly…

Comments closed

Data Analysis with Window Functions

Published 2024-04-18 by Kevin Feasel

Erika Balla looks out the window:

Window functions are an advanced feature of SQL that provides powerful tools for detailed data analysis and manipulation without grouping data into single output rows, which is common in aggregate functions. These functions operate on a set of rows and return a value for each row based on the calculation against the set.

In this article, we delve into window functions in SQL Server. You will learn how to apply various window functions, including moving averages, ranking, and cumulative sums, to achieve comprehensive analytics on data sets. 

Click through for several examples.

Comments closed

Snake Draft Ordering in SQL Server

Published 2024-04-15 by Kevin Feasel

Aaron Bertrand demands order:

I play fantasy football. To start each season, there is a draft, where players are randomly assigned draft order. We’ve started some seasons with a “snake” draft, to make the selection of players most fair and balanced. A snake draft works like this (a simple example with four players):

  • First pick in the first round picks last in the second round.
  • Second pick in the first round picks third in the second round.
  • Third pick in the first round picks second in the second round.
  • Last pick in the first round picks first in the second round.
  • Then, in the third round, it reverts to the original order: The first pick from the first round picks first again (giving them two picks back-to-back).

Read on to see how to implement this in T-SQL. I’m happy to report that I anticipated the answer, though Aaron notes a couple of issues that I didn’t catch in my “compile the code in my head” approach.

Comments closed

Returning a Row when there’s No Row to Return

Published 2024-04-11 by Kevin Feasel

Erik Darling has an existential dillema:

Rather selfishly, I do this for my stored procedures, for all the reasons in the first sentence. Especially when debugging stored procedures, you’ll want to know where things potentially went wrong.

In this post, I’m going to walk through a couple different ways that I use to do this. One when you’re storing intermediate results in a temporary object, and one when you’re just using a single query.

Read on for an example of how to do this.

Comments closed

A Review of Powerops

Published 2024-04-05 by Kevin Feasel

Nikola Ilic reviews a new tool:

In the last 2-3 months, I had an opportunity to try and test the new external tool, called Powerops. This is an external tool developed by Truviz, the company you might already know as the creators of various Power BI “goodies”.

Now, you are probably expecting “another Power BI external tool that solves a specific challenge”…I’ll tell you straight away – you can’t be more wrong! Powerops is a mix of various existing external tools – think of DAX Studio, Tabular Editor, ALM Toolkit, Measure Killer, etc. – but it’s also more than a simple sum of the features of the aforementioned tools.

Read on for the full review. Pricing is free for the first two months, and then either buy a license or you can upload up to two files per month and remain in the free tier.

Comments closed

Finding Row Counts in SQL Server

Published 2024-04-03 by Kevin Feasel

Kevin Wilkie breaks out the abacus:

Today, I was working with SQL Server to get row counts from several tables so I thought I’d be smart and work with some functions in SQL Server to make it smarter / easier.

Now, if I am truly only getting “straight” row counts from these tables, I would be able to create a query like the below that would provide the answers with no problem:

Read on for the normal approach, as well as a more complicated approach made necessary due to some business logic requirements.

Comments closed