Re: problem using identity column as primary key

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 02/24/04 

Date: Tue, 24 Feb 2004 15:53:31 -0000

Hi Nikhil,

If you want something that doesn't leave holes in the sequence, don't use
IDENTITY. To put it more strongly, you shouldn't attach any meaning to the
values in an identity column, it just is an automatically generated value.
It isn't even guaranteed to be unique on it's own (because you can reset it
with a different start value).

You don't have to worry about the number of values in an identity column.
Using an int will give you more than 2 billion rows, and that will last
quite a while. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Nikhil Patel" <nikhil0100@aol.com> wrote in message
news:e6OJGxu%23DHA.712@tk2msftngp13.phx.gbl...
> Hi,
>     I am thinking of creating an identity column to use it as primary key
in
> my table. But the problem is that once I delete some records, the SQL
Server
> would not reuse the IDs of the deleted records. So there will be holes in
> the sequence. I know many people use Identity columns to generate unique
id.
> How do they deal with the holes in the identity columns? Do I need to
worry
> about the size of the identity column or whether or not the column would
run
> out of IDs or any other problem?
>
> thanks...
> -Nikhil
>
>

Relevant Pages

  • Re: problem using identity column as primary key
    ... Identity is a great way of generating a primary key. ... values in a clean sequence without and holes is not an issue, ... > about the size of the identity column or whether or not the column would ...
    (microsoft.public.sqlserver.programming)
  • problem using identity column as primary key
    ... I am thinking of creating an identity column to use it as primary key in ... would not reuse the IDs of the deleted records. ... How do they deal with the holes in the identity columns? ... about the size of the identity column or whether or not the column would run ...
    (microsoft.public.sqlserver.programming)
  • Re: Sequence numbering
    ... Could you try using a column with a IDENTITY column. ... ClaimNumbers within the DB, but each -inbound- file will contain unique ... declare @Seq BigInt ... Select @Seq as Sequence ...
    (microsoft.public.biztalk.general)
  • Re: Primary keys
    ... gaps in the sequence. ... Don't even assume an IDENTITY column will be unique ... unless you have a unique constraint on it. ...
    (microsoft.public.sqlserver.server)
  • Re: Computed field (easy question I think)
    ... Personality conflicts notwithstanding, it is clear that if you require sequential numbers with no interruptions, an IDENTITY column and/or a computed column are both impractical options for you. ... id and a sequence)? ... It may match or even exceed your own in some cases (not in SQL Server necessarily, ...
    (microsoft.public.sqlserver.programming)