SQL Server Performance Forum – Threads Archive
information_schema.columns
Hi all, I have 2 identical databases in a server. The following query on these databases giving the output in different sort order :select column_name from information_schema.columns where table_name = ‘mytable’ On one database the results are diplayed in alphabetical order and on the other results are diplayed in the order that the columns were created in the table. Can anyone tell me what dictates this order ? The collation sequence is same on both databases. Both of these databases were infact created by same script and almost at the same time.
Order is not guaranted unless you use order by clause. If you don’t use this clause order depends on execution plan. In your case difference could be because of more fresh statistics on system tables (just a guess).
.. and also ensure there is no column level collation defined in between these 2 database/tables/columns. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
column level collation is also same on both of these tables. I want to confirm that the order depends on the execution plan. If there is no order by clause, then the order depends on the indexes defined on the columns right ? But, being system tables both are having same indexes on same columns.
quote:If there is no order by clause, then the order depends on the indexes defined on the columns right ?
Not only on indexes. Query optimizer could choose different execution plan for different execution. Query optimizer might not use indexes at all if it finds table scan more efficient. Different connection settings could cause separate execution plans to be produced for two executions, I’ve already mentioned statistics… and so on.
If you don’t specify the ORDER BY, SQL Server will try to return the information in the quickest possible way. Among the other things already mentioned that could mean that pages that are already in the buffer might be displayed before the pages are retrieved from disk. A clustered index might work in most cases, too, but that is not guaranteed upon each execution. Having that said, ORDER BY is the only reliable way to get that sorting you expect. —
Frank
http://www.insidesql.de
]]>