Best method for auto increment a PK

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best method for auto increment a PK

Author

Topic

solent
Starting Member

33 Posts

Posted - 2006-03-09 : 13:25:44

what is the best method that one can use to auto increment the PK of data type int.

I know one method is using MAX(ID) + 1 but are there any other methods that are better in any way?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 13:32:52

The best method is to use identity option on the column. Using MAX(ColumnName) + 1 is a very bad practice.

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-09 : 13:44:29

An integer IDENTITY column is the easiest way to do it. SQL Server automatically generates the next number when you insert a new row, and there is much less overhead.

CODO ERGO SUM

solent
Starting Member

33 Posts

Posted - 2006-03-09 : 13:58:27

the reason i do not want to use the IDENTITY column is that when i run my insert sproc i need to insert to the same ID to table1 as well as to table2. can that be done if the column is IDENTITY and if yes can you give me some guidlines to it.

thanks for the quick replies guys.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-03-09 : 13:59:33

You can use the SCOPE_IDENTITY() function to return the last identity value used in your table. You can then use this value to insert the row in your second table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 14:05:30

Here is an example. Table1 has 3 columns: Column1 (Identity), Column2, and Column3. Table2 has 2 columns: ColumnA (PK for Table2), and ColumnB (FK reference back to Table1)

DECLARE @i int

INSERT INTO Table1 (Column2, Column3)
VALUES('SQLTeam', 'Rules')

SET @i = SCOPE_IDENTITY()

INSERT INTO Table2 (ColumnA, ColumnB)
VALUES('PKValue1', @i)

Tara Kizer
aka tduggan

solent
Starting Member

33 Posts

Posted - 2006-03-09 : 14:32:42

ok thanks very much for the replies.

this will work even if i pass the @i as an output parameter to an int variable to the asp.net application and then send it as an input parameter to the Insert sproc for table2 because that is how it has to work. Both procedures must be executed separetly.

again thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-09 : 14:38:15

Yes.

Tara Kizer
aka tduggan

solent
Starting Member

33 Posts

Posted - 2006-03-09 : 15:30:11

thanks tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources