SQL Server Primary Keys

I have a database table architecture question.  Is it better to design a primary key with the standard pk_id where it is an "int" or a guid?
LVL 2
CipherISAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
GUID uses 4 time more resources compared PK (int).

Generally, GUID has to be avoided unless the operation intended the database to perform requires it. More more see: http://blog.codinghorror.com/primary-keys-ids-versus-guids/

And because GUID is random, it is not suitable as cluster key thus not efficient as cluster key. And when a new GUID gets created it causes further fragmentation and causes bad performance.

Mike
HuaMin ChenProblem resolverCommented:
Hi,
Int is OK, and you can also declare it as an identity, like
CREATE TABLE new_employees
(
 id_num int IDENTITY(1,1),
 fname varchar (20),
 minit char(1),
 lname varchar(30)
)

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
First thing that you should ask is: "Do I really need a GUID?"
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
As Expert says the GUID uses more resource to store the data.

 it took little more time to read the that column data, if you have large numbers of rows.

 If you declare it as the Primary column it will create the index on it and if you select Int as a type , Indexes will be more faster and efficient.
Scott PletcherSenior DBACommented:
Int is preferred over GUID, for the reasons noted above.  But, whenever possible, a natural PK from actual data column(s) in the table is vastly preferred.

But what is absolutely critical to understand is that the PK should not automatically be the clustering index key.  The clustering key(s) are the most critical factor in table performance.  Whenever possible, use a natural clustering key that matches the data usage of the table.

For example, if a given table is always searched on state and city, cluster the table by state and city, not by some arbitrary number!  If you also want to add an identity to act as a simple PK that's fine, but it should not be the clustering key in this example.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CipherISAuthor Commented:
Thank You.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.