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.
Submit an article or tip