Posted by Hugo Kornelis on 12/04/07 20:57

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. <shudder>

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

[Back to original message]