GUID stored in a varchar field

Question

I have inherited several databases that use GUIDs as PKs. Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by

myID = 'xxx'+convert(varchar(40),newID())

In general its a bit of a mess

What are the performance implications for this design and is it worth re-working the tables to convert the datatypes. Tables are generally around 1/2 M records with a couple table in the 2-4M record range.

The impetus of this question is that I am trying (unsuccessfully) to optimize an proc that joins 24 tables and views and the server is not handling it very well.

Thanks for any insight

Answer 1

In general, it is a bad idea to have long clustered index keys. For that it is not important if the key is made up of several short columns or few long columns. This makes the maintenance of every index as well as the read access of every index on that table a lot more expensive that it has to be. As the PK is by default enforced by a clustered index, you most likely have a very long clustered index key.

Also in general it is a good idea to have you clustered index key be monotonously increasing. As your column is valued based on NEWID it will distribute new rows all over the table causing fragmentation. The extra long key will make this even worse.

And finally, in general it is a good idea to not spend too much time tuning performance preemptively. If you do new development follow best practices (like the two recommendations above). With existing software, fix only the stuff that is causing a headache.

Now it looks like you have that headache already. Therefore I would start by migrating those primary keys to either identity columns (preferred) or to uniqueidentifier columns that default to newsequentialid().

But this is a difficult change to do if you can't take downtime. I that case I would start by adding new indexes to support you current strongest headache (read: stored procedure). Than work on cleaning up the mess afterwards.

Answer 2

Using UNIQUEIDENTIFER uses much less space than VARCHAR. This is because UNIQUEIDENTIFER uses only 16 bytes, whereas using VARCHAR uses at a minimum 36 bytes. Based on your situation, yes the performance would be improved and improving the reliablility of your index keys. If you use SQL Servers UNIQUEIDENFIER there is no option for duplication and if you use this in default value then always value is there.

Answer 3

If you have the time:

1. Add a nullable Integer column to each table.
2. Using Row_Number(), set the Integer column value (ordering by the create-timestamp on the table).
3. Go back and set the Integer column to non-nullable, and make it an Identify field.
4. Make this Integer column the clustered-index (if one already exists, then make the old clustered-index a non-clustered one first).
5. Anywhere this table is referenced as a foreign-key, add a nullable Integer column and set it's value based on the value in the primary-key table.
   

Here is where you'll need to apply a little more elbow-grease.
If you don't want to bother rewriting all the existing logic that persists data into these legacy tables then:

1. The identity column you added will set itself, so existing insert/update statements won't need any further re-work unless the table references foreign-keys you have also replaced with integers.
2. For other tables where this primary-key table is referenced as a foreign key, you could simply add a trigger that populates the foreign-integer-key based on that crazy xxx-guid-varchar you have there.
3. This should guarantee the foreign-integer-key's you've added to these tables are populated correctly; without the need to alter any existing code that inserts/updates these legacy tables.

Now you can go about "fighting your biggest performance fires" by rewriting your problem queries to join on the new integer fields, instead of those crazy triple-x-guid-varchar's you got going on there.

Good Luck!