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.
我正在帮助实现一个SQL Server 2008数据库,我有几个列,我想默认为序列中的下一个数字,但仍然可以随意手动插入数字。在PostgreSQL中,这是通过SERIAL数据类型和序列完成的。不幸的是,SQL Server中不存在这些东西。这就是我所知道的不起作用。
- 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.
- 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.
- 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.
除非您将IDENTITY_INSERT设置为on,否则标识列不允许插入。在这种情况下,它可以按照需要工作,但根据MSDN,这个属性一次只能在一个表上设置,我想为多个表执行此操作。
可以将函数设置为字段的默认值,但由于函数无法在数据库中设置值,因此一旦设置了当前值,就无法更新计数。
可以使用存储过程来递增数字并返回它,但不能将它们设置为列上的默认值。
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.
我现在所做的工作就是简单地创建一个存储过程,它返回我想要的值并将其编码到LINQ API中,以便它自动发生。这会产生一些我想删除的样板代码。
Is what I want even possible in SQL Server?
我想在SQL Server中甚至可能吗?
2 个解决方案
#1
No. Identity column or roll your own are basically your choices.
不会。自己的身份栏或卷也基本上是你的选择。
#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).
有充分的理由不这样做 - 您必须实现SQL所做的所有锁定规则,以确保您获得唯一值 - 如果值必须返回到您的数据,那么执行此操作可能会产生性能成本或业务层(例如,因此您可以在保存之前在内存中构建对象图)。
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.
如果你真的想在内存中构建一个完整的对象图,然后一次保存就可以使用GUID,但是那些有自己的缺点。
#1
No. Identity column or roll your own are basically your choices.
不会。自己的身份栏或卷也基本上是你的选择。
#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).
有充分的理由不这样做 - 您必须实现SQL所做的所有锁定规则,以确保您获得唯一值 - 如果值必须返回到您的数据,那么执行此操作可能会产生性能成本或业务层(例如,因此您可以在保存之前在内存中构建对象图)。
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.
如果你真的想在内存中构建一个完整的对象图,然后一次保存就可以使用GUID,但是那些有自己的缺点。