April 1, 2008 at 4:10 am
ayan_m:
Dude you need to chill.:)
The question of the day is merely a test of one's own knowledge and where it is found to be lacking one should profit from the experience as one will be now better educated in the arcane arts of databse administration and/or T-SQL. 😀
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
April 1, 2008 at 4:22 am
Extended summary:
majorbloodnock (4/1/2008)
1. There are practical version differences between SQL2K and SQL2K5 regarding T-SQL handling.2. If you work on "theoretically correct" and don't then test, you'll get caught out at some point.
3. QOTD authors are fallible.
4. The devil's in the detail.
5. A DBA's capacity for debate is seemingly boundless 😉
6. There's more to learn from QOTD than simply answering the question. I got the answer right and thought this was a simple, straightforward QOTD until I read the debates about SQL2K/SQL2K5 differences.
7. Some people never learn. After 8 pages of discussion, 2 people can simply post "(a) is right"! :w00t:
Derek
April 1, 2008 at 4:25 am
I react without reading entire forum's discussion..
This was a flaw in SQL-Server until compatibility level:90. :w00t:
So far I was understanding based on queries executed many time... This quation and forum's explanation cleared all my doubts..
I have learned based on past experiences (Practical), but that known bug in SQL-server, I never find anywhere..
Thanks ...
April 1, 2008 at 4:52 am
Personally, I didn't think any of the answers were correct. Now, I didn't use a column named Start_Date, but that shouldn't matter. We have an "AlarmDef" table with the first column ad_alarm_num. Both SQL Server 2000 and 2005. I ran the query:
select ad_alarm_num, * from AlarmDef order by ad_alarm_num desc
In both cases, the query returns:
AD_Alarm_Num, AD_Alarm_Num, ... (the rest of the columns in AlarmDef).
As the no answer said you'd get the column twice, no answer was correct, IMO. To be honest, the results that I got matched the results that I expected.
Terry
April 1, 2008 at 4:57 am
Your expectations are/were flawed/jaded by the slack implementation of SQL in SQL Server 2000 🙂
Your opinion is noted, but is incorrect. 😉
Correct SQL syntax behaviour is demonstrated by, dare I say it here, Oracle and SQL Server 2005 (Mode 90). 😀
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
April 1, 2008 at 5:11 am
I feel disgusting, flogging such a long-dead horse, but I can't resist:
Mode 90 SQL2005 implements SQL correctly therfore the correct answer is (d)!
The accuracy of the answer does not depend on which SQL Server implementation is more correct, but simply empirical results.
The fact of the matter is that none of the answers were correct:
- A is wrong on SQL 2005 (compat 90), and also fails to mention the duplicate column
- D is wrong on SQL 2000 / 2005 (compat mode 80)
Which of two definitely wrong answers is more right is, in my opinion, a silly debate 🙂
The question would simply be thrown out in any professional or academic testing situation - which this is not!
And as many have noted, the wasted point is well worth it for learning about the issues this question has raised:
- Queries can have errors converting from 2000 to 2005 compat mode 90
- 2000 / compat mode 80 can have very unexpected behaviour when ordering by aliased columns that have the same name as table columns in the result set.
And if anyone knew all this before, then they were silly for even bothering to answer, as they then knew that none of the answers provided were correct! 🙂
I promise I won't post on this again - I can't imagine the number of people whose inboxes have been flooded by this topic over the past couiple of days!
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 1, 2008 at 5:26 am
*raises head above parapet*
First of all apologies for the late response - I was out of action yesterday.
I was going to apologize for not being more specific in the question, but then again would it have caused this much discussion? So I am not. I feel that more people have learned more about this topic than if the question was 100% unambiguously worded! I never intended this to be a 'trick' question - merely a learning exercise, a way of passing on some knowledge that I had stumbled upon.
As has been pointed out many times in this thread, it does work in SQL2000, but possibly more by flaw than by design.
It is quite funny that those people whose concern is more about the points, have more than made up for their loss by posting here!
Also to those people who get on their high horse about how this works in SQL2000 andhow I should have been more specific, no-one has commented on how this works in SQL7 or SQL6.5 - so they too are guilty of 'assuming' a version as much as me! So what SQL6.5 and SQL7 are no longer supported - I bet someone (poor soul), somewhere is still using them.
Many thanks to Shaun McGuile for somewhat fighting my corner on this one
majorbloodnock too - I guess we are all fallible!
Note to self - be more specific in future or the whole point of the question will be lost!
April 1, 2008 at 5:31 am
'Twas a pleasure, mate!
(note to self must curb my caustic, mean, aggressive streak more). 😀
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
April 1, 2008 at 5:47 am
Shaun McGuile (4/1/2008)
SQL server 2000 is flawed and SQL server 2005 (mode 80) is flawed.Mode 90 SQL2005 implements SQL correctly therfore the correct answer is (d)!
I run a query in SQL 2000, it returns the correct values that I expected, but it really wasn't supposed to, but it does, and did back in SQL6.5 too.
So when you were running this type of query prior to SQL 2005, were you saying that SQL 2000 was flawed then because it was returning the correct values that you expected?
If it was easy, everybody would be doing it!;)
April 1, 2008 at 5:58 am
Yes, for all SQL Server 6.5/7/2000/2005/2008 and any other relational/object database;-
If they do not return an error for this type of Select returning two identically named columns then they are flawed/'dirty' - the result is 'ok' in Enterprise manager/SQL Analyser but in a real world system.:)
Supppose you want to pass the result onto another piece of code C# (or other language).
Suppose you want to create a report.
How do you distinguish between the identically named columns? By column number? Bad bad bad evil practice! (You will be shot/multilated/etc.) 😉
If you use the, heaven forbid, graphical query building part of Enterprise Manager and try to have a column a second time in you result set it gets renamed/aliased to Expr1. Did you never wonder about this? 🙂
One of the things app developers should always do is refernce by column name.:D
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
April 1, 2008 at 6:29 am
Shaun McGuile (4/1/2008)
Supppose you want to pass the result onto another piece of code C# (or other language).Suppose you want to create a report.
How do you distinguish between the identically named columns? By column number? Bad bad bad evil practice! (You will be shot/multilated/etc.)
Just a question, but why would you care? Both columns have the same value. Which version of column A is immaterial, since they are the same value.
Terry
April 1, 2008 at 6:46 am
Terry,
Though you are correct in the statement that both are identical please see the post by wayne.mcdaniel this thread, page 6 I think.
(aliasing a different column to be the same name as a different existing column).
The columns in this case are not identical in content.
Native mode SQL2005/SQL2008 and Oracle care about this and believe the query to be malformed. 🙂
Going forward 2005/2008 (mode 90/100?) will be what is in use, we all here now know one of the traps in migration from 2000 to a higher version. 😀
Do not get me wrong;-
I agree the question should have stated 2005/Mode 90.:)
An apology has been posted by the Quetion Poster.
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
April 1, 2008 at 6:52 am
in SQL 2000 it will work.
April 1, 2008 at 6:59 am
:crazy: Arrrgh! :crazy:
Thats it I'm not posting on this thread anymore!
Other than to say its been educational for me to see how many poor, naive, neophytes
have fallen for this question. 😀
I'm gonna get MS to put this one in the exams! 😛
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
April 1, 2008 at 7:09 am
Question:
How many DBAs does it take to change a lightbulb?
Answer:
Which version?
Why change it? It works for me!
Have you tested the lightbulb, or is it just theoretically not working?
Etc. etc. etc.
:Whistling:
Semper in excretia, suus solum profundum variat
Viewing 15 posts - 76 through 90 (of 182 total)
You must be logged in to reply to this topic. Login to reply