Post reply

Question on primary keys and index with included columns

  • April 5, 2019 at 4:03 pm

    #2870941

    In a table with two columns (Id int, Name varchar(100)), is there any performance difference between:

    1. A clustered primary key on Id

    and

    2. An index on Id that includes the Name column?

    The table is used in queries that lookup the Name by joining other tables using the Id column.

    • This topic was modified 5 years, 7 months ago by  Sam-263310. Reason: improve grammar

  • April 5, 2019 at 6:21 pm

    #2877609

    No, with the exception that the primary key is unique and your other index is not.

    Other than that, they'll work identically.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 5, 2019 at 8:40 pm

    #2884713

    I'd say it depends on the operation being performed on the table. A SELECT statement should be fairly close in performance, because both index options contain all the necessary data. In the case of a non-clustered index, you are taking up twice as much space though, once for the heap that is the table itself and once more for the non-clustered index that has all the columns of the table. INSERT and UPDATE statements may also be slower because it has to make each write in 2 places.

Viewing 3 posts - 1 through 2 (of 2 total)

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