Column Aliases – Why bother?

Column aliases are great because you can make seemingly meaningless column names become more readable and also obfuscate the underlying database structure. They are not mandatory but did you know this…

An alias can result in a logical error

A logical error is where you get the wrong response from the database or application but you don’t get a syntax error that is caught by an error handler. This is particularly problematic in database developement as the error will cause data corruption without warning and this can happen very fast.

example:

Take the following select statement. The aliases are obvious (if a little pointless)

SELECT
    COL1 as a
   ,COL2 as b
   ,COL3 as c
FROM dbo.table1

the keyword AS is not mandatory so you could write this as

SELECT
  COL1 a
 ,COL2 b
 ,COL3 c
FROM dbo.table1

This is dangerous and demonstrates the reason why you should always format your code to make it more readable. Take this revised unformatted code without aliases. The brevity of the statement should make the error easier to spot

SELECT Col1 Col2,Col3 FROM dbo.table1

Heres the output

Col2        Col3
----------- -----------

Note that there are only two columns returned due to the missing comma. What is less obvious is that Col2 is actually the data from Col1

If we use a column alias on all the columns we will avoid this as a missing comma would produce an error but also we could just format the code to make the missing comma easier to spot. Using the AS keyword may not be mandatory but it is advisable for ease of reading and also because it may become mandatory in the future.

The following produces an error…

SELECT
    COL1 as a
    COL2 as b
   ,COL3 as c
FROM dbo.table1

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Col2'.

Making it much easier to spot the error even without the AS keyword

SELECT
    COL1 a
    COL2 b
   ,COL3 c
FROM dbo.table1

I like aliases and I use them often and never come across this issue in my own code because I am painstakingly adamant that all code should be formatted correctly.

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in SQL Stuff and tagged , . Bookmark the permalink.

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