Identity Columns - Design Question

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

> Is it a good practice to use identty columns as primary keys? or should
> we generate our own keys using a seed table?
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

> Does using Identity give any performance advantages?
Yes, it is , especially if it has a CLUSTERED INDEX .
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
> 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,
>|||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.
"Uri Dimant" wrote:

> 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 newsgpo
up
>
>
> Yes, it is , especially if it has a CLUSTERED INDEX .
>
>
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
>|||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.
Anith|||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
"S Chapman" wrote:

>
> 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,
>|||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 divantage 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.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
> 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,
>|||Agreed Tony
"Tony Rogerson" wrote:

> 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 th
e
> next number.
> The one divantage 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.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
> news:1149591940.115554.74460@.y43g2000cwc.googlegroups.com...
>
>