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 the
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.databases.ms-sqlserver/browse_thread/thread/247beeec1ed895db/24b50840c2836164?lnk=st&q=Id+(indentity)+is+increments+on+faults.&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