March 31, 2008 at 6:30 am
In SLQ2k works fine.:P
March 31, 2008 at 6:57 am
there are still many more SQL 2000 instances out there than 2005, so most people would answer this based on their 2000 experience. So the correct answer is a)
point back please.
---------------------------------------------------------------------
March 31, 2008 at 6:58 am
Since no version specified, I thought that wasn't an issue...WRONG!
This was a good exercise as it pointed out something that wasn't obvious.
I'm working on SQL 2000 right now for my current client, and the plan is to upgrade the DB to SQL 2005 later this year. Now I have something new to check for.
But like everyone else I want my points too! 😀
If it was easy, everybody would be doing it!;)
March 31, 2008 at 7:31 am
It is a bad query, but it will run in 2000 or in compatability mode. If this were a select into statement it would generate an error. In 2005, it will generate the following error.
Msg 209, Level 16, State 1
Ambiguous column name 'start_date'.
The question should be modified to include on which version of SQLServer you are running the query.
Q
Please take a number. Now serving emergency 1,203,894
March 31, 2008 at 7:40 am
SQL2K is obviously a bit broken then as trying to use such a resultant table for further SQL will cause all sorts of problems.
Logically everyone should have thought about it and picked the correct answer the 209 level 16 one as that is logically correct,
ORDER BY needs unique column naming to work
SELECT ColA, ColA, ColA from TableX is valid but not when used with ORDER BY ColA. :hehe:
no matter what the database version they use. 😀
By the way I jumped straight in without thinking and gave the SQL2K answer 😉 DOH!
We do not deserve the points for falling into the trap 😛
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
March 31, 2008 at 7:51 am
As with most other folks, I didn't see anything pertaining to SQL 2005, so assumed that it applied to any version of SQL Server...and subsequently got it wrong! Oh well...
March 31, 2008 at 7:52 am
Logically everyone should have thought about it and picked the correct answer the 209 level 16 one as that is logically correct,
We do not deserve the points for falling into the trap 😛
--Shaun
logically correct - maybe; physically incorrect - definitely
Questions should not be traps based on the phrasing of the question - I still want my point back 🙂
---------------------------------------------------------------------
March 31, 2008 at 7:54 am
but george - 'database administration is all about deception'
-Shaun Tzu's Art of DB Admin. 🙂
Hiding under a desk from SSIS Implemenation Work :crazy:
March 31, 2008 at 7:54 am
Choosing the logical answer is quite often not the way to go if you're not sure! Many of these questions are designed to highlight cases where the logical answer is the WRONG one! :w00t:
If this one was designed to be a trap, then that would imply that its writer knew that the different versions would behave differently, thus they would have specified 2005.
March 31, 2008 at 7:59 am
Shaun McGuile (3/31/2008)
but george - 'database administration is all about deception'-Shaun Tzu's Art of DB Admin. 🙂
LOL 😀
---------------------------------------------------------------------
March 31, 2008 at 8:04 am
Points Smointz!
Who cares (apart from the people you know/work with/'the point hound(s)')? 🙂
Doesn't mean anything really its not like you get paid - they don't make prizes!
Along as we all learned that there is more to the question than just what is written.:hehe:
Would like some comment from the Question poster, please?
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
March 31, 2008 at 8:30 am
I disagree! it works perfectly in both 2K and 2K5. i have both versions and i tried it out just to be double sure.
------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]
March 31, 2008 at 8:43 am
bimpe_d - your 2005 is running in 2000 (version 8) compatibility mode. 🙂
version 9 mode - native 2005 fails.
(2000 is flawed).
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
March 31, 2008 at 8:52 am
I ran a test with SQL Server 2005 compatability mode 80 and the query worked. When I made the compatability mode 90 it gave me the "ambiguous column name" error. The question should be more specific as to what version of SQL Server to run it on.
March 31, 2008 at 8:56 am
This one darn near drove me nuts - what with 3 possibilities 2K, 5K, with 2 compatibility levels - so my answer was more of a pseudo psychological analysis which by happen stance pointed to the answer that the questioner expected.
A serious suggestion for Steve Jones and those site members who have undoubtedly a vast knowledge of SQL server in all of its manifestations
For Steve - set up a panel which will vet the questions before they are published.
For the knowledgeable site members volunteer to join the panel
I believe the QOD is a wonderful prod to help me learn more and more about the subtle capabilities of SQL server. I place the QOD on the same plane as the articles published by the distinguished authors who volunteer their time and talents to help the rest of us, and want to see it continue!
Viewing 15 posts - 31 through 45 (of 182 total)
You must be logged in to reply to this topic. Login to reply