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

This article was previously published under Q274189
This article has been archived. It is offered "as is" and will no longer be updated.
SQL Server 2000 Windows CE Edition supports the uniqueidentifier data type. By default, the SQL Server NEWID function is not used in SQL Server CE to generate a unique value for the uniqueidentifier column. However, the ROWGUIDCOL attribute for a column has been overloaded to indicate that a default value should be generated. This behavior is specific to SQL Server 2000 Windows CE Edition.
Applications that create a new table with the uniqueidentifier column in a SQL Server CE database or that subscribe to a publication to download a replica table from SQL Server 2000 (that has a uniqueidentifier column) may need to add new rows to that table.

However, using SQL Server CE, you can still create a table with a uniqueidentifier column and insert data by using the following workarounds:

  • 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.

Article ID: 274189 - Last Review: 12/05/2015 21:50:02 - Revision: 1.2

Microsoft SQL Server 2000 Windows CE Edition

  • kbnosurvey kbarchive kbgrpdssqlce kbhowto kbsqlserv2000 KB274189