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

Question

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

Answer 1

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.