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 функції у нас є можливість підключитися до того ж сервера і бази, але вже в іншому з'єднанні і викликати процедуру, яка генерує цікавить нас номер, не накладаючи тривалих блокувань.
Для цього доведеться зробити кілька додаткових дій крім написання самої функції:
Тепер перейдемо від "многа букф" до більш зрозумілому T-SQL коду.
Що цікаво, на тому ж TSQL ми зробимо і саму збірку - не потрібен навіть VisualStudio, але про це пізніше.
(Всі запити виконуємо в тій базі, де нам потрібні лічильники)
Налаштовуємо сервер - включаємо CLR:
if exists (select * from sys. configurations where name = 'clr enabled' and value_in_use = 0)
exec ( 'sp_configure' 'show advanced options' ', 1')
exec ( 'sp_configure' 'clr enabled' ', 1')
Налаштовуємо базу - дозволяємо в ній працювати unsafe збірок:
declare @sql nvarchar (max)
set @sql = N'alter database '+ DB_NAME () + N' set trustworthy on '
Створюємо схему _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.
constraint PK_List_ID primary key clustered (ID),
constraint AK_List_Name unique (Name)
Тепер створимо тригер, який для кожного внесеного в _Generator.List лічильника буде створювати в схемі з ім'ям G $ імя_счетчіка. функції NextValue і CurrentValue. Причому в залежності від параметра IsWorkWithHoles, реалізації функції NextValue не однакові.
create trigger [_Generator]. [TR_List_UpdateGenerator] on [_Generator]. [List]
with execute as owner
after insert. update. delete
set ansi_nulls on
@Name nvarchar (128),
Тепер така дрібниця, як CLR збірка.
Підозрюю, що не всі розробники БД дружніх відносинах з C # і VisualStudio і представляють, як скомпілювати збірку.
Швидше за все також мало хто захоче довіритися збірці, викладеної у вигляді dll.
Тому скомпілюємо і створимо збірку прямо в T-SQL. Єдина вимога - на самому SQL Server повинен бути встановлений .NET Framework 3.5:
@t table (txt varchar (255))
@temp varchar (255),
@sql varchar (8000),
@cs varchar (max)
-- Робимо базу даних trustworthy
set @sql = 'alter database' + db_name () + 'set trustworthy on'
exec xp_cmdshell 'set'
select @temp = substring (txt. 6. 255)
where txt like 'TEMP%'
set @cs = 'using System;
public partial class PGenerator
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 "))
SqlCommand GenValue = new SqlCommand ( "_ Generator.GenerateValue", IsolatedConn);
SqlParameter ret = new SqlParameter ();
SqlInt32 Val = (int) GenValue.Parameters [ "ReturnValue"]. Value;
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 "))
SqlCommand GenValue = new SqlCommand ( "_ Generator.GenerateValueHole", IsolatedConn);
SqlParameter ret = new SqlParameter ();