March 31, 2008 at 10:51 am
This is one of those questions the professors in college speak of when they grade you. The "good profs" (arguably, some may not think them good, I do, but as stated above it is my opinion) toss this question out and do not use it as part of your grade.
Humbly, though, I must admit, I was not aware of the "order by" flaw in 2000. Having not written a query that incorrectly uses the order by, there may be some userfulness here.
Jamie
March 31, 2008 at 11:16 am
Second attempt to post. Page cannot be display on first.
On my SQL Server 2005 system I was able to run the query:
select createdate,*
from dbo.aspnet_membership
order by createdate desc
without error. The createdate column is the first column in the result set and also appears in the column list produced by the "*" in its correct location. The result set is in reverse createdate order (most recent date first).
Unfortunately, this database was ported from SQL 2000 and the compatiability mode was not raised to 9 on this database so my test did not produce the error I had expected. However, whenever the answer varies by version the question should specify the version. Before long we will have 2008 to also consider but most SQL Server is still on 2K and probably will be for at least another year, maybe two.
-- Mark D Powell --
March 31, 2008 at 11:47 am
This is actually much cooler than it looks - a poster above noted this but I did not understand until I tested...
If you specify a column alias that matches a column name that is output by the "*" wildcard, SQL Server 2000 will consistently use the aliased column in preference to the actual table column in the order by, even if you qualify the column name in the ORDER BY clause!
This is crazy behaviour, I can see why they dropped support for it in 2005 (compatibility 90):
I agree, this is crazy behavior, and it only gets more interesting. I modified my script, the alias is used for the ORDER BY clause, the actual field is used for the WHERE clause.
USE [master]
GO
-- Create Sample Database
CREATE DATABASE [QotdTestDB]
GO
-- Set Compatibility to 80
EXEC dbo.sp_dbcmptlevel @dbname=N'QotdTestDB', @new_cmptlevel=80
GO
Use QotdTestDB
Go
Create Table Employee (
EmpID INT,
EmpName Varchar(20),
Start_Date DateTime,
End_Date DateTime
)
GO
Insert Employee (EmpID, EmpName, Start_Date, End_Date)
Select 1, 'Adams', Datediff(dd,1,GetDate()), DateDiff(dd,-1,GetDate()) Union
Select 2, 'Davis', Datediff(dd,2,GetDate()), DateDiff(dd,-2,GetDate()) Union
Select 3, 'Jones', Datediff(dd,3,GetDate()), DateDiff(dd,-3,GetDate()) Union
Select 4, 'Smith', Datediff(dd,4,GetDate()), DateDiff(dd,-4,GetDate())
Go
-- This query will run, but the answers are not what I expected
Select End_Date as Start_Date, * from Employee
Order by [Employee].[Start_Date] desc
GO
Select End_date as Start_Date, * from Employee
Where Start_Date < GetDate()-3 -- This uses Start_Date in the table
Order by [Employee].[Start_Date] desc -- This uses Start_Date the alias
GO
-- Drop the test database
Use [Master]
DROP Database [QotdTestDB]
GO
March 31, 2008 at 12:31 pm
Like many others, I missed the question, but I learned something today. In my world, that evens the score.
In hindsight, it's a good question. I'm glad that compatibility 90 "fixes the glitch."
March 31, 2008 at 2:37 pm
I was able to successfuly execute the select statement
select HireDate, * from employees order by HireDate desc
on Northwind employees tables in SQL 2005 and SQL 2000 (using both management consoles).
Why the answer specified in choice (a) is wrong? :unsure:
Only because it will not run successfully under very specific conditions....SQL2K DB on SQL2K5 DB platform with SQL2K version...Can Upgrade Advisor catch this? Because I sure didn't!
March 31, 2008 at 10:16 pm
Hi,
Its work fine with server 2000 but not with 2005
April 1, 2008 at 12:20 am
Oh Dear!
I have tested the same query in SQL Server 2000 / 2005. It is working fine. Option 1 is the right answer. May be this question should have been written more clearly.
Thanks
Un happy Member
Kindest Regards,
Atif Saeed Khan
April 1, 2008 at 12:41 am
Its work fine with 2000 but not 2005.Before any question specify sql server version:)
April 1, 2008 at 1:47 am
barb.w:
answer (a) is wrong not in the context of the SQL version you are running but in the syntax of the SQL used in SQL Server 2000 and 2005(mode 80).:)
The ORDER BY clause should use the table column i.e. the one from the * part of the select but it is using the expression referenced column.
As pointed out a where clause will use the correct column.
This will only lead to confusion when writing queries.
Selects should only return a set of uniquely named columns, although SQL Server 2000 will allow otherwise.
The order by clause should use a set of uniquely named columns.
This is best practice - SQL Server 2000 is sloppy! 😛
Hiding under a desk from SSIS Implemenation Work :crazy:
April 1, 2008 at 1:58 am
Wow. Seven pages already!
OK, a summary. What this question has illustrated:
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 😉
Semper in excretia, suus solum profundum variat
April 1, 2008 at 2:30 am
I also answered based upon SQL Server 2000.
Please consider environment before printing this message 🙂
April 1, 2008 at 3:44 am
HI All
The query will work without any error. the suggested answer is wrong
the answer for the question is
Retrieve all columns additionally the date column as a atfirst column
by all
shamsudheen
April 1, 2008 at 3:54 am
Given Ans is incorrect query will work without any error, Option "A" is correct.
On getting incorrect ans, I have tested on both Sql-Server 2000 and 2005; this works properly, without any error.
This fails on Oracle, but we work on SQL-Server.
This is a contradictory question.
April 1, 2008 at 4:00 am
People should read all the previous posts before commenting.
The last two guys obviously have learned nothing!:hehe:
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)!
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
April 1, 2008 at 4:04 am
Why such a contradictory question in a forum like SQL Server Central. its really upsets me.:D
Please consider environment before printing this message 🙂
Viewing 15 posts - 61 through 75 (of 182 total)
You must be logged in to reply to this topic. Login to reply