TOP Tricks (T-SQL Tuesday #25)

T-SQL Tuesday is here again, brought to us this month by Allen White (blog|@SQLRunr). I’ve read Allen’s blog many times for PowerShell advice, but today he’s asking for your favorite T-SQL tricks.

I’m not sure if this qualifies as a “trick” per se since it’s pretty simple and well documented. But it has come up several times in the past few weeks during code reviews, so I am sure that it’s something that not everyone is familiar with.

It seems like most T-SQL developers know that the TOP keyword can be used to SELECT the TOP n number of records. I use it all the time when trying to get a quick look at the data, and SSMS has it as a built in menu option.

Selecting the first 100 records of a table is easy.

 
SELECT TOP 100 *
FROM Sales.Customer;

So what if you don’t want 100 records, but instead you want to dynamically specify the number of rows to be returned? Often, developers will try to do something like this is a stored procedure:

DECLARE @NumRows int = 10;
SELECT TOP @NumRows * FROM Sales.Customer;

This results in an error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘@NumRows’.

An accurate but not very helpful error message.

From here, many developers will turn to dynamic SQL to solve the problem. While dynamic SQL is perfectly appropriate in many situations, it’s not really needed here at all. The sad part is that the developer was almost there with the first attempt, they were just missing one little thing – the parentheses.

DECLARE @NumRows int = 10;
SELECT TOP (@NumRows) * FROM Sales.Customer;

The TOP keyword can also be used with other DML statements such as INSERT, UPDATE and DELETE. In those cases the parentheses are required, even if you specify only a number. The parentheses should be there when you SELECT a TOP number as well, but they are not required for reasons of backwards compatibility and in my experience, no one uses them. More info here.

In fact, in Management Studio, when you right click on a table and choose SELECT TOP 1000 Rows…, the query SSMS produces has a TOP 1000 without parentheses. And this is still true in SQL 2012 (at least in CTP 3).

One last note: if the TOP amount used as an input parameter varies wildly, you may run into problems due to parameter sniffing. A query with SELECT TOP 100000 records might look very different than a SELECT TOP 10, but the stored procedure will be compiled based on the first value passed in. There are ways to work around that, one of them being the full dynamic SQL solution.

Thanks for reading and happy Tuesday!

This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to TOP Tricks (T-SQL Tuesday #25)

  1. Pingback: Allen White : T-SQL Tuesday #25 Followup - Just in Time for the Holidays

  2. Rachel says:

    Thank you so much! I needed this exact solution only with a value in a temp table instead of a variable. Basically I needed to remove the top x records from my temp table, per sale, and this did the trick beautifully.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.