SQL index question (C#)

Hello,

im quite new to MS SQL.
My question is how to generate indices
MySql has an attribute AUTO_INCREMENT, but MS SQL doesn't seem to have this.

Example:
I have a user table with [Id] und [Username] and a mail table with [UserId] and [Address]
If I want to add a new user, can the database generate the new Id itself. And if, where do I get this new index from for adding a mail address
Or do I have to generate the new index myself If yes, how resolve the next index number extra query before the insert, to get the last index

thanks and greets from germany!
Dirk

Answer this question

SQL index question (C#)

mago
And when I do the INSERT from a managed stored procedure
int index = (int)sqlCommand.Parameters["@@identity"];
IMBogus1

Something like that (there is nothing wrong with using SET and a parameter instead of SELECT):

"INSERT INTO Table (...) VALUES (...); SELECT SCOPE_IDENTITY();"

and then you might execute ExecuteScalar or something instead of ExecuteNonQuery.

Note that SCOPE_IDENTITY() is better than @@Idenetity.
Nick Hanson

set @yourvar = @@identity
sarvesh m
Thanks,

that was what I was looking for...

What is the right way to receive the newly created index after an INSERT

thanks.
Tryst

Of course it has. Assuming you are using EM or SSMS set column's Is Identity property to true and adjust Identity Increment and Seed if necessary.

If you are a DDL guy then do something like:

CREATE TABLE [dbo].[TableName](

[Id] [int] IDENTITY(1,1) NOT NULL,

...
SQL index question (C#)

Answer this question