跨多个表维护身份值 [英] Mainting Identity value across multiple tables

问题描述

在一种情况下，我们在多个表中都有一个名为Customer_Number的列.该列是所有表中的标识列，但是有一种方法可以使该列在所有表中唯一.

We have a situation where we have a column called Customer_Number in multiple tables. This column is identity column in all the tables, but is there a way that I can make this column unique among all the tables.

例如，如果我在table_one中添加一行，并且如果有人在table_two，table_three或table_four的Customer_Number列中添加另一行，则Identity列现在将其分配为1.

for example if I add a row in table_one and identity column assigns it value 1 now if someone add another row in Customer_Number column of table_two , table_three or table_four it should be assigned 2.

我该如何做，我一直在网上阅读，看来我必须创建一个表来记录任何表的最后生成值，并从该表中获取MAX()值并将其加1.获取下一个可用值，是否有更简单的方法?

how can I do this, I have been reading online and it seems I have to create a table to keep record of the last generated value for any of the table and get MAX() of values from that table and add 1 to it to get the next available value, is there a simpler way of doing this?

推荐答案

我自己没有使用过，但是我认为您需要新的

I have not used it myself but I think you need the new Sequence Object

您将创建一个序列对象，然后使用Identity值从序列对象中获取下一个值.

You would Create a Sequence Object and rather then using Identity values just get the next value from your sequence object.

创建序列对象

CREATE SEQUENCE Sqnc_Number_Generator AS INT   --<-- This can be Bigint as well
    START WITH   1  -- Start with value 1
    INCREMENT BY 1  -- Increment with value 1
    MINVALUE  1     -- Minimum value to start is 1
    MAXVALUE  50000 -- Maximum it can go to 5000
    NO CYCLE        -- Do not go above 5000
    CACHE 500        -- Increment 500 values in memory rather than incrementing from IO

获取下一个值

SELECT NEXT VALUE FOR dbo.Sqnc_Number_Generator AS NxtValue;

这篇关于跨多个表维护身份值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！