Microsoft KB Archive/274189

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

Article ID: 274189

Article Last Modified on 7/12/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition

-



This article was previously published under Q274189



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. 

Keywords: kbhowto kbsqlserv2000 kbgrpdssqlce KB274189

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.