Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 204

(GUID).'' Uniqueidentifiers occupy 16 bytes of storage. They're also touted as universally unique. In
some instances a uniqueidentifier can be a good choice.

Table 8-3: Large Data Type Location Matrix

Percentage of outside Frequency
data row storage of retrieval Recommendation

Low Low In-row storage. The low percentage of overflowing rows

makes storing the data in the data row acceptable.

High Low Out of row storage. By always forcing this data to be stored

outside the data row, you're eliminating the burden on SQL
Server to determine how to store the data. Also, since the data
in question is rarely retrieved the occasional performance
degradation is acceptable.

Low High In-row storage. Since the rows are not likely to require out of

row storage and these columns are frequency accessed they
should be stored in the data row.

High High Unknown. Try to avoid this scenario. There's no solid

direction when this occurs. Instead try splitting the table into
multiple tables. The goal would be to make all the new tables
fall into one of the three previous categories.

However, some objections have been given against using them:

They're too large to be an effective index: Uniqueidentifiers are 16 bytes long, which some believe
is too long for columns used in indexes or primary keys. Instead, using an integer or even bigint
is advised because they're only 4 or 8 bytes long, respectively. Although this is true, this alone
isn't compelling enough to warrant not using them.

❑

They're too hard to remember: When displaying a uniqueidentifier value in SSMS, it appears
as a 36-character string of letters and numbers. For example, 53F001FE-5CBF-4141-87FC-
C42B634FF464 is a valid uniqueidentifier. Again, although this is true, they're not really meant
for ''human'' consumption. They're really only meant for the computer.

❑

Next, are some reasons they should be used:

❑ Merge replication: Uniqueidentifiers are used with merge replication. If the environment you

work in involves Merge Replication, you're already using uniqueidentifiers. SQL Server man-
ages merge replication by adding a uniqueidentifier column (with the rowguid property set to
yes) to a table, if it's not already there. Allowing SQL Server to automatically alter a table and
add a column could be very problematic.

❑ Data entry (OLTP) applications: For a table that experiences a high degree of row inserts such

as a traditional data entry or OLTP type of application, uniqueidentifers can be a tremendous
enhancement. The reason is the uniqueidentifier value can be generated at the client before the
insert occurs. That's not possible with identity columns. In the ubiquitous Order-Order Detail