March 31, 2009 at 2:46 pm
Hi All,
I have something I can't necessarily explain perfectly and wondered if anyone can be of help. We recently switched over a SQL-2000 server database application to a SQL-2005 database application and one of my developers here wrote the following query:
SELECT s.Session_ID,
sd.Employee_ID,
Convert (VARCHAR(12), ss.[Start_Date],101) [Start_Date]
FROM [Sessions] s
JOIN Sessions_Detail sd
ON sd.Session_ID = s.Session_ID
JOIN Sessions_Schedule ss
ON ss.Session_ID = sd.Session_ID WHERE s.Course_ID = '4F6F9831-9F15-44A2-B18F-93E6665CC390'
ORDER BY ss.[Start_Date] DESC
He expected his results to be in the ss.[Start_Date] (datetime) order from the actual table but in fact the result set ordered the result via the converted varchar field results [Start_Date].
I told him to change the name of the output field to [StartDate] and see what the result was. Sure enough, it resulted correctly by the expected date. He claims that SQL2000 would output correctly with the "ss." specifier and in fact would not allow an order by of the named output field without the actual formula/statement in the order by clause (i.e. order by convert(....)).
My explanation was: that this had to be a new feature that was added in SQL2005 and why would you convert the field on an output dataset to a different type and then call it the same field name?
Any thoughts would be appreciated....thanks.
-Mark Douglass
Trinity Data Solutions
March 31, 2009 at 3:10 pm
Mark Douglass (3/31/2009)
He claims that SQL2000 would output correctly with the "ss." specifier and in fact would not allow an order by of the named output field without the actual formula/statement in the order by clause (i.e. order by convert(....)).
He's correct on the first part and incorrect on the second. SQL 2000 would allow ORDER By Start_Date, and it would have sorted by the converted value.
Edit: Actually, thinking about it, I believe he's wrong on the first point too. I've had a similar case on SQL 2000 and there was nothing I could do (other than changing the alias name) to get it to sort correctly.
Order by's the only clause that's evaluated after the column aliases are processes and on SQL 2000 (at least) it would use an alias even if the same column existed in the table, even if the table name was specified
My explanation was: that this had to be a new feature that was added in SQL2005 and why would you convert the field on an output dataset to a different type and then call it the same field name?
Less of a new feature and more of a fix. There were several odd behaviours with SQL 2000's sort.
The aliasing a column with the same name is fairly common. Possibly to avoid breaking an app that depended on the column name when some calculations were needed.
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
March 31, 2009 at 3:44 pm
Hi Gail,
Thanks for the quick response. Now I just want to clarify 1 point that I don't quite understand:
On the original query, the ss.[Start_Date] identifier sorts by the converted value on both SQL2000 and SQL2005. We've verified that on 2 different servers with the same database.
Why doesn't either one actually sort by the ss.[Start_Date] column when you specifically ask for it? If you change the output field name, the ss.[Start_Date] works as expected.
I really appreciate your time.
Thanks!
-m
March 31, 2009 at 4:04 pm
Mark Douglass (3/31/2009)
On the original query, the ss.[Start_Date] identifier sorts by the converted value on both SQL2000 and SQL2005. We've verified that on 2 different servers with the same database.
Is the DB still in Compat mode 80?
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
April 1, 2009 at 6:07 am
According to the Upgrade Advisor column aliases in the ORDER BY clause cannot be prefixed by the table alias.
Why not try dropping the alias and then using the column name that you defined.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2009 at 8:05 am
Yes it is.
April 1, 2009 at 8:07 am
Hi Grant,
Thanks for responding. Dropping the table identifier does work as the sort to the converted field. My question is why does it ignore the table designation on the ORDER BY statement.
I just want to understand why the behavior works the way it does.
Thanks,
-m
April 1, 2009 at 8:14 am
I think Gail has it right. They "fixed" the incorrect behavior from 2000. That leads to problems like what you encountered.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2009 at 8:28 am
Mark Douglass (4/1/2009)
My question is why does it ignore the table designation on the ORDER BY statement.I just want to understand why the behavior works the way it does.
Probably because that's the behaviour SQL 2000 had and the DB is in compat mode 80. Change to mode 90 (2005) and you'll see a different behaviour.
Create table TestOrder (
id int,
TheOrder int
)
insert into TestOrder values (1,1)
insert into TestOrder values (2,2)
insert into TestOrder values (3,3)
insert into TestOrder values (4,4)
insert into TestOrder values (5,5)
Compat mode 80:
select id, 10-TheOrder as TheOrder
from TestOrder t
Order by TheOrder
select id, 10-TheOrder as TheOrder
from TestOrder t
Order by t.TheOrder
Returns:
[font="Courier New"]idTheOrder
55
46
37
28
19
idTheOrder
55
46
37
28
19
[/font]
Switch to compat mode 90, same query:
[font="Courier New"]idTheOrder
55
46
37
28
19
idTheOrder
19
28
37
46
55[/font]
As I said, SQL 2000 had some strange behaviour (read bugs) around the Order By. They are fixed in 2005, but in compat mode 80 the behaviour is the same as it was in 2000, intentionally.
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
April 1, 2009 at 8:29 am
Thank you so much! I appreciate the help.
April 1, 2009 at 8:37 am
Hi
But to my understanding Order by Clause is the which gets executed as the last step in SQL query and it is completely based on what the alias was used. So if i look at in this way then the alias which was used "Start_Date" is used for sorting not the one inside the table.
If my understanding is wrong please correct me.
Thanks -- Vijaya Kadiyala
April 1, 2009 at 8:48 am
Hi Vijaya,
It does get executed last but the behavior of the ORDER BY does not actually work the way you would expect it to work when prefixing the table alias. See Gail's example above. This whole issue started out with getting unexpected results.
Just so everyone knows, I changed the compatibility mode to 2005 and it is working properly. So the original statement:
SELECT
convert(varchar, ss.[Start_Date]) [Start_Date]
FROM SomeTable ss
ORDER BY ss.[Start_Date]
in compatible 80 mode would always return the [Start_Date] sorted by the converted values.
When switched to 90 mode, it would actually sort the values by the ss.[Start_Date] NOT the [Start_Date] converted output column.
It is a bug in SQL2000 that was fixed in 2005. We only ran into it when we were doing something with date fields and needed to see the "last" date something occurred. When the developer ran a query like the one above, he found that the results were incorrect. Since we JUST upgraded this server from 2000->2005 the week before, he claimed that this behavior was a problem with the new install of 2005.
I didn't thus we came here to settle the argument and figure out why this worked like it did.
Does this help?
April 1, 2009 at 8:57 am
Mark Douglass (4/1/2009)
Since we JUST upgraded this server from 2000->2005 the week before, he claimed that this behavior was a problem with the new install of 2005.
Why do developers always blame the server, the database, the network, etc instead of thinking 'maybe it's a problem with my code'?
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
April 1, 2009 at 9:00 am
I think it's an easy way for them to shift the blame from themselves. Especially this developer who completely hates Microsoft and complains every time something goes wrong. He was so sure that it was a new bug in 2005 and he had to eat crow this morning when I proved the behavior DID exist in 2000 - he didn't like that. Had to take back all those nasty things he said.
Can you say sheepish quiet?
April 1, 2009 at 9:16 am
Mark Douglass (4/1/2009)
Can you say sheepish quiet?
Yup. Seen it enough times with devs that I used to work with. Usually after they had told me that a query was running as fast as possible and I gave it back to them running in 1/10th the time.
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply