0

I have a table in SQL Server containing some user related info where the primary key is id (auto increment by 1) and has a column named userId. Each user can only has one record in the table, so I have added a unique constraint on column userId. As per SQL Server docs, SQL Server will automatically create an index for the unique constraint column.

For the usage on the table, there can be many update and insert operations, as well as select operations, and that's where my questions arise.

I see that the index that got created automatically by SQL Server on the unique constraint column is a non-clustered index, where it is good for update and insert operations, but for select operation, it is not as fast as the clustered index. (ref. differences-between-a-clustered-and-a-non-clustered-index)

  1. For this table, there can be many select by userId operations. From the performance perspective, should a clustered index on userId be created, given that clustered index is the fastest for read operations ?

  2. If yes, but a non-clustered index has already been automatically created on column userId, could a clustered index still be created on the userId column? (I have found some similar question, from the answers, it seem like if doing so, it will first search through the non-clustered index, then it will points to the clustered index and continue that search non-clustered-index-and-clustered-index-on-the-same-column)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
once
  • 536
  • 7
  • 21
  • Define the column as the `primary key` (which apparently it is) and SQL Server will use that as the clustered index. – Gordon Linoff Sep 04 '21 at 14:24
  • 4
    By default the primary key, in your case the auto-incremented id, is clustered. This column is doing you no good. Delete the id, make userId the primary key and it will then be clustered. – Jonathan Willcock Sep 04 '21 at 14:29
  • I agree with recommendation by @JonathanWillcock. However, if you do use the `ID` primary key column too (e.g. as a surrogate key), you can specify the primary key as non-clustered and the unique constraint as clustered. There is no requirement the PK be clustered. – Dan Guzman Sep 04 '21 at 14:39
  • One thing that I forgot to point out. By definition only one index on a table can be clustered (since the records are stored on disk in the order of the clustered index); all others are non-clustered. Also the clustered index (if you have one - it is not a requirement) of necessity is unique. You should therefore choose your clustered index to be that unique index, which will be of most practical use. This is usually the index most used for `SELECT`, but it could be the index most used for `UPDATE` depending on your table usage. – Jonathan Willcock Sep 04 '21 at 15:20
  • @JonathanWillcock There is no requirement for a clustered index to be unique. – SMor Sep 04 '21 at 16:13
  • 1
    Clustered and non-clustered indexes have no performance differences for `select` when defined on the same columns. Commonly, NCI's don't contain `INCLUDE` columns (though they usually should) and that is where the difference can show up, because CI's include all columns – Charlieface Sep 04 '21 at 21:06
  • 2
    @SMor Depends how you define that. The physical index itself must be unique, but if your index definition is not unique then SQL Server automatically adds an internal uniqueifier – Charlieface Sep 04 '21 at 21:11
  • There is no requirement for a clustered index to be unique. `create table dbo.Test (ID int not null); create clustered index ix_Test_ID on dbo.Test(ID); insert dbo.Test (ID) values (1), (1), (1); select * from dbo.Test;` – AlwaysLearning Sep 05 '21 at 00:43
  • @Charlieface So again - any index can be clustered. There is no requirement or restriction on what that index can contain. The engine will "unique-ify" it for implementation purposes but that is mostly irrelevant at this level of discussion. – SMor Sep 05 '21 at 18:01

1 Answers1

1

Assuming your table was created in the following manner:

CREATE TABLE dbo.users
(
  id int identity(1,1),
  userId int,
  userName varchar(100),
  emailAddress varchar(100),
  constraint PK_dbo_users primary key (Id)
);

alter table dbo.users
add constraint UNQ_dbo_users_userId UNIQUE(userId);

... then you already have a clustered index on "id" column by default.

A table can only have one clustered index, as Jonathon Willcock mentioned in the comments. So you cannot add another clustered index to userId column.

You also cannot recreate the clustered index to switch it to the userId column, as the constraints must much the existing constraint. Also, assuming there are foreign key references involved from other tables, you would have to drop the foreign keys before you can drop the users table.

Another option is to create a nonclustered covering index with an INCLUDE clause that contains all the columns needed for your query. This will avoid key lookups in the query plan.

For example:

create nonclustered index IX_dbo_users
on dbo.users (userId) include (id, userName, emailAddress);

Whether the PK and/or clustered index should be on userId or Id column depends on your users queries. If more queries, or more important queries, rely on "id" having clustered index, then keep it. Etc.

But if your table does not already have a clustered index, then yes, add it on userId column.

John.Poston
  • 21
  • 5