0

In one of our tables there is an existing non-clustered Primary Key on a UNIQUEIDENTIFIERcolumn, heavily used in FKs.

We now want to add an IDENTITY column and create a unique clustered index for this.

I do not need an explanation of clustered vs. non-clustered or about "what is a primary key?".

I want to

  1. drop all indexes
  2. add the new column with IDENTITY
  3. create the unique clustered index on the new column
  4. re-create all indexes

My question are:

  1. Will the new clustered index be used as look up key?
  2. Will the Primary Key use the clustered key?
  3. What implications could be, using a clustered key and a non-clustered PK side by side?

Thank you!

Improve this question

1 Answer 1

Reset to default
1
  1. Will the new clustered index be used as look up key?

Yes the clustered key will be the look up key.

  1. Will the Primary Key use the clustered key?

Yes primary key always has a clustered index attached to it.

  1. What implications could be, using a clustered key and a non-clustered PK side by side?

This depends on the usage of your table and the associated columns.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Could it be, that you missed the point? I do have a primary key which I want to keep. The clustered index would be a new index... Btw: A PK does not always have a clustered index attached to it. This is just the default behavior.
@Shnugo:- So you mean you have a primary key and you are creating unique clustered index on some other column?
@Shnugo:- Ok so my answer still stands valid as far as I think as clustered index affects the order in which records are stored on disk.(And I am hoping that your primary key doesnt have the clustered index attached as there can be only 1 clustered index on the table.) While look up the clustered index will be used.
If you re-read my question you'll see, that the existing PK is non-clustered. The main point is: Will the optimizer look for a clustered index for lookups in any case or will it first look for an PK even if it is non-clustered?
@Shnugo:- Yes look at my earlier comment, I have already said, While look up the clustered index will be used. While looks up the clustered key index will be used instead of the primary key.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.