What is the purpose of the ROWGUIDCOL property?

Greetings,

What is the point of Microsoft defining a ROWGUIDCOL property that can
be attached to a 'uniqueidentifier' column? This is defined as a column
that is 'globally unique', but doesn't the uniqueidentifier datatype
already guarantee that? To make matters more confusing, they tell you
in Books Online to add a Unique constraint because ROWGUIDCOL does not
guarantee uniqueness...so what's the point?

Apparently the only functionality attached to this property is that only
one such column can exist per table and that it can be queried using the
$ROWGUIDCOL keyword in SQL.

Can anyone tell me the rationale for when to use this and when not to,
or what the purpose of this property is?

Thanks,

Sam Bendayan
DB Architect
Ultimate Software
sam.bendayan@gmail.com

*** Sent via Developersdex http://www.developersdex.com ***

Re: What is the purpose of the ROWGUIDCOL property?

On 04 Dec 2007 16:19:48 GMT, Sam Bendayan wrote:

>Greetings,
>
>What is the point of Microsoft defining a ROWGUIDCOL property that can
>be attached to a 'uniqueidentifier' column? This is defined as a column
>that is 'globally unique', but doesn't the uniqueidentifier datatype
>already guarantee that? To make matters more confusing, they tell you
>in Books Online to add a Unique constraint because ROWGUIDCOL does not
>guarantee uniqueness...so what's the point?

Hi Sam,

Starting from the end:

* Having a column defined as uniqueidentifier with a default of NEWID()
or NEWSEQUENTIALID() does not by itself uniqueness. First, I believe
that there is some (extremely unlikely) chance that generated guid
values can be duplicates; second, SQL Server does not prevent you from
manually entering data in this column, overriding the default.

Other that that, explicitly declaring uniqueness in a column gives
several other benefits as well. It gives a wealth of information to the
optimzier regarding data distribution, it makes the column eligible as
the target of a foreign key constraint, and it automatically creates an
index that can be used to speed up queries accessing this column.


The point of setting the ROWGUIDCOL property is to enable you to use
$ROWGUID instead of the column name in a SELECT list. Frankly, I shudder
when I attempt to think of scenario's where this would be useful, since
it implies that you select from a table without knowing yourself what
column is used for what purpose.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Re: What is the purpose of the ROWGUIDCOL property?

Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
> The point of setting the ROWGUIDCOL property is to enable you to use
> $ROWGUID instead of the column name in a SELECT list. Frankly, I shudder
> when I attempt to think of scenario's where this would be useful, since
> it implies that you select from a table without knowing yourself what
> column is used for what purpose.

It's used a by several features in SQL Server. Merge replication is one.
The new FILESTREAM feature in SQL 2008 also requires the table have a
ROWGUIDCOL.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx