MS SQL Server generate ID

[gustaf111]

Hello,
I have two problems that are connected that I would like to solve. I hope anybody that knew this have time to help me!

1) I have an application that requests a new ID from my MS SQL Server Express 2088. I would like the application to get next avaiable ID. e.g if the ID's are 1,2,3,5 then I would like the application to get ID = 4 and if the list lock like: 1,2,3,4 then I would like the application to get ID = 5.

2) How do I prevent two application to get the same ID from the SQL server ?


Cheers Gustaf

 

[simian336]

if the ID's are 1,2,3,5 then I would like the application to get ID = 4

Why is one skipped over? Are you allowing them to be deleted?

Simi

 

[gustaf111]

Hello simian,

There will be possibility to add one number manually. But if it is big problem I canm skip it ...

The problem number two is more important

Cheers gustaf

 

[gustaf111]

When I insert a new record in a table can I increment one column, and get that number back so I can use it ?

 

[simian336]

if you define the column as Identity field it will automatically incrament for you. That is the most common way.

Simi

 

[gustaf111]

Okay. Will the column increment when I do an insert ? How do I do which ID number my insert got ? If someone do an insert almost at the same time....

Thanks simian!

 

[gmmastros]

Identity columns are the normal way of doing something like this. Done properly, you will not need to worry about multiple (simultaneous) inserts because SQL Server will handle that for you.

There is a built-in function called Scope_Identity() that will return the last inserted id based on the "scope" of the operation. This means you will get the ID for the row you just entered and others will get the ID for the row they just entered.

To work with an identity column....

Create Table #TestIdentity(Id Int Identity(1,1), Name VarChar(10), Color VarChar(10))

Insert into #TestIdentity(Name, Color) Values('George','Orange')
Select Scope_Identity() As MyNewId

Insert into #TestIdentity(Name, Color) Values('Tom','Blue')
Select Scope_Identity() As MyNewId

Insert into #TestIdentity(Name, Color) Values('Mary','Pink')
Select Scope_Identity() As MyNewId

Insert into #TestIdentity(Name, Color) Values('Bob','Purple')
Select Scope_Identity() As MyNewId

Drop Table #TestIdentity

Note that the code shown above uses a temp table to insert in to. This is done to demonstrate what the subsequent inserts and selects would look like.

Please note that you may find advice on the internet telling you to use @@Identity. Do not do this because it is not always safe to do so. It's not safe when there are triggers on your tables that insert in to other tables with identity columns. This may not be the case in your situation right now, but it may in the future.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gustaf111]

Thanks!!! Great suport!!

I access the SQL from a excel VBA. How do I write the Scope_Identity() from VBA ?

 

[gmmastros]

I assume you are using something like ADO to connect to the database. If not, the following code may (or may not) work for you.

cSQL = "Set NOCOUNT On;"
cSQL = cSQL & "Insert into YourTable(Name, Color) Values('Tom','Blue')"
cSQL = cSQL & "Select Scope_Identity() As NewId"

You would then execute the SQL String the same way you would any other query that returns a recordset. In this situation, your recordset will have exactly one row and one column named "NewId".


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gustaf111]

Thanks!! And it is safe to do it after each other ? I will get my "ID" ?

 

[gustaf111]

I use MS Server Express 2088. When I create the table in Management studio I can not find the Identity Data type. Whick should I use ?

 

[simian336]

It needs to be and interger. Then down at the bottom in the column propeties under identity specification (is identitiy)=yes.

Simi

 

[gustaf111]

Thanks!