This one is quite subjective, and I'm sure I will face plenty of
opposition - not only because it's a preference thing and many people
are married to their preferences, but also because it violates the
strict interpretation of the standard. Personally, I'm more worried
about the former than the latter - I have no concerns whatsoever that
SQL Server will eradicate the = notation for column aliases, nor do I
worry that the code I write needs to work when ported to Oracle, DB2,
MySQL, etc. (I highly doubt this will be the biggest problem in such an
event). If these concerns are important to you, you may want to stop
reading.
To be honest, there are four six different ways you can define the alias for a column in a SELECT statement - the alias being the column name the application or API sees when the result is returned from SQL Server. The methods I've seen used are:
SELECT 1 AS x; -- #1
SELECT x = 1; -- #2
SELECT 'x' = 1 ; -- #3
SELECT 1 x; -- #4
SELECT 1 AS 'x'; -- #5
SELECT 1 'x'; -- #6
|
(I've ignored [alias] and "alias" variations for brevity.)
But I really want to discuss only two of these methods.
First, I want to discard item #3 entirely (which I've edited thanks to techvslife's comments). Why? Because using string literals as column aliases has been deprecated for some time now. You can see how prevalent this is in your environment with the following query:
SELECT [object_name], instance_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Deprecated Features%'
AND instance_name LIKE 'String literals as column aliases%'; |
I'd also like to discard #4, simply because I find it completely unreadable. Without using either = or AS, it makes it very hard to interpret if that is intended to be an alias, or if x is actually a column name and the author simply forgot a comma. I feel the same way about leaving out AS to denote a table alias, or WITH to denote a table hint (maybe I'll treat those in another post).
And #5 and #6 I'm going to ignore because they're the same as #1 and #4 but with single quotes around the alias. Personally I find the single quotes around aliases to be distracting, making the select list tougher to read no matter which aliasing convention you use. All alias names are strings; why would I want to make it look like it is intended to be data? Also note that #5 and #6 are not defined in any standard, they just seem to be allowed into the syntax (I haven't studied the standards lately but I don't believe these forms are documented).
So that leaves #1 and #2.
I prefer #2 simply for readability reasons. Remember that even in cases where you're "the database guy," the T-SQL code you write is not consumed solely by you - it is also consumed by application developers who want to understand your query (and in a lot of cases, the priority is understanding the shape of the result set); it will also be consumed by your successors, should you move on to a different project, a different company, or worse. Let's take a look at a very simple example of a query against the Sales.SalesOrderHeader table in AdventureWorks2008R2. I've added some new columns using calculations, and you can see how different it can be for someone trying to read the code and determine what the column names are. On the left pay attention to how your eyes have to move all over the script to locate the alias names that have been provided; on the right, it is a much simpler scan down the left hand side. (Click for larger.)
Now, this all depends on whether you are already following somewhat
logical coding conventions in the first place. If your SELECT list looks
like stream of consciousness from James Joyce, which I see more often than I'd like, then whether you use AS or = isn't
going to matter at all. This is with word wrap turned on in the T-SQL editor, and is much worse with word wrap turned off.
Raise your hand if you've seen this kind of code (or have written such a mess yourself). I suggest always writing code with word wrap turned off - in T-SQL at least, there aren't
many cases where a line *needs* to go off-screen, and having word wrap
turned off will protect you from doing it inadvertently.
In SQL Server 2012, there are easier ways of determining the shape of a resultset from a stored procedure, as long as it is the *first* resultset; in this case, it doesn't matter how ugly your queries are, you never have to look at them to determine the names of columns, their data types, or the order they are returned:
SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(N'dbo.UsingAS'), NULL)
ORDER BY column_ordinal;
|
Results:
That doesn't mean you should stop paying attention to formatting once you've upgraded to SQL Server 2012; your code should still be tidy and readable, this just gives you a quick way to examine result sets from code written by less motivated people, without having to refactor the code first (which is what I often end up having to do when I am trying to troubleshoot unreadable code).
If I haven't convinced you to use = for column aliases, I hope I've at least convinced you to use AS if you're currently using method #3 or #4 above.