Is it a good practice to use identty columns as primary keys? or should
we generate our own keys using a seed table?

Does using Identity give any performance advantages?

Thank you,

Hi

If you don't care about gaps  you can use an IDENTITY propertry as a
PFRIMARY KEY , just thinking  about your business requirements which I don't
know , actuallty  there was lots of discussions about this in this newsgpoup

Yes, it is , especially if it has a CLUSTERED INDEX .




Show quoteHide quote

There is a slight performance gain on large tables.
But I don't use them anymore due to re-design issues on upgrades and third
party ware and a number of other integrations.


Show quoteHide quote

This is an altered version of an older response on the same subject, with
emphasis on fundamentals:

A key is an attribute or set of attributes that can uniquely identify an
entity in the conceptual model. At the corresponding logical level, a column
or set of columns that can uniquely identify a row in a table is defined as
a key. In reality, an entity may have more than one candidate that provides
such identification ( hence are called candidate keys ), however due to
obvious reasons one of those candidate keys should be treated as primal (
hence called primary key ) based on practical considerations. Such
considerations should include:
  i. Familiarity - meaningful to the user.
ii. Stability - non-volatile, should not be altered frequently.
iii. Simplicity - so that queries are easy to express and optimize.
iv. Irreducibility - no proper subset of the key should uniquely identify a
row in the table. ( some treat this also as a derivation to 2NF, which
prohibits partial key dependencies )

Having stated the above guidelines, regardless of the complexity in your
business model & data requirements, an intelligent database design can find
a middle ground by trading off certain characteristics without compromising
the integrity of the data. And one such compromise is the logical surrogate
key which usually defies the characteristic of familiarity but provides
excellent stability and simplicity. Simple and stable keys are often
recommended over composite/volatile keys esp. when they are used in
referential integrity constraints.
http://www.datraverse.com/technology/sql.php

One of the fundamental principles guiding relational design, is a concept
called physical data independence, which can be simply put as: the users
must be presented with a logical view of the data & need not deal with the
physical implementation and storage details. That is primarily why we deal
with tables, columns, keys etc ( which is logical) instead of files, disk
indices, row positions etc (which is physical).  Thus you will see why and
how a surrogate in a clean logical model, should also conform to physical
data independence.

Features like identity, GUID etc provide uniqueness, but there are questions
regarding whether they provide sufficient data independence esp. with
ordering of identity column values at the time of generation. Some aspects
of this issue were addressed here:
http://blogs.msdn.com/sqltips/default.aspx?p=2

The non-updateability of identity columns arguably invalidates relational
assignments and therefore the information principle. Since the internal
mechanism that generates identity values are mostly unknown to the user,
practical data design and quality guidelines involving data verification and
validation cannot be applied to such DBMS generated values either.

At the logical level, performance should be least of your considerations in
key selection, since the primary goal of keys is entity identification that
preserves data integrity. Performance is purely dependent upon the physical
implementation of a database. Obviously values generated and tied closely
and directly to the physical model tend to be better performing. Also values
that are smaller in physical size can improve certain query performance due
to faster disk access i/o etc. Given that identity values are of numeric
datatypes and often smaller in physical size, queries utilizing identity
columns tend to have the same performance benefits.

I have read ("i have never tried it out myself") --- Identity values cannot
be used in merge replication.

Best Regards
Vadivel

http://vadivel.blogspot.com


Show quoteHide quote

If you have no natural primary key then using the IDENTITY property is
significantly better than roling your own because you won't get the same
contention (blocking and serialisation) you get with other roled methods.

IDENTITY does give a performance advantage - there is no locking to get the
next number.

The one disadvantage is that you can get gaps because the calculation for
next number is not transaction aware but that doesn't matter in most cases
i've seen in business.

You really need to PRIMARY KEY using your natural key and then use a
surrogate key (the IDENTIITY) everywhere else - in sps, joins etc... the
primary key is then just meta data.

eg.

create table sector (
    sector_name    nvarchar(100)    not null constraint pk_sector primary
key nonclustered,
    id    int    not null constraint sk_sector unique clustered
)

create table sector_coverage (
    sector_id    int    not null references sector( id ),
    individual_id    int    not null references individual( id )
)

Tony.

Show quoteHide quote

Agreed Tony

Show quoteHide quote

Index Tuning
Running Sum Query ?
Creating dynamic table in SP
User defined fields via application
Access "inserted" / "deleted" from stored procedure
Passing Date Variables
SQL 2005: How to store output from Stored Proc in a Table?
how to update time portion of datevalue to 00:00:00?
finding lower case data
query plan on sql server 2005