如何在Microsoft SQL Server中实现序列？

交易安全！对于2012年之前的SQLServer版本...（感谢Matt G.） 讨论中缺少的一件事是事务安全性。如果您从序列中获取一个数字，那么该数字必须是唯一的，并且没有其他应用程序或代码能够获取该数字。在我的情况下，我们经常从序列中提取唯一的数字，但实际的事务可能需要很长时间，因此在提交事务之前，我们不希望其他人获取相同的数字。 我们需要模仿Oracle序列的行为，即在提取数字时保留数字。 我的解决方案是使用xp_cmdshell在数据库上获取单独的会话/事务，以便我们可以立即更新整个数据库的序列，即使在事务完成之前也可以。

--it is used like this:
-- use the sequence in either insert or select:
Insert into MyTable Values (NextVal('MySequence'), 'Foo');

SELECT NextVal('MySequence');

--you can make as many sequences as you want, by name:
SELECT NextVal('Mikes Other Sequence');

--or a blank sequence identifier
SELECT NextVal('');

该解决方案需要一个单独的表来保存已使用的序列值，并使用一个过程创建第二个自主事务，以确保并发会话不会混乱。您可以拥有尽可能多的唯一序列，它们通过名称引用。下面的示例代码被修改为在序列历史记录表上省略请求用户和日期戳（以进行审计），但我认为对于示例来说，更简单更好 ;-).

  CREATE TABLE SequenceHolder(SeqName varchar(40), LastVal int);

GO
CREATE function NextVAL(@SEQname varchar(40))
returns int
as
begin
    declare @lastval int
    declare @barcode int;

    set @lastval = (SELECT max(LastVal) 
                      FROM SequenceHolder
                     WHERE SeqName = @SEQname);

    if @lastval is null set @lastval = 0

    set @barcode = @lastval + 1;

    --=========== USE xp_cmdshell TO INSERT AND COMMINT NOW, IN A SEPERATE TRANSACTION =============================
    DECLARE @sql varchar(4000)
    DECLARE @cmd varchar(4000)
    DECLARE @recorded int;

    SET @sql = 'INSERT INTO SequenceHolder(SeqName, LastVal) VALUES (''' + @SEQname + ''', ' + CAST(@barcode AS nvarchar(50)) + ') '
    SET @cmd = 'SQLCMD -S ' + @@servername +
              ' -d ' + db_name() + ' -Q "' + @sql + '"'
    EXEC master..xp_cmdshell @cmd, 'no_output'

    --===============================================================================================================

    -- once submitted, make sure our value actually stuck in the table
    set @recorded = (SELECT COUNT(*) 
                       FROM SequenceHolder
                      WHERE SeqName = @SEQname
                        AND LastVal = @barcode);

    --TRIGGER AN ERROR 
    IF (@recorded != 1)
        return cast('Barcode was not recorded in SequenceHolder, xp_cmdshell FAILED!! [' + @cmd +']' as int);

    return (@barcode)

end

GO

COMMIT;

现在要让该过程正常工作，您需要启用xp_cmdshell，有很多好的描述如何做到这一点，以下是我在尝试使事情正常工作时所做的个人笔记。基本思路是您需要在SQLServer Surface Area Configuration中打开xp_cmdshell，并将一个用户帐户设置为xp_cmdshell命令将运行的帐户，该帐户将访问数据库以插入序列号并提交它。

--- LOOSEN SECURITY SO THAT xp_cmdshell will run 
---- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
---- To update the currently configured value for advanced options.
RECONFIGURE
GO
---- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
---- To update the currently configured value for this feature.
RECONFIGURE
GO

—-Run SQLServer Management Studio as Administrator,
—- Login as domain user, not sqlserver user.

--MAKE A DATABASE USER THAT HAS LOCAL or domain LOGIN! (not SQL server login)
--insure the account HAS PERMISSION TO ACCESS THE DATABASE IN QUESTION.  (UserMapping tab in User Properties in SQLServer)

—grant the following
GRANT EXECUTE on xp_cmdshell TO [domain\user] 

—- run the following:
EXEC sp_xp_cmdshell_proxy_account 'domain\user', 'pwd'

--alternative to the exec cmd above: 
create credential ##xp_cmdshell_proxy_account## with identity = 'domain\user', secret = 'pwd'


-—IF YOU NEED TO REMOVE THE CREDENTIAL USE THIS
EXEC sp_xp_cmdshell_proxy_account NULL;


-—ways to figure out which user is actually running the xp_cmdshell command.
exec xp_cmdshell 'whoami.exe'  
EXEC xp_cmdshell 'osql -E -Q"select suser_sname()"'
EXEC xp_cmdshell 'osql -E -Q"select * from sys.login_token"'

通过SQL，您可以使用这种策略；

CREATE SEQUENCE [dbo].[SequenceFile]
AS int
START WITH 1
INCREMENT BY 1 ;

使用此 SQL 读取唯一的下一个值

SELECT NEXT VALUE FOR [dbo].[SequenceFile]

如果您正在使用 SQL Server 2005，您可以选择使用 Row_Number。

我完全同意并在去年的一个项目中实践了这个方法。

我只是创建了一个包含序列名称、当前值和增量数量的表。

然后，我创建了两个过程来添加和删除它们。还有两个函数来获取下一个值和获取当前值。

另一个使用身份列的问题是，如果您有多个表需要唯一的序列号，则身份列无法工作。正如Corey Trager所提到的那样，自己编写的序列实现可能会出现一些锁定问题。
最直接等价的解决方案似乎是创建一个SQL Server表，其中只有一个用于身份验证的列，它取代了单独类型的“序列”对象。例如，如果在Oracle中您有两个从一个序列中获取的表，例如Dogs <--序列对象--> Cats，那么在SQL Server中，您将创建三个数据库对象，所有表都像Dogs <--带有身份列的宠物--> Cats。您将向Pets表插入一行以获取序列号，在获得用户实际宠物类型后，您将像往常一样插入Dogs或Cats表。任何其他公共列都可以从Dogs/Cats表移动到Pets超类型表中，但会有一些后果：1）每个序列号都将有一行，2）在获取序列号时无法填充的任何列都需要具有默认值，3）需要连接才能获取所有列。

如果您想使用连续键插入数据，但不想再次查询数据库以获取刚插入的键，则我认为您只有两个选择：

1. 通过存储过程执行插入操作，该存储过程返回新插入的键值
2. 在客户端实现序列（这样您就知道插入之前的新键）

如果我正在进行客户端密钥生成，我喜欢 GUID。 我认为它们非常美丽。

row["ID"] = Guid.NewGuid();

那行代码应该躺在某个跑车的引擎盖上。