-3
SELECT * FROM project_table ORDER BY EmployeeName DESC;

I want to update my table with this query. Apparently this only shows the result. I want to update my table permanently with ORDER BY DESC.

Filburt
  • 17,626
  • 12
  • 64
  • 115
Nishanth
  • 1
  • 3
  • 1
    Why? That's what `ORDER BY` is for when doing a `SELECT`. – squillman Apr 11 '22 at 15:05
  • 2
    There is no "permanent" order to your table. If you want to view your data this way, use an `ORDER BY` clause. – Tim Biegeleisen Apr 11 '22 at 15:05
  • 2
    In relational databases **table rows do not have inherent ordering**. You can specify the ordering on each SELECT query; in the absence of an `ORDER BY` clause the engine can provide the rows in any order, and this order can change over time without notice. – The Impaler Apr 11 '22 at 15:05
  • 2
    You can't. A `SELECT` rowset is intrinsically unordered if there is no `ORDER BY`, regardless of the table. Stick this query in a view and use that instead. – Jeroen Mostert Apr 11 '22 at 15:05
  • Select query only does show the result doesn't reflect in my table. I want it updated once and for all by descending order. – Nishanth Apr 11 '22 at 15:07
  • Is there a way to update the table instead of just view it? – Nishanth Apr 11 '22 at 15:07
  • Generally speaking, SQL table does not have a *natural* order. – PM 77-1 Apr 11 '22 at 15:08
  • SQL Server doesn't work that way. You can't tell it to store data in a table in a specific order. If you do this update then you'll, at some point, still receive unordered results if you do a `SELECT * FROM project_table`. – squillman Apr 11 '22 at 15:08
  • @Nishanth No. Relational tables do not work like Excel sheets or flat files. They don't have ordering. – The Impaler Apr 11 '22 at 15:09
  • 1
    There is nothing to reflect in your table. The only way to retrieve the table's results is through `SELECT`, and if there is no `ORDER BY` there, you'll get back the rows in any order that is convenient for the engine. Notably, you *cannot* enforce the order used in that case through the table alone. It will *often* appear to be the order of the clustered index, but depending on this would be a grave mistake: as soon as page splits, parallelism or even scans from other queries are introduced, all bets are off. `ORDER BY`, always. A view can be used to avoid having to specify this all the time. – Jeroen Mostert Apr 11 '22 at 15:10
  • So the only way is to use select+ order by query and copy and store it in another table? – Nishanth Apr 11 '22 at 15:11
  • 2
    No, you're not understanding what we're telling you. The only way is to forget that you want to store your data ordered (because you can't) and instead ALWAYS use `ORDER BY` in your `SELECT`. – squillman Apr 11 '22 at 15:13
  • Read on SQL Server's [`clustered index`](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15). – PM 77-1 Apr 11 '22 at 15:13
  • Please read the comments carefully, over and over if you have to. Copying data to a new table will not help, because that other table *has no intrinsic order either*. Depending on this is going to break, if not today, then somewhere in the future. – Jeroen Mostert Apr 11 '22 at 15:14

1 Answers1

-1

Normaly you should not force a custom sort order on a table because that could impact performance. And in addtion sql server never guarantees you a sorted resultset except when you use ORDER BY ....

You could use a clustered index to force a custom sort order. But even that is not a guarantee that a SELECT * FROM table will give you sorted result set.

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15

But I would not recommend to use clustered indexes to do anything like that. clustered indexes are great to be able to join tables together in an efficient way.

If you need the data in sorted order you should build a table valued function that returs sorted order or build a stored procedure that returns a sorted result.

Johannes Krackowizer
  • 621
  • 4
  • 16