3

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

Charles Bryant
  • 995
  • 2
  • 18
  • 30
  • 3
    The only (really the ***only***) way to guarantee a specific order of the result is to use `ORDER BY`. If you don't use it, the DB is free to return the rows in any order. –  Jan 15 '15 at 10:28
  • I tripped over this when I added second index. See also http://stackoverflow.com/a/6936967/10245 – Tim Abell Feb 16 '15 at 10:52

1 Answers1

2

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.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • I have never seen this happening before. I thought a clustered index stores and sorts the data by the key, in this case being the id. http://msdn.microsoft.com/en-gb/library/ms190457.aspx So naturally the order you select from the db would be the order in which they are stored? – Charles Bryant Jan 15 '15 at 10:31
  • 1
    @CharlesBryant The stored data is sorted by the clustered index but, what do you think will happens if the database engine decides to parallelize the query execution? You'll have unsorted results. – dario Jan 15 '15 at 10:40
  • 1
    Additionally, if your table has some other indices, SQL engine may use these and return data in their order - looking at your query, you may have index on date field, which is likely not used in first case. Like many people have noted already - you must always use ORDER BY. – Arvo Jan 15 '15 at 11:02