0

Perfomance-wise, is it the same to query a table on a non-unique GUID in these two cases?

Case 1: Primary key on multiple columns while the significant column is the first item in the primary key

CREATE TABLE xyz (
    a uniqueidentifier NOT NULL,
    b uniqueidentifier NOT NULL,
    c uniqueidentifier NOT NULL,
    CONSTRAINT [PK_xyz] PRIMARY KEY (a, b, c) CLUSTERED
)

Case 2: Clustered non-unique index on the column I will use the most to query the table

CREATE TABLE xyz (
    a uniqueidentifier NOT NULL INDEX IX_xyz_a CLUSTERED,
    b uniqueidentifier NOT NULL,
    c uniqueidentifier NOT NULL,
    CONSTRAINT [PK_xyz] PRIMARY KEY (a, b, c)
)

Most of the time my table would be queries as SELECT ... FROM xyz WHERE a = ...

1
  • "non-unique GUID in these two cases?" ... "Case 1: Primary key" ?...is the GUID, or at least the other GUID columns in combination, unique then or not?
    – J.D.
    Jun 15, 2023 at 3:42

1 Answer 1

1

The SELECT will be very close in cost.

In both cases you'll seek through the BTree to the first row with that value for a, and scan the leaf level of the clustered index to find all the other rows for that value of a. In both cases all the rows for a value of a will be contiguous.

But case 2 will be larger and slower to update as it has two index structures instead of just one.

2
  • So you are saying if anything, the first case is better overall and not making my SELECT FROM WHERE a = any slower, right?
    – Bishoy
    Jun 15, 2023 at 2:29
  • 1
    Yes. Similar SELECT performance. Fewer indexes. Jun 15, 2023 at 2:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.