Please explain... surrogate key, PK

My question relates to the bold portions of the first sentence below. I am stuck at the first sentence.

"The storage requirements for your surrogate key can have a cascading effect if your clustered index is defined on the same key columns (the default for a primary key constraint)."
"The storage requirements for your surrogate key can have a cascading effect if your clustered index is defined on the same key columns (the default for a primary key constraint)." The clustered index key columns are used by all nonclustered indexes internally as the means to locate rows in the table. So if you define a clustered index on a column x, and nonclustered indexes—one on column a, one on b, and one on c—your nonclustered indexes are internally created on column (a, x), (b, x), and (c, x), respectively. In other words, the effect is multiplied."

Open in new window


Q1: surrogate key: When there is no candidate key to use as primary key, a surrogate key is added to function as PK, correct?

Q2: What dose same key columns means here? Please explain possibly using the Table1 below.

Q3: What default for a primary key means in our example below?

Table1 (note ID is surrogate, identity column added to this table to be uses as PK)
=============================
ID    Col1   Col2    Col3
----  ------      ------     -----
1
2
3
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
Brian CroweDatabase AdministratorCommented:
I basically did and actually the rest of the text that you posted expounds on it pretty clearly.  I'm not sure what the context for it is which makes it harder to interpret but basically it seems to be saying that using a surrogate key instead of a natural key can increase the storage requirements of your table if you create additional indexes on the natural key columns since the primary key is always included in any non-clustered indexes.
0
 
Brian CroweDatabase AdministratorCommented:
A surrogate key is an alternate value used to uniquely identify a record in a table.  Often this is an IDENTITY column.  Surrogate just means that it is a value non-significant to the record itself.  For instance although a social security number should uniquely identify a person it would still make sense to use a surrogate key as the primary key for a person table and by extension as the foreign key in any referencing tables.  This would allow you to change a person's SSN without having to cascade that value to other tables.

The primary key, whether it is a single 4-byte integer or 3 string columns, is included in any non-clustered indexes you create on a table which can significantly impact the footprint of that table depending on the number of indexes.

Hope this helps
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Brian,

Thank you for the comment but my focus was the words in bold fonts which you have not addressed in your comment.

Q1: surrogate key: When there is no candidate key to use as primary key, a surrogate key is added to function as PK, correct?

Q2: What dose same key columns means here? Please explain possibly using the Table1 below.

Q3: What default for a primary key means in our example below?

On the definition of surrogate key I suppose I have it right. It is re-stated here as a starting  point to continue with Q2, and Q3.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Can you please rewrite the following sentence stating the same thig:

"The storage requirements for your surrogate key can have a cascading effect if your clustered index is defined on the same key columns (the default for a primary key constraint)."
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Obviously I have problem understanding English.  The rest of the text I included for reference only but my focus was the very first sentence.

Avoiding any reference to memory space usage or index etc. I thought with a different way of saying what the following is saying will help me to understand this sentence. If not, then I will re-read your post couple of times until I get it.

"The storage requirements for your surrogate key can have a cascading effect if your clustered index is defined on the same key columns (the default for a primary key constraint)."

Thanks,

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.