Changing the identity seed & increment programatically

31 Aug 2006 2:43 PM

almurph

Hi,

        I want to change the identity values of an identity column of a
table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc.... instead of the
usual 1, 2, 3, 4, etc....
        The current values of the identity seed & identity increment
are the
default values of "1" respectively.

        I'm going to use the following commands. Let me know if you
think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-

Firstly change the identity increment value to 2

ALTER TABLE     A
ALTER COLUMN    identityColumn
IDENTITY        (1, 2)

Then reseed all values so that odd become

DBCC CHECKIDENT('A', RESEED, 0)

    Comments/corrections/suggestions much appreciated.
Al.

31 Aug 2006 2:47 PM

Aaron Bertrand [SQL Server MVP]

> I want to change the identity values of an identity column of a
> table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc.... instead of the
> usual 1, 2, 3, 4, etc....

Why don't you just create a computed column that is IDENTITY_COLUMN * 2?

A

31 Aug 2006 3:04 PM

Anith Sen

Sounds like an awful kludge.

If the sequence of values are critical, avoid identity columns & derive the
numbers ( windowing function, view, subquery etc. ). Otherwise, use Aaron's
suggestion to create a computed column.

--
Anith

31 Aug 2006 4:06 PM

Arnie Rowland

Please don't post the same question in multiple newsgroups -UNLESS
cross-posting is indicated.

It helps us help you when we can bounce ideas off of each other.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

<almu***@altavista.com> wrote in message

Show quote

news:1157035399.540219.286280@i3g2000cwc.googlegroups.com...
> Hi,
>
>        I want to change the identity values of an identity column of a
> table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc.... instead of the
> usual 1, 2, 3, 4, etc....
>        The current values of the identity seed & identity increment
> are the
> default values of "1" respectively.
>
>
>        I'm going to use the following commands. Let me know if you
> think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
>
>
> Firstly change the identity increment value to 2
>
>
> ALTER TABLE     A
> ALTER COLUMN    identityColumn
> IDENTITY        (1, 2)
>
>
> Then reseed all values so that odd become
>
>
> DBCC CHECKIDENT('A', RESEED, 0)
>
>
>    Comments/corrections/suggestions much appreciated.
> Al.
>

Changing the identity seed & increment programatically - please help!