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.
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.