A GUID is a 16-byte identifier that can be generated with NEWID() to produce values unique across a server. Its apparent convenience can make it an attractive primary key, especially when a commercial product requires one. Before adopting it, distinguish a genuine cross-system uniqueness requirement from ordinary uniqueness within one table or database. section 4.2
Mistake #12: treating global uniqueness as free. A wide primary key consumes more disk space and memory in indexes. Because index pages hold only 8,000 bytes, wider keys require more pages, causing SQL Server to read more pages during retrieval. A narrow artificial IDENTITY primary key, with a unique nonclustered index on the natural key when needed, avoids carrying a wide key through clustered and nonclustered indexes. section 4.2
Randomness is the second cost. When a GUID-backed primary key is clustered, random incoming values disrupt the clustered order. SQL Server must move data between pages to keep the index ordered, creating page splits. The result is more I/O, slower INSERT and UPDATE operations, and index fragmentation. section 4.2
Use a GUID primary key deliberately:
- Confirm that uniqueness must extend beyond the table before paying for a 16-byte key. section 4.2
- Consider SEQUENCE when uniqueness across a database is sufficient. section 4.2
- Use an IDENTITY artificial key and a unique nonclustered index when the natural key must remain unique without widening all indexes. section 4.2
- Expect fragmentation and write overhead if random GUID values define a clustered index. section 4.2
GUIDs in symmetric-key encryption
A GUID can identify a symmetric key rather than a table row. KEY_GUID('SalaryKey') returns the symmetric key’s GUID for ENCRYPTBYKEY(), allowing a salary value to be encrypted after the key is opened; the key is then closed to prevent use by another query. section 14.6.1
Listing 14.10 Populating the EncryptedSalary column
OPEN SYMMETRIC KEY SalaryKey
DECRYPTION BY CERTIFICATE SalaryCert ;
UPDATE dbo.Employees
SET EncryptedSalary =
ENCRYPTBYKEY(KEY_GUID('SalaryKey'), CAST(Salary AS VARCHAR(10))) ;
CLOSE SYMMETRIC KEY SalaryKey ;
The listing shows KEY_GUID used as the first ENCRYPTBYKEY argument while encrypting Salary into an EncryptedSalary VARBINARY column. section 14.6.1
