В SQL Server 2012 появилась долгожданная возможность генерировать числа из последовательности - объект SEQUENCE.

Хорошо, что SEQUENCE-ом можно пользоваться, как функцией, а не только, как DEFAULT значением на поле таблицы и не увязывать момент получения значения с моментом физического добавления записи.
Но в реальной жизни довольно популярное требование - динамическое формирование имени последовательности (или любой другой способ реализации "хочу нумерацию с начала года/месяца"). Также часто хотят иметь хоть какой-то механизм "повторного" получения "пропущенных значений".

Обычно предлагаемые в решения по генерации "бизнес-номеров", написанные на TSQL и базирующиеся на таблицах с текущими значениями счетчиков, имеют один недостаток - блокировки. Действительно, если мы "генерируем" новый номер, то на время генерации мы должны заблокировать счетчик, чтобы в другом соединении не было получено такое же значение. При этом зачастую номер нам нужно получать в рамках уже открытой транзакции, что чревато тем, что два изначально независимых бизнес-процесса будут в лучшем случае долго блокировать один другого, а в худшем - окажутся не настолько независимыми, чтобы избежать взаимоблокировки (deadlock).

Кроме того, генерацию номера на базе таблицы счетчиков нельзя "завернуть" в функцию, чего бы очень хотелось для реализации конструкций вида:

insert into MyTable(DocNum, DocDate, Comment)
select 
    Generator.NextValue('SequenceFor_DocNum'), IncomeDate, Comment
from #SomeBuffer

поскольку в функциях запрещены любые изменения данных и мы не можем изменить текущее значение.

Но не совсем любые!
В функциях есть возможность вызывать расширенные хранимые процедуры и CLR процедуры и функции.

А вот в CLR функции у нас есть возможность подключиться к тому же серверу и базе, но уже в другом соединении и вызвать процедуру, генерирующую нужный номер, не накладывая длительных блокировок.
Для этого придется сделать несколько дополнительных действий помимо написания самой функции:

  • сделать unsafe сборку
  • передавать из функции в сборку имя сервера и базы данных, чтобы можно было без лишних запросов сформировать строку соединения
  • указать в строке соединения Enlist=false, чтобы транзакция, в которой будет происходить генерация, не "подключилась" к той транзакции, из которой мы пытаемся получить новое значение счетчика
  • убедиться, что учетная запись, под которой запущен SQL Server, имеет права на подключение к базе данных, в которой будут использоваться счетчики (по умолчанию, у такой учетки есть права sysadmin, но шаловливые руки администраторов способны на многое)
  • сделать set trustworthy on для той же базы данных

Причем сборку мы будем создавать из самого же SQL Server - не нужен даже VisualStudio, но об этом позже.

(Все запросы выполняем в той базе, где нам нужны счетчики)

Настраиваем сервер - включаем CLR:

if exists (select * from sys.configurations where name='clr enabled' and value_in_use=0)
begin
    exec('sp_configure ''show advanced options'', 1')
    exec('reconfigure')
    exec('sp_configure ''clr enabled'', 1')
    exec('reconfigure')
end
go

Настраиваем базу - позволяем в ней работать unsafe сборкам:

declare @sql nvarchar(max)
set @sql = N'alter database '+DB_NAME()+N' set trustworthy on'
exec(@sql)

Создаем схему _Generator, в которой будут находиться основные объекты, необходимые для манипуляции со счетчиками:
if SCHEMA_ID('_Generator') is null exec ('create schema _Generator')

Создаем таблицу, в которой будут храниться параметры счетчиков:

create table _Generator.List
(
     ID uniqueidentifier not null default newid() ,   
     Name sysname not null,   
     StartValue int not null constraint DF__Generator_List_StartValue default 0,   
     IsWorkWithHoles tinyint,   
     constraint PK_List_ID primary key clustered(ID),   
     constraint AK_List_Name unique (Name)
)
go

Теперь создадим триггер, который для каждого внесенного в _Generator.List счетчика будет создавать в схеме с именем G$имя_счетчика, функции NextValue и CurrentValue. Причем в зависимости от параметра IsWorkWithHoles, реализации функции NextValue несколько различаются.

create trigger [_Generator].[TR_List_UpdateGenerator] on [_Generator].[List]
with execute as owner   
after insert, update, delete
as  
begin
    set nocount on 
    set ansi_nulls on
    declare   
        @Name nvarchar(128),  
        @IsWorkWithHoles tinyint,   
        @ID uniqueidentifier,  
        @FunctionName nvarchar(128),   
        @sql nvarchar(maX)   
    declare cursd cursor local static forward_only for  
        select N'G$'+i.Name   
        from deleted i   
    open cursd   
    while 1=1   
    begin  
        fetch next from cursd into @Name  
        if @@FETCH_STATUS <> 0 break     
        if object_id(@Name+N'.CurrentValue', N'FN') is not null  
        exec(N'drop function '+@Name+N'.CurrentValue')   
        if object_id(@Name+N'.NextValue', N'FN') is not null 
        exec(N'drop function '+@Name+N'.NextValue')   
        if object_id(@Name+N'.NextValueHole', N'FN') is not null  
        exec(N'drop function '+@Name+N'.NextValueHole')   
        if object_id(@Name+N'.RegisterHole', N'P') is not null  
        exec(N'drop procedure '+@Name+N'.RegisterHole')  
    -- If generator's schema is "empty" (i.e. it was used only by generator)   
        if not exists(select * from sys.all_objects where schema_id=schema_id(@Name))  
        exec(N'drop schema '+@Name)   
    end
    
    declare cursi cursor local static forward_only for   
        select i.Name, i.ID, i.IsWorkWithHoles   
        from inserted i   
    open cursi   
    while 1=1   
    begin   
        fetch next from cursi into @Name, @ID, @IsWorkWithHoles   
        if @@FETCH_STATUS <> 0 break        
  
        set @FunctionName = 'NextValue' + case when @IsWorkWithHoles=1 then 'Hole' else '' end   
      
        if SCHEMA_ID(@Name) is null   
            exec(N'create schema G$'+@Name)
  
        if object_id(@Name+N'.CurrentValue') is null   
        begin
              set @sql = N'create function G$'+@Name+N'.CurrentValue(@Sequence nvarchar(256))
returns int as 
  begin   
    declare @CV int
 
    select @CV=CurrentValue   
    from _Generator.Sequence   
    where Name='''+@Name+N'.''+isnull(''.''+@Sequence, '''') 
  
    if @@rowcount = 0    
        select  @CV = StartValue  
        from _Generator.List    
        where ID = ''' +convert(varchar(40), @ID)+N'''
  
        return @CV
end' 
         exec(@sql)   
        end     
        if object_id(@Name+N'.NextValue') is null   
        begin   
            set @sql = N'create function G$'+@Name+N'.NextValue(@Sequence nvarchar(256)) returns int as   
    begin   
     return (_Generator.'+@FunctionName+N'('''+@Name+'.''+isnull(''.''+@Sequence, ''''), @@SPID, @@SERVERNAME, DB_NAME()))
  
end'
              exec(@sql)   
        end  
    end 
end

Обратите внимание - триггер создан с опцией with execute as owner, что позволяет фактически превратить операции по вставке данных в таблицу в DDL операции, расширяющие синтаксис в нашей базе. При этом создающий генератор пользователь вовсе не должен иметь какие-либо права на модификацию структуры БД.
Кроме того, кому-то будет удобнее так отлаживать сам механизм.

Создаем таблицу, в которую будем помещать неиспользованные значения счетчиков:

create table _Generator.Hole  
(   
     Sequence nvarchar(256) not null,   
     HoleValue int not null  
     constraint PK__Generator_Hole primary key(Sequence, HoleValue)   
)   
go

Процедура добавления генератора в таблицу _Generator.List.
Она в общем-то скорее нужна, чтобы продемонстрировать, что достаточно дать права например на запуск одной процедуры для создания новых генераторов.

create procedure _Generator.New   
    @Name sysname, -- Имя генератора   
    @StartValue int, -- Начальное значение   
    @IsWorkWithHoles tinyint -- Работа с пропущенными значениями   
as 
begin
    set nocount on
    if not exists(
        select * 
        from _Generator.List 
        where Name = @Name
        )
  
    insert into _Generator.List(Name, StartValue, IsWorkWithHoles)
    values(@Name, @StartValue, @IsWorkWithHoles)
end  
go 

Теперь наконец-то создадим таблицу с текущими значениями счетчиков и процедуру, генерирующую значения.

Процедур будет 2 - одна для генераторов, для которых указана возможность работы с пропущенными значениями. Можно было обойтись и одной, но это хуже с точки зрения оптимизации быстродействия - лишние запросы и/или параметры и проверки для генераторов, у которых пропущенные значения неактуальны:

create table _Generator.Sequence 
(   
  Name nvarchar(256) not null,   
  CurrentValue int not null,   
  constraint PK_Sequence_GenID_Name primary key clustered(Name)   
)
  
go
  
-- Никогда не используйте эту процедуру "напрямую"!
  
create procedure _Generator.GenerateValue  
@Sequence nvarchar(256)   
as  
begin
    set nocount on
  
    declare   
        @TC int,   
        @Value int
  
    select  
        @TC = @@TRANCOUNT,  
        @Value = null
    
    begin try
  
        begin transaction
  
            update s set 
            @Value = CurrentValue = CurrentValue + 1   
            from _Generator.Sequence s with(holdlock)  
            where Name = @Sequence
  
            if @@ROWCOUNT = 0    
               begin  
                   select   
                       @Value = l.StartValue
                   from _Generator.List l  
                   where l.Name = SUBSTRING(@Sequence, 1, isnull(nullif(CHARINDEX('.', @Sequence), 0), 256)-1)   
  
                   if @@ROWCOUNT = 0 
                   raiserror('Generator not found', 16, 1)
    
                   insert into _Generator.Sequence(Name, CurrentValue)   
                   values(@Sequence, @Value)
  
              end
  
        commit
  
    end try   
    begin catch
  
        if @@TRANCOUNT > @TC   
        rollback
  
    end catch
  
    return @Value  
end
  
go
  
-- Никогда не используйте эту процедуру "напрямую"!   
create procedure _Generator.GenerateValueHole   
@Sequence nvarchar(256)   
as  
begin
  
    set nocount on
    declare   
        @TC int,   
        @Value int
    
    select  
        @TC = @@TRANCOUNT,   
        @Value = null 
    
    declare @Hole table(HoleValue int)   
  
    begin try
  
        begin transaction  
        -- Попытка найти зарегистрированные ранее пропущенные значения.  
        -- Можно сделать чуть хитрее, для получения "дырок" в порядке возрастания/убывания.
  
            delete top(1) h
                          output deleted.HoleValue into @Hole
                          from _Generator.Hole h with(holdlock)
                          where Sequence = @Sequence
    
            if @@ROWCOUNT = 1  
            begin
  
                select top(1) 
                    @Value = h.HoleValue   
                from @Hole h   
            end 
            else 
            -- if there was no any hole...   
            begin  
                update s set 
                @Value = CurrentValue = CurrentValue + 1   
                from _Generator.Sequence s with(holdlock)   
                where Name = @Sequence  
                -- If there is no Sequence yet
                                  if @@ROWCOUNT = 0    
                    begin
                                                  select  
                        @Value = l.StartValue   
                      from _Generator.List l   
                      where l.Name = SUBSTRING(@Sequence, 1, isnull(nullif(CHARINDEX('.', @Sequence), 0), 256)-1)   
                      if @@ROWCOUNT = 0 raiserror('Generator not found', 16, 1)  
                        insert into _Generator.Sequence(Name, CurrentValue)   
                        values(@Sequence, @Value)   
                    end 
           end    
        commit  
        end try
  
        begin catch   
            if @@TRANCOUNT > @TC   
            rollback
                end catch
  
    return @Value   
end  
go

Ну и процедура регистрации пропущенных значений:

create procedure _Generator.RegisterHole   
@Sequence nvarchar(256),   
@HoleValue int  
as  
begin  
    set nocount on  
    insert into _Generator.Hole   
    (  
        Sequence,    
        HoleValue   
    )
      select 
        @Sequence,   
        @HoleValue   
    where not exists(   
        select *   
        from _Generator.Hole with(holdlock)   
        where Sequence = @Sequence  
            and HoleValue = @HoleValue   
                    )   
end
go

Теперь собственно CLR сборка.

Далеко не все разработчики баз данных в дружеских отношениях с C# и VisualStudio и представляют, как скомпилировать сборку.
Скорее всего также мало кто захочет довериться сборке, выложенной в виде dll.

Поэтому скомпилируем и создадим сборку прямо в T-SQL. Единственное требование - на самом SQL Server должен быть установлен .NET Framework 3.5:

declare   
    @t table(txt varchar(255))
  
declare  
    @temp varchar(255),   
    @sql varchar(8000),   
    @cs varchar(max)   
  
      -- Делаем базу данных trustworthy
      set @sql = 'alter database '+db_name()+' set trustworthy on'  
      exec(@sql)
      
      insert into @t   
      exec xp_cmdshell 'set'
  
      select @temp = substring(txt, 6, 255)   
      from @t    
      where txt like 'TEMP%'
  
      set @cs = 'using System;
  
using System.Data;   
using System.Data.SqlClient;   
using System.Data.SqlTypes;   
using Microsoft.SqlServer.Server;
  
namespace DeColores   
{   
public partial class PGenerator   
{   
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,Name = "NextValue")]   
public static SqlInt32 NextValue(SqlString Sequence, SqlInt32 SPID, SqlString ServerName, SqlString DatabaseName)  
{
    using (SqlConnection IsolatedConn = new SqlConnection("Integrated Security=true; Initial Catalog=" + DatabaseName.ToString() + ";  
    server=" + ServerName.ToString() + "; Application Name=_Generator_for_" + SPID.ToString() + "; Enlist=false"))   
    {  
        try  
        {   
            IsolatedConn.Open();   
            SqlCommand GenValue = new SqlCommand("_Generator.GenerateValue", IsolatedConn);   
            GenValue.CommandType = CommandType.StoredProcedure;   
            GenValue.Parameters.AddWithValue("Sequence", Sequence);   
            SqlParameter ret = new SqlParameter();   
            ret.ParameterName = "ReturnValue";   
            ret.DbType = DbType.Int32;   
            ret.Direction = ParameterDirection.ReturnValue;       
            GenValue.Parameters.Add(ret);   
            GenValue.ExecuteNonQuery();   
            SqlInt32 Val = (int)GenValue.Parameters["ReturnValue"].Value;   
            return Val;   
       }   
       catch   
      {   
        return SqlInt32.Null;   
      }   
    }  
}
  
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, Name = "NextValueHole")]   
public static SqlInt32 NextValueHole(SqlString Sequence, SqlInt32 SPID, SqlString ServerName, SqlString DatabaseName)   
{
    using (SqlConnection IsolatedConn = new SqlConnection("Integrated Security=true; Initial Catalog=" + DatabaseName.ToString() + "; server=" + ServerName.ToString() + "; Application Name=_Generator_for_" + SPID.ToString() + "; Enlist=false"))  
{  
    try   
    {  
        IsolatedConn.Open();   
        SqlCommand GenValue = new SqlCommand("_Generator.GenerateValueHole", IsolatedConn);   
        GenValue.CommandType = CommandType.StoredProcedure;   
        GenValue.Parameters.AddWithValue("Sequence", Sequence);   
        SqlParameter ret = new SqlParameter();   
        ret.ParameterName = "ReturnValue";   
        ret.DbType = DbType.Int32;   
        ret.Direction = ParameterDirection.ReturnValue;   
        GenValue.Parameters.Add(ret);   
        GenValue.ExecuteNonQuery();   
        SqlInt32 Val = (int)GenValue.Parameters["ReturnValue"].Value;   
        return Val;   
    }   
    catch   
    {  
        return SqlInt32.Null;   
    }   
}
  
}
  
};
  
}
  
'
  
  
    declare   
        @lpos int,   
        @prevpos int  
  
    set @prevpos = 1  
    set @sql = 'if exist '+@temp+'\generator.cs ( del '+@temp+'\generator.cs)'
   
    exec xp_cmdshell @sql, no_output   
  
    while 1=1    
    begin  
    set @lpos = charindex(char(13), @cs, @prevpos)+2   
    if @lpos = 2 break   
    set @sql = 'echo '+substring(@cs, @prevpos, @lpos - @prevpos-2)+' >> '+@temp+'\generator.cs'    
    exec xp_cmdshell @sql, no_output   
    set @prevpos = @lpos   
    end
    
set @sql = 'C:\WINDOWS\Microsoft.NET\Framework\v3.5\csc.exe /out:'+@temp+'\generator.dll /target:library /unsafe '+@temp+'\generator.cs'
  
exec xp_cmdshell @sql , no_output   
create assembly Generator    
from @temp + '\generator.dll'  
with permission_set = unsafe;
  
go

Создадим сами CLR функции:

CREATE FUNCTION [_Generator].[NextValue](@Sequence [nvarchar](4000), @SPID [int], @ServerName [nvarchar](4000), @DatabaseName [nvarchar](4000))  
RETURNS [int] WITH EXECUTE AS CALLER   
AS   
EXTERNAL NAME [Generator].[DeColores.PGenerator].[NextValue]   
go
  
CREATE FUNCTION [_Generator].[NextValueHole](@Sequence [nvarchar](4000), @SPID [int], @ServerName [nvarchar](4000), @DatabaseName [nvarchar](4000))   
RETURNS [int] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Generator].[DeColores.PGenerator].[NextValueHole]
go

 
Все готово.
Теперь - примеры использования.
Регистрируем генератор Test с начальным значением последовательностей 0 и возможностью работать с пропущенными значениями:

exec _Generator.New
     @Name = 'Test',
     @StartValue = 0,
     @IsWorkWithHoles = 1

Просто получение значения для последовательности "123":

select G$Test.NextValue('123')

 Динамическое формирование имени последовательности:

select sv.number, G$Test.NextValue('number'+convert(varchar(20), sv.number%3))
from master.dbo.spt_values sv
where sv.[type] = 'P'
and sv.number < 100 

Убеждаемся, что откат транзакции не приводит к "откату" значения счетчика:

begin tran
  select G$Test.NextValue('TestRollback')
rollback
select G$Test.NextValue('TestRollback')

Регистрируем пропущенное значение и получаем его из "стандартной" функции:

select max(G$Test.NextValue('TestHole')) 
from master.dbo.spt_values sv
where sv.[type] = 'P' and sv.number < 100

exec _Generator.RegisterHole 'Test..TestHole', 12
  
select G$Test.NextValue('TestHole')

Вот собственно и всё.
Конечно, данный код приведен исключительно в качестве примера и заготовки. Например, в реальном применении лучше регистрировать пропущенные значения также в изолированной транзакции через CLR. И совсем не обязательно создавать функции-генераторы в триггере.Кроме того, можно делать различные макроподстановки в имени последовательности при генерации значения в самой CLR функции, например, заменять %YY% на 2 последние разряда текущего года. В нашем реальном проекте например сделано больше десятка подобных макро и другие дополнительные возможности вроде генерации не просто числа, а готовой форматированной строки....

Но это уже на вкус и цвет коллег по цеху.