November 3, 2008 at 9:55 pm
First off let me say that I hope this isn't a dumb question; I have the feeling it's one of those that once you findthe answer you slap your forehead and think why didn't I know that.
I'm not certain if BOL has anything about these 2 methods at least in how they compare to each other and even if it did I'm not sure how I'd search on it so I'm seeking the help of the forumn members.
What is the difference (the Pros & Cons; the reason why you should or should; ect) between writing a SELECT query (be it by itself or part of some program object like an SP) that uses Variable Assignment like this:
SELECT [SomethingWicked]= 'ThisWayComes',
[BadFriday] = 13
FROM FOOBAR_ORIGINAL FB
WHERE 1 = 1
AND 'WhatDoesFoobarMean' = FB.TheQuestionIs
and using field or column aliases like this:
SELECT 'ThisWayComes' AS 'SomethingWicked',
13 AS 'BadFriday'
FROM FOOBAR_ORIGINAL FB
WHERE 1 = 1
AND 'WhatDoesFoobarMean' = FB.TheQuestionIs
I've seen both methods used but neever a good explination of why one ver the other.
Thanks
Kindest Regards,
Just say No to Facebook!November 3, 2008 at 10:17 pm
It's not a dumb question at all. It's more of a VHS vs BetaMAX deal though (they happen to have dervied from distinct products not set up to talk to each other (DBase and Sybase for example). There are several optional ways to set up aliases for columns, and those happen to be 2 of the common ones.
That being said - you should note that at least one of the formats is being deprecated in the next version of SQL (the one using quotes to denote an alias). The deprecated list is found here:
http://msdn.microsoft.com/en-us/library/ms143729.aspx
Since everything seems to lean toward ANSI compliance sooner or later, the ANSI standard looks to be:
SELECT value AS [alias] --note - with or with out the [ ]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 4, 2008 at 9:10 am
Matt,
Thanks for the followup/info as well as the URL on deprecated items.
If I follow correctly you say that the method of
TABLE.ColumnName AS 'MyAlias'
for providing a column alias is shceudled for deprecation ina future version. When I checked the URL you sent it indicates that it is the assignment style
[MyAlias]=TABLE.ColumnName)
for generating aliases that is going to be deprecated.
Thanks Again!
Kindest Regards,
Just say No to Facebook!November 4, 2008 at 9:19 am
Actually - I didn't describe it well. If you look at the 4th column there you will see the replacement styles. The one slated for deprecation is one of the assignment styles, but it's the "assignment with single quotes" style, as in:
select 'SomethingEvil'='ThisWayComes'
The bracketed assignment still seems to be "okay" per the standards folks. What I was trying to get to is that the MS Dev teams seem to be leaning towards ANSI standards, so the "ultra-safe" method is to use ANSI-style aliasing, which is the AS syntax with square brackets, as in :
SELECT 1 as [MyValue]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 4, 2008 at 11:42 am
Yeah, personally I can't get used to the brackets, they just don't work as well, visually, as the single quotes, especially when you have some form of syntax highlighting. I guess if they changed the specification of literal text to use '[' as well like this:
SELECT TABLE.Column AS [MyColumn], [SomeText] AS [LiteralText]
FROM MYTABLE
vs
SELECT TABLE.Column AS 'MyColumn', 'SomeText' AS 'LiteralText'
FROM MYTABLE
then it might be less confusing but even then when you compare the 2, it just doesn't look natural to use the brackets instead of the quotes. I haven't read the ANSI standards so I'm not familiar with the history/background. Do you know why the standard leans to using the brackets verses the single quotes or even double quotes for that matter?
Since SQL is a high level language (as I understand the term to mean), it seems to mean that use of brackets in place of quotes would be counter intuitive and make the language lower (for lack of a better description). Aside from explictly idnetofying or poiting out a word or piece of text whatother use does the quote have in the langauge? The brackets serve as more then just text identifiers. Just doesn't make sense (to me at least) to go with brackets as the Standard.
Thanks Again for the followup.
BTW, forgot to mention this last time but your signature line is great. Especially in light of what day it is today.
Kindest Regards,
Just say No to Facebook!November 4, 2008 at 12:12 pm
No. Literal text hasn't been changed and your first query would throw an error saying no such column as SomeText
SELECT tbl.Column AS [MyColumn], 'SomeText' AS [LiteralText]
FROM MyTable AS tbl
The [] aren't necessary unless the alias is a reserved word or has spaces in, both or which you shouldn't do. So this works just as well
SELECT tbl.Column AS MyColumn, 'SomeText' AS LiteralText
FROM MyTable AS tbl
If I changed the alias to [Literal Text] (with a space) then the brackets would be needed.
If you wanted to put the [] everywhere they were optional, it would look like this
SELECT [tbl].[Column] AS [MyColumn], 'SomeText' AS [LiteralText]
FROM [dbo].[MyTable] AS [tbl]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2008 at 12:38 pm
And, just to confuse the issue some more 😉
ANSI standard quoted identifiers is the double-quote - so, the following will work also (as long as you have not disabled quoted identifiers).
SELECT t.column AS "My Column"
FROM dbo.MyTable AS "t";
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply