How to replace all non-sequential GUIDs with sequential ones?

views:

182

answers:

+1 Q:

How to replace all non-sequential GUIDs with sequential ones?

I have a brown-field SQL Server 2005 database that uses standard, unsorted GUIDs as the majority of the primary keys values and also in clustered indexes (which is bad for performance).

How should I go about changing these to sequential GUIDs? One of the challenges would be to replace all of the foreign key values as I change each the primary key.

Do you know of any tools or scripts to perform this type of conversion?

+5 A:

remember that you can only use the newsequentialid() function as a default

so create a new table with 2 columns. Insert the key from you original table into this one (leave the other column out it will fill itself)

join back to the original table and update the PK with the newsequantialid, if you have cascade update the FKs should update by themselves

SQLMenace 2009-06-19 13:31:32

+1 - I have used a similar approach in the past but I updated the foreign keys myself.

Matt Spradley 2009-06-19 13:34:15

How do you know it's bad for performance?

The advantage of GUIDs is that you don't have to worry about multiple processes creating records at the same time. It can simplify your code considerably, depending on the program.

Jeremy Stein 2009-06-19 13:32:51

Inserts are slow. Page fragmentation is high. If I had a duplicate database populated with sequential IDs, then I could tell you how much slower :)

Even Mien 2009-06-19 13:47:09

it is bad because it causes excessive page splits, newid() is bad while newsequentialid() is not they bott create GUIDs

SQLMenace 2009-06-19 13:48:48

Talked about at the end of this podcast: http://www.dotnetrocks.com/default.aspx?showNum=455

Even Mien 2009-06-19 13:49:06

I also have some code showing just that here http://sqlblog.com/blogs/denis_gobo/archive/2009/02/05/11743.aspx

SQLMenace 2009-06-19 13:50:09

@Even Mien: Indeed. That caused a problem with the Windows 7 downloads recently - http://www.sqlskills.com/BLOGS/PAUL/post/Why-did-the-Windows-7-RC-failure-happen.aspx

adrianbanks 2009-06-19 13:50:57

The reason they're bad for performance is because they're in a clustered index. That defines the physical order for the table, but it's basically keyed on something that is randomly sorted. Every time you add a new record it's most likely getting inserted somewhere in the middle, forcing SQL to reorder the table and leading to all the problems he mentioned.

GalacticCowboy 2009-06-19 13:51:56

Non sequential GUIDs are bad for performance due to the way clustered indexes work- with sequential Ids there will not be performance issues with page splits. Clustered indexes also work well if the rows are inserted in order. That's why identity fields work so well with clustered indexes. – RichardOD 0 secs ago [delete this comment]

RichardOD 2009-06-19 13:53:25

ansaurus

tags:

views:

answers:

How to replace all non-sequential GUIDs with sequential ones?

related questions