You are a database administrator on an instance of SQL Server 2008. You are creating the TrxHistory table in the Sales database. You want to create a surrogate key for the table that will store a globally-unique identifier (GUID).
Users at your branch offices will insert many rows into the table, and users in the accounting department at your corporate office will frequently run queries against the table. You want to minimize the size and fragmentation for the clustered primary index on the TrxHistory table.
Which action should you take?
A. Define the surrogate key as an int and include the IDENTITY property.
B. Define the surrogate key as a uniqueidentifier and use the NEWID function in the column’s DEFAULT definition.
C. Define the surrogate key as a uniqueidentifier and use the NEWSEQUENTIALID function in the column’s DEFAULT definition.
D. Define the surrogate key column as a uniqueidentifier and include the ROWGUIDCOL property.
Correct Answer: C
Explanation/Reference:
A uniqueidentifier data type is used to store globally-unique identifiers (GUIDs). A GUID is a unique 16-byte integer that is unique across servers and networks.
For uniqueidentifier columns, you can use the NEWSEQUENTIALID function in the column’s DEFAULT definition to generate a GUID each time a row is inserted into the table. For example, you could use the following statement to create the TrxHistory table:
CREATE TABLE TrxHistory (ID uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID(),Date datetimeoffset,Description nvarchar(50));
You can also use the NEWID function to generate a GUID. However, the NEWSEQUENTIALID function will generate sequential GUID values and minimize fragmentation of the index. You should note that the NEWID function can be also be used in the VALUES clause of an INSERT statement, but the NEWSEQUENTIALID function can only be used in a DEFAULT definition.
You should not define the surrogate key as an int and include the IDENTITY property. In this scenario, you wanted the column to store a globally-unique identifier.
This cannot be accomplished using an int data type. You should use a uniqueidentifier data type to store a GUID.
You should not define the surrogate key as a uniqueidentifier and use the NEWID function in the column’s DEFAULT definition. In this scenario, you wanted to minimize index size and fragmentation. Using the NEWSEQUENTIALID function to generate the GUIDs would generate them sequentially, instead of randomly, and minimize fragmentation on the index.
You should not define the surrogate key column as a uniqueidentifier and include the ROWGUIDCOL property. The ROWGUIDCOL property can be specified for a single uniqueidentifier column in a table to identify the column as a row GUID column. This allows you to use $ROWGUID to reference the column, but does not ensure uniqueness, nor affect how the GUID values are generated, and would not minimize index fragmentation in this scenario.
