Simple T-SQL Tips : Small things that matters

Hi everyone, I came across a SQL course online with so many advance topics that I found so challenging.

Nevertheless, it is the small things that any SQL developer do every day that really matters. I want to share with 3 quick killer SQL query mistakes that you can encounter every day in your work.

Tip number 1:

“Indexes do not work when data type conversion took place in your predicates”

If you have a table with column [PostalCode] that is string (i.e VARCHAR), then writing something like:

SELECT * FROM Table_Name

WHERE PostalCode = 1000

This is OMG what the heck you are doing! The type of PostalCode is VARCHAR, the type of (1000) is integer, then your predicate (PostalCode = 1000) has different data types and SQL has to do data type conversion in memory and will not use your index, in case you have index in PostalCode.

The right thing to do is:

SELECT * FROM Table_Name

WHERE PostalCode = ‘1000’

 Now both PostalCode and (1000) are strings and no type conversion is required. If you have index on PostalCode, it will be used.

Tip number 2:

“Your predicate or filter in WHERE statement, should not touch the column name side of the filter in order to use indexes”

The bad way:

SELECT * FROM Table_Name

WHERE RIGHT(FirstName,3) = ‘mr.’

The right way:

SELECT * FROM Table_Name

WHERE FirstName LIKE ‘mr.%’

FirstName is the column name, and you should not touch it, manipulate it or use scalar functions on it, or else no index will be applied. In other words, a table scan will happen which is so long and expensive.

So always avoid doing manipulation on the FirstName because this is your column infromation. Do not use UPPER(), Substring() or any other manipulation on it. Instead, do your manipulation on the other side of the statement.

Sometime you want to check if the year of birth is 2013 for example, so instead of writing WHERE YEAR(HireDate) = 2013, write something like WHERE HireDate >= 1/1/2013 AND HireDate < 1/1/2014

Tip number 3:

Use COALESCE() instead of  ISNULL().

COALESCE is a standard SQL  function while ISNULL() is a T-SQL function, so COALESCE is more standardized way to do stuff.

They both do the same stuff, if the first argument is NULL, then return the second argument. One different though is that COALESCE will do type and size conversion while ISNULL() will not.

Example:

 You have a table with title column that stores (Mr.) and (Miss.) for each person, and this column accepts NULLs. You designed the column to be CHAR(5). Now you want to view the content of this column, and you will use ISNULL to replace NULL values with ‘unknown’. So you would say:

SELECT ISNULL(Title,’Unknown’)

FROM TableName

The problem and surprise here is that ISNULL() will always return the size and type of the first argument which is CHAR(5), so if there is a NULL, it will return ‘Unkow’  instead of ‘Unkown’ .

Instead, you can say:

SELECT COALESCE(Title,’Unknown’)

FROM TableName

This was a quick post that I wanted to share because while the Database developers puts the right indexes, the developers sometimes screw things without knowing that.

By ammar hasayen Posted in SQL Tagged

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