0

I have a table with two columns c1 and c2 both are integer. I have to select top 500 latest records with out order by desc. Table has more than 50 million records.

  1. c1 is clustered index with "order by asc".
  2. Cannot remove the clustered index done for c1 since it has too many dependencies.
  3. I need to sort the table in descending order of c2.
  4. So when I write a select query with out "order by desc" it should sort in descending order of c2..

Any possible way to achieve this.

manu vijay
  • 367
  • 3
  • 13
  • @ZoharPeled this is a different question – manu vijay Feb 13 '19 at 11:43
  • It might be a different question but the problem is exactly the same - as well as the solution - Without an order by clause there is no way to guarantee the order of the rows returned from a select statement. This is the bottom line, and it is reflected in both the answer provided to this post by TT as well as in the answer provided to the dupe post by yours truly. – Zohar Peled Feb 13 '19 at 11:45
  • @ZoharPeled I am trying to select top 500 latest record . Order by desc is causing performance issue. So I am checking for any alternate solution to get latest record with out using order by in select statement – manu vijay Feb 13 '19 at 11:56
  • 1
    Can't you simply add an index on c2 descending? That should help performance issues for such a select statement. – Zohar Peled Feb 13 '19 at 12:02
  • So if I add non clustered index in c2 desc can I avoid order by desc in select statement? – manu vijay Feb 13 '19 at 12:06
  • No, you can't avoid adding the `order by` in your select statement, but the sorting operation should be able to use that index and therefor have a better performance then without it. – Zohar Peled Feb 13 '19 at 12:34
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188375/discussion-between-manu-vijay-and-zohar-peled). – manu vijay Feb 14 '19 at 04:00
  • I don't see what's to chat about here. – Zohar Peled Feb 14 '19 at 09:19

1 Answers1

2

There is no way to achieve this with certainty, as a SQL table is an unordered collection of rows. Any ordering without mentioning an ORDER BY clause is incidental and can change arbitrarily.

For reference, from SELECT - ORDER BY Clause (Transact-SQL):

The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

TT.
  • 15,774
  • 6
  • 47
  • 88
  • Yes this is right. – Suraj Kumar Feb 13 '19 at 11:29
  • Is it possible to sort in desc by default using non clustered index on c2 – manu vijay Feb 13 '19 at 11:31
  • @manuvijay No, and this is by definition. – TT. Feb 13 '19 at 11:32
  • For clustered index if I set order by desc will it order in desc by default? – manu vijay Feb 13 '19 at 11:33
  • @manuvijay No, It's not. Using an order by clause is the only way to guarantee the order of the rows returned from a select statment, in any relational database, not only in SQL Server. – Zohar Peled Feb 13 '19 at 11:34
  • @manuvijay You get the ordering exactly as you specify in the ORDER BY clause, irrespective of any indexes on the table whether clustered or not. – TT. Feb 13 '19 at 11:34
  • Since I have 100 million records for selecting latest record if I use order by desc this affects the performance. – manu vijay Feb 13 '19 at 11:36
  • When we give clustered index order by ID desc this will store data physically in that order does it? – manu vijay Feb 13 '19 at 11:37
  • @manuvijay Read this thread on Stack Overflow, which covers this: [SQL Server indexes - ascending or descending, what difference does it make?](https://stackoverflow.com/q/743858/243373). – TT. Feb 13 '19 at 11:38
  • @manuvijay The physical rows will be stored on disk (or memory) in the order as specified by the clustered index if present. But this is "under the hood" so to speak. It has no bearing on the actual order of rows returned by a select statement without an order by clause. – TT. Feb 13 '19 at 11:40
  • The order of the physical storage of the data has no baring on the the order of the rows in the select statement. The rows have to be physically stored on the disk is some order - but that doesn't mean that the logical concept of a table has an inherit order - it doesn't. Rows in a relational database are unsorted by nature. As a result - a select statement without an order by clause will return rows in an arbitrary order (Please note that arbitrary is not the same as random). – Zohar Peled Feb 13 '19 at 11:42