How do I create a trigger to insert a value into an ID field that is Max([ID Field])+1 on insert

When I add a new record I want SQL Server to automatically add a fresh ID.

There are already some records which have been migrated over (from Access) and until I finish preparing the server for other required functionality I will be manually migrating further records over (if this affects any possible answers).

What are the simplest ways to implement this.

Answer

The simplest way would be to make the column an IDENTITY column. Here is an example of how to do this (it’s not as simple as ALTER TABLE).