Организация пользовательских счетчиков (генераторов) в Microsoft SQL Server

В 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 последние разряда текущего года. В нашем реальном проекте, например, сделано больше десятка подобных макро и другие дополнительные возможности вроде генерации не просто числа, а готовой форматированной строки....

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

Last updated on 2024-01-17

Организация пользовательских счетчиков (генераторов) в Microsoft SQL Server

Дополнительные ресурсы