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:
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.