Over a million developers have joined DZone.

SQL Server Compact Code Snippet of the Week #1 : Locate the ROWGUIDCOL Column

To stay on top of the changing nature of the data connectivity world and to help enterprises navigate these changes, download this whitepaper from Progress Data Direct that explores the results of the 2016 Data Connectivity Outlook survey.

During the next many weeks, I plan to publish a short, weekly blog post with a (hopefully) useful code snippet relating to SQL Server Compact. The code snippets will come from 3 different areas: SQL Server Compact T-SQL statements, ADO.NET code and samples usage of my scripting API.

The ROWGUIDCOL column property is defined like this in Books Online:

Indicates that the new column is a row global unique identifier column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

ROWGUIDCOL automatically generates values for new rows inserted into the table.

(You can also use a default of NEWID() to automatically assign values to uniqueidentifier columns)

The ROWGUIDCOL is used by Merge Replication, all Merge Replicated tables must have a ROWGUIDCOL column.

Enough talk, show me the code snippet:

SELECT column_flags, column_name, table_name 
FROM information_schema.columns
WHERE column_flags = 378 OR column_flags = 282

I am using the undocumented “column_flags” column to determine the ROWGUIDCOL column, and the reason for the 2 different values is that a uniqueidentifier column can be either NULL or NOT NULL.

Turn Data Into a Powerful Asset, Not an Obstacle with Democratize Your Data, a Progress Data Direct whitepaper that explains how to provide data access for your users anywhere, anytime and from any source.

Topics:

Published at DZone with permission of Erik Ejlskov Jensen, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}