Press "Enter" to skip to content

Category: T-SQL

The Peril of Local Variables

Published 2020-04-01 by Kevin Feasel

Erik Darling dives into the tradeoffs you make when using local variables in stored procedures to avoid parameter sniffing:

In a stored procedure (and even in ad hoc queries or within dynamic SQL, like in the examples linked above), if you declare a variable within that code block and use it as a predicate later, you will get either a fixed guess for cardinality, or a less-confidence-inspiring guess than when the histogram is used.

The local variable effect discussed in the rest of this post produces the same behavior as the OPTIMIZE FOR UNKNOWN hint, or executing queries with sp_prepare. I have that emphasized here because I don’t want to keep qualifying it throughout the post.

This deserves a careful read-through.

Comments closed

Tracking Object Changes and Views

Published 2020-04-01 by Kevin Feasel

Ed Pollack has a solution for tracking when the underlying objects which make up a view change:

When a view’s underlying objects change, the view itself will not change. This can result in a view where the data types of columns, as well as nullability, precision, and scale can be reported inaccurately. When this happens, it is possible for queries against these columns to return errors, truncate data, perform poorly, or otherwise behave in unexpected ways.

This article will delve into views, how they are defined, and how T-SQL can be used to programmatically test the validity of views and ensure they never become stale.

Click through for an interesting article with plenty of code demos.

Comments closed

Using T-SQL to Generate a Login Migration Script

Published 2020-03-30 by Kevin Feasel

Thomas Rushton shows us how to migrate logins from one server to another using just T-SQL:

There are three things to look at:

1. Creating Logins from Windows accounts
2. Creating logins that are authenticated by SQL Server
3. Assigning membership of the appropriate server roles

Note that we’re just creating a snapshot of what’s there – we’re not aiming to keep these things in true synchronisation between servers. Note also that this script will only create accounts that don’t exist. If there’s an account already there with the same name, the script generated will not recreate it with new options.

We get the final script as well as a nice walkthrough of each component.

Comments closed

Avoid Default String Lengths

Published 2020-03-30 by Kevin Feasel

Kenneth Fisher warns us against creating strings without specifying a length:

Every now and again I see someone get lazy and declare a string (CHAR, NCHAR, VARCHAR and NVARCHAR) without specifically declaring what the length is going to be. This can lead to some interesting problems. First of all it’s usually going to be a length of one.

I’d take this one step further and say avoid creating strings without specifying a length in products like ADO.NET as well—there, you won’t get the single-character length Kenneth mentions here, but you do get one plan per character length passed in, which does a great job of bloating the plan cache.

Comments closed

Trimming Strings with T-SQL

Published 2020-03-18 by Kevin Feasel

Andy Levy saves us all several characters at a time:

Every now and then, we encounter data that needs to be cleaned up because it’s got leading and/or trailing spaces. Or maybe you’re storing short data in a CHAR(N) field, so when you query it, you’re getting trailing spaces. For time immemorial, we’ve had to wrap these fields in rtrim(ltrim(fieldname)) to do the deed.

Effective with SQL Server 2017, that’s no longer the case. 

The eight keystrokes add up over time. In all seriousness, I am happy that TRIM() is a thing in SQL Server 2017. And Andy gives us a little bonus to make it worth your refactoring while.

Comments closed

Quickly Finding Distinct Values with T-SQL

Published 2020-03-17 by Kevin Feasel

Paul White does some amazing things with T-SQL, news at 11:

I will be looking for distinct values in the BountyAmount column of the dbo.Votes table, presented in bounty amount order ascending. The Votes table has just under 53 million rows (52,928,720 to be exact). There are just 19 different bounty amounts, including NULL.

The Stack Overflow 2013 database comes without nonclustered indexes to minimize download time. There is a clustered primary key index on the Id column of the dbo.Votes table. It comes set to SQL Server 2008 compatibility (level 100), but we will start with a more modern setting of SQL Server 2017 (level 140):

Getting the query down from 10.5 seconds to 1ms is crazy.

Comments closed

Supporting Multiple NULL Values with a Unique Constraint

Published 2020-03-13 by Kevin Feasel

Itzik Ben-Gan walks us through a workaround in T-SQL:

Suppose that you have a database in a platform that supports the standard unique constraint and you need to migrate that database to SQL Server. You may face issues with the enforcement of unique constraints in SQL Server if the unique columns support NULLs. Data that was considered valid in the source system may be considered invalid in SQL Server. In the following sections I’ll explore a number of possible workarounds in SQL Server.

I use a simplified version of this as an interview question, so it’s nice to see an entire article from Itzik on the topic, including a couple solutions way outside the box.

Comments closed

Selecting From a Stored Procedure

Published 2020-03-13 by Kevin Feasel

Madhivanan shows us how to query the results of a stored procedure:

One of my friends asked me “Is it possible to query Stored Procedure resultset like a table. ie select * from (EXEC Stored_procedure)?”

Well. Querying the resultset from the Stored Procedure like Table can be done using OPENROWSET function

This is a fairly novel approach to the problem. In the past, I’ve inserted the results of a stored procedure into a temp table, but you can only do that if the procedure itself doesn’t call INSERT INTO ... EXEC ....

Comments closed

From IDENTITY to Sequences

Published 2020-03-12 by Kevin Feasel

Andy Levy recommends checking out sequences:

The SEQUENCE object eliminates all of this. It gives you a simple way to just grab a new number and increment for the next caller. It’s very fast as there’s no visible table I/O, and it’s unaffected by rollbacks.

And it’s so much easier to use! You just ask for the next number in the sequence!

I like sequences, though the interesting thing is that 2020 me has created them a lot less frequently than 2012 me was sure I would. I’m glad they’re in the product, however.

One thing I should point out is that sequences are like identity columns in that you can have gaps due to user behavior, such as rolling back transactions. If you absolutely need gap-free sets of numbers, you’re back to Andy’s Method One, except everything has to be serializable and wrapped in explicit transactions.

Comments closed

LAST_VALUE() and Windows

Published 2020-03-03 by Kevin Feasel

Jeet Kainth explains the importance of specifying your window when using LAST_VALUE():

To return the actual last value, add the additional clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This ensures that for each row the LAST_VALUE() function looks at all rows; from the very first row to the very last row.

Click through for the example. Remember that the default is going to the current row, not the entire data set.

Comments closed