SQL Server 2005: natural sort order for 2-columns tables with unique constraint


SQL Server 2005: natural sort order for 2-columns tables with unique constraint



I have this table in my SQL Express 2005 database:

CREATE TABLE [dbo].[test_sort_order](     [Col1] [int] IDENTITY(1,1) NOT NULL,     [Col2] [nchar](50) COLLATE French_CI_AS NULL,  CONSTRAINT [PK_test_sort_order] PRIMARY KEY CLUSTERED  (     [Col1] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],  CONSTRAINT [UQ_test_sort_order] UNIQUE NONCLUSTERED  (     [Col2] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] 

When running a flat SQL query against it (typically SELECT * FROM test_sort_order) I get a resultset ordered according to the 2nd column (Col2)

I would expect it to be (naturally) ordered against the PK column (which as a matter of fact is the order of insertion of the records) In fact, if you remove the unique constraint from Col2, it is what you get.

And things are getting even more mysterious when you keep the unique constraint and add a 3rd column (any type) to this table since the result of a SELECT * is then sorted according to the natural order.

Any idea why such a behaviour? And how to get rid of it. Thanks




Attached SQL 2000 DB in a SQL 2005 Server, Now getting VB6 App throwing errors

1:



Best way to store a file size in bytes?
SELECT is a set-oriented operation and, as sets are unordered, no specific order in the resultset is guaranteed, unless you explicitly use ORDER BY..
SQL SERVER perfomance?


How to select getdate() from subsonic?


74 out of 100 based on 44 user ratings 194 reviews