single quotes as column aliases

Question

I ran across something I had never seen before.  The stored procedure writer had written a query that set all the columns in the Select list to an alias.  However, when creating the alias instead of using something like "Select num number from ..." or "Select num [First Number]" or "Select num AS number" or "Select num as "First Number"  From ..."  or "Select num as [first number] from..."  They entered "Select num AS 'First Number' From ..."   

And it worked!?!  Then I looked up in MSDN column aliasing for SQL Server 2005, and lo and behold, MSDN actually uses that formulation as its example of column aliasing.   Are they now saying this is the preferred usage?  This was not how I was taught SQL column aliasing back in the day. 

Another issue that cropped up because of this is that when the writer used the column alias in the order by clause he utlized the single quotes there as well.  But the SqL Server Update Analyzer for R2 reports this as an update exception.  But when I tried running the query in SQL 2008, it ran just fine.  Ditto SQL 2005.

But MSDN states " A sort column can include an expression, but when the database is in SQL Server (90) compatibility mode, the expression cannot resolve to a constant. "  Isn't that what a string expression in single quotes evaluates too?

So now I am thoroughly confused.  Can someone explain the logic here?

---

Edward R. Joell MCSD MCDBA

Answer 1

It worked for me too in 2008:

SELECT ProductID, 'ProductName' = Name, ListPrice, 
      'Color'=coalesce(Color, 'N/A')
FROM AdventureWorks2008.Production.Product
ORDER BY 'ProductName'
GO
/* ProductID	ProductName	ListPrice	Color
1	Adjustable Race	0.00	N/A
879	All-Purpose Bike Stand	159.00	N/A
712	AWC Logo Cap (XL)	8.99	Multi
3	BB Ball Bearing	0.00	N/A   .....*/

Related article:

http://www.sqlusa.com/articles2005/columnaliasandcte/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

went to the link.  Interesting but non-germane.

Anyone experience my surprise, or is this old news.

---

Edward R. Joell MCSD MCDBA

Answer 3

Well, I also knew it and saw such samples of code. I personally always use [] for new field aliases or just an alias, if it's an OK name (no spaces, etc.)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

I personally use [] when i write on SSMS or SSRS or any DBA App, but when i developing in C/C#/VB.NET atc' i use '' usually. why ?!? i dont know :-) maybe since '' is the way usually i write a string in developing.

* i did not notice that i do it till now :-) i have just checked it now and saw it

---

Answer 5

The use of literals as column alias, is in the list of deprecated features in SQL Server 2012. At least the form:

'string' = expression

My suggestion is to stop using it, and use any of the other options.

SQL Server, Deprecated Features Object
http://msdn.microsoft.com/en-us/library/bb510662(v=sql.110).aspx

Look for "String literals as column aliases".

---

AMB

Some guidelines for posting questions...

Answer 6

Double-quotes adheres to the ANSL-SQL standard. Unfortunately, [] has caught on better in the SQL Server community... For a long time, I used double-quotes, until I realized I was fighting a losing battle and I have given up on that now.

Or rather, I avoid using delimiters in the first place, but when I really *have* to do it...

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 7

I always use square brackets myself, but I had no issue with understanding double quotes as delimiters.  The idea of using a single quote was a surprise

Here is the text in the Select clause MSDN entry for R2
Is an alternative name to replace the column name in the query result set. For example, an alias such as Quantity, or Quantity to Date, or Qty can be specified for a column named quantity.
Aliases are used also to specify names for the results of expressions, for example: 
USE AdventureWorks2008R2;
GO 
SELECT AVG(UnitPrice) AS 'Average Price' 
FROM Sales.SalesOrderDetail; 

Here is the text from the select clause entry in 2012
column_ alias 
Is an alternative name to replace the column name in the query result set. For example, an alias such as Quantity, or Quantity to Date, or Qty can be specified for a column named quantity.
Aliases are used also to specify names for the results of expressions, for example: 
USE AdventureWorks2012;
GO 
SELECT AVG(UnitPrice) AS [Average Price] 
FROM Sales.SalesOrderDetail; 
column_alias can be used in an ORDER BY clause. However, it cannot be used in a WHERE, GROUP BY, or HAVING clause. If the query expression is part of a DECLARE CURSOR statement, column_alias cannot be used in the FOR UPDATE clause. 

The deprecation listing suggested by Hunchback uses a format, which I am not sure refers to this issue.  specifically using 'string literal' = expression in the Select Clause. 

Did you go there Naomi?  Do you understand it to refer to our issue or something else?

But I think it is noteworthy that MS changed their MSDN entry to show what we all have agree upon as the most accepted usages.

---

Edward R. Joell MCSD MCDBA

Answer 8

Yes, I checked that link. It's the 'ColName' = Expression form which is deprecated.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

So Double Quotes is the ANSI standard, square brackets was the SQL Server 7.0 and before standard.  So does anyone know when it first became possible to use single quotes as delimiters? What standard does that represent?  (Interestingly the article on Identifiers http://msdn.microsoft.com/en-us/library/ms175874(v=sql.105).aspx does not list single quotes as an option for delimiting identifiers.)

And why did Microsoft stop recognizing recommending single quotes as delimiters.

---

Edward R. Joell MCSD MCDBA

Answer 10

Mr Waldrop

If you had read the thread you would realized that the question had to do with why single quotes were allowed, not on whether double quotes or square brackets were the best choice.  You would have also seen that Naomi N agreed with me a few lines below, that the particular link suggested by Hunchback had to do with a completely different format than the one being discussed. 

Please do not mark something as an answer 12 minutes after I just indicated the thread was not answered.  Please do no mark a thread as being answered without reading the thread.

---

Edward R. Joell MCSD MCDBA

Answer 11

So Double Quotes is the ANSI standard, square brackets was the SQL Server 7.0 and before standard.  So does anyone know when it first became possible to use single quotes as delimiters? What standard does that represent?  (Interestingly the article on Identifiers http://msdn.microsoft.com/en-us/library/ms175874(v=sql.105).aspx does not list single quotes as an option for delimiting identifiers.)

And why did Microsoft stop recognizing recommending single quotes as delimiters.

---

Edward R. Joell MCSD MCDBA

I can't speak to why MS decided to stop recommending single quote identifiers, I can only say when I stopped using them; in writing (the dreaded) dynamic sql, it became much easier to read and troubleshoot when I didn't have to keep track of 'object' and could eyeball [object].

I'd have to scrape the rust of my skull, but I seem to recall single quote identifiers all the way back to 7.0, at least.

Answer 12

Part 1:  (I don't want to get in trouble with you, answering slightly off topic, so part 2 specifically answers one of your questions... ;-)

Languages are compiled/interpreted/parsed, and there are occasional surprises or anomalies or backward compatibility situations, which may be surprises or not.  For example, I asked a question about this a while back, and through the course of discussing, similar behavior (spaces can be left out in certain specific instances) exists in several other languages too.

Select 123 as Var1
Select 123 Var1
select 123as Var1
Select 123Var1

All four do the same thing, even with the omitted spaces and the omitted word "AS". What probably happened is a compiler parser was written years ago that works, and remnants of its code persists to this day.

Similarly, allowing the single quote is probably a combination of backward compatibility going back to Sybase days.  One piece of trivia I can add, is that the single quote doesn't work in Oracle, although double quotes and the braces do.

Part 2:

Regarding SSRS, although it's running SQL, against a SQL server database, the parser isn't the same one as in SSMS: In extremely general terms, simple queries work the same, complex ones that work in SSMS don't always work in SSRS.  And although you probably could come up with a definitive list of exactly what commands or scenarios trigger the difference, sometimes we just have to concede "it's different, and sometimes what works in SSMS doesn't work in SSRS", and perhaps rewrite the query, or perhaps just put the query into a proc and call the proc.

Answer 13

Up to SQL Server 6.0 single and double quotes were entirely equivalent and you could use any of them. This was not the least handy when you worked with dynamic SQL and needed to nest quotes.

In SQL Server 6.5 Microsoft added the ability to use "" to quote identifier to comply with the ANSI standard. But you had to enable the new SET option QUOTED_IDENTIFER to get that functionality.

By time that option is on by default in most environments - but not all. The setting is still off by default in OSQL, SQLCMD, BCP and SQL Server Agent. (And please don't ask why!)

[] as an identifier delimiter was added in SQL 7, I guess with inspiration taken from Access. A possible reason that they added [] is that if you have an application that started it's life in SQL 6.0 or before, changing all double quotes for string delimiters to single quotes is a major challenge. The system I work with is that old, although our solution is that I've fixed our load tool to change double quotes to single quotes across the board. So I am for one is quite glad that single quotes still works for delimiting aliases.

Although, I entirely agree that it is illogical.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 14

Erland

It makes some kind of sense that the compiler parser was designed to interpret single quotes and double quotes exactly the same.  The main issue with this is when set quoted_indentifiers = ON, it would appears that single quotes can be used as both identifiers and literal strings while double quotes will only be interpreted as identifiers.   Was this the case in 6.5?  I need to test this.  I will run a query

Select yadayada 'hoka', ladeda "Lada", tootaloo [toot] 
FROM table where tootaloo = 'this' 

And see what happens.  I'll be back.

Update:

I ran the query below in SQL Server 2005 and 2008 and it returned everything as per specifcations

SELECT     AIType 'Type', AITypeDesc "Desc" , AITypeColor [Color]
FROM         tblAIType
Where AITypeDesc  = 'Foreign Military Sales issues'

So for fun and giggles I ran the query below slightly changed from the one above.

SELECT     'AIType', "AITypeDesc", [AITypeColor]
FROM         tblAIType
Where AITypeDesc  = 'Foreign Military Sales issues'

This time it returned the first column as the literal string 'AIType'.  So then I did this.

SELECT     'AIType' 'Type', AITypeDesc "Desc" , AITypeColor [Color]
FROM         tblAIType
Where AITypeDesc  = 'Foreign Military Sales issues'

This time it returned the AIType as a literal string, but still used Type as the column alias.

If I may quote Mr Spock  'It is behaving most illogically;  almost human.'

---

Edward R. Joell MCSD MCDBA