I recently needed to create a table in which the primary key was unique. I wanted to use the ID as a key to hold session data for a Web application, but I needed a more unique key than the ID column. So, I decided to use SQL Server's globally unique ID (GUID) feature to create the ID.
The plan was to use a SQL Server stored procedure to generate the GUID whenever anyone entered a new record. I wanted to use the uniqueidentifier data type for the primary key column. So, I opened Access, pointed it at SQL Server, then chose the uniqueidentifier data type for the column that would contain the unique ID. After I defined the table's structure, I saved the definition, then opened the table and added a few rows. I was happy to see a GUID magically appear in each row as I entered the data. However, my happiness was short-lived.
Upon testing my stored procedure, I found that the uniqueidentifier data type doesn't work exactly like the Identity value. When SQL Server tried to execute my stored procedure, it complained about the Insert statement, sending me an error message that said I couldn't insert a NULL for that column. Hmmm.
After researching the possible problems in SQL Server Books Online (BOL), I found the cause. You must use the NewID function to create the GUID instead of letting SQL Server create it for you. You can then insert the GUID into the record. You can make NewID the default so that it creates the GUID when you insert a record, or you can explicitly call NewID in a stored procedure to set the GUID value when you perform an insert. Explicitly calling NewID has an advantage: When you call it to set the GUID value, you can capture the value in your code, then return it from the stored procedure for use in your application. Before using GUIDs, read up on them in BOL to fully understand how they work.