TSQL Tuesday #20: T-SQL Best Practices

Once a month, on the 2nd Tuesday, the SQL Server online community has a virtual party of sorts called T-SQL Tuesday.  T-SQL Tuesday was started by Adam Mechanic (blog|twitter) back in 2009 and has been going strong ever since.  This month, I decided to crash the party with a submission of my own.  The topic-du-jour is T-SQL Best Practices, hosted by Amit Banerjee (blog|twitter).

I’ll keep this short and sweet.

Wherever possible don’t use functions in the where clause.  Please.  Pretty please?  Why?  because it affects the way SQL Server optimizes the query and in most cases prevents proper usage of indexes.  I’ll show you what I mean.

USE AdventureWorks;
GO
CREATE INDEX IX_Contact_LastName ON Person.Contact (LastName);
GO

-- sad dba
SELECT LastName FROM Person.Contact
WHERE LEFT(LastName,3) = 'Alb'
GO

-- happy dba
SELECT LastName FROM Person.Contact
WHERE LastName like 'Alb%'
GO

Two perfectly legitimate queries, returning the same data. But take a look at the execution plans for those queries.

Even though, logically speaking, these queries are doing the same thing, the LEFT function on the first query is preventing the optimizer from using an index seek operation.  Instead, it’s scanning the entire index (much like a full table scan).  This isn’t a huge problem on such a small example, but believe me, it becomes a ginormous problem on larger tables and more complex queries.

So please, whenever possible, avoid this pitfall.  Your DBA will thank you.

Also recommended: