GUIDs
Mike Collins
company,
our DBA has said we need to use GUIDs for replication. Sometimes we have
stand alone databases that we create that we need to resync back to our
server. But I read a discussion that seemed to frown on using GUIDs...and
even identities if I read it correctly.
Also, I read that if you had to rebuild your database a question was posed
"How would you validate your data if you were using GUIDs?" But no
alternatives were given. GUIDs have to be used if you want to use
replication...correct? Can someone please explain alternatives to using GUIDs
or identity keys and what it means to keep the surrogate key internal in the
context of needing to rebuild a database? Thanks in advance for any
responses.
sloan
"You have to use GUID's for replication"
NO... this is a false statement. There are work arounds. However. "Using
GUID's for 2 way replication makes the MOST SENSE", I'd agree with
statement.
However, IDENTITY's cause big headaches in replication.
If your subscribers are READONLY, then just turn of the identity's on the
subscribers. However, you illude to the fact that they are NOT readonly, as
in , they have to push data back up to the publisher at times.
..
The issue is overlapping values for the primary keys.
By using GUID's you can almost guarantee that the primarykey will be unique
on a table. Thus it makes replication much easier.
If the subscriber has to "send new info back up the pipe" to the publishers
(as you illude to), then I'd recommend guid's as the primary key.
The other (numbnutt) solution (when the replication is 2 way), is to give
each subscriber a "range". (Let me emphasize the "numbnutt" part of this)
Lets consider the EmpID as the primary key for the Emp table.
Publisher gets 1-999
Subscriber1 gets 1001-1999
Subscriber2 gets 2000-2999
Because, if subscriber 2 has to push an Emp back up the publisher, you gotta
ensure a unique value on the EmpID column.
GUID's make it easier.
"How would you validate your data if you were using GUIDs?"
I have no idea what this means.....
There is a learning curve factor of using guid's. Aka, if you need to look
up the Orders for EmpID = 13, you can open the Orders table, and find
EmpID=13 fairly quickly.
If you need to look up orders for EmpUUID =
{C8870086-C1FC-477d-84F2-B27C87AB6904}
, then its a little tougher to open up the Orders table and find EmpUUID =
{C8870086-C1FC-477d-84F2-B27C87AB6904}
(aka, you can't do it from memory, you gotta copy and paste alot)
There shouldn't be "data validation" on the primary key. The primary key is
mostly a enforced unique constraint, making sure there are no duplicates.
Anyway, there's some food for thought.
I use GUID's now, not just for replication needs, but also for the fact I
can build relationships outside of the db.. in my biz logic, and then insert
them.
Take an example where I add a new Customer (Customer table) and Orders for
that customer.
If I use identities, then I don't know the CustomerID of the new customer
until ~~after I insert it. Aka, I usually have to insert the Customer, get
the SCOPE_IDENTITY value (or @@IDENTITY for old schoolers), and pass it
back. Lets say I get back 1333 as the CustomerID
Now that I have it, I can add the Orders, with the foreign key value for
CustomerID as 1333.
I know you can hack it together in tsql, and have it in one procedure.
However, using GUID's I can build ALL relationships in my biz layer, and
then pass everything in.
<MyStrongDS>
<Customer>
<CustomerUUID>{18A6D770-F866-4279-AE28-2B39BD8F7160}</CustomerUUID>
<CustomerName>John Smith</CustomerName>
</Customer>
<Customer>
<CustomerUUID>{73F18F2E-D361-4193-98D3-58586F819D2C}</CustomerUUID>
<CustomerName>Mary Jones</CustomerName>
</Customer>
<Order>
<ItemUUID>{D87AA8D2-10B6-4e2d-925D-03479651E984}</ItemUUID>
<Quantity>100</Quantity>
<CustomerUUID>{18A6D770-F866-4279-AE28-2B39BD8F7160}</CustomerUUID>
<Order>
<Order>
<ItemUUID>{095119B2-4EDC-4621-A1E9-A196CBEF8082}</ItemUUID>
<Quantity>5</Quantity>
<CustomerUUID>{18A6D770-F866-4279-AE28-2B39BD8F7160}</CustomerUUID>
<Order>
<Order>
<ItemUUID>{D87AA8D2-10B6-4e2d-925D-03479651E984}</ItemUUID>
<Quantity>77</Quantity>
<CustomerUUID>{73F18F2E-D361-4193-98D3-58586F819D2C}</CustomerUUID>
<Order>
If you look closely, you see that
John Smith has 2 orders, 100 items of some Item and 5 items of another Item.
Mary Jones has 1 order, 77x.
John and Mary are NEW customers. And I was able to build the relationship
***outside*** of the db, because I'm using a GUID for the CustomerUUID.
Instead of "waiting" for the SCOPE_IDENTITY to tell me what John and Mary's
CustomerID's will be.
Its powerful stuff, once you figure it out.
..
The dba is mostly right. "need is a strong word, but guid's make
replication much much easier.
You said "must resync back" , which points stronger to the GUID solution.
My advice is to get out of the comfort zone, and embrace the GUID as a
primary key.
You might dig up some slight performance issues with it, but get over it.
..
My experience has been that most people resist guid's at first.
Then the first time they build a relationship OUTSIDE of the db, they're
like "Whoah, I didn't know you could do that"
..
sloan
If you don't know what a bulk INSERT is, then see
http://support.microsoft.com/default.aspx?scid=kb;en-us;315968
MS's example is rudimentary, but you can figure it out. It lacks tiered
development, but it'll get you there.
PS
The bonus of using BULK insert is that index-rebuilding is delayed until
AFTER all the records get into the db.
Read that again, .. its a powerful tool if you figure out what it is saying.
..
I have some openxml examples at:
http://www.sqlservercentral.com/columnists/sholliday/
http://spaces.msn.com/sholliday/
"Mike Collins" <MikeC...@discussions.microsoft.com> wrote in message
news:D469F6DF-D9D5-44D4...@microsoft.com...