Select with column names - syntax

Question

Select with column names - syntax

Jennifer Levy

Ten Centuries

Points: 1125
More actions
January 12, 2010 at 11:07 am

#138256

This is mostly curiosity on my part. I'm working on revising some long-running queries that other developers have written and have come across some syntax that's new to me. I was wondering if there's any difference between what I've been using and what I'm seeing.
If I'm running a select statement and need to name the columns, I'm used to putting the column names in square brackets.
The queries I'm modifying have been using single quotes instead of square brackets. I'm seeing both all over the documentation I've found, but no explanation as to whether or not SQL handles them differently, if it's a version difference (I do most of my work in 2K5, the dev who wrote this script does about equal work in 2K5 and 2K8), or if it's just a personal preference and makes no difference to SQL Server one way or the other.
Jennifer Levy (@iffermonster)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jcdyntek Ten Centuries Points: 1153 More actions · Answer 1

I believe you are referring to column aliases and if so you can do it with single quotes,double quotes, or brackets, there is no performance impact.

However, if there are no special characters or spaces in the name you choose(Preferred method) then you do not need any delimiters around the text.

Paul White SSC Guru Points: 150468 More actions · Answer 2

See Delimited Identifiers (Database Engine) for full details. Square brackets always work, double quotes depend on the setting of the session's QUOTED_IDENTIFIER set option.

Personally, I prefer square brackets where necessary.

Paul

edit: fixed tag

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

george sibbald SSC Guru Points: 104210 More actions · Answer 3

This blog would suggest qoutes have the potential to be misinterpreted and lead to errors:

http://msmvps.com/blogs/robfarley/archive/2009/12/29/apostrophes-around-column-aliases.aspx

---------------------------------------------------------------------

Paul White SSC Guru Points: 150468 More actions · Answer 4

george sibbald (1/12/2010)
This blog would suggest qoutes have the potential to be misinterpreted and lead to errors:
http://msmvps.com/blogs/robfarley/archive/2009/12/29/apostrophes-around-column-aliases.aspx

Very nice link. I've never used single quotes in this way myself (just seems dumb!)

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jennifer Levy Ten Centuries Points: 1125 More actions · Answer 5

Nice link indeed! Thanks, all, for satisfying my curiosity. As I said, I'd never seen the single quotes used in column aliases, only the square brackets, and it looks like there's a reason for that. Looks like I'm going to be doing more than just seeing where I can better the performance of these queries.

Jennifer Levy (@iffermonster)

Rob Farley SSCrazy Points: 2739 More actions · Answer 6

Thanks for the compliments. I like it when people find my posts helpful.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au