How common an anti-pattern is representing GUID primary keys using character data?

I was pondering GUIDs as primary keys rec开发者_Go百科ently, and was reminded of the most egregious misuse of them I've ever encountered:

This database contained a lot of Entity-Detail parent-child relationships, like Receipt, which had LineItems. Most of the Detail tables (LineItem in this case) used GUID primary keys. But instead of being stored using MSSQL's uniqueidentifier type, they were stored as 38-character strings, in the form '{00000000-0000-0000-0000-000000000000}'. Oh, and they were almost always in nvarchar (Unicode) columns, clocking in at 76 bytes a piece (instead of 16 bytes for a uniqueidentifier).

And how often were these fields joined on? In almost every single query in the system. Hundreds of client databases, millions of records fitting this profile. Bad.

The system did not, to the best of my memory, precede SQL Server 7.0, when the uniqueidentifier was introduced. It was just a sheer failure of knowledge / research that led to this problem.

I have two questions:

• How common, in your experience, is this anti-pattern?

• It seems obvious that a join on a 76-byte Unicode string would be dramatically slower than a join on a 16-byte binary number, with indexes or without. But can anyone provide an idea of just what a performance hit this might entail? Assume you index the join columns in either scenario.

I think the problem is not so much the inherent speed difference between joining on 76 byte keys and 16 byte keys but more on:

How many rows can you pack into each 8k page (where you get more page splits / more fragmented indexes / worse performance)....

Also -- you didn't mention if those pretend GUID's were sequential or not. If they were part of the primary key and that KEY was clustered then every insert could have potentially reorganised the complete btree of the table........

Also any nonclustered indexes you have on the table contain the primary key (so they can do lookups on querys not 100% satisfied by the nonclustered index). So your nonclustered indexes are going to be much, much bigger than if they were on a table with a UNIQUEIDENTIFIER type.

I haven't seen the GUID's modelled as strings in any company I've worked for but I have seen a few tables where the pk was clustered and a GUID was chosen for no particular reason. Worked fine for small datasets and then..... performance problems in production.