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: July 12, 2004 - Revision: 1.2
- Microsoft SQL Server 2000 Windows CE Edition
|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.