is there a way to have a table sorted automatically?

Question

is there a way to have a table sorted automatically?

water490

Ten Centuries

Points: 1112
More actions
June 30, 2025 at 9:29 pm

#4615723

Hi Everyone
I have one table that has a unique requirement to be sorted in order for the table to have any value. I remember the sort order now but in the future I may forget the order and have to spend time to figure out why the data doesn't look right in the table. It would be great if there is a way to ask SS to automatically sort the table each time a "select * from dbo.Table" is called. I don't want to specify the ORDER BY clause because I may not remember it in the future. Is this possible? If yes, how would I do it?
Thank you

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16237 More actions · Answer 1

no way - while a clustered index on the desired columns will in many cases output the data on that order, there are cases where it won't.

if you have issues with remembering which order some rows should be (which is determined by the ORDER BY clause) then keep that on an excel workbook - if you forget just go there and check it.

water490 Ten Centuries Points: 1112 More actions · Answer 2

i just tried the clustered index approach. it worked. thank you for this!!

This reply was modified 2 weeks, 4 days ago by water490.

DesNorton SSC-Insane Points: 24539 More actions · Answer 3

A clustered index may appear to retain the sort order. However, there is zero guarantee that it will always be sorted correctly.

The ONLY way to guarantee sort order is to use ORDER BY.

You can create a view that sorts correctly.

CREATE VIEW dbo.MyView
AS
SELECT Field1, Field2, ...., Fieldn
FROM dbo.MyTable
WHERE ...
ORDER BY ...
GO

SELECT * FROM dbo.MyView;

How to post data/code on a forum to get the best help.

Ken McKelvey SSCoach Points: 18927 More actions · Answer 4

I think a view with an ORDER BY needs TOP in the SELECT.

Also, selecting from a view with an ORDER BY does not guarantee order of the result. An ORDER BY on the view is also needed.

Grant Fritchey SSC Guru Points: 399183 More actions · Answer 5

Ken McKelvey wrote:

I think a view with an ORDER BY needs TOP in the SELECT.
Also, selecting from a view with an ORDER BY does not guarantee order of the result. An ORDER BY on the view is also needed.

Actually, neither of these accurate. Here's some sample code:

--use AdventureWorks to test this
--unsorted view
CREATE VIEW UnsortedData
AS
SELECT a.PostalCode
FROM Person.Address AS a;
GO

--query against unsorted view
SELECT ud.PostalCode
FROM UnsortedData AS ud
ORDER BY ud.PostalCode DESC;

You'll note that the view is not sorted. I then sort the data from the view. No TOP included. This works because a view is just a query, nothing more. Querying a view is just querying a query. We could rewrite the view as a sub-select or even a CTE and it would behave exactly the same way. As to the TOP, you got it backwards. To ensure order when running a TOP query, you must have an ORDER BY. That's how that works, not requiring a TOP if you have an ORDER BY. Clearly, you can run a query without a TOP and still use an ORDER BY. And, for this query, the Execution plan shows how it resolves everything:

2025-07-01_06-49-31

As to the original question, a nonclustered index also stores data in order of the key (with the same proviso from DesNorton). If you needed to have a whole table stored in two different orders, an INCLUDE statement with all the columns for the table would effectively create two clustered indexes for a given table. And yes, with attendant overhead of needing to maintain that index. TANSTAAFL always applies.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ken McKelvey SSCoach Points: 18927 More actions · Answer 6

Grant Fritchey wrote:

As to the TOP, you got it backwards.

I have just tried to create a view with an ORDER BY in SQL 2019 and got the following:

Msg 1033, Level 15, State 1, Procedure Test, Line 41 [Batch Start Line 9]

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

While I cannot find the reference, I am almost 100% sure that the ORDER BY clause needs to be in the outermost statement to be guarenteed. ie For the following:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE VIEW Test
AS 
SELECT TOP 100 PERCENT *
FROM YourTable
ORDER BY YourOrderCol;
GO

using

SELECT * FROM Test;

does not guarentee the row order of the result but:

SELECT * FROM Test ORDER BY YourOrderCol;

does guarentee the row order of the result.

dmbaker SSCertifiable Points: 5143 More actions · Answer 7

Why not use a stored procedure? Would give you the flexibility to sort any way you want, and you can name the procedure something that indicates how the data is sorted (so you won't forget).