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.

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

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

SELECT
 ID OrderID
 ,ClientID CID
 ,TestName
 ,TotalSample
 FROM #OrderDetails

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.

SELECT
 ID OrderID
 ,ClientID CID
 ,TestName
 TotalSample
 FROM #OrderDetails
 

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:

SELECT
 ID AS OrderID
 ,ClientID AS CID
 ,TestName AS Test
 ,TotalSample AS SampleForTest
 FROM #OrderDetails
 

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.

One thought on “Use proper aliasing method in SQL Server

Comments are closed.