Working with
Max(id) + 1
is not an ideal solution. You should work by setting the column as
Identity
. Here is how you can do this
IDENTITY (Property) (Transact-SQL)[
^]
[EDIT]
You can return the Identity value in three different ways.
1.
IDENT_CURRENT[
^] T-SQL
2.
@@IDENTITY[
^] T-SQL
3.
SCOPE_IDENTITY()[
^] T-SQL
Each one of them have their own way of returning the identity value.
E.g If you have a Table called
Movie
with columns
MovieID, Name
, datatype
int
and
nvarchar(30)
respectively and
MovieID
is set as an
Identity
column, then
INSERT INTO Movie VALUES('BatMan')
SELECT SCOPE_IDENTITY()
T-SQL will yield the last inserted movie,"BatMan" identity value.
Things to note regarding an
Identity
T-SQL,
1. A table will contain only one
Identity
column.
2. An
Identity
column can only be applied to
tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0)
datatypes.