What would we ever do without database sequences?

In SQL Server, sometimes people are tempted to insert a record in a table with an autonumber primary key and then select the maximum primary key to see what number comes out.  I strongly recommend using sequences instead of autonumber in modern versions of SQL Server (2012+), it will save your sanity when trying to determine if there are concurrency issues.  This is not so much the case if all your insertion activities are consolidated into a stored procedure.  In the event that you are doing in-line SQL on an older version of SQL Server, I’ve seen the following practice that looks good to me and pretty simple:

String qry=@"INSERT INTO
TBL_CAR(empl_no,
Driver_Name,
Division_Name,
Last_Date_Modified)
VALUES('{0}','{1}','{2}','{3}')
SELECT SCOPE_IDENTITY()";

return ExecuteScalar(String.Format(qry, emplNo, drn, division, date));

Aside from the glaring problem that this code is probably vulnerable to SQL injection attacks, doing the insert and the select in one operation gives us access to scope_identity and we can use it for inserting child records in another table in subsequent operations.