clustered vs unique non clustered

rajesh yadav 171 Reputation points
2021-03-27T09:03:00.507+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,485 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-03-27T09:45:37.203+00:00

    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.

    0 comments No comments

  2. Jeffrey Williams 1,886 Reputation points
    2021-03-27T16:07:04.003+00:00

    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.

    0 comments No comments

  3. CathyJi-MSFT 21,061 Reputation points Microsoft Vendor
    2021-03-29T09:55:40.323+00:00

    Hi @rajesh yadav ,

    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.

    0 comments No comments