Microsoft регулярно упрекают за то, что вSQL Server до сих пор нет реализации чего-то подобного SEQUENCE в Oracle.
В рамках подготовки к докладу о всевозможных идентификаторах, счетчиках и прочая в Russian SQL Server User Group я заинтересовался тем, что предлагает сей конкурент и понял, что ничего, кроме старого знакомого IDENTITY, "но по-другому" для известных мне бизнес-задач там нет.
Допустим, есть цикличность. Это хорошо, но мягко говоря, не на первом месте по необходимости.
Хорошо, что SEQUENCE можно пользоваться, как функцией, а не только, как DEFAULT значением на поле таблицы и не увязывать момент получения значения с моментом физического добавления записи.
Но главные реальные требования - динамическое формирование имени последовательности (или любой другой способ реализации "хочу нумерацию с начала года/месяца") и хоть какой-то механизм "повторного" получения "пропущенных значений" отсутствуют и там.
А если бизнес не может предъявить хоть какие-то критерии управления такими последовательностями, то собственно все равно, какая функция генерируют эти последовательности - IDENTITY, SEQUENCE или NEWID - особой разницы нет, для номера накладной и счета-фактуры все три решения подходят одинаково плохо.
Обычно предлагаемые в решения по генерации "бизнес-номеров", написанные на 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 для той же базы данных
Теперь перейдем от "многа букф" к более понятному T-SQL коду.
Что интересно, на том же TSQL мы сделаем и саму сборку - не нужен даже 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')
-- Delete *.RegisterHole stored prcedure, if exists
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
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
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
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%'
-- 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 последние разряда текущего года. В нашем реальном проекте например сделано больше десятка подобных макро и другие дополнительные возможности вроде генерации не просто числа, а форматированной строки....
Но это уже на вкус и цвет коллег по цеху.