Question about ROWGUIDCOL property!

[nwm76]

Hello Gurus,

I'm in the middle of my 70-229 certification and have been messing around with defining identity and rowguidcol values in some tables. I'm struggling to understand what the rowguidcol property actually does. Allow me to illustrate my problem:

create table t1 (
c1 uniqueidentifier rowguidcol default newid() not null,
c2 varchar(20)
)

and:

create table t2 (
c1 uniqueidentifier default newid() not null,
c2 varchar(20)
)

Both statements seem to be equivalent as far as I can see in terms of auto-generating GUID values in c1. The only difference I can spot is that the first statement allows you to query c1 using:

select rowguidcol from t1

whereas the 2nd statement doesnt allow this. Is this the only difference or am I missing a trick somewhere?

Thanks guys,
Neil

 

[Catadmin]

That is true in and of itself, but don't forget one important thing about RowGuidCol:

It's used in Replication.

RowGuidCol generates a completely unique ID across your network (not just on a table or on a database).

Identity just implements a "count" of sorts that is incremented by any number you please and starts at (Seed) any number you please, but can be duplicated on another database, another table, and even another column. Quite literally, I believe, you can have two columns on the same table, both set as Identity with a Seed of 5 and an increment of two which means both columns start at 5 and then count 7, 9, 11, 13, 15, etc.

Because RowGuidCol is so completely unique, it is used in publications/articles to keep track of information that has and has not been replicated to the subscribers.

Or at least that's the way I understand it. If anyone has info to the contrary, please correct me.

Hope this helps.





Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

newid() by it self will generate a completly unique value everytime it is run.

The rowguidcol is used by replication so that if you define your own column for it to use to control the replication (god only knows why you'd want to do this) it can still find the values.

Uniqueidentifiers (when auto generated) are created by using the servers mac address, name, system time to the thousands of a second, and random numbers to create the unique string. (As far as I understand that's what's used to create the string. I haven't actually found any docs on it. If someone from Microsoft wanted to chime in here that would be very cool.)

Good luck on the 229. I'm taking mine tomorrow.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)