TechNet Wiki v2

Организация пользовательских счетчиков (генераторов) в Microsoft SQL Server - Статьи TechNet - Россия (Pусский) - TechNet Wiki

В 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

Обратите внимание - триггер создан с опцией 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 - одна для генераторов, для которых укist(Name, StartValue, IsWorkWithHoles)

values(@Name

,
 @StartValue, @IsWorkWithHoles)

end

go

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

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

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 begin

select

>


                      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%'
  
sp; 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); ret.ParameterName = "an>


            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)'
   
    set
@prevpos = 1  

    set
@sql = 'if exist '+@    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))  
  

go

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

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


    
    








    

    

    
        

            
                C 2015 Microsoft Corporation. All rights reserved.
                Terms of Use 
                Trademarks 
                Privacy Statement 
                [Copied from] v5.6.915.0
                
            

            
                This page has been extacted by Pete Laker, Microsoft Azure MVP & Microsoft IT Implementer
            
        

        

        

        

        
            X