See the full index.
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 alias = expression notation, 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.
See the full index.
So Kevin, do you use ONLY ANSI code, everywhere? You never use any vendor-specific implementations, anywhere?
I agree with you that you should follow ANSI standards, but with the caveats that (a) they make sense in the first place and (b) there isn't a substantial benefit to doing it a different way. For me, the readability benefit far outweighs the likelihood that I'll ever have to port this code from SQL Server to a different type of system. And if I do, this is going to be the least of my worries.
Because in reality, how many times in your career have you had to take an entire database codebase and try to move it to a different platform? What portion of ANSI is really compatible with all RDBMS flavors anyway?
I prefer following ANSI standards. This article fails because this IS a bad habit, using non-standard proprietary extensions over standards that are portable.
Thanks for this article. I also prefer the = notation as it feels more logical and is more readable to me. My shop is very unlikely to ever move away from MS SQL. I have no good reason to stick to a standard for its own sake when it makes my job harder. How does one petition the standards body to include this in the next update?
Anyway, thank you for validating my position.
I agree that having the alias first then the definition of that alias (e.g., column concatenations/operations) is easier to read, but it isn't standards compliant and who knows what problems it could cause. If you're willing to use two lines for every column-selection result, you could do something like
SELECT
null
AS "FirstCol"
, "t1"."Col1"
AS "Alias1"
, "t1"."Col2"
AS "Alias2"
, "t1"."Col3" + "t1"."Col4" + "t1"."Col5" + "t1"."Col6"
AS "Alias3456"
, null
AS "LastCol"
FROM
"dbo.TABLE1"
AS "t1"
JOIN "dbo.TABLE2"
AS "t2" ON "t2"."Col1" = "t1"."Col1"
Most other programming languages also have the name of the variable to which an expression is assigned on the left, and the definition of that assignment on the right. So SQL standard isn't very standard compared to most languages…but Lisp allows things like calling a function and defining that function later in the code, so it has company. Also note that this is the "standard" within the MS Access query-builder for aliasing, and probably other query-builders as well (although the SQL they generate reverts to standard SQL with "as").
For easily generating decent looking SQL without having to figure out how many spaces to use, and for easier parsing/find/replace, just aligning the column definitions and aliases might be better. I also think that even though it makes the code less pretty, maybe always using object-identifiers is a good idea in case you ever have to change anything in the query and one fieldname is contained in part of another fieldname (e.g., if you query a table that contains fields CodeMod and Mod, Mod2, Mod3, Mod4, and then you want to change Mod to Mod1, a simple find/replace turns CodeMod into CodeMod1 also, not what you want; but always specifying them in the query as "CodeMod" and "Mod" easily avoids that problem).
So maybe this is the best and easiest for standards, easy-typing, readability, and maintainability:
SELECT
null
AS "FirstCol"
, "t1"."Col1"
AS "Alias1"
, "t1"."Col2"
AS "Alias2"
, "t1"."Col3" + "t1"."Col4" + "t1"."Col5" + "t1"."Col6"
AS "Alias3456"
, null
AS "LastCol"
FROM
"dbo"."TABLE1"
AS "t1"
JOIN
"dbo"."TABLE2"
AS "t2"
ON "t2"."Col1" = "t1"."Col1"
AND "t2"."Col2" = "t1"."Col1"
AS I read through the years of comments I can tell those who have worked in large multi-DBMS systems and those who have not.
Use AS people. Code-readability comes second to standards. This is just silly to argue over.
I liked your opinion; well thought out. I may switch some day in the future.
For my part, I mainly decided not to switch over because the potential improvement in readability isn't enough for me. There's just too much momentum/investment in the current method and not enough ROI (in my mind) for a switch. That's how it goes sometimes.
Also, when thinking through what this change would mean for me, I realized 2 minor things (that matter to me currently):
1. When I'm looking for column-names, I don't actually parse visually from left-to-right; I parse more right-to-left. I actually scan on the right end for " AS " and go from there (and when it's syntax-highlighted in SSMS's default of blue, it's even easier).
2. I don't actually spend much time caring what the column names are during maintenance. When I do, and I know the column name, I use [Ctrl]+F.
What are the chances of double quotes "" being deprecated in a future release of SQL-Server? As it is Standard I'm guessing this would never be deprecated?
True when expression is long, left is best
@brykneval That works well enough (I still don't like having to scan across to find where all the column names start), but only if all of your column names fall under a certain count. When you start having expressions, and expressions that can span multiple lines, you will soon see it is not so easy to have visual alignment anywhere other than at the left margin,
Normally I write like this, with Tab on highest of select column length
SELECT CustomerId
, CustomerName AS [Customer Name]
, CustomerDescription AS [Customer Description]
FROM Customer
But surely = is readable too with proper spacing
Wow, all this about a harmless bit of notation. I learnt TSQL out of a couple of books and have always used AS. The alternaive of using = was never even mentioned, but I can see the cosmetic advantage.
I agree with your point that it "is quite subjective" and yes you did receive plenty of opposition. I'm just very glad that experts like you are prepared to share your knowledge freely – amateurs like me are so appreciative of any help and tips whatsoever.
@robotik nonsense. SELECT your_favorite_band AS [1direction]; – not that you should *want* to have aliases that start with numbers, since it can cause problems for other code down the pipeline as well.
In SQL Server, you cannot use aliases starting with a numeric character unless you use single quotes around the alias. So if you want aliases starting with id's or dates, you just have to use single commas.
first things first!!!
I think it stems from the way most queries come to exist!
I believe that most people while writing SQL start by trying to express WHAT we have (existing column(s)) and then try to express HOW we can convert them into something that we believe we need. After they got that part they are forced to give it some name. So they don't care about it as long as they can get away with it. the fact that we need to give a name implies that there is valid question : Why we need a name? Because other query expects a particular name… because a data processing function expects a specific name… There is always WHY
It often depends the sequence of writing first query than processing code?
Anyway, I also favor '=' assignment style over the AS signment.
I find it easier to read. I scan column aliases and if/when i need to understand how one particular value came to be – zoom in to see how it was done.
I also see it the same way as i assign values to variables in pretty much all programming languages in existence. An not only to variables. We put namespaces class names, function names, associative array key names, book titles etc etc etc before definitions
Imagine a file :
<?
{
{
(!$arg1 && $arg1) ? 'Yes' : 'No' as $outcome;
}(true as $arg2, false as $arg1) as function importantMethod
} implementing Interface_A extending SomeBaseClass as SomeImportant class
php?>
or
create (
not null identity int id,
not null varchar(50) name
) as table backward
This is what AS signment feels to me – backward Yoda style.
I believe that this
Simon Sinek believes that 'why' always should come first and in his start with why ted talk he gives great example for this rule.
Name or Alias is to me an expression of purpose, of intent which has precedence of means. And when i need to understand working of particular SQL query, intent and purpose are more important.
So i put first things first. 🙂
Right on.
If (big IF) all you write are simple selects that do nothing but return named table columns or trivial expressions, it doesn't make much difference where you write something like
select
t.first_name + ' ' + t.last_name as "Name" ,
coalesce(t.addr1,") as "AddressLine1" ,
…
or
select
Name = t.first_name + ' ' + t.last_name ,
AddressLine1 = coalesce(t.addr1,") ,
…
Either way you can lay it out in a clean tabular manner that makes things clear.
However, once you start writing real-world queries with result set columns defined as long expressions (e.g. 100+ characters in length) expressions or correlated subqueries, the 'as alias' bit gets lost in the noise, not matter how you lay things out.
If someone who has never seen your SELECT statment can't look at it and pretty much instantly answer the questions, "How many columns are in the result set?" and "What are there names?", you're not doing your job as a programmer: writing code for the next guy — who might well be you, 3 years hence — who has to understand and modify the code, usually in a hurry.
Yes. I've been advocating this from the start of SQL Server all the way back in 4.x days. We write "SELECT @x = 3", but not "SELECT x = 3"? Parameters can't be passed as "EXEC myproc 3 as @x", and nobody in the universe assigns variables from left to right in any other programming language (Let 3 = x??).
Yes, it's not ANSI standard, but I've written SQL conversion scripts that can flip those guys around – they are very easy to parse out – if the day really comes, and honestly we marry ourselves so deeply to the flavor of SQL that you'd be better off just going out and getting a new job delivering pizza than trying to port a sophisticated SQL Server app to Oracle. Sheesh.
So, Yay!
Sorry to say but I disagree strongly, I much prefer AS to define a column alias, because (1) it is ANSI standard and (2) '=' carries the wrong semantics .
'=' usually means a boolean logic test, or an assignment statement.
So,
SELECT tabename.colname AS anothername
is my preference, no matter where the SQL is stored.
Have a nice day! 🙂
David, disagree with who? Pretty sure I advocate the method you're talking about (except I wouldn't use 'alias' with single quotes around it – this is deprecated and makes it look like a string literal).
Sorry. Disagree. When I have a list of 8-10 columns, all aliased, it is MUCH easy to see what they are when using "=". This is especially true when columns have formatting or other manipulations:
'dte' = convert(varchar(10),datecreated,12)
is much easier to read than than the other options.
Jason, it's an opinion, and I can't please everyone. But while you get hung up on standards, ask yourself if you ever use GETDATE(), identity columns, DDL triggers, filtered indexes, ISNULL, etc. These are cases where we go outside of the standard for convenience or performance. Until alias = expression syntax is deprecated it's going to be what I use because of the reasons I mentioned above. I favor long-term readability over the "awful" stumbling block this might cause a new SQL Server user for the first two and a half minutes of their experience with SQL Server.
This is awful. Not only do you confuse the use of "" (double quotes for identifiers) and " (single quotes for string literals), you call it a "bad habit to kick" to try to write standard SQL.
Have you ever considered that writing standard SQL would actually improve readability? Others who know the standard would immediately understand what you are attempting to do. Others who know Oracle or Postgres or several other DBMSes would immediately understand your SQL. Others trying to learn SQL would know the way to create an alias that works in the majority of DBMSes in use today. Yet, you call it a "bad habit"… 🙁
Thankfully, the Microsoft documentation states "The AS clause is the syntax defined in the SQL-92 standard for assigning a name to a result set column. This is the preferred syntax to use in Microsoft® SQL Server™." http://technet.microsoft.com/en-us/library/aa196243(v=SQL.80).aspx
Isn't = already overloaded anyhow? It's used as an assignment operator and a comparison operator.
So true…all of it. You changed my mind about =.
done (though given the flood of connect filings, I wonder if anyone reads them all, let alone acts on them. Actually, my advice for them would be to concentrate on the most heavily voted items, and items filed by you and other wise men, supermvps, sql gods, or whatever the term is. As it is, it took them a long time to fully implement ben-gan's windowing functions, and we're still waiting on Sommarskog's read/write table valued parameters with 226 yes votes, 3 no votes. In general they've been moving slower than they used to.
http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/299296/relax-restriction-that-table-parameters-must-be-readonly-when-sps-call-each-other
)
You could probably add a comment to this Connect item about the different topics. I only highlighted one were it implies that short-circuiting can be relied upon:
http://web.archive.org/web/*/http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/690017/case-coalesce-wont-always-evaluate-in-textual-order
Thanks for checking that, they need to clean up these things in BOL. I just noticed that, not just SELECT, but the "CASE" help topic also uses single quote aliases — after noticing that it still fails to warn that CASE ordering (short-circuiting) is NOT guaranteed when using aggregates. Can you unofficially advise the officials? Accurate docs are the cheapest way to cut back on support calls, and they cut back on my pain also.
'Price Range' =
CASE
WHEN ListPrice = 0 THEN 'Mfg item – not for resale'
etc.
@techvslife, I've been officially advised to recommend against any form of alias names in 'single quotes' regardless of what the deprecation rules or documentation *currently* state.
fwiw, added help correction to connect:
http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/724381/use-of-single-quotation-marks-as-column-identifier-in-help
I agree. You may want to note additionally that the most common reason for using string literals (spaces, initial numbers, etc in column names) is covered by using the SQL quoted identifier, the double-quotation mark (or the proprietary form, brackets). (MS should change the documentation for SELECT to use double-quote marks or brackets.)
The reason btw for NOT using string literals as an identifier is the confusion that may result from super overloading (e.g. SELECT 'A' 'B'; where 'B' is–an alias!).
So Microsoft's SELECT help should definitely be using one of these instead:
SELECT 'A' as "My Col";
SELECT "My Col"='A';
SELECT 'A' AS [7];
SELECT [7]='A';
@techvslife, thanks for the correction. I will admit I have based my assumptions on the wording, and not the example, since the deprecation was first announced (I believe before 2005 was released).
Still, I do not believe format #2 is at risk. In fact I think the other string literal formats are more at risk, if for no other reason than the backward compatibility issues raised since format #2 is in extremely widespread use.
But "at risk" is pretty relative anyway – since there is no deprecation warning about any of these now, and even if a deprecation warning comes in the *next* release after SQL Server 2012, we're talking ~SQL Server 2024 before the deprecation final support kicks in.
As further confirmation, see Microsoft support here:
http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/125027/unclear-deprecation-of-column-alias-in-select-list
Also see the "REPLACEMENT" column (i.e. not deprecated syntax) on the deprecated features page. But the name chosen for the deprecated feature is too broad.
Aaron,
Correction: Your format #3 (col 'my alias') has NOT been deprecated (and in fact is used in the sql server 2012 help as the main example of aliasing under "SELECT").
What is deprecated is 'my alias'=col. Wouldn't that suggest that #2 format may be at risk (although not yet deprecated) since it is no longer an equal?
http://msdn.microsoft.com/en-us/library/ms143729(v=sql.110).aspx
@Marc, I think aliasing a table without AS makes it slightly less readable. And if you have folks in your shop that are still writing old-style ANSI joins (FROM a, b, c) then, like the "column alias" notation, you may find yourself wondering if that second reference is a table/view or a alias. I've seen this happen with nolock a lot – people will put FROM table nolock and not realize that nolock has merely become an alias. If they used AS and WITH consistently, there'd be absolutely no question. Those two extra keystrokes may be a lot of work, but they're worth it IMHO.
Bobbin along: In my experience, usually – once it is written – the most frequent access to a stored procedure is reviewing the column list so you understand what comes out of it. This is for application / API developers that need to consume the stored procedure as is, or stored procedure authors verifying whether they need to write another procedure or modify the existing one if, say, a few columns are not there.
Sure, the expression is important too, but this usually only comes about when troubleshooting or implementing specification changes for a specific output column (e.g. maybe the formula changes due to tax rate updates), not during the normal development lifecycle. Even then, seeing the alias first makes it easier to get to the expression you're interested in. Again, usually. Maybe your shop works differently than all the shops I've worked with.
Like I said at the beginning of the article, I don't expect to change your mind, and I understand that this is quite subjective. But other than the case where you need to write code that works across platforms (and ignoring for the moment that it is practically impossible to do so), I'm not seeing any strong arguments the other way. For example, I don't understand in what circumstance you need to know the expression first – does that also imply that you sometimes don't use aliases at all because they're not important? I also don't buy the overload argument – AS is already more overloaded than = and column aliasing is really not that much different from assigning a value to a variable (or setting a variable equal to another variable). And finally, I don't believe for a second that anyone here finds it more readable to see the expression run on across the screen before the alias is shown.
I don't know what the rest of you are doing that requires seeing the alias as the primary concern. For me, when reading a SELECT statement I want to know first of all what is going on in the column expression then if there is an alias tied to it. Aliases are for output, they don't do any work and do not, IMO, warrant the status of first place. To get the point accross, the [alias]=[column] over overloads the '=' sign. I would in that case prefer a non-operator like ':' i.e. MyAlias: @var = column operation. But then we'd have to do away with those pesky standards and the confuscation would grow ever greater.
p.s. This won't work of course, but for readability & logic, I would have argued for something like e.g.:
FROM
tblA JOIN
tblB=(SELECT x, y, z=CAST(x as int))
ON tblB.x=tblA.x JOIN
tblC ON tblC.x=tblB.x
SELECT
xy = tblA.x + tblB.y,
z = tblB.z,
x = CAST(tblC.x AS float)
etc.
The overload argument against "=" is not good as Bertrand points out; nothing could be more overloaded than 'AS'; the question is, could it confuse the compiler?–presumably it would have been deprecated if it could. I don't have a sense of whether "=" for aliasing will ever be deprecated, but I also use t-sql proprietary versions of update and delete all the time (UPDATE … FROM).
However, confusing colleagues etc. are other considerations.
Alias=val is a lot easier to read because of the alignment: the alias, i.e. shorthand, for an expression will of course generally be much shorter than the expression it aliases.
I also like putting table aliases on the left for the same reason:
FROM
tblA JOIN
(SELECT x, y, x) AS
tblB ON tblB.x=tblA.x JOIN
tblC etc.
An additional complaint with standard sql: it puts SELECT in the wrong logical processing order, which is a bit of a nuisance. FROM should come first.
(@celko, There are lots of arguments for sticking with the standard, and one is the power of habit. But habit also gets one used to things that are flawed, and the sql standard is not perfect (what could be).)
I just applied the alias=val construct to a big ugly 120 column query used to build CSV file to send to another party every month. These columns have names like "BLXRZQ" and "BLDGFKR", and most every month I spend hours on the phone and hacking the query trying to bend my round data-pegs to fit their square data-holes. Then they change their mind and need something moved somewhere else and the fun starts all over.
In this sort of situation ("Standards? We don't need no stinkin' standards") this alias=val construct really helps.
I only use = in a select clause if I'm assigning values to variables. So I always alias a column with AS, and normally use = for assignment. Just makes sense in my head. That way, when I'm looking at my code, I know that equals means assignment or boolean condition (in any language) and AS means alias.
Is it considered bad form to alias a table without AS? I always do Tablename Aliasename, with no As or =.
I have been trying for a few years to convince my team of the benefits of using = instead of AS. They are finally catching on, as project code is becoming more complex with our growing product.
Ian,
I like your UPDATE construction:
If I want to see what's going to happen I can quickly comment out the update & set and add a "select" like so
–UPDATE MyAlias
–set
select
col1 = 'abc',
col2 = 'def',
col3 = " –etc
from MyTable MyAlias
Our team takes it a step further to include an commented SELECT statement embedded in every UPDATE statement, to facilitate test execution:
UPDATE t SET
–SELECT t.col1, t.col2, t.col3,
col1 = 'abc'
, col2 = 'def'
, col3 = 'ghi'
FROM MyTable AS t
I prefer AS for aliasing because I think there is a semantic difference between = and AS. = implies an assignment–copying a value into a new place, while AS implies an alias, show me <col> AS <alias>. LastName = m.surname looks like you should be able to manipulate LastName and m.surname independently.
I also agree with the overloading arguement to some extent, adding a Non-Scalar meaning for = makes it more mentally taxing to process the code because assignments and aliases are not the same thing but can be done in the same places (unlike the comparison = which requires a keyword nearby).
Yes, AS is overloaded in the CAST() expression but that is an argument for changing CAST, not for adding a new meaning to =.
—
JimFive
…ok, so "public function A" isn't valid C# code 😉
So to sum up, we have two points so far:
1) Standards
2) Readability
Standards
If that is the concern of your shop, then you have your decision.
Readability
I doubt there will ever be a consensus on "Readability" in any language.
I really don’t find the query below readable:
SELECT
A
, B
When word wrapping in English, commas never end up at the beginning of the line. I believe this may be because commas aren’t as important as the actual text. I prefer
SELECT
A,
B
I also prefer my C# code not to have opening braces on a line by itself, but instead:
public function A {
Code Here;
}
But Visual Studio has preferences so each C# coder can be happy with his results.
What are the chances of the majority agreeing with me on these points? Pretty slim.
I think consistency may be more important than "readability".
When we read a novel, it takes a few pages to get used to the writer's way of putting his words together. That's his "style", what he determined as "readable". After we get used to it, we can then enjoy the rest of the novel.
Same with our code…if we're consistent, it can quickly become readable to someone behind us.
Thanks for the intersting point of view. I used to use the "column 'alias'" method, then switched to using "column AS alias".
I think the argument about overloading "=" has value. The argument that "AS" is overloaded is false because it is consistent across the language. Any time we are trying to rename or re-type an element, we use "AS". When changing the name of a table, a column, or a query (CTE), "AS" is used to make that change. When changing from one datatype to another, "AS" (in CAST) is used to make that change.
With computed columns, the use of "AS" is needed to be consistent with renaming an element, a calculation in this case. However, using the "AS" after the definition would have been difficult when laying out a table definition.
(
Subtotal DECIMAL(12,2),
TaxAmt DECIMAL(12,2),
Freight DECIMAL(12,2),
TotalDue AS Subtotal + TaxAmt + Freight
)
I think the "=" syntax could have worked, but placing the definition before the "AS" would look strange:
(
Subtotal DECIMAL(12,2),
TaxAmt DECIMAL(12,2),
Freight DECIMAL(12,2),
Subtotal + TaxAmt + Freight AS TotalDue
)
I think the reason the "=" is easier to read for some is that the character is significantly different from characters around it, allowing it to stand out visually. Additionally, we as programmers are used to seeing the target of an assignment on the left and its definition on the right so this syntax is more familiar.
I see all these complaints about overloading, but they don't make sense to me. = can be used for assignment, equality, and column alias. AS can (or needs to be) used for:
– table alias
– column alias
– CTE alias (WITH x AS (def))
– CAST AS data type
– computed column definition (col AS CONVERT(data type, other col)
I still submit that this:
Last_Name = CAST(Column2 AS VARCHAR(2))
…is much easier to read than:
CAST(column2 AS VARCHAR(2)) AS LastName
… simply because my eyes don't have to parse all the expression stuff to find the column name. But hey, I can't tell you what makes reading easier for you.
As for Celko and all the other folks complaining solely about standards, I thought I gave a pretty good disclaimer about that at the beginning of the blog post. Portability isn't a concern for everyone.
I prefer SELECT FieldName AS [ColumnTitle] for columns, though I only use FROM tablename t (HINTS) for tables. The equals sign gets used for SET and SELECT statements for setting variables, and filling my queries with ='s for both operations and aliases is grounds for confusion (for myself and the other developers I work with; I've never seen '=' used for an alias in any of the code I've worked with).
My habits come from learning TSQL on the job, and my own preferences for clarity. (I also prefer commas at the beginnings of new columns. I'm THAT guy at the office, and I'm ok with it.)
SELECT
Column1 AS [First_Name]
,CAST(Column2 AS VARCHAR(2)) AS [Last_Name]
FROM Users u (nolock)
I'll keep using [Expression] as [Alias]
= is an operator. and [Alias] = [Expression] is a microsoft proprietary extension. I'll stick with something closer to ANSI, since I write SQL across multiple SQL (semi-)compliant platforms.
Developing bad habits that limit me to Microsoft specific platforms is career limiting.
I meant Microsoft SQL Server 2008.
@Frank Hell,
I can't speak for all databases, but neither of your queries appear to work in SQL 2008. Besides, even if they did, wouldn't the correct attempt with [Alias] = [Value] be:
SELECT @i = MaxOfColumnA = MAX(ColumnA) FROM TableX;
Consider this:
DECLARE @i AS INT;
SET @i = 10;
–This is clear for every beginner, you have a descriptive alias:
SELECT @i = MAX(ColumnA) AS MaxOfColumnA FROM TableX;
–This will fail – obviously rubbish:
SELECT MaxOfColumnA = @i = MAX(ColumnA)FROM TableX; –???
Thanks Aaron for the nice writeup. I have encountered difficulties with AS especially in long expressions, i have run into them in SP's used for generating reports.
I am going to try using = and see how it works out.
Just two quick comments (I'm on a cruise so this is painful):
(1) AS lines up on the right nicely until you have complex expressions that run off the screen.
(2) this may overload equals, but AS is overloaded as well. Has everyone forgotten about CAST(column AS data type) over CONVERT(data type, column)?
I have to agree with Joe. = also has other meanings and, even if you are a SQL expert, the implication is either equivalence or assignment, which are logical and mathematical operations, rather than labeling. With the use of AS there is no confusion as to intent. The mental shorthand that ALL humans employ will make use of that fact and understand the intent and the impact implicitly without having to digest the code.
I must admit, my initial response after reading the first couple paragraphs was the same indignation I see some others expressing here. However, I think you make a persuasive argument. I do agree that the "=" format is more readable, at least in your examples. So I'm going to give it a try for a little while and see how it works for me.
I also agree with everybody's comments that adhering to standards is critical but the thing is, standards constantly evolve as we find better ways to do things. We need to not be too closed-minded.
"Bad Habit"? It's just the ANSI Standard and it doesn't hurt. It this your biggest problem that you have? To replace "AS" by "="?
Listen to Joe Celko – he's smarter that we all toghether!
My 2 cents: "=" sucks, use "AS" as intended!
I agree with Patrick Holmes. If you format your code to align the "AS"s, it's every bit as readable as putting the alias first. In fact, it's more readable to people more used to other dialects of SQL.
Sorry, but I think you're rationalizing a bad (but not critical) habit.
The ANSI Standard is <expr> AS <alias>; this is used in many other places, too. Your dialect overloads the equal sign, destroys portability and it is a bitch to read if you know SQL.
If readability is what it is all about, then surely simply using a formatting tool such as SQL Pretty Printer or Redgate SQL Prompt will achieve what you are looking for at the same time as maintaining standard SQL compliance. The code below before formatting:
select
address_line1 as a1,
address_line2 as a2,
address_line3 as a3,
address_line4 as a4,
town,
county,
postcode,
country
from addresses
And then the same code after formatting with alignment of all the aliases lined up to the right making the visibility of the aliases very clear:
SELECT
address_line1 AS a1
, address_line2 AS a2
, address_line3 AS a3
, address_line4 AS a4
, town
, county
, postcode
, country
FROM
addresses
Actually, alias=variable was more confusing to me when I first encountered it because I was trained in SQL standards and that wasn't part of it.
Also, who could possibly be confused that in:
SUM(Percent_Discount * Amount_Charged) Total_Discount
that Total_Discount wasn't intended as an alias instead of a mistaken missing comma?
OK, if there was a:
GROUP BY Total_Discount
I admit I would be confused
Aaron,
Thanks for the blog, and a nice writeup. I have to say that for the sake of expediancy, and old habits, I have always done #3.
select Sum(*) 'Sum of Sales'
from Sales
…
I know it will go away, and I've been trying to migrate to AS, but your comment on Twitter, and your post here, makes me think that the = syntax is actually better. The points you raise, finding the column name, readability, are good ones and since I'm a SQL Server only guy for now, I think I'll move to #2.
Thanks again.
@Thom, I appreciate that migrations and standards across platforms are real things. I was just stating that in my experience they're few and far between. In any case, that's why I said (regarding adhering to standards or hedging bets on deprecation of = notation):
"If these concerns are important to you, you may want to stop reading."
I explained the benefit of = over AS – the column alias names are always lined up to the left, as opposed to appearing at the *end* of the expression (which can often be of extremely varying length, especially in the area of reports). I don't expect everyone to agree with my subjective determination of what "readability" means to me.
@bystander, there's a big difference between "has been deprecated" and "may someday be deprecated." As I said, regardless of whether the = syntax is standard, I find it quite unlikely that it will ever be deprecated. I think its use is FAR more common than the ugly and unreadable column 'alias' syntax. YMMV.
@AaronBertrand – "Mythical Migration" = Dude, you are too theoretical to count in any meaningful way, drop out of the discussion.
On the other hand I am currently working on a project where we are combining a third party system with a conglomeration of SQL Server, Oracle, DB2 and some other database pieces.
My biggest headache right now is a bunch of really bright people solving similar problems as if there are no standards. Every one of these solutions was an 'improvement' on some pointy-haired boss' reliance on a sub-optimal standard.
As you say "I'm going to have much bigger problems to worry about in a migration scenario than ensuring the most basic parts of my syntax are completely compatible." – but why add to the mix when it is really just as easy to apply consistency.
Besides – I don't actually see any benefit to = instead of as. It is not more readable to me, quite the opposite.
Thanks for your blog – and your thick skin. I appreciate your response to me, and your time. The importance of this is not any individual tip or thought but rather the process of having new thoughts and discussing them.
You're funny:
= is not ANSI standard and as such might end up being deprecated in a future release. But you apparently don't really care:
"I have no concerns whatsoever that SQL Server will eradicate the = notation for column aliases"
But just a few lines below, you write this about string literals as column aliases:
"First, I want to discard item #3 entirely. Why? Because using string literals as column aliases has been deprecated for some time now."
Glad to know I'm not the only one who converts AS to =
🙂
I use AS, but I've started thinking I would use = on my own often because, when doing some large data manipulation updates, I tend to do
UPDATE MyAlias
set
col1 = 'abc',
col2 = 'def',
col3 = " –etc
from MyTable MyAlias –I tend not to use AS here since it's not that ambiguous compared to column naming
inner join MyOtherTable
–blah blah
If I want to see what's going to happen I can quickly comment out the update & set and add a "select" like so
–UPDATE MyAlias
–set
select
col1 = 'abc',
col2 = 'def',
col3 = " –etc
from MyTable MyAlias
inner join MyOtherTable
–blah blah
You're right, it is also a lot more readable but "AS" is just a habit I have stuck with for years…
I also still use the SQL 2000 QA keyboard shortcuts – that's the first thing I change when running management studio. Ctrl+Shift+C and Ctrl+Shift+R to comment/uncomment. I can't remember the keystrokes for comment/uncomment otherwise 🙂
Thom, my point about the standards, is that on any complex system in SQL Server, do you really expect to write *only* code that adheres to the standard, and will also work on MySQL, DB2, Oracle, etc.? It's nearly impossible to do unless you just create a few tables and put *all* of the logic in the app/API. If I had to change all my = to AS due to some mythical migration, it might be a lot of work, but it's not hard. I'm going to have much bigger problems to worry about in a migration scenario than ensuring the most basic parts of my syntax are completely compatible.
This can work against you too … want to write old-school outer joins with *= and =*, just so those will work on other platforms that still support them? You'll also need to fix these when migrating to newer versions of SQL Server. We have enough things to worry about in SQL Server just writing code that will last beyond a few releases, that we don't need to constantly burden ourselves with adhering only to the standard, unless we are really in a situation where migration to a new platform is likely. In my experience such migrations occur more in Joe Celko's classroom than in the real world.That all said, I use = vs. AS because it provides much better readability, but I do adhere to the standard when there is no obvious reason not to. Almost everybody uses GETDATE() but I prefer CURRENT_TIMESTAMP.
Aaron – I find your lack of faith (in the standards) disturbing.
When I am working on multiple systems it is SO painful to correct one person's weird usage. It is like having a programmer who chooses to write all of his comments in Esperanto – it forces everyone around you to learn your oddity AND it messes with the potential for automation.
Also – over the years I have seen Y2K kick in repeatedly. By this I mean you choose to ignore the possibility of porting to other databases so you just write any goofy thing you feel like because you want to. This code often outlasts the original use by many years. Wasn't that the message of Y2K – your code belongs to the company and they will squeeze every last useable bit out of this and make it last forever.
Again, not tying to change anyone's mind about this, just tying to make you think about it.
@Steve: I did not suggest that this had anything to do with tuning. For me this is about readability and maintainability.
@Peter: I stated inline already that I always use AS for table aliases (and WITH for hints). I've seen too many people complain that the following "hint" didn't make this query any faster:
SELECT COUNT(*) FROM dbo.bigtable NOLOCK
Of course "NOLOCK" is now the alias for the table, not a hint that tells SQL Server to use READ UNCOMMITTED.
@Steve Morris, AS is also used in CAST though.
I prefer AS due to the fact that its use is restricted to aliasing tables and columns, whereas = already has too many jobs to do. Also I think giving the real name first and the alias afterwards is logically more appealing – but maybe that´s just me. Not something I would consider part of a "tuning" exercise anyway, I think the most important thing is to fit in with the house style & stay consistent.
How about table aliases? With or without AS?
SELECT *
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
SELECT *
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID
I gotta disagree on this one, Aaron, if only for the reason that AS is the ANSI standard. Of course, the Standard isn't always useful for purely SQL Server people. So I punt on "standardized" ways of doing things lots of times because a SQL Server-specific implementation is ENORMOUSLY more effective or a major time saver, this little clause just doesn't fall into either catagory.
Just my two cents…
-Kev
@JNK yes, I certainly don't expect to convince everyone. But I must admit that situation is probably the minority – once a stored procedure is written, its consumers – be it API, app devs, end users, etc. – are usually more concerned about the output rather than the source. Source is only relevant when troubleshooting or refactoring. IME.
I still like the Column AS Alias approach because for the procs I work in, it's more important to know the source of the data than the target.
I couldn't agree more. This is one of the first things I do when handed a chunk of code to tune; convert all of the AS's to ='s.
Thanks mordechai, I didn't want to bring [square brackets] into this specific discussion, but could you explain more what you mean? Do you mean:
ColumnOrExpression [ColumnAlias]
or
ColumnOrExpression AS [ColumnAlias]
or
[ColumnAlias] = ColumnOrExpression
?
Aaron, I agree it's a matter of preference. for my part I tend to find [columnAlias] most unambiguous
My very first programming language was Pascal, and this is very similar syntax. I always found it to be very readable and easily understood.
Good job, and thanks for reminding us of this alternative.
Kelly, you could write it this way I guess, if you wanted to keep the SELECT query source self-documenting:
INSERT dbo.table(c1, c2, c3)
SELECT c1 = @val, c2 = @val, c3 = @val;
But if you're using either the single- or multi-row VALUES clause rather than a SELECT as the source, you don't have that option. I don't know if I can tell you why that is. 🙂
I agree about the readability of using '='. Which brings me to my next point, why does SELECT have alias=val, UPDATE has SET col=val, yet INSERT doesn't have some similar convention of col=val?
Aaron,
Nice post! I have not totally kicked the habit of using "expression AS alias," but I can attest to the frustration of developing code with a decent amount of column aliases only to have to rewrite for a business user who tried to paste into an MS Query in Office where the aliases often do not carry over. Although this has happened less with Office 2007 and higher, the cringe every time it happens is worth habitually using "alias = expression."
Anyway, wonderful stuff as usual.
Kevin
Always been a "column as alias" person myself, just seems to be a more standard practice. Even LINQ generates sql that way (Not exactly an endorsement in my opinion).
However, in seeing the alias=col I do like the readability better, which is really important as you pointed out.