UID as PK

Thanks, Erland. Yes, these are GUIDs, however, because it's several
years old the columns are defined as Char(36) and populated by NewID()
rather than what you suggest.

My more general question is why one needs a unique key. If the data
will truly be unique, then select a key from the columns in the table.
But if the data aren't always going to be unique, why have a PK at all?

You mentioned replication, and these tables get mirrored back and forth
among three servers that each handle some of the app servers, and a
central archive server, so maybe that's why they need a unique key -- I
supose two different working servers could create the same row (in the
critical columns) if their app servers get the same loan to underwrite.
I'm a software developer who does some DB definition and programming,
not a DBA, so I never got education about these matters.

Thanks,
Jim Geissman

In article , esquel.DeleteThis@sommarskog.se
says...
> JG (jgeissman@socal.rr.com) writes:
> > I notice some of the tables at work use a UID, that's a default created
> > during insertion, as a non-clustered primary key. I wonder what's the
> > purpose of that -- it seems like extra work that merely confirms the
> > UIDs are all different, but so what? I assume the DBA specialist who
> > created this knew what he was doing, and I'm missing something.
>
> That is a GUID?, a value of the data type uniqueidentifier?
>
> There are plenty of situations where surrogate keys are called for. The
> natural key is too unwieldy to work with, or cannot be relied on being
> 100% unique. This leads more than one database designer to slap surrogate
> keys on all tables, without really trying to find the natural key.
>
> There may also be features in SQL Server or an application framework that
> requires a surrogate to work with. One such example is merge replication
> which requires all table to have a rowguid column.
>
> Without knowing more about your tables at work, it's impossible to say
> whether these columns are really useful or not.
>
>
>
 >> Stay informed about: UID as PK 

JG (jgeissman@socal.rr.com) writes:
> Thanks, Erland. Yes, these are GUIDs, however, because it's several
> years old the columns are defined as Char(36) and populated by NewID()
> rather than what you suggest.

That's certainly not efficient. Takes more space, and character comparison
is more expensive, unless the columns has a binary collation.

By the way, these columns has to be very old, if the excuse is that
they were added before SQL Server had the uniqueidentifier data type -
this type was added in SQL 7.

> My more general question is why one needs a unique key. If the data
> will truly be unique, then select a key from the columns in the table.
> But if the data aren't always going to be unique, why have a PK at all?

Most of the time, you do want to have a PK, even if there is no unique
key in the data. Makes it easier if you present a row in a GUI, and then
want to update that same row.

In many cases when there is no 100% unique key in the data, there is
a near-unique key. Maybe because there is a difference in an attribute
that was over-looked, or not deemed worthwhile to record in the database.

> You mentioned replication, and these tables get mirrored back and forth
> among three servers that each handle some of the app servers, and a
> central archive server, so maybe that's why they need a unique key -- I
> supose two different working servers could create the same row (in the
> critical columns) if their app servers get the same loan to underwrite.

Maybe, but the replication functions in SQL Server requirs a real
uniqueidentifier column, so in this case this is something homegrown.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: UID as PK 

JG wrote:
> My more general question is why one needs a unique key.

http://www.psoug.org/files/codd-1970.pdf
--
Daniel A. Morgan
University of Washington
damorgan.DeleteThis@x.washington.edu (replace x with u to respond)
 >> Stay informed about: UID as PK 

"JG" wrote in message

>
> My more general question is why one needs a unique key. If the data
> will truly be unique, then select a key from the columns in the table.
> But if the data aren't always going to be unique, why have a PK at all?
>

Because duplicate data (redundancy) causes anomalies, incorrect results and
wastes storage and processor cycles (among other things).

In a truly relational DBMS all data would be represented as relations, which
don't have duplicate tuples and always have keys. SQL compromises this
principle and because of this database designers have to go to a lot more
trouble to get the right results out of the database. Generally speaking the
SQL language makes it hard to work with duplicate rows, even though keys are
optional in SQL tables. For most purposes SQL statements require a key in
order to properly identify individual rows. There are certain exceptions and
workarounds but to take full advantage of the SQL language you usually need
to ensure the keys are being enforced.

This is fundamental stuff so I suggest you might want to read a good book on
the basics, such as:

http://www.amazon.com/Introduction-Database-Systems-C-J-Date/dp/0321197844

--
David Portas
 >> Stay informed about: UID as PK 

Thanks everyone. I see the newer tables use uniqueidentifier as the
data type for this key column.

Jim
 >> Stay informed about: UID as PK 

>> My more general question is why one needs a unique key.  If the data will truly be unique, then select a key from the columns in the table.   But if the data aren't always going to be unique, why have a PK at all?  <<

Think about it. A VIN is the unique identifier for an automobile. It
is a great key; unique, required by law, has verifiability and is easy
to validate, etc. Now look at car color; it changes. Nobody wants to
drive some of the things we has in the 1960's -- have you seen an
avocado green refrigerator? When I re-paint my 1965 Studebaker Silver
Hawk to candy apple red, it is still the same automobile.

Some attributes are invariant and some are not.
 >> Stay informed about: UID as PK 

..>> Anything that is required by law is likely to be a difficult key,
since laws are different in different countries. <<

The VIN is both ISO and **very international**' people lie to buy and
sell automobiles. If you want a really bad international standard
the quote the IIBAN ..
 >> Stay informed about: UID as PK 

--CELKO-- (jcelko212@earthlink.net) writes:
> Think about it. A VIN is the unique identifier for an automobile. It
> is a great key; unique, required by law,

Anything that is required by law is likely to be a difficult key, since
laws are different in different countries.


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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: UID as PK