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!

Shnugo
  • 66,100
  • 9
  • 53
  • 114

1 Answers1

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.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • 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 Oct 08 '15 at 09:19
  • @Shnugo:- So you mean you have a primary key and you are creating unique clustered index on some other column? – Rahul Tripathi Oct 08 '15 at 09:21
  • @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. – Rahul Tripathi Oct 08 '15 at 09:26
  • 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 Oct 08 '15 at 09:28
  • @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. – Rahul Tripathi Oct 08 '15 at 09:32