Implementing Surrogate Key

Question

Post reply

Implementing Surrogate Key

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
August 2, 2012 at 3:49 am

#392875

How to implement surrogate key ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

shahsn11 (8/2/2012)
How to implement surrogate key ?

CREATE TABLE MyTable (

MyTableID INT IDENTITY(1,1) PRIMARY KEY,

.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 2

So does that mean that Surrogate Key is only the concept. And in lay man terms(i.e. mine) can we say that PK=Surrogate Key.

If the above is true that what is the difference between Pk and surrogate key.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

shahsn11 (8/2/2012)
So does that mean that Surrogate Key is only the concept. And in lay man terms(i.e. mine) can we say that PK=Surrogate Key.
If the above is true that what is the difference between Pk and surrogate key.

A surrogate key is by defination meaningless, aka a simple integer with no meaning attached which serves as a primary key. A primary key is a combination of one or more columns that uniquely identify a row. So it's possible this is for example a customerID, such as AW001578. This has meaning, as it is customer number 1578 of the Adventure Works company. Or it can be an emailaddress to uniquely identify a person. This is often called the "natural key".

So, a surrogate key is a primary key, but a primary key is not necessarily a surrogate key.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

shahsn11 (8/2/2012)
So does that mean that Surrogate Key is only the concept. And in lay man terms(i.e. mine) can we say that PK=Surrogate Key.
If the above is true that what is the difference between Pk and surrogate key.

A PK is a means of uniquely identifying a row. It's either an attribute(s) of the data (natural key), or it can be generated by the RDBMS (surrogate key). Here's a nice article to get you started.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden