MS SQL Server Indentity Jumps

Hi All

I have a table in SQL Server with ID having indentity inrement by one.
Table has not any trigger. Frequently ID in the table jumps.

Any help !!!

ThanksHi

If the transaction fails and rolls back the identity value will not be
reused and can therefore jump. Identities are not guaranteed to be
contiguous.

There is an example in Books online on how to fill non-contiguous identity
values.

John

"Jashan" <Jashan101@.hotmail.com> wrote in message
news:db88da90.0310131559.6d511798@.posting.google.c om...
> Hi All
> I have a table in SQL Server with ID having indentity inrement by one.
> Table has not any trigger. Frequently ID in the table jumps.
> Any help !!!
> Thanks|||Jashan101@.hotmail.com (Jashan) wrote in message news:<db88da90.0310131559.6d511798@.posting.google.com>...
> Hi All
> I have a table in SQL Server with ID having indentity inrement by one.
> Table has not any trigger. Frequently ID in the table jumps.
> Any help !!!
> Thanks

IDENTITY columns are not guaranteed to maintain a sequence of numbers
with no gaps. The new identity value is assigned before the INSERT is
executed, but if the INSERT fails or is rolled back in a transaction,
then the value will not be reused. Books Online also says that the
value may jump if there are a lot of deletions happening on the table
- see the "IDENTITY (property)" topic.

Another possibility is that someone simply inserted the values you see
with IDENTITY_INSERT, perhaps as part of a bulk load operation.

If you need to ensure that there are no gaps in the values in your
column, then you'll need to code your own solution. You could search
Google for "mssql and sequences" to get some ideas.

Simon

Ms Sql

Can anybody help me about this??

I'm new from using the MS SQL and i need a function/procedure/trigger......

there is this function from Delphi (FormatCurr) that I used to create a new record id
...FormatCurr('0000000000',Tqry.FieldByName('Budg etNo').AsInteger+1........

but then i want to make the same thing in MS SQL but i dont know what function to used so instead i did this dumb coding thing

CREATE PROCEDURE budget_newrecord
AS
DECLARE @.budgetno_old int,@.budgetno_new CHAR(10), @.budget_no int
SELECT TOP 1 @.budgetno_old = cast(budgetno as int)from budgetinfo order by budgetno desc

SET @.budget_no = @.budgetno_old + 1

IF @.budget_no <= 0
SET @.budgetno_new = '0000000001'
ELSE IF @.budget_no < 10
SET @.budgetno_new = '000000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100
SET @.budgetno_new = '00000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000
SET @.budgetno_new = '0000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000
SET @.budgetno_new = '000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000
SET @.budgetno_new = '00000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000
SET @.budgetno_new = '0000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000000
SET @.budgetno_new = '000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '00' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '0' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000000
SET @.budgetno_new = cast(@.budget_no as char)

PRINT @.budgetno_new
RETURN @.budgetno_new
GO

Quote:

Originally Posted by clear1140

Can anybody help me about this??

I'm new from using the MS SQL and i need a function/procedure/trigger......

there is this function from Delphi (FormatCurr) that I used to create a new record id
...FormatCurr('0000000000',Tqry.FieldByName('Budg etNo').AsInteger+1........

but then i want to make the same thing in MS SQL but i dont know what function to used so instead i did this dumb coding thing

CREATE PROCEDURE budget_newrecord
AS
DECLARE @.budgetno_old int,@.budgetno_new CHAR(10), @.budget_no int
SELECT TOP 1 @.budgetno_old = cast(budgetno as int)from budgetinfo order by budgetno desc

SET @.budget_no = @.budgetno_old + 1

IF @.budget_no <= 0
SET @.budgetno_new = '0000000001'
ELSE IF @.budget_no < 10
SET @.budgetno_new = '000000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100
SET @.budgetno_new = '00000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000
SET @.budgetno_new = '0000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000
SET @.budgetno_new = '000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000
SET @.budgetno_new = '00000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000
SET @.budgetno_new = '0000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000000
SET @.budgetno_new = '000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '00' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '0' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000000
SET @.budgetno_new = cast(@.budget_no as char)

PRINT @.budgetno_new
RETURN @.budgetno_new
GO

try:

@.budgetno_new = right('000000000'+ rtrim(ltrim(cast(@.budget_no as varchar(12))),9)|||

Quote:

Originally Posted by ck9663

try:

@.budgetno_new = right('000000000'+ rtrim(ltrim(cast(@.budget_no as varchar(12))),9)

Hey ck9663....... thanks a lot...... it really works.......... thank you!!!!!!!!!!!