January 17, 2013 at 8:24 am
andy.brown (1/17/2013)
webrunner (1/17/2013)
I got this when I ran it against a server running Microsoft SQL Server 2005 - 9.00.4053.00:Results:
(No column name)
0
Messages:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
(1 row(s) affected)
May not be relevant to the logic of the result or correct answer, but does anyone know why this error happened when I tried it?
Thanks for any help,
webrunner
SQL 2005 didn't support inserting multiple rows using VALUES.
Ah, great. Thanks for this information.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 17, 2013 at 9:14 am
Very interesting discussion thanks Hugo. Thanks for the question.
January 17, 2013 at 9:39 am
Aliases are not for columns where there is only one column possible.
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
January 17, 2013 at 9:42 am
Outstanding question.
Hugo Kornelis (1/17/2013)
For any query that uses more than a single table, you should always use the table prefix on ALL column references. (And because most table names tend to be long and I prefer my query to be human readable, this automatically implies that you should also always provide an alias for each of the tables used in the query.
I find that, purely out of habit, I use aliases/prefixes even on queries that involve only a single table. Kind of like using your car's turn signal at 2am at an intersection where there's no one around -- even though it's not needed, it's better to act out of habit than have to consciously remember to do it when it's needed.
Rob Schripsema
Propack, Inc.
January 17, 2013 at 9:42 am
chgn01 (1/17/2013)
Aliases are not for columns where there is only one column possible.
You are completely right - if you never make typo's, never have anyone else changing your tables, and always remember all the code you (and others) have previously written and deployed when modifying tables.
For all mere mortals, prefixing columns (either with full table name or, preferably, alias) is one of the important tools to keep the phone quiet at night.
January 17, 2013 at 9:48 am
Hugo Kornelis (1/17/2013)
chgn01 (1/17/2013)
Aliases are not for columns where there is only one column possible.You are completely right - if you never make typo's, never have anyone else changing your tables, and always remember all the code you (and others) have previously written and deployed when modifying tables.
For all mere mortals, prefixing columns (either with full table name or, preferably, alias) is one of the important tools to keep the phone quiet at night.
I'm with Hugo. Even with just 1 column, an alias really is helpful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2013 at 9:51 am
Hugo Kornelis (1/17/2013)
chgn01 (1/17/2013)
Aliases are not for columns where there is only one column possible.You are completely right - if you never make typo's, never have anyone else changing your tables, and always remember all the code you (and others) have previously written and deployed when modifying tables.
For all mere mortals, prefixing columns (either with full table name or, preferably, alias) is one of the important tools to keep the phone quiet at night.
You are so right. I wish I had a dollar for every time a developer came to me asking why their code which hasn't been changed in umteen versions suddenly stopped working (ambiguous column error) because of an upgrade.
January 17, 2013 at 10:05 am
chgn01 (1/17/2013)
Aliases are not for columns where there is only one column possible.
In the same way that you don't have to specify a column list when inserting into a table where you know the number and sequence or the columns. Everything works fine until somebody adds a new column, or recreates the table with the columns in a different order. Why take the risk?
January 17, 2013 at 10:12 am
Cliff Jones (1/17/2013)
Hugo Kornelis (1/17/2013)
chgn01 (1/17/2013)
Aliases are not for columns where there is only one column possible.You are completely right - if you never make typo's, never have anyone else changing your tables, and always remember all the code you (and others) have previously written and deployed when modifying tables.
For all mere mortals, prefixing columns (either with full table name or, preferably, alias) is one of the important tools to keep the phone quiet at night.
You are so right. I wish I had a dollar for every time a developer came to me asking why their code which hasn't been changed in umteen versions suddenly stopped working (ambiguous column error) because of an upgrade.
Next time, show them this QotD and say "consider yourself lucky you got an error - it could have been a behaviour change, and those tend to go unnoticed until it's too late".
January 17, 2013 at 10:13 am
Great QoTD.
Thanks..
January 17, 2013 at 10:53 am
I totally agree with you, it is nice to have all aliases listed just for maintainability.
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
January 17, 2013 at 12:21 pm
Lokesh Vij (1/16/2013)
I did observed this strange behavior, but was not aware of the reason behind this. Thanks 🙂
I didn't observed this never before, and obviously I went wrong :pinch:
January 17, 2013 at 1:21 pm
Nice question. I hope I never see someone doing this on purpose.
January 18, 2013 at 12:32 am
Great question!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 18, 2013 at 12:37 am
+1
Very intelligent question...
Regards,
Ravi.
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply