Using the ROWGUIDCOL Attribute Flag

GUID stands for Globally Unique IDentifier, and works hand-in-hand with the ROWGUIDCOL flag. When you indicate that a unique identifier column is to use the ROWGUIDCOL flag, Microsoft guarantees that the column created will contain a unique value that cannot be duplicated, or regenerated by any other computer in the world. Only one column in a given table can contain the ROWGUIDCOL flag.
The fact that these columns cannot contain duplicate values with any other column in any other table on any other computer in the world makes GUID great for merging databases together. Replicated and merged databases will be discussed in a future course in this series.

If you do not need a column to be globally unique, do not use the Uniqueidentifier datatype and the ROWGUIDCOL flag. The Uniqueidentifier datatype takes 16 bytes of storage for every row of data in the table. This can quickly grow the storage space requirements for your table. For example, if your table stores one million rows of data, use of the Uniqueidentifier column will require 16 megabytes to store the data for that column. This is in addition to the other columns of data in the table.

Using the Uniqueidentifier datatype with the ROWGUIDCOL flag does not mean that values are populated every time you insert a row of data into the table. If you want data to automatically be inserted by SQL Server, you must create a default value using the NEWID MS SQL Server 2012 function. The following code fragment would create a table named finance, where the transactionid column automatically generates a GUID:

CREATE TABLE finance (transactionid Uniqueidentifier
ROWGUIDCOL DEFAULT NEWID(), transactionamount money)

The above code will automatically insert a new GUID value in the transactionid column if one is not already supplied by an insert statement. If you wish to update the value in the transactionid column where the transactionamount value is 500, you could issue this Transact-SQL statement:
In the next lesson, you will learn how to create and use your own datatypes.

UPDATE finance
SET transactionid = NEWID()
WHERE transactionamount = 500