I am trying to write an efficient query for deleting chunks of data. To this end I hoped to avoid an index scan by using the primary key to get the oldest records. However I'm seeing some unexpected results returned.
I hoped this
SELECT TOP 15 OrderID FROM [Order]
Would give me the oldest 15 records because I could rely on the primary key incrementing and therefore the order of storage would be low to high in the table.
However, this returns a different result set
SELECT TOP 15 OrderID FROM [Order] ORDER BY DateCreated ASC
Which seems to be a more accurate but more expensive way of getting the result I need.
Confusingly, this
SELECT TOP 15 * FROM [Order]
Gives a different set of OrderID s (PK) to this
SELECT TOP 15 OrderID FROM [Order]
I understand that http://msdn.microsoft.com/en-gb/library/ms189463.aspx explains that order cannot be guaranteed without an ORDER BY clause but expected the PK to order for me and can't explain the differences between the final two select clauses.
CREATE TABLE
as well as all indexes so that we can fully understand the current structure of the table, most importantly the clustered index. Also, what is OrderID? An IDENTITY column? Can't you just ORDER BY OrderID instead? – Aaron Bertrand Sep 10 '14 at 15:41