CODEWITHSUNDEEP
sql-serverclustered-index
Charles Bryant
Charles Bryant

Reputation: 1015

SQL Server rows not in order of clustered index

I have a table that has a clustered index on the id

[SomeID] [bigint] IDENTITY(1,1) NOT NULL,

When I do 

select top 1000 * from some where date > '20150110'

My records are not in order

When I do:

select top 1000 * from some where date > '20150110' and date < '20150111'

They are in order?

Index is :

 CONSTRAINT [PK_Some] PRIMARY KEY CLUSTERED 
(
    [SomeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I have never come across this before, does anyone have an idea of what is happening and how I can fix this.

Thanks

Upvotes: 3

Views: 627

Answers (1)

juergen d
juergen d

Reputation: 204756

You can't rely on an order if you do not specify one. Add an order by clause.

Otherwise the DB will just grab the result as fast as possible and that is not always in the order of the index.

Upvotes: 2

Related Questions