GUIDs as Primary Key
-
Hello All,
I am new to Replication and would like to hear some thoughts regarding GUIDs as Primary Keys. We have 4 sites across the country. At head-quarters we have four databases that need to be replicated to each of these sites. Lets say Merge replication for now.
All of our tables were originally designed with GUIDs as our Primary Keys. I am on the side of fence who cant stand the idea of GUIDs as the Clustered Primary Key. These databases at Headquarters perform a high volume of inserts, updates, and deletes. Inserts are SLOW due the the GUID keys.
Questions:
1) When using Replication, do the GUIDs need to be Clustered Primary Keys?
2) Can I create a composite key of like IDENTITY and ServerID as my Clustered Primary Key and use that in Replication?
3) How can I avoid using GUIDs in replication?
Any thoughts would be greatly appreciated?
Thanks
Greg
-
Your situation is why uniqueidentifier columns (guid) were created in SQL Server.
I suggest that your problem with inserts are not because of guids, but because the tables are physically ordered (clustered) by the guid column. Inserting records in an essentially physical random order would be sure to generate a lot of page splits and then consequently extents.
Look to see if you can create a clustered index on something more reasonable (date inserted, customer number, order number, etc). I won't promise that you will like guids, but maybe you won't mind them as much.
Hope this helps
Mark -
Mark,
When using Replication, can any column be the Clustered Index, or does it need to be the Primary Key or a Unique column? I was told that the Clustered Index needs to be a unique column whether it the the primary key or a different column. Do you know of any articles that support this claim or not? I would rather use a different key due to the page splits and consequent extents. If I need a GUID for replication, it shouldn't need to be the Clustered Index for that matter, it only needs to be an attribute of the table with really no meaning except used by replication.
Greg
-
I am not much of an expert on replication and what is required or not, but I believe the only concern regarding indexes (clustered or not) are to consider that a replication transaction on a field in a primary key or unique index will be done by using a Delete and Insert, rather than an Update statement.
No, clustered indexes do not have to be unique indexes, do not have to be the Primary Key, and they can be made of multiple columns. BOL gives the sensible warning when using multiple columns to not make it too wide, as it will make other indexes larger (since they must store the clustered index value).
See BOL "Using Clustered Indexes".
Mark -
The thing with indices is that they help SQL server perform its work efficiently (clustered or non-clustered) - They *should* have no impact at all on the data you receive, just how quickly/efficiently is is retrieved. Thus, it makes no difference how you index your table.
A clustered index needn't be unique.
Yes, a clustered index based on a GUID column will slow down your inserts, but if you are joining to other tables with GUIDs as your foreign key and don't do a lot of inserts, then it may well be suitable as a clustered index..
On my first point, I lie slightly as things such as indexed views muddy the waters, but for the most part, see indices as something that helps SQL, but does not affect the results of your statements.
Cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply