|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why SQL2k and SQL2005 Return Different Order for sysindexkeys?But the return order of 'colid' for the first key is different! (only the first key, not the others). My select query is: "SELECT [indid], [colid] FROM AWT..sysindexkeys where [id] in ( select [id] from AWT..sysobjects where [name]='VendorItem' ) and [indid] in ( select [indid] from AWT..sysindexes where [name] like N'VendorItem%%' and [id] in (select [id] from AWT..sysobjects where [name] = 'VendorItem')) ORDER BY [indid] asc;" Return result: [ SQL2000 ] [ SQL2005 ] indid colid indid colid ------ ------- ------ ------ 1 6 1 1 1 3 1 2 1 4 1 3 1 1 1 4 1 2 1 6 2 3 2 3 2 4 2 4 2 6 2 6 2 1 2 1 2 2 2 2 Obviously, the order of first key's colid for the 2 platform are different, but the other keys behind have same return orders. So do I miss anything in the select statement or there is some settings I don't know for the 2 platforms. Any help is appreciate! Mac. How many SQL 2000 and SQL 2005 databases/tables have you used in this research?
Order is only relevant when retrieving data in SQL, and only if the ORDER BY clause is used. Is colid identity? How were the values inserted? ML --- http://milambda.blogspot.com/
Show quote
"Mac" wrote: The spacing for the return reuslt is better to read like:> I have an exact table in both SQL2000 and SQL2005. > But the return order of 'colid' for the first key is different! (only the > first key, not the others). > > My select query is: > > "SELECT [indid], [colid] FROM AWT..sysindexkeys > where [id] in ( select [id] from AWT..sysobjects where [name]='VendorItem' > ) and [indid] in ( select [indid] from AWT..sysindexes where [name] like > N'VendorItem%%' and [id] in (select [id] from AWT..sysobjects where [name] = > 'VendorItem')) ORDER BY [indid] asc;" > > > Return result: > > [ SQL2000 ] [ SQL2005 ] > indid colid indid > colid > ------ ------- ------ > ------ > 1 6 1 > 1 > 1 3 1 > 2 > 1 4 1 > 3 > 1 1 1 > 4 > 1 2 1 > 6 > 2 3 2 > 3 > 2 4 2 > 4 > 2 6 2 > 6 > 2 1 2 > 1 > 2 2 2 > 2 > > Obviously, the order of first key's colid for the 2 platform are different, > but the other keys behind have same return orders. > > So do I miss anything in the select statement or > there is some settings I don't know for the 2 platforms. > > Any help is appreciate! > > > Mac. [ SQL2000 ].key 1 (indid, colid){ (1, 6), (1, 3), (1, 4), (1, 1), (1, 2) } [ SQL2005 ].key 1 (indid, colid){ (1, 1), (1, 2), (1, 3), (1, 4), (1, 6) } [ SQL2000 ].key 2 (indid, colid){ (2, 3), (2, 4), (2, 6), (2, 1), (2, 2) } [ SQL2005 ].key 2 (indid, colid){ (2, 3), (2, 4), (2, 6), (2, 1), (2, 2) } Mac. As far as I can see the results are the same. You only order by indid in your query. If you want the
result to be ordered by colid as well, you should add that to your ORDER BY clause. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Mac" <M**@discussions.microsoft.com> wrote in message news:FC6236C2-5117-4BBF-BC4E-E0FAF1C297BB@microsoft.com... > > > "Mac" wrote: > >> I have an exact table in both SQL2000 and SQL2005. >> But the return order of 'colid' for the first key is different! (only the >> first key, not the others). >> >> My select query is: >> >> "SELECT [indid], [colid] FROM AWT..sysindexkeys >> where [id] in ( select [id] from AWT..sysobjects where [name]='VendorItem' >> ) and [indid] in ( select [indid] from AWT..sysindexes where [name] like >> N'VendorItem%%' and [id] in (select [id] from AWT..sysobjects where [name] = >> 'VendorItem')) ORDER BY [indid] asc;" >> >> >> Return result: >> >> [ SQL2000 ] [ SQL2005 ] >> indid colid indid >> colid >> ------ ------- ------ >> ------ >> 1 6 1 >> 1 >> 1 3 1 >> 2 >> 1 4 1 >> 3 >> 1 1 1 >> 4 >> 1 2 1 >> 6 >> 2 3 2 >> 3 >> 2 4 2 >> 4 >> 2 6 2 >> 6 >> 2 1 2 >> 1 >> 2 2 2 >> 2 >> >> Obviously, the order of first key's colid for the 2 platform are different, >> but the other keys behind have same return orders. >> >> So do I miss anything in the select statement or >> there is some settings I don't know for the 2 platforms. >> >> Any help is appreciate! >> >> >> Mac. > > > The spacing for the return reuslt is better to read like: > [ SQL2000 ].key 1 (indid, colid){ (1, 6), (1, 3), (1, 4), (1, 1), (1, 2) } > [ SQL2005 ].key 1 (indid, colid){ (1, 1), (1, 2), (1, 3), (1, 4), (1, 6) } > > [ SQL2000 ].key 2 (indid, colid){ (2, 3), (2, 4), (2, 6), (2, 1), (2, 2) } > [ SQL2005 ].key 2 (indid, colid){ (2, 3), (2, 4), (2, 6), (2, 1), (2, 2) } > > > Mac. > But the return order of 'colid' for the first key is different! Because the optimizer is free to choose any order it wishes (it will try to pick the most efficient way) unless otherwise constrained. Your resultset is only dictated to be ordered by indid. Why do you expect colid to obey any order, when you've basically (by not saying anything) told it to order all other columns randomly? Did you know that it is possible (though unlikely) for you to get these results in a different order on the same machine, at different times of the day or when different things are going on in the server? A table is an unordered set. SELECT * without ORDER BY is an unordered set. If you want SQL Server to consistently return results ordered by indid then by colid, use ORDER BY indid, colid. Otherwise, your result really is not all that shocking, never mind wrong. > Your resultset is only dictated to be ordered by indid. Why do you expect You forgot "phase of the moon" :-)> colid to obey any order, when you've basically (by not saying anything) told > it to order all other columns randomly? Did you know that it is possible > (though unlikely) for you to get these results in a different order on the > same machine, at different times of the day or when different things are > going on in the server? -- Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com Mac (M**@discussions.microsoft.com) writes:
> I have an exact table in both SQL2000 and SQL2005. If you want to know in which order the columns appear in the index,> But the return order of 'colid' for the first key is different! (only the > first key, not the others). you should also sort by indexkeys.keyno. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I think I figure out the reason of the differences:
the sort orders of CLUSTERED and NONCLUSTERED index are different in SQL2005 wherease SQL2000 returns the same order. Problem detail: For efficiency, the first key index is create with "CLUSTERED" key word " CREATE UNIQUE CLUSTERED INDEX [VendorItem001] ON [dbo].[VendorItem]([VendorCode], [ItemCode], [Model], [SeasonCode], [SeasonYear]) ON [PRIMARY]". All other indexes are without the key word: "CREATE UNIQUE INDEX [VendorItem002] ON [dbo].[VendorItem]([ItemCode], [Model], [VendorCode], [SeasonCode], [SeasonYear]) ON [PRIMARY]". If I create the same table and their indexes with all NONCLUSTERED, then both SQL2005 and SQL2000 are consistent! So, the next question is "why SQL2005 changes this behavior?" Mac. Show quote "Erland Sommarskog" wrote: > Mac (M**@discussions.microsoft.com) writes: > > I have an exact table in both SQL2000 and SQL2005. > > But the return order of 'colid' for the first key is different! (only the > > first key, not the others). > > If you want to know in which order the columns appear in the index, > you should also sort by indexkeys.keyno. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Mac wrote:
Show quote > I think I figure out the reason of the differences: There is no behaviour change. Didn't you read the replies you got? In> the sort orders of CLUSTERED and NONCLUSTERED index are different in SQL2005 > wherease SQL2000 returns the same order. > > Problem detail: > For efficiency, the first key index is create with "CLUSTERED" key word > " CREATE UNIQUE CLUSTERED INDEX [VendorItem001] ON > [dbo].[VendorItem]([VendorCode], [ItemCode], [Model], [SeasonCode], > [SeasonYear]) ON [PRIMARY]". > > All other indexes are without the key word: > "CREATE UNIQUE INDEX [VendorItem002] ON [dbo].[VendorItem]([ItemCode], > [Model], [VendorCode], [SeasonCode], [SeasonYear]) ON [PRIMARY]". > > If I create the same table and their indexes with all NONCLUSTERED, > then both SQL2005 and SQL2000 are consistent! > > So, the next question is "why SQL2005 changes this behavior?" > > Mac. > > ANY version of SQL Server the order returned by a SELECT statement is undefined and potentially unpredictable unless you specify an ORDER BY clause. If you never knew or never noticed this before then go back and add ORDER BY clauses wherever the ordering is important to you. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Mac (M**@discussions.microsoft.com) writes:
Show quote > I think I figure out the reason of the differences: the sort orders of Because by specifying a partial ORDER BY clause, you have give SQL Server> CLUSTERED and NONCLUSTERED index are different in SQL2005 wherease > SQL2000 returns the same order. > > Problem detail: > For efficiency, the first key index is create with "CLUSTERED" key word > " CREATE UNIQUE CLUSTERED INDEX [VendorItem001] ON > [dbo].[VendorItem]([VendorCode], [ItemCode], [Model], [SeasonCode], > [SeasonYear]) ON [PRIMARY]". > > All other indexes are without the key word: > "CREATE UNIQUE INDEX [VendorItem002] ON [dbo].[VendorItem]([ItemCode], > [Model], [VendorCode], [SeasonCode], [SeasonYear]) ON [PRIMARY]". > > If I create the same table and their indexes with all NONCLUSTERED, > then both SQL2005 and SQL2000 are consistent! > > So, the next question is "why SQL2005 changes this behavior?" the permission to do so. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics