HOWTO: Insert New Values to a Uniqueidentifier Column in SQL Server CE (274189)

MORE INFORMATION

• Manually insert a formatted 16-byte binary value in the INSERT statement, as described in the INSERT topic of SQL Server CE Books Online:

  Columns created with the uniqueidentifier data type store specially formatted 16-byte binary values. During an insert operation, variables with a data type of uniqueidentifier and string constants in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (36 characters including hyphens, where x is a hexadecimal digit in the range 0-9 or a-f) can be used for uniqueidentifier columns. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid value for a uniqueidentifier variable or column.

• The ROWGUIDCOL attribute for a uniqueidentifier column has been overloaded to indicate that a default value should be generated.
  
  Here is sample code that demonstrates how it works:
  

  
   Create table test1(col1 int, col2 uniqueidentifier rowguidcol)
  
   Insert into test1 (col1) values (1)
  
   Select * from test1
  					

  
  Using remote data access (RDA) or replication, if the table on the server has the ROWGUIDCOL attribute set ON for the column, then the column can be set up on SQL Server CE with the the ROWGUIDCOL attribute and SQL Server CE creates the GUID value for INSERT statement.
  
  This workaround applies only to the current release of SQL Server CE and is not forward-compatible with SQL Server. On SQL Server 2000, the ROWGUIDCOL property does not automatically generate values for new rows that are inserted into the table. This workaround may not apply in future releases. Also, a table can only have one ROWGUIDCOL column.
  
  The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. Thus, it is possible to manually insert the same formatted 16-byte binary value to a uniqueidentifier column more than once. If you need to enforce uniqueness, create a unique index on that column.