GUID Primary Keys

Question

GUID Primary Keys

Jonathan Matt

SSCarpal Tunnel

Points: 4027
More actions
September 11, 2006 at 3:03 pm

#115343

I have inherited a database (approx. 75 tables) that has GUID Primary Keys on each table. Since the default was accepted each of these tables has a clustered index on these GUID columns. I am aware of all the disadvantages of clustered indexes on GUID columns.
It seems the only way to drop the clustered index is to drop the primary key and re-add the key without the clustered index. This is a daunting prospect however because all of the foreign key relationships would be lost when I drop the primary key. They would all have to recreated for each table.
I am wondering if the effort is worth it.
Has anyone else gone down this path.
Any advice greatly appreciated!
JM

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

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

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 1

It is the only way !

When you generate the script for it with EM, you'll see it starts with dropping the relationship(s), dropping the PK, creating the PK and recreating the relationship(s) ..... within one transaction ! That way integrity is guaranteed.

Depending on the time you estimate for your action, plan it !

Is it worth the effort ? I guess it will, depending on how many insert/update is going on at your db (degradation of indexes/page splits).

Keep in mind to create an other clustered index ! But this time, chose it more appropriate, or use an incrementing column (if possible unique).

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

Yup, I've got a system just like it. GUID primary keys all with clustered indexes. However I've got just over a thousand tables.

How I've approached it is to change the indexes bit by bit, looking at the tables with the most inserts (ie highest rate of page splits) first and working down to the slower moving tables.

Some I won't bother to change, the lookup tables with 20-50 rows that never change.

Make sure you document all the foreign keys before you start, and make sure you don't have any data that violates existing keys (from foreign keys created with NO_CHECK). That way, you can be sure to get back to the same state afterwards

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

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 3

this proc may ease your work ...

-- TEST IT - TEST IT --

Create procedure Spc_ALZDBA_PK2NonClustered

@ParentTbName varchar(128)

, @ParentTbOwner varchar(128) = NULL

, @Commentaar varchar(128) = ''

as

-- Declare @ParentTbName varchar(128)

-- , @ParentTbOwner varchar(128)

---select @ParentTbName = 'T_ParentMC', @ParentTbOwner = NULL

SET nocount on

create table #tmpPKeys(

TABLE_QUALIFIER sysname,

TABLE_OWNER sysname not null,

TABLE_NAME sysname not null,

COLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

PK_NAME sysname null )

Create index ix#tmpPKeys on #tmpPKeys (TABLE_QUALIFIER, TABLE_OWNER,TABLE_NAME, KEY_SEQ)

-- Get PK-info

insert into #tmpPKeys

exec sp_pkeys @table_name = @ParentTbName

, @table_owner = @ParentTbOwner

-- [ , [ @table_qualifier = ] 'qualifier' ] -- DBName

create table #tmpFKeys

(PKTABLE_QUALIFIER sysname not null,

PKTABLE_OWNER sysname not null,

PKTABLE_NAME sysname not null,

PKCOLUMN_NAME sysname not null,

FKTABLE_QUALIFIER sysname not null,

FKTABLE_OWNER sysname not null,

FKTABLE_NAME sysname not null,

FKCOLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

UPDATE_RULE smallint not null,

DELETE_RULE smallint not null,

FK_NAME sysname not null,

PK_NAME sysname not null,

DEFERRABILITY int not null)

Create index #tmpFKeys on #tmpFKeys (FK_NAME, KEY_SEQ)

-- Get FK-info (all dependant objects)

insert into #tmpFKeys

exec sp_fkeys @pktable_name = @ParentTbName

, @pktable_owner = @ParentTbOwner

-- [ , [ @pktable_qualifier = ] 'pktable_qualifier' ]

-- { , [ @fktable_name = ] 'fktable_name' }

-- [ , [ @fktable_owner = ] 'fktable_owner' ]

-- [ , [ @fktable_qualifier = ] 'fktable_qualifier' ]

print 'print ''-- verwerking ' + @ParentTbName + ' / ' + @commentaar + ' / Begin '' + convert(varchar(25),getdate(),121) '

print 'Begin transaction trxAlterPK2ClIx --' + @ParentTbName

print ' '

print '-- Drop Referencing constraints'

select 'Alter Table [' + FKTABLE_OWNER + '].[' + FKTABLE_NAME + '] drop constraint [' + FK_NAME + ']' + char(13) + 'GO '

from #tmpFKeys

where Key_SEQ = 1

order by FKTABLE_OWNER, FKTABLE_NAME, FK_NAME

print '-- drop PK-constraint'

select 'Alter Table [' + TABLE_OWNER + '].[' + TABLE_NAME + '] drop constraint [' + PK_NAME + ']' + char(13) + 'GO '

from #tmpPKeys

where Key_SEQ = 1

order by TABLE_OWNER, TABLE_NAME

print '-- Create PK_Constraint'

select 'Alter Table [' + P1.TABLE_OWNER + '].[' + P1.TABLE_NAME + '] WITH NOCHECK add constraint ' + P1.PK_NAME + ' PRIMARY KEY NONCLUSTERED ( '+

P1.COLUMN_NAME

+ case when P2.COLUMN_NAME is null then '' else ', ' + P2.COLUMN_NAME end

+ case when P3.COLUMN_NAME is null then '' else ', ' + P3.COLUMN_NAME end

+ case when P4.COLUMN_NAME is null then '' else ', ' + P4.COLUMN_NAME end

+ case when P5.COLUMN_NAME is null then '' else ', ' + P5.COLUMN_NAME end

+ case when P6.COLUMN_NAME is null then '' else ', ' + P6.COLUMN_NAME end

+ case when P7.COLUMN_NAME is null then '' else ', ' + P7.COLUMN_NAME end

+ case when P8.COLUMN_NAME is null then '' else ', ' + P8.COLUMN_NAME end

+ case when P9.COLUMN_NAME is null then '' else ', ' + P9.COLUMN_NAME end

+ case when P10.COLUMN_NAME is null then '' else ', ' + P10.COLUMN_NAME end

+ case when P11.COLUMN_NAME is null then '' else ', ' + P11.COLUMN_NAME end

+ case when P12.COLUMN_NAME is null then '' else ', ' + P12.COLUMN_NAME end

+ case when P13.COLUMN_NAME is null then '' else ', ' + P13.COLUMN_NAME end

+ case when P14.COLUMN_NAME is null then '' else ', ' + P14.COLUMN_NAME end

+ case when P15.COLUMN_NAME is null then '' else ', ' + P15.COLUMN_NAME end

+ case when P16.COLUMN_NAME is null then '' else ', ' + P16.COLUMN_NAME end

+ case when P17.COLUMN_NAME is null then '' else ', ' + P17.COLUMN_NAME end

+ case when P18.COLUMN_NAME is null then '' else ', ' + P18.COLUMN_NAME end

+ ' ) ' + char(13) + 'GO '

from #tmpPKeys P1

left join #tmpPKeys P2

on P1.TABLE_OWNER = P2.TABLE_OWNER and P1.TABLE_NAME = P2.TABLE_NAME and P1.KEY_SEQ = 1 and P2.KEY_SEQ = 2

left join #tmpPKeys P3

on P1.TABLE_OWNER = P3.TABLE_OWNER and P1.TABLE_NAME = P3.TABLE_NAME and P1.KEY_SEQ = 1 and P3.KEY_SEQ = 3

left join #tmpPKeys P4

on P1.TABLE_OWNER = P4.TABLE_OWNER and P1.TABLE_NAME = P4.TABLE_NAME and P1.KEY_SEQ = 1 and P4.KEY_SEQ = 4

left join #tmpPKeys P5

on P1.TABLE_OWNER = P5.TABLE_OWNER and P1.TABLE_NAME = P5.TABLE_NAME and P1.KEY_SEQ = 1 and P5.KEY_SEQ = 5

left join #tmpPKeys P6

on P1.TABLE_OWNER = P6.TABLE_OWNER and P1.TABLE_NAME = P6.TABLE_NAME and P1.KEY_SEQ = 1 and P6.KEY_SEQ = 6

left join #tmpPKeys P7

on P1.TABLE_OWNER = P7.TABLE_OWNER and P1.TABLE_NAME = P7.TABLE_NAME and P1.KEY_SEQ = 1 and P7.KEY_SEQ = 7

left join #tmpPKeys P8

on P1.TABLE_OWNER = P8.TABLE_OWNER and P1.TABLE_NAME = P8.TABLE_NAME and P1.KEY_SEQ = 1 and P8.KEY_SEQ = 8

left join #tmpPKeys P9

on P1.TABLE_OWNER = P9.TABLE_OWNER and P1.TABLE_NAME = P9.TABLE_NAME and P1.KEY_SEQ = 1 and P9.KEY_SEQ = 9

left join #tmpPKeys P10

on P1.TABLE_OWNER = P10.TABLE_OWNER and P1.TABLE_NAME = P10.TABLE_NAME and P1.KEY_SEQ = 1 and P10.KEY_SEQ = 10

left join #tmpPKeys P11

on P1.TABLE_OWNER = P11.TABLE_OWNER and P1.TABLE_NAME = P11.TABLE_NAME and P1.KEY_SEQ = 1 and P11.KEY_SEQ = 11

left join #tmpPKeys P12

on P1.TABLE_OWNER = P12.TABLE_OWNER and P1.TABLE_NAME = P12.TABLE_NAME and P1.KEY_SEQ = 1 and P12.KEY_SEQ = 12

left join #tmpPKeys P13

on P1.TABLE_OWNER = P13.TABLE_OWNER and P1.TABLE_NAME = P13.TABLE_NAME and P1.KEY_SEQ = 1 and P13.KEY_SEQ = 13

left join #tmpPKeys P14

on P1.TABLE_OWNER = P14.TABLE_OWNER and P1.TABLE_NAME = P14.TABLE_NAME and P1.KEY_SEQ = 1 and P14.KEY_SEQ = 14

left join #tmpPKeys P15

on P1.TABLE_OWNER = P15.TABLE_OWNER and P1.TABLE_NAME = P15.TABLE_NAME and P1.KEY_SEQ = 1 and P15.KEY_SEQ = 15

left join #tmpPKeys P16

on P1.TABLE_OWNER = P16.TABLE_OWNER and P1.TABLE_NAME = P16.TABLE_NAME and P1.KEY_SEQ = 1 and P16.KEY_SEQ = 16

left join #tmpPKeys P17

on P1.TABLE_OWNER = P17.TABLE_OWNER and P1.TABLE_NAME = P17.TABLE_NAME and P1.KEY_SEQ = 1 and P17.KEY_SEQ = 17

left join #tmpPKeys P18

on P1.TABLE_OWNER = P18.TABLE_OWNER and P1.TABLE_NAME = P18.TABLE_NAME and P1.KEY_SEQ = 1 and P18.KEY_SEQ = 18

where P1.KEY_SEQ = 1

order by P1.TABLE_OWNER, P1.TABLE_NAME

print '-- Create FK-Constraints'

print '-- keep FK column-ordinal equal to PK column-ordinal'

select 'Alter Table [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] WITH NOCHECK add constraint [' + FK1.FK_NAME + '] FOREIGN KEY ( '+

FK1.FKCOLUMN_NAME

+ case when FK2.FKCOLUMN_NAME is null then '' else ', ' + FK2.FKCOLUMN_NAME end

+ case when FK3.FKCOLUMN_NAME is null then '' else ', ' + FK3.FKCOLUMN_NAME end

+ case when FK4.FKCOLUMN_NAME is null then '' else ', ' + FK4.FKCOLUMN_NAME end

+ case when FK5.FKCOLUMN_NAME is null then '' else ', ' + FK5.FKCOLUMN_NAME end

+ case when FK6.FKCOLUMN_NAME is null then '' else ', ' + FK6.FKCOLUMN_NAME end

+ case when FK7.FKCOLUMN_NAME is null then '' else ', ' + FK7.FKCOLUMN_NAME end

+ case when FK8.FKCOLUMN_NAME is null then '' else ', ' + FK8.FKCOLUMN_NAME end

+ case when FK9.FKCOLUMN_NAME is null then '' else ', ' + FK9.FKCOLUMN_NAME end

+ case when FK10.FKCOLUMN_NAME is null then '' else ', ' + FK10.FKCOLUMN_NAME end

+ case when FK11.FKCOLUMN_NAME is null then '' else ', ' + FK11.FKCOLUMN_NAME end

+ case when FK12.FKCOLUMN_NAME is null then '' else ', ' + FK12.FKCOLUMN_NAME end

+ case when FK13.FKCOLUMN_NAME is null then '' else ', ' + FK13.FKCOLUMN_NAME end

+ case when FK14.FKCOLUMN_NAME is null then '' else ', ' + FK14.FKCOLUMN_NAME end

+ case when FK15.FKCOLUMN_NAME is null then '' else ', ' + FK15.FKCOLUMN_NAME end

+ case when FK16.FKCOLUMN_NAME is null then '' else ', ' + FK16.FKCOLUMN_NAME end

+ case when FK17.FKCOLUMN_NAME is null then '' else ', ' + FK17.FKCOLUMN_NAME end

+ case when FK18.FKCOLUMN_NAME is null then '' else ', ' + FK18.FKCOLUMN_NAME end

+ ' ) REFERENCES [' + FK1.PKTABLE_OWNER + '].[' + FK1.PKTABLE_NAME + '] ( ' +

FK1.PKCOLUMN_NAME

+ case when FK2.PKCOLUMN_NAME is null then '' else ', ' + FK2.PKCOLUMN_NAME end

+ case when FK3.PKCOLUMN_NAME is null then '' else ', ' + FK3.PKCOLUMN_NAME end

+ case when FK4.PKCOLUMN_NAME is null then '' else ', ' + FK4.PKCOLUMN_NAME end

+ case when FK5.PKCOLUMN_NAME is null then '' else ', ' + FK5.PKCOLUMN_NAME end

+ case when FK6.PKCOLUMN_NAME is null then '' else ', ' + FK6.PKCOLUMN_NAME end

+ case when FK7.PKCOLUMN_NAME is null then '' else ', ' + FK7.PKCOLUMN_NAME end

+ case when FK8.PKCOLUMN_NAME is null then '' else ', ' + FK8.PKCOLUMN_NAME end

+ case when FK9.PKCOLUMN_NAME is null then '' else ', ' + FK9.PKCOLUMN_NAME end

+ case when FK10.PKCOLUMN_NAME is null then '' else ', ' + FK10.PKCOLUMN_NAME end

+ case when FK11.PKCOLUMN_NAME is null then '' else ', ' + FK11.PKCOLUMN_NAME end

+ case when FK12.PKCOLUMN_NAME is null then '' else ', ' + FK12.PKCOLUMN_NAME end

+ case when FK13.PKCOLUMN_NAME is null then '' else ', ' + FK13.PKCOLUMN_NAME end

+ case when FK14.PKCOLUMN_NAME is null then '' else ', ' + FK14.PKCOLUMN_NAME end

+ case when FK15.PKCOLUMN_NAME is null then '' else ', ' + FK15.PKCOLUMN_NAME end

+ case when FK16.PKCOLUMN_NAME is null then '' else ', ' + FK16.PKCOLUMN_NAME end

+ case when FK17.PKCOLUMN_NAME is null then '' else ', ' + FK17.PKCOLUMN_NAME end

+ case when FK18.PKCOLUMN_NAME is null then '' else ', ' + FK18.PKCOLUMN_NAME end

+ ' ) ' + char(13) + 'GO '

from #tmpFKeys FK1

left join #tmpFKeys FK2

on FK1.FK_NAME = FK2.FK_NAME and FK1.KEY_SEQ = 1 and FK2.KEY_SEQ = 2

left join #tmpFKeys FK3

on FK1.FK_NAME = FK3.FK_NAME and FK1.KEY_SEQ = 1 and FK3.KEY_SEQ = 3

left join #tmpFKeys FK4

on FK1.FK_NAME = FK4.FK_NAME and FK1.KEY_SEQ = 1 and FK4.KEY_SEQ = 4

left join #tmpFKeys FK5

on FK1.FK_NAME = FK5.FK_NAME and FK1.KEY_SEQ = 1 and FK5.KEY_SEQ = 5

left join #tmpFKeys FK6

on FK1.FK_NAME = FK6.FK_NAME and FK1.KEY_SEQ = 1 and FK6.KEY_SEQ = 6

left join #tmpFKeys FK7

on FK1.FK_NAME = FK7.FK_NAME and FK1.KEY_SEQ = 1 and FK7.KEY_SEQ = 7

left join #tmpFKeys FK8

on FK1.FK_NAME = FK8.FK_NAME and FK1.KEY_SEQ = 1 and FK8.KEY_SEQ = 8

left join #tmpFKeys FK9

on FK1.FK_NAME = FK9.FK_NAME and FK1.KEY_SEQ = 1 and FK9.KEY_SEQ = 9

left join #tmpFKeys FK10

on FK1.FK_NAME = FK10.FK_NAME and FK1.KEY_SEQ = 1 and FK10.KEY_SEQ = 10

left join #tmpFKeys FK11

on FK1.FK_NAME = FK11.FK_NAME and FK1.KEY_SEQ = 1 and FK11.KEY_SEQ = 11

left join #tmpFKeys FK12

on FK1.FK_NAME = FK12.FK_NAME and FK1.KEY_SEQ = 1 and FK12.KEY_SEQ = 12

left join #tmpFKeys FK13

on FK1.FK_NAME = FK13.FK_NAME and FK1.KEY_SEQ = 1 and FK13.KEY_SEQ = 13

left join #tmpFKeys FK14

on FK1.FK_NAME = FK14.FK_NAME and FK1.KEY_SEQ = 1 and FK14.KEY_SEQ = 14

left join #tmpFKeys FK15

on FK1.FK_NAME = FK15.FK_NAME and FK1.KEY_SEQ = 1 and FK15.KEY_SEQ = 15

left join #tmpFKeys FK16

on FK1.FK_NAME = FK16.FK_NAME and FK1.KEY_SEQ = 1 and FK16.KEY_SEQ = 16

left join #tmpFKeys FK17

on FK1.FK_NAME = FK17.FK_NAME and FK1.KEY_SEQ = 1 and FK17.KEY_SEQ = 17

left join #tmpFKeys FK18

on FK1.FK_NAME = FK18.FK_NAME and FK1.KEY_SEQ = 1 and FK18.KEY_SEQ = 18

where FK1.KEY_SEQ = 1

order by FK1.FKTABLE_OWNER, FK1.FKTABLE_NAME, FK1.FK_NAME

print '-- Create FK-Indexes'

print '-- keep FK column-ordinal / order equal to PK column-ordinal / order (asc/desc)'

select 'Create index [XFK_' + FK1.FK_NAME + '] on [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] ( '+