I have a database used by several clients. I don't really want surrogate incremental key values to bleed between clients. I want the numbering to start from 1 and be client specific.
我有几个客户使用的数据库。我真的不希望代理增量键值在客户端之间流血。我希望编号从1开始,并且是特定于客户端的。
I'll use a two-part composite key of the tenant_id as well as an incremental id.
我将使用tenant_id的两部分组合键以及增量ID。
What is the best way to create an incremental key per tenant?
每个租户创建增量密钥的最佳方法是什么?
I am using SQL Server Azure. I'm concerned about locking tables, duplicate keys, etc. I'd typically set the primary key to IDENTITY and move on.
我正在使用SQL Server Azure。我担心锁定表,重复键等。我通常将主键设置为IDENTITY并继续前进。
Thanks
2 个解决方案
#1
2
Are you planning on using SQL Azure Federations in the future? If so, the current version of SQL Azure Federations does not support the use of IDENTITY as part of a clustered index. See this What alternatives exist to using guid as clustered index on tables in SQL Azure (Federations) for more details.
您是否计划将来使用SQL Azure Federations?如果是这样,当前版本的SQL Azure Federations不支持将IDENTITY用作聚簇索引的一部分。请参阅此更多详细信息,在SQL Azure(联合)中对表使用guid作为聚簇索引存在哪些替代方法。
If you haven't looked at Federations yet, you might want to check it out as it provides an interesting way to both shard the database and for tenant isolation within the database.
如果您还没有查看过Federations,您可能需要查看它,因为它提供了一种有趣的方法来分割数据库和数据库中的租户隔离。
Depending upon your end goal, using Federations you might be able to use a GUID as the primary clustered index on the table and also use an incremental INT IDENTITY field on the table. This INT IDENTITY field could be shown to end-users. If you are federating on the TenantID each "Tenant table" effectively becomes a silo (as I understand it at least) so the use of IDENTITY on a field within that table would effectively be an ever increasing auto generated value which increments within a given Tenant.
根据您的最终目标,使用联合,您可以使用GUID作为表上的主聚集索引,并在表上使用增量INT IDENTITY字段。可以向最终用户显示此INT IDENTITY字段。如果您在TenantID上进行联合,则每个“租户表”实际上变成了一个孤岛(至少我理解它),因此在该表中的字段上使用IDENTITY实际上将是一个不断增加的自动生成值,该值在给定租户中递增。
When \ if data is merged together (combining data from multiple Tenants) you would wind up with collisions on this INT IDENTITY field (hence why IDENTITY isn't supported as a primary key in federations) but as long as you aren't using this field as a unique identifier within the system at large you should be ok.
当\如果数据合并在一起(组合来自多个租户的数据)时,您最终会在此INT IDENTITY字段上发生冲突(因此,为什么IDENTITY不支持作为联合中的主键)但只要您不使用此字段作为系统中的唯一标识符,你应该没问题。
#2
1
If you're looking to duplicate the convenience of having an automatically assigned unique INT key upon insert, you could add an INSTEAD OF INSERT trigger that uses MAX of the existing column +1 to determine the next value.
如果您希望复制插入时自动分配的唯一INT键的便利性,则可以添加INSTEAD OF INSERT触发器,该触发器使用现有列+1的MAX来确定下一个值。
If the column with the identity value is the first key in an index, the MAX query will be a simple index seek, very efficient.
如果具有标识值的列是索引中的第一个键,则MAX查询将是一个简单的索引查找,非常有效。
Transactions will ensure that unique values are assigned but this approach will have different locking semantics than the standard identity column. IIRC, SQL Server can allocate a different identity value for each transaction that requests it in parallel and if a transaction is rolled back, the value(s) allocated to it are discarded. The MAX approach would only allow one transaction to insert rows into the table at a time.
事务将确保分配唯一值,但此方法将具有与标准标识列不同的锁定语义。 IIRC,SQL Server可以为并行请求它的每个事务分配不同的标识值,如果事务被回滚,则丢弃分配给它的值。 MAX方法只允许一个事务一次在表中插入行。
A related approach could be to have a dedicated key value table keyed by the table name, tenant ID and current identity value. It would require the same INSTEAD OF INSERT trigger and more boilerplate to query and keep that key table updated. It wouldn't improve parallel operations though; the lock would just be on a different table's record.
相关方法可以是具有由表名,租户ID和当前标识值键控的专用键值表。它需要相同的INSTEAD OF INSERT触发器和更多的样板来查询并保持更新密钥表。但它不会改善并行操作;锁只会在另一张桌子的记录上。
One possibility to fix the locking bottleneck would be to include the current SPID in the key's value (now the identity key is a combination of sequential int and whatever SPID happened to allocate it and not simply sequential), use the dedicated identity value table and insert records there per SPID as necessary; the identity table PK would be (table name, tenant, SPID) and have a non-key column with the current sequential value. That way, each SPID would have its own dynamically allocated identity pool and would only ever have its own SPID specific records locked.
修复锁定瓶颈的一种可能性是将当前SPID包含在密钥的值中(现在,标识密钥是顺序int和发生分配它的任何SPID的组合,而不仅仅是顺序),使用专用标识值表并插入必要时根据SPID记录;身份表PK将是(表名,租户,SPID)并具有带有当前顺序值的非键列。这样,每个SPID都有自己动态分配的标识池,并且只能锁定自己的SPID特定记录。
Another downside is maintaining triggers that have to be updated whenever you change the columns in any of the special identity tables.
另一个缺点是维护触发器,每当您更改任何特殊标识表中的列时都必须更新这些触发器。
#1
2
Are you planning on using SQL Azure Federations in the future? If so, the current version of SQL Azure Federations does not support the use of IDENTITY as part of a clustered index. See this What alternatives exist to using guid as clustered index on tables in SQL Azure (Federations) for more details.
您是否计划将来使用SQL Azure Federations?如果是这样,当前版本的SQL Azure Federations不支持将IDENTITY用作聚簇索引的一部分。请参阅此更多详细信息,在SQL Azure(联合)中对表使用guid作为聚簇索引存在哪些替代方法。
If you haven't looked at Federations yet, you might want to check it out as it provides an interesting way to both shard the database and for tenant isolation within the database.
如果您还没有查看过Federations,您可能需要查看它,因为它提供了一种有趣的方法来分割数据库和数据库中的租户隔离。
Depending upon your end goal, using Federations you might be able to use a GUID as the primary clustered index on the table and also use an incremental INT IDENTITY field on the table. This INT IDENTITY field could be shown to end-users. If you are federating on the TenantID each "Tenant table" effectively becomes a silo (as I understand it at least) so the use of IDENTITY on a field within that table would effectively be an ever increasing auto generated value which increments within a given Tenant.
根据您的最终目标,使用联合,您可以使用GUID作为表上的主聚集索引,并在表上使用增量INT IDENTITY字段。可以向最终用户显示此INT IDENTITY字段。如果您在TenantID上进行联合,则每个“租户表”实际上变成了一个孤岛(至少我理解它),因此在该表中的字段上使用IDENTITY实际上将是一个不断增加的自动生成值,该值在给定租户中递增。
When \ if data is merged together (combining data from multiple Tenants) you would wind up with collisions on this INT IDENTITY field (hence why IDENTITY isn't supported as a primary key in federations) but as long as you aren't using this field as a unique identifier within the system at large you should be ok.
当\如果数据合并在一起(组合来自多个租户的数据)时,您最终会在此INT IDENTITY字段上发生冲突(因此,为什么IDENTITY不支持作为联合中的主键)但只要您不使用此字段作为系统中的唯一标识符,你应该没问题。
#2
1
If you're looking to duplicate the convenience of having an automatically assigned unique INT key upon insert, you could add an INSTEAD OF INSERT trigger that uses MAX of the existing column +1 to determine the next value.
如果您希望复制插入时自动分配的唯一INT键的便利性,则可以添加INSTEAD OF INSERT触发器,该触发器使用现有列+1的MAX来确定下一个值。
If the column with the identity value is the first key in an index, the MAX query will be a simple index seek, very efficient.
如果具有标识值的列是索引中的第一个键,则MAX查询将是一个简单的索引查找,非常有效。
Transactions will ensure that unique values are assigned but this approach will have different locking semantics than the standard identity column. IIRC, SQL Server can allocate a different identity value for each transaction that requests it in parallel and if a transaction is rolled back, the value(s) allocated to it are discarded. The MAX approach would only allow one transaction to insert rows into the table at a time.
事务将确保分配唯一值,但此方法将具有与标准标识列不同的锁定语义。 IIRC,SQL Server可以为并行请求它的每个事务分配不同的标识值,如果事务被回滚,则丢弃分配给它的值。 MAX方法只允许一个事务一次在表中插入行。
A related approach could be to have a dedicated key value table keyed by the table name, tenant ID and current identity value. It would require the same INSTEAD OF INSERT trigger and more boilerplate to query and keep that key table updated. It wouldn't improve parallel operations though; the lock would just be on a different table's record.
相关方法可以是具有由表名,租户ID和当前标识值键控的专用键值表。它需要相同的INSTEAD OF INSERT触发器和更多的样板来查询并保持更新密钥表。但它不会改善并行操作;锁只会在另一张桌子的记录上。
One possibility to fix the locking bottleneck would be to include the current SPID in the key's value (now the identity key is a combination of sequential int and whatever SPID happened to allocate it and not simply sequential), use the dedicated identity value table and insert records there per SPID as necessary; the identity table PK would be (table name, tenant, SPID) and have a non-key column with the current sequential value. That way, each SPID would have its own dynamically allocated identity pool and would only ever have its own SPID specific records locked.
修复锁定瓶颈的一种可能性是将当前SPID包含在密钥的值中(现在,标识密钥是顺序int和发生分配它的任何SPID的组合,而不仅仅是顺序),使用专用标识值表并插入必要时根据SPID记录;身份表PK将是(表名,租户,SPID)并具有带有当前顺序值的非键列。这样,每个SPID都有自己动态分配的标识池,并且只能锁定自己的SPID特定记录。
Another downside is maintaining triggers that have to be updated whenever you change the columns in any of the special identity tables.
另一个缺点是维护触发器,每当您更改任何特殊标识表中的列时都必须更新这些触发器。