Menu
Will my select * query always return the rows from my database table in the same order?
My table has a 'clustered index' on one column. Does that change the answer?
396
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
Remember that the clustered index is the physical order in which the table is stored on disk. So if your clustered index is defined as ColA, ColB queries will be faster when order in the same order as your clustered index. If SQL has to order B,A it will require post execution sorting to achieve the correct order.
No, that order doesn't matter (or at least: shouldn't matter). Any decent query optimizer will look at all the parts of the WHERE clause and figure out the most efficient way to satisfy that query.
The column ordering in a composite Non-Clustered Index matters – when you have to perform Range Scans! As I'm always saying in my various workshops: almost everything in SQL Server is about Indexing, and Indexing itself is just presorting data!
The order of the returned rows will not always be the same unless you explicitly state so with the ORDER BY clause. So, no.
And no; just because your 1000 queries have returned the same order it's no guarantee the 1001th query will be in the same order.
72
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
