Category: SQL Server

Local Disasters

I see this kind of pattern a lot in paging queries where people are doing everything in their power to avoid writing dynamic SQL for some reason.

It’s almost as if an entire internet worth of SQL Server knowledge and advice doesn’t exist when they’re writing these queries.

Quite something. Quite something indeed.

First, let’s get what doesn’t work out of the way.

DECLARE @order_by INT = 3

SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY CASE WHEN @order_by = 1 THEN p.Score
              WHEN @order_by = 2 THEN p.CreationDate 
              WHEN @order_by = 3 THEN p.Id 
              ELSE NULL 
         END;
GO

You can’t write this as a single case expression with mismatched data types.

It’ll work for the first two options, but not the third. We’ll get this error, even with a recompile hint:

Msg 8115, Level 16, State 2, Line 46
Arithmetic overflow error converting expression to data type datetime.

What Works But Still Stinks

Is when you break the options out into separate case expressions, like so:

DECLARE @order_by INT = 1

SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY CASE WHEN @order_by = 1 THEN p.Score ELSE NULL END,
         CASE WHEN @order_by = 2 THEN p.CreationDate ELSE NULL END,
         CASE WHEN @order_by = 3 THEN p.Id ELSE NULL END;
GO

This will work no matter which option we choose, but something rather disappointing happens when we choose option three.

Here’s the query plan. Before you read below, take a second to try to guess what it is.

What Stinks Even Though It Works

My issue with this plan is that we end up with a sort operator, even though we’re ordering by Id, which is the primary key and clustered index key, and we use that very same index. We technically have the data in order, but the index scan has False for the Ordered attribute, and the Sort operator shows a series of expressions.

The Sort of course goes away if we add a recompile hint, and the Scan now has True for the Ordered attribute.

DECLARE @order_by INT = 3

SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY CASE WHEN @order_by = 1 THEN p.Score ELSE NULL END,
         CASE WHEN @order_by = 2 THEN p.CreationDate ELSE NULL END,
         CASE WHEN @order_by = 3 THEN p.Id ELSE NULL END
OPTION(RECOMPILE);
GO 

You Shouldn’t Do This

Unless you’re fine with recompile hints, which I don’t blame you if you are.

SQL Server seems to get a whole lot more right when you use one, anyway.

My point though, is that adding uncertainty like this to your queries is more often than not harmful in the long term. Though this post is about local variables, the same thing would happen with parameters, for example:

DECLARE @order_by INT = 3
DECLARE @sql NVARCHAR(MAX) = N'
SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY CASE WHEN @order_by = 1 THEN p.Score ELSE NULL END,
         CASE WHEN @order_by = 2 THEN p.CreationDate ELSE NULL END,
         CASE WHEN @order_by = 3 THEN p.Id ELSE NULL END;
';

EXEC sys.sp_executesql @sql, N'@order_by INT', 1;
EXEC sys.sp_executesql @sql, N'@order_by INT', 3;
GO

The way to address it would be something like this:

DECLARE @order_by INT = 3
DECLARE @sql NVARCHAR(MAX) = N'
SELECT p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
ORDER BY ';

SET @sql += 
CASE WHEN @order_by = 1 THEN N'p.Score'
     WHEN @order_by = 2 THEN N'p.CreationDate'
     WHEN @order_by = 3 THEN N'p.Id' 
     ELSE N'' 
END;

EXEC sys.sp_executesql @sql
GO

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Number One

In case you missed it for some reason, check out this post of mine about local variables. Though it’s hard to imagine how you missed it, since it’s the single most important blog post ever written, even outside of SQL Server. It might even be more important than SQL Server. Time will tell.

While live streaming recently about paging queries, I thought that it might make an interesting post to see what happens when you use variables in places other than the where clause.

After several seconds of thinking about it, I decided that TOP would be a good enough place to muck around.

Unvariables

Let’s say you’ve got these two queries.

DECLARE @pagesize INT = 10000;
SELECT TOP (@pagesize) p.Id
FROM dbo.Posts AS p
ORDER BY p.Id;
GO 

DECLARE @pagesize INT = 10000;
SELECT TOP (@pagesize) p.Id
FROM dbo.Posts AS p
ORDER BY p.Id
OPTION(RECOMPILE);
GO

Without a RECOMPILE hint, you get a 100 row estimate for the local variable in a TOP.

Which of course might could lead to some problems if you were selecting way more than 100 rows via TOP.

Not Unexpectedly

You can manipulate what the optimizer thinks it’ll get with optimizer for hints:

DECLARE @pagesize INT = 10000;
SELECT TOP (@pagesize) p.Id
FROM dbo.Posts AS p
ORDER BY p.Id
OPTION(OPTIMIZE FOR(@pagesize = 1));
GO

And of course, when used as actual parameters, can be sniffed.

DECLARE @pagesize INT = 10000;
DECLARE @sql NVARCHAR(1000) = 
N'
  SELECT TOP (@pagesize) p.Id
  FROM dbo.Posts AS p
  ORDER BY p.Id;
'

EXEC sys.sp_executesql @sql, N'@pagesize INT', 1;
EXEC sys.sp_executesql @sql, N'@pagesize INT', 10000;
GO 

Got More?

In tomorrow’s post, I’ll look at how local variables can be weird in ORDER BY. If you’ve got other ideas, feel free to leave them here.

There’s not much more to say about WHERE or JOIN, I’m looking for more creative applications ?

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.