Cleveland DBA

SQL Server administration in Cleveland, OH

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.

Advertisements

July 12, 2011 - Posted by | T-SQL | , ,

2 Comments »

  1. –don’t use functions in the where clause

    I have seen definitely it’ll produce problem.

    Comment by Muthukkumaran Kaliyamoorthy | July 13, 2011 | Reply

  2. […] Colleen Morrow [Blog | Twitter] explains with an example why using functions in the predicate of a T-SQL query can be bad karma for your […]

    Pingback by T-SQL Tuesday #20 Wrap-up and a few smiles « TroubleshootingSQL | July 14, 2011 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: