Identity Column

Hi, all:
Server: SQL Server 2000.
Aplication: Visual Basic 6.
S.O. Windows 2003 Server.
I have a table that has an identity column (Identity = yes,autoincrement =
1, Seed = 1).
When I Insert a row which creates a truncate error, the system increases the
counter (identity column by 1) - Why if the system didn`t allow to insert th
e
new row?
How can I prevent the system from increeseing the value on the identity
column in this situation?
Thanks.
--
RickYou can't. Identity values are guaranteed unique. They are not guaranteed
consecutive.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:7E677379-046E-44A9-A1C9-2F0C7D4CA502@.microsoft.com...
> Hi, all:
> Server: SQL Server 2000.
> Aplication: Visual Basic 6.
> S.O. Windows 2003 Server.
> I have a table that has an identity column (Identity = yes,autoincrement =
> 1, Seed = 1).
> When I Insert a row which creates a truncate error, the system increases
> the
> counter (identity column by 1) - Why if the system didn`t allow to insert
> the
> new row?
> How can I prevent the system from increeseing the value on the identity
> column in this situation?
> Thanks.
> --
> Rick|||Rick,
It is the behavior of the IDENTITY property. See the following for a
possible workaround:
http://groups.google.com/group/comp...64?lnk=st&q=Id+(indentity)+is+increments+on+fau
lts.&rnum=1&hl=en#24b50840c2836164
HTH
Jerry
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:7E677379-046E-44A9-A1C9-2F0C7D4CA502@.microsoft.com...
> Hi, all:
> Server: SQL Server 2000.
> Aplication: Visual Basic 6.
> S.O. Windows 2003 Server.
> I have a table that has an identity column (Identity = yes,autoincrement =
> 1, Seed = 1).
> When I Insert a row which creates a truncate error, the system increases
> the
> counter (identity column by 1) - Why if the system didn`t allow to insert
> the
> new row?
> How can I prevent the system from increeseing the value on the identity
> column in this situation?
> Thanks.
> --
> Rick|||Rick wrote:
> Hi, all:
> Server: SQL Server 2000.
> Aplication: Visual Basic 6.
> S.O. Windows 2003 Server.
> I have a table that has an identity column (Identity =
> yes,autoincrement = 1, Seed = 1).
> When I Insert a row which creates a truncate error, the system
> increases the counter (identity column by 1) - Why if the system
> didn`t allow to insert the new row?
> How can I prevent the system from increeseing the value on the
> identity column in this situation?
> Thanks.
Why does it matter if the assigned numbers are sequential? If that does
matter in your database design, then it's likely using an identity is
not the correct choice.
David Gugick
Quest Software
www.imceda.com
www.quest.com