Search code examples
sqlsql-servertablesorter

How To Prevent Automatic Sorting On Primary Key in SQL Server


I want to save my data in table order by time that they insert, but in SQL Server they will order by primary key.

What can I do ?


Solution

  • So there are two factors here which you may be confounding; the physical ordering of the data (i.e how your computer literally prints the bits onto your hard drive) and the logical ordering.

    The physical ordering is always defined by your clustered index. 9 times out of 10, this also happens to be your primary key, but it doesn't have to be. Think of "Clustered Index" as "How it's physically sorted on my disk". You want it stored a different way, change how you "cluster" it (by the way, if you don't have a clustered index, that's called a "heap", and it doesn't guarantee the physical order of the data).

    The logical ordering however, does not exist. SQL is based on relational algebra, and relational algebra operates on mathematical sets. The definition of a set contains no information about the inherent ordering of the set, only it's constituents.

    Now when you query data out, you can specify an order by which will return the data to you in the order you asked for. However by default, there is no guarantee or assumption of any ordering (even that of how it's physically stored on disk; even if it happens to return in that order from time to time).