Identity Vs. UniqueIdentifier - With a twist (!?)

[Andreas Håkansson]

I'm about to build a datebase that will be used in a replication-based
envorinemet and I have some questions about what method I should
use for my ID columns in the tables.

It's my first time building a datebase of this size so as you can imagine
I have had a lot of bridges to cross and questions to get answered,
this is one of them.

My "problem" has to do with what kind of datatype I should be using
with my ID columns (PK's) in the tables. I first though, oh well using
any kind of identity field will only leave me with an overflow error in
the future (even if I choose a bigint - it's just a question of time, though
a long time), but that was soon put to ease when I read the following
article http://www.sqlteam.com/item.asp?ItemID=4123

Even so I though I'd check up abit on the uniqueidentifier datatype
and it's advantages / disadvantages. The inital "problem" I realised
was that they are 16 long and an int is only 4, so it's four times as
large - That means more data for each row in each table.

But then again, in my case using an int will actually mean more data
to be replicated then if I were to use uniqueidentifiers directly for my
PK's. Why?

Well as you proberbly already know. SQL Server uses ROWGUID's
quite extensivly in an replicated environement to keep track of each
row in the replication. So if I dont have such a column in my tabels,
SQL Server will provide on when the data is being replicated.

So if I were to use Int's wouldnt that mean that I would have to replicate
4 (int) + 16 (SQL provided ROWGUID column) = 20 opposed to only
16 is I were to make my PK's into uniqueidentifiers and set the "Is RowGuid"
property to True, then there will be a column for the server to use when
replicating the data, as well as I have an "endless" number of ID's to use
for my rows.

There is another problem http://www.sqlteam.com/item.asp?ItemID=283
with GUID's and thats the fact that you cant get the guid from a new
row imediatly after it's created like you can use the @@identity when you
use identity columns, but what if I were to tell you that my GUID's will
be generated from an application before insertion (since the application
needs to tag each object with an unique id), doesnt this free me from that
problem? Ok not if I use triggers or sp right?

Any comments / ideas and feedback on this topic?

// Andreas Håkansson

[Tony Rogerson]

The way I've implemented this scenario in the past and present is to use an
identity property as my primary key - I will then use idenitity ranges for
my different sites, also, the uniqueidentifier rowguidcol on there as well
just for replication purposes.

When picking up the newly inserted row make sure you use SCOPE_IDENTITY().

--
Tony Rogerson SQL Server MVP
Independant Consultant
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK Independent SQL Server User Group; FAQ; KBase
etc..]

"Andreas Håkansson" <and...@dbi.se> wrote in message
news:uNvXk2pFBHA.1392@tkmsftngp05...

[Andreas Håkansson]

Yes, but doesnt this mean 4 additional bytes of data for each row? Or is
that out weighed by the increase in search time when searchign on an
ID column?

"Tony Rogerson" <tonyro...@compuserve.com> wrote in message
news:Ou9gu7pFBHA.2208@tkmsftngp07...

[Tony Rogerson]

You aren't going to notice a difference, 4 bytes per row really isn't alot
in the schema of things; the convience of the identity out weighs any
increase in space imho.

--
Tony Rogerson SQL Server MVP
Independant Consultant
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK Independent SQL Server User Group; FAQ; KBase
etc..]

"Andreas Håkansson" <and...@dbi.se> wrote in message

news:O$PEuKqFBHA.1388@tkmsftngp05...

[Andreas Håkansson]

Ok so both an ID and a GUI column in my tables. Both of them can be
set to PK's. But what about the relations between the tabels, do you
created the relations based on the identity column or the guid column.
My guess would be the id column. Is there any point in creating
the relations for both of them ?

"Tony Rogerson" <tonyro...@compuserve.com> wrote in message

news:uRyb6MqFBHA.1660@tkmsftngp04...

[Tony Rogerson]

You can only have one pk per table, make that on the ID (idenitty) column.

Just leave the uniqueidentifier one alone, create it with a default of
newid(), rowguidcol not null and create a unique nonclustered index on it -
aside from that forget its there!

Do all you FK's etc... on the identity column

--
Tony Rogerson SQL Server MVP
Independant Consultant
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK Independent SQL Server User Group; FAQ; KBase
etc..]

"Andreas Håkansson" <and...@dbi.se> wrote in message

news:#iuwnbqFBHA.980@tkmsftngp04...

[Andreas Håkansson]

Yep I agree on all but to use the newid() and forgett about the column
exists =)

I might not have stated this very clear before, but the database will be
used by
a win32 application and the application wi'll be responsible for creating
the
GUID's since the application needs to know the GUID for each object (row)
it loads/creates directly. If I were to create a new object (row in db) with
the
application and have newid() assign a GUID to it, I would have to query the
database to get the ID and then stick it in the object (in this case an
instance of
a class) in my application.

But this isnt a problem for me to setup. On thing though - for replication
purpose.
Should I set the "Is RowGuid" to "yes" for my uniqueidentifier column ?

"Tony Rogerson" <tonyro...@compuserve.com> wrote in message

news:e7zLomqFBHA.2224@tkmsftngp07...

[Tony Rogerson]

Yep, otherwise merge replication won't work !

I would as a safety measure put the NEWID() default on the column; this
shouldn't effect the app as it will be specifying the value.

--
Tony Rogerson SQL Server MVP
Independant Consultant
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK Independent SQL Server User Group; FAQ; KBase
etc..]

"Andreas Håkansson" <and...@dbi.se> wrote in message

news:O8YfD5qFBHA.1832@tkmsftngp05...