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

Article translations Article translations
Article ID: 274189 - View products that this article applies to.
This article was previously published under Q274189
Expand all | Collapse all

SUMMARY

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.

MORE INFORMATION

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.

Properties

Article ID: 274189 - Last Review: July 12, 2004 - Revision: 1.2
APPLIES TO
  • Microsoft SQL Server 2000 Windows CE Edition
Keywords: 
kbgrpdssqlce kbhowto kbsqlserv2000 KB274189
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com