//
you're reading...
SQL Server, T-SQL

T-SQL performance anti-patterns. Part 1 – Data Types

What you don’t know could hurt your query performance.

I will present a seven part series on the most common T-SQL performance anti-patterns. Part 1 – Data Types, Part 2 – Using Functions, Part 3 – Multi-Statement UDFs, Part 4 – Dirty Reads, Part 5 – Query Hints, Part 6 – The Dreaded Cursor, Part 7- Nested Views

Using the wrong data types

  • Use the data type that is in your database. Use it in your parameters and in your variables. Avoid SQL Server implicit conversions. Implicit conversions, or you have to put in explicit conversions, result in performing an additional function on your columns. Performing functions on any of your filtering columns (WHERE clause or JOIN criteria) you’re looking at generating table scans negating your indexes. Lets say you perform a CAST on the column in order to compare it to a character type then any index on that column will NOT be used.

    Look at the following:

    Implicit Conversion – Results in index scan

    SELECT e.BusinessEntityID,

    e.NationalIDNumber

    FROM HumanResources.Employee AS e

    WHERE e.NationalIDNumber = 112457891;

    Correct Data Type (No Implicit Conversion) – Results in index seek

    SELECT e.BusinessEntityID,

    e.NationalIDNumber

    FROM HumanResources.Employee AS e

    WHERE e.NationalIDNumber = ‘112457891’;

    ….Use the correct data types !

  • Advertisements

    About ldgaller

    Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

    Discussion

    No comments yet.

    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: