How do I get something similar to the SERIAL PG datatype in SQL Server 2008?

Question

I'm helping implement a SQL Server 2008 database and I have several columns that I'd like to default to the next number in a sequence, but still be able to insert numbers by hand into it at will. In PostgreSQL this is done with the SERIAL datatype and a sequence. Unfortunately those things do not exist in SQL Server. Here's what I know WON'T work.

1. An identity column does not allow inserts unless you set IDENTITY_INSERT to on. In that case it works as desired but according to the MSDN this property can only be set on one table at a time and I'd like to do this for multiple tables.
2. A function could be set as a default value for a field but since a function cannot set values inside the database there's no way to update the count once the current value is set.
3. A stored procedure could be used to increment the number and return it but they cannot be set as a default value on a column.

The work around I've put in place for now is to simply create a stored procedure that returns the value I want and code it into the LINQ API so that it happens automatically. This produces some boilerplate code that I would like to remove.

Is what I want even possible in SQL Server?

Answer 1

No. Identity column or roll your own are basically your choices.

Answer 2

There are good reasons not to do this - you have to implement all the locking rules that SQL does in order to ensure you get a unique value - and there can be a performance cost for doing that if the value has to get back to your data or business tier (e.g. so you can build an object graph in memory before saving it).

An alternative if you really want to build a full object graph in memory and then save it in one shot is to use GUIDs but those have their own draw-backs.