June 18, 2008 at 6:03 am
I am migrating my database from 2000 to 2005. So compatability changes, and one of the changes is how the order by clause is accessed in select statement.
I read a article which states that
SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1
For 2000:The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.
For 2005:The execution plan for this query shows that the sort operator orders the rows returned from t_table and then the values for the derived column c1 defined in the SELECT list are computed.
I created same table in 2000 and 2005 with same number of rows and select query. I did not find any difference in execution plan for both the compatability. Is there any difference in execution plan?
June 18, 2008 at 8:48 am
The optimizer, along with a whole of the rest of the engine, was changed between 2000 and 2005. It's not at all surprising that you'll see different execution plans in some circumstances. I've found that in most cases, I get better execution plans in 2005, but I've seen situations where the plans were worse. Usually some adjust to the query or indexes actually makes it run better than it was in 2000. But no, it's not unusual to see differences. You'll also see differences between 64 bit and 32 bit versions of 2005.
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply