How to convert int primary key to uniqueidentifier

Question

Post reply

How to convert int primary key to uniqueidentifier

PA

SSC-Addicted

Points: 403
More actions
July 3, 2010 at 6:24 pm

#220837

Hi,
I have implemented a database in SQL-server 2008. In all tables, I have set all the primary keys to int. (Still, there is no data in database.)
I want to convert the primary key's type to uniqueidentifier. SQL-Server doesn't let me convert the type.
Please advice me!
Thanks,

Viewing 15 posts - 1 through 15 (of 16 total)

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

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 1

~~My advice would be.... don't do it.~~

UPDATE:

The advice above is incorrect. Please see my post below at

https://www.sqlservercentral.com/forums/topic/how-to-convert-int-primary-key-to-uniqueidentifier#post-3946514

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PA SSC-Addicted Points: 403 More actions · Answer 2

Dude, have u understood what I was looking for? I guess you didn't get my question, read more!!:hehe:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why, why, why do you want uniqueidentifiers? My advice is safe as Jeff's. Don't.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 4

Joe Celko (7/3/2010)
You have no idea what you are doing. You want to mimic 1950's pointer chain file systems In SQL and have no idea what an RDBMS really is. Stop programing; you are dangerous and ignorant.
If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?" And I like to remind them that it takes six years to become a Journeyman Union Carpenter in New York State. Not Master, Journeyman.

Why do you always have to be so bloody nasty, Joe? You're a really smart cookie but most folks just don't like you because you flame people all the time. Yeah... I agree that sugar coating stuff is sometimes not the way to go but you just don't have to take it to the level you always take it to. You will do more good for people by convincing them through code and by example instead of telling them that they're ignorant and they should stop programming. Be an inspiration for a change.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 5

azadpeyma.parham 32837 (7/3/2010)
Hi,
I have implemented a database in SQL-server 2008. In all tables, I have set all the primary keys to int. (Still, there is no data in database.)
I want to convert the primary key's type to uniqueidentifier. SQL-Server doesn't let me convert the type.
Please advice me!
Thanks,

Howdy... sorry for my previous short answer.

Whether you agree with Celko or not about INT, natural keys, and all the other supposed RDBMS zeal that he cites, shifting from an INT datatype PK to a GUID is a really bad idea. If you use unordered GUID's, whatever index you have will always become highly fragmented. If that index happens to be a clustered index, there will be a huge number of page splits over time and the index will require constant maintenance. An INT only takes 4 bytes to store. A GUID takes 16 bytes.

If you're not convinced and insist on the change you want, then you'll need to drop and create any existing tables (you said they were empty so no special consideration there). You could take a stab at fixing them all by generating the scripts for all of the tables and doing a search'n'replace on "[int] IDENTITY(1,1)" and change that to just "GUID". Then, run the script.

UPDATE:

The crossed out advice above is incorrect. Please see my post below at

https://www.sqlservercentral.com/forums/topic/how-to-convert-int-primary-key-to-uniqueidentifier#post-3946514

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PA SSC-Addicted Points: 403 More actions · Answer 6

Jeff,

I am completely conviced. Your explanation was so thourough and precise. I was wrong. Actualy, it was not my fault, I was kinda misled, since I have seen many enterprise products which are following this idea. I thought having uniqueidentifier for primary key would have some pros... it might be because of number of records... I don't know really.

Anyway, thanks for your explanation.

PA SSC-Addicted Points: 403 More actions · Answer 7

Dear Joe,

U made me think. thanks for your effort, anyway. I think there are enough job openings, don't worry. You won't be jobless. ;-):-D

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 8

azadpeyma.parham 32837 (7/4/2010)
Jeff,
I am completely conviced. Your explanation was so thourough and precise. I was wrong. Actualy, it was not my fault, I was kinda misled, since I have seen many enterprise products which are following this idea. I thought having uniqueidentifier for primary key would have some pros... it might be because of number of records... I don't know really.
Anyway, thanks for your explanation.

Thanks for the feedback. Let me add a couple of other important notes just to make sure you have the complete picture especially if you end up using something like replication...

The use of IDENTITY for replication can cause some pretty nasty duplication problems if you have what I call "dual mastering" going on. That's where you have two servers that generate their own ID's for the same tables. Because I'm a "batch" programmer for the most part, I don't usually need to do such a thing but I believe the proper name for such a thing is "Merge Replication" and can be accomplished in several different ways that I won't go into here. The reason why IDENTITY sucks here is obvious... identical tables on two separate servers can generate identical IDENTITY(INT) values. Folks will try to overcome that with a GUID because they think they're guaranteed to be unique (they're actually [font="Arial Black"]not [/font]guaranteed to be unique but I won't get into that here, either). That, notwithstanding and provided you use a unique constraint, GUIDs can make replication easier. ~~I just don't like all the other nasty stuff GUIDs do and avoid storing them at all cost.~~ You can also use some of the "range" work arounds to make sure that dual mastering provides no dupes.

~~Other's will surely disagree but, for me, there are no pro's to using a GUID for anything except to generate random numbers over large sets. I'd personally never use a GUID for a PK.~~

UPDATE:

The crossed out advice above is incorrect. Please see my post below at

https://www.sqlservercentral.com/forums/topic/how-to-convert-int-primary-key-to-uniqueidentifier#post-3946514

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden (7/5/2010)
I'd personally never use a GUID for a PK.

I might, there are a few reasons.

Badly-designed front-end app needs to generate the surrogate key itself. Silly design, but it's one you'll encounter from time to time.

Dispersed system where there's a rollup to a central site. What Jeff mentioned with replication.

If I do use uniqueidentifier as pk, the clustered index goes ELSEWHERE, and the pk is a nonclustered pk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PA SSC-Addicted Points: 403 More actions · Answer 10

Dear Jeff,

Thanks for the explanation, I learnt a lot. I can't say how much thankful I am. I hope I can compensate it some time.

Yours sincerely,

Parham

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 11

One other practical concern with GUIDs for PKs is the ability to run queries. When you troubleshoot, most often I find that I use the PK to specify the record I want. When cut-and-paste isn't available, I find it easier to type in an INT over a GUID any day.

Also, PKs oftern end up as FKs. Joins with INTs are faster than joins with GUIDs.

PA SSC-Addicted Points: 403 More actions · Answer 12

Thanks Gail for the valuable comments. I should study more about indexes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Maybe start with this series: http://www.sqlservercentral.com/articles/Indexing/68439/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 14

UPDATE:

Like the rest of the world, I had been duped into thinking that Random GUIDs are bad. The two most cited reasons are that they're 4 times bigger than an INT/2 times bigger than a BIGINT and that they fragment a lot because of their randomness.

The first part of that is still true. They're pretty big at 16 bytes. I'll also kowtow to the fact that they suck for range scans... but so does do things like an IDENTITY column, SEQUENCE column, NEWSEQUENTIALID() column and, especially, Celko's VARCHAR() columns. 😀

What they don't actually suck at is fragmentation. In fact, you can actually use them to prevent fragmentation of the all-to-well-known "HotSpot" created by the former.

What if I told you that you can insert 100,000 rows per day into a Random GUID Clustered Index for 58 days before it finally hits 1% fragmentation?

See the following 'tube for proof. Watch it to the end which comes about 5 minutes after I'm properly interrupted by the moderator for being out of time. It turns out that Random GUIDs aren't the problem. We've been drinking the proverbial Purple Kool-Aid for more than 2 decades on both the subjects of Random GUID fragmentation and supposed "Best Practice" index maintenance. And, yeah... I was obviously a big time drinker of the "Purple Stuff" myself but not anymore.

Here's the link. Seriously... even if you have no interest in GUIDs, I use Random GUIDs to destroy what we've all come to know as "Best Practice" Index Maintenance. It has serious application to other things that fragment. It seriously changed my life when it comes to "Index Maintenance" and, yep, about GUIDs.

https://www.youtube.com/watch?v=qfQtY17bPQ4

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)