When I use SQL Server with an int based identity column, I always use the auto increment. But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what is the best practices way of doing this.
The naive approach would be to query the data, increment it, and then use that for an insert.
INSERT INTO myTable ( ID, Data ) Values ( (SELECT MAX(ID) FROM myTable) + 1, "My actual data." )
But I'm unsure if this is actually the best approach. Namely, since this is all one statement, is there a risk for a heavily utilized system to have another row inserted between selecting the MAX(ID) and the insert.
Edit: Sql Server version 2012.
Edit 2: To specify, I'm looking for a DML solution as I do not have DDL rights to modify the table.