question

rajeshyadav-0048 avatar image
0 Votes"
rajeshyadav-0048 asked Cathyji-msft commented

clustered vs unique non clustered

I have a query that is fully covered by a unique non clustered index,

I want to know that is this unique non clustered index is going to give me the benefit of the ordered clustered index.

or is there any cavitate, because I have heard that clustered index is ordered. at the same time, I noticed that u can order the key columns in non clustered index also.

so what is the difference as far as the ording of clustered and unique non clustered index is concerned?

yours sincerely


sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @rajeshyadav-0048,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Both clustered indexes and non-clustered indexes are ordered.

There are two advantages with a covering non-clustered index, both coming from the fact that all data that is needed can be read from the NC index alone:

  1. No need for key lookups. If you need read many rows the key lookups can be expensive.

  2. In case of a scan, the NC index is typically cheaper to scan, because it is smaller. (The exception is if the NC index includes all columns in the table, in which case it's on par with the clustered index.)

I guess that by "cavitate", you mean caveat. Yes, there is the general caveat: they add overheads to updates.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

If by ordered - you mean the results are ordered, then that is incorrect. To get ordered results from a query - you must specify an ORDER BY in that query.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @rajeshyadav-0048,

Clustered index and unique non clustered index are ordered.

You can sort the records and store clustered index physically in memory as per the order. Allows you to stores data pages in the leaf nodes of the index.

A non clustered index contains the ordered data for the columns specified in that index, with pointers that tell us where to go to find the rest of the data from that row.

Suggest you read below blogs to get more information.

Clustered vs Non-clustered Index: Key Differences with Example
Clustered vs Nonclustered: What Index Is Right For My Data?


If the response is helpful, please click "Accept Answer" and upvote it, thank you.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.