Auto-increment value in SQL

Question

Post reply

Auto-increment value in SQL

Craig Kornacki

SSC Enthusiast

Points: 109
More actions
March 30, 2004 at 7:13 am

#84658

I work for a document imaging company. One of my tasks is to create a form that auto-increments with a Work Order number each time the customer opens the form. I know what I want to happen, but I'm having a hard time with the details.
The customer owns SQL Server 2000. I can create a database with a field that can store a number (starting with 1). Then, I want that field to increase by 1 each time the form is opened in the browser.
Right now, I want to know how to make the database do it's job. I think I may want to do something like MAX()+1, but how do I put that into place? I have no experience with data manipulation within SQL (some experience with Microsoft Access.) Also, is there an increment field or an 'increment identity' to make this happen?
My secondary concern is using VBScript to look to the "auto-increment" field and populate that data in the Work Order number field. (...But that might be a question for another site.)
If anyone has any ideas how to make the auto-increment field work in SQL Server, I'll be happy.
I appreciate your help.
Craig Kornacki

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

When you create the table use the identity for the column definition

CREATE TABLE testuser (THE_ID_COL INT IDENTITY(1,1))

You can change the 1,1 to reflect changes in either the starting number, or the increment.

Shamless self promotion - read my blog http://sirsql.net

Craig Kornacki SSC Enthusiast Points: 109 More actions · Answer 2

I tried your suggestion and the ID_COL field is blank! At what point would this field have a value in it?

Thanks for your help!

Craig K.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

Whenever you insert a record. You create a proc to just return an incremental value by using a procedure and scope_identity(), which you could then use to populate another table

create proc instest

as

begin tran

insert testuser default values

rollback tran

select scope_identity()

exec instest

Shamless self promotion - read my blog http://sirsql.net

Craig Kornacki SSC Enthusiast Points: 109 More actions · Answer 4

So, if you populated the table once, you would be able to retrieve and increment each time you accessed it. Is that correct?

Thanks.

Craig

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

Populating the table just adds rows, this is best used as an identity when doing an insert. If you wanted to insert against more than one table for example and keep the identity between the two you would incorporate the sp above, as this would give you an incremental value without actually putting any data in that table (it's like an incremental lookup table).

Shamless self promotion - read my blog http://sirsql.net

Craig Kornacki SSC Enthusiast Points: 109 More actions · Answer 6

Thanks. The script will probably work with SQL Server 2000, but I need to test it with SQL Server 7.0 in the office. I don't think scope_identity can be used in 7.0.

What script would I use for 7.0?

Thanks again-

Craig

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 7

You could try using @@identity, scope_identity is new to SQL2k

http://www.developerfusion.com/show/3704/2/

Shamless self promotion - read my blog http://sirsql.net

Craig Kornacki SSC Enthusiast Points: 109 More actions · Answer 8

OK! I've made some progress...

Couple of questions. How do I reset the counter back to 1? Is there a max limit to the number that this field can go up to?

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 9

DBCC CHECKIDENT (<tablename&gt --Resets the ident

Max limit on the number is the max value of the column, in this case int so 2,147,483,647....you could use bigint which would take you up to 9,223,372,036,854,775,807.

Shamless self promotion - read my blog http://sirsql.net

vhanda SSCrazy Points: 2240 More actions · Answer 10

Identity values are useful for uniquely identifying rows in a table, not really suited for tracking arbitrary incremental values, as it appears you are trying to do. If you're looking to track number of accesses against some table and store this value somewhere, you might do something like this in a user sproc:

-- start example

begin tran

select @incvar = trackingcol with (tablockx, holdlock) from sometable

set @incvar = @incvar + 1

update sometable set trackingcol = @incvar

commit

select @incvar -- retrieve new the value

-- end example

In this example, your app would call this sproc to perform the manipulation and the "select @incvar" will retrieve the value back to the app.

It is essential that you perform the next number calculation and update the value in the same transaction to maintain integrity of this value. The tablockx & holdlock ensure that nobody else retrieves or clobbers this value until the transaction ends. Also, by centralizing this stuff in a sproc, you can add also logic to reset the counter independent of any identity restrictions on the column.

Hope this helps

Vik