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

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%'

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

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