CODEWITHSUNDEEP
sqlsql-serverforeign-keysguid
anveshtummala
anveshtummala

Reputation: 432

Having an Identity column in a table with primary key with varchar type

I was told to create an autID identity column in the table with GUID varchar(40) as the primary key and use the autID column as a reference key to help in the join process. But is that a good approach?

This causes a lot of problems like this

CREATE TABLE OauthClientInfo
(
    autAppID INT IDENTITY(1,1)
    strClientID VARCHAR(40),   -- GUID
    strClientSecret VARCHAR(40) 
)

CREATE TABLE OAuth_AuthToken
(
    autID INT IDENTITY(1,1)
    strAuthToken VARCHAR(40),
    autAppID_fk INT 
        FOREIGN KEY REFERENCES OauthClientInfo(autAppID)
)

I was told that having autAppID_fk helps in the joins vs having strClientID_fk of varchar(40), but my point to defend is we unnecessarily adding a new id as a reference that some times forces to make joins.

Example, to know what is the strClientID that the strAuthToken belongs, if we have strClientID_fk as the reference key then the OAuth_AuthToken table data make sense a lot for me. Please comment your views on this.

Upvotes: 0

Views: 1222

Answers (3)

SMor
SMor

Reputation: 2862

I was told to create an autID identity column in the table with GUID varchar (40) as the primary key and use the autID column as a reference key to help in the join process. But is that a good approach?

You were told this by someone that confuses clustering and primary keys. They are not one and the same, despite the confusing implementation of the database engine that "helps" the lazy developer.

You might get arguments about adding an identity column to every table and designating it as the primary key. I'll disagree with all of this. One does not BLINDLY do anything of this type in a schema. You do the proper analysis, identify (and enforce) any natural keys, and then you decide an whether a synthetic key is both useful and needed. And then you determine which columns to use for the clustered index (because you only have one). And then you verify the appropriateness of your decisions based on how efficient and effective your schema is under load by testing. There are no absolute rules about how to implement your schema.

Many of your indexing (and again note - indexing and primary key are completely separate things) choices will be affected by how your tables are updated over time. Do you have hotspots that need to be minimized? Does your table experience lots of random inserts, updates, and deletes over time? Or maybe just lots of inserts but relatively few updates or deletes? These are just some of the factors that guide your decision.

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

As far as I have read, Auto increment int is the most suitable column for clustered index.

And strClientID is the worst candidate for PK or cluster index.

Most importantly you haven't mention the purpose of StrClientID. What kind of data does it hold, how does it get populated?

Upvotes: 0

Bill Gauvey
Bill Gauvey

Reputation: 105

You need to use UNIQUEIDENTIFIER data type for GUID columns not VARCHAR

Upvotes: 0

Related Questions