First Time, Long Time
I’ve written about all of these separately in various places, so if you’ve been reading my blog(s) and Stack Exchange answers for a while, these may seem old news.
Of course, collecting them all in one place was inspired by another recent Q&A.
Let’s get going.
SELECT TOP ( 1 ) CONVERT(NVARCHAR(11), u.Id) FROM dbo.Users AS u;
Yep. I’d ignore this one all day long.
DECLARE @NumerUno SQL_VARIANT = '10000000'; SELECT * FROM dbo.Users AS u WHERE u.Reputation = @NumerUno;
That’s awfully presumptuous.
I don’t even have A index on Reputation, nevermind enough index to facilitate an entire Seek Plan.
I’ve seen this catch people off guard. They fix the implicit conversion, and expect an index seek.
CREATE INDEX tabs ON dbo.Comments(UserId); CREATE INDEX spaces ON dbo.Votes(UserId); SELECT TOP (1) * FROM dbo.Comments AS c JOIN dbo.Votes AS v ON v.UserId = c.UserId WHERE c.UserId = 22656;
The check for this happens at the join. There’s no further down-plan check on the index access operations.
If there were, it’d see this:
Only matching rows come out, anyway. The join predicate is, like, implied in the where clause.
Oh Um Sweatie No No No No No
CREATE INDEX handsomedevil ON dbo.Users(Reputation) WHERE Reputation > 1000000; SELECT COUNT(*) AS records FROM dbo.Users AS u WHERE u.Reputation > 1000000;
So the simple parameterization thing fires off a warning about a filtered index that we used not being used.
yep yep yep yep yep yep
First Of All, Ew.
This thing needs some boundaries. Maybe like available memory should figure in or something?
Call me, I have lots of great ideas.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.