Use proper aliasing method in SQL Server

SQL Server is a wonderful relational database management system; it has increased little more productivity of developer by providing intelisense in SQL Server Management Studio (SSMS). If you are using SQL Server 2008+, you will get intelisense for column/table name which helps you to press some less key stroke to type down column/table name. SSMS saves you from typing complete name for column/table, it we atleast expect that alias should be in proper way by using “AS” keyword which takes only two keystroke. If you don’t apply “AS” while aliasing something, I would consider it as a bad practice as sometime it leads to big misunderstanding or an error and I have encountered one recently while supervising some of new SPs written by Jr. Developer.

Let us see one small example to prove which kind of misunderstanding or error can happens by not writing very short keyword “AS” while applying column/table alias.

We will create following temporary table for this article.

[sourcecode language=”sql”]IF OBJECT_ID(‘tempdb..#OrderDetails’) IS NOT NULL
DROP TABLE #OrderDetails

CREATE TABLE #OrderDetails(
ID INT IDENTITY(1,1)
,ClientID VARCHAR(10)
,TestName VARCHAR(20)
,TotalSample INT
)

INSERT INTO #OrderDetails
SELECT ‘CHEM02′,’VOCMS GROUP 1’,4 UNION ALL
SELECT ‘CHEM02′,’SVOC GROUP 1’,6 UNION ALL
SELECT ‘CHEM02′,’SVOC STARS’,12 UNION ALL
SELECT ‘CHEM02′,’PESTICIDE’,4 UNION ALL
SELECT ‘SHAW01′,’PESTICIDE’,10 UNION ALL
SELECT ‘SHAW01′,’SVOC STARS’,3 UNION ALL
SELECT ‘SHAW01′,’SVOC GROUP 1’,9 UNION ALL
SELECT ‘EPAW01′,’SVOC GROUP 1’,14[/sourcecode]

Once we will create this temporary table, let us create one simple SELECT query.

[sourcecode language=”sql”]SELECT
ID OrderID
,ClientID CID
,TestName
,TotalSample
FROM #OrderDetails[/sourcecode]

I would consider above query written practice as bad practice because it has not used “AS” keyword as while giving alias to first two column of SELECT query. Here is the result set of above query:

Let us execute one more select query.

[sourcecode language=”sql”]SELECT
ID OrderID
,ClientID CID
,TestName
TotalSample
FROM #OrderDetails
[/sourcecode]

This query is again bad query as there is no “AS” keyword. There is one small mistake in SELECT statement. Can you find it without reading further or executing the query???????

Don’t cheat and write comment in this article whether you have actually found that small mistake without executing query or reading further?

Above SELECT query is almost (not 100%) same as earlier SELECT query though this SELECT query will be successfully executed without an error but it will return only three column and not four column. Here is the screen capture of above SELECT query.

If you run this SELECT query, it will run and return three columns. If you see third column header, it is “TotalSample” rather “TestName”. We forgot “,” (comma) between two column and SQL Server considered “TotalSample” as an alias of column “TestName” which is wrong, isn’t it? Have you identified the mistake without running second SELECT query?

Now, I will run third SELECT query as follows:

[sourcecode language=”sql”]SELECT
ID AS OrderID
,ClientID AS CID
,TestName AS Test
,TotalSample AS SampleForTest
FROM #OrderDetails
[/sourcecode]

This is I call a perfect query, it uses proper alias with “AS” keyword. Here is the screen capture of third attempt:

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Use proper aliasing method in SQL Server”

Comments are closed.