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
Best way to store a file size in bytes?
SQL SERVER perfomance?
How to select getdate() from subsonic?