dbatools. Часть 2. Реализация последовательности (sequence)

добавлено: 20 дек 11
понравилось:0
просмотров: 2621
комментов: 4

теги:

Автор: gds

Итак, второй пост из серии dbatools будет посвящен реализации последовательности в SQL Server. Конечно SQL Server Denali уже не за горами в, котором это уже реализовано. Но тем кто сейчас использует 2005/2008/2008R2 думаю будет интересно. Так же, поводом написания этого поста послужило немалое количество задаваемых вопросов на форуме.

И вот я решил опубликовать свою версию реализации последовательности. как уже говорилось в первой части все объекты будут создаваться в сжеме dbatools.
--Реализация Sequence 
----------------------
-- Создание линка (для 2008/2008R2)
if exists(select * from sys.servers where name = '_LOOPBACK_' and is_linked = 1)
 exec sp_dropserver @server = '_LOOPBACK_' 
declare @s nvarchar(128)
set @s = @@SERVERNAME
-- Непосредственно создание сервера
exec sp_addlinkedserver   
   @server='_LOOPBACK_', 
   @srvproduct='',
   @provider='SQLNCLI10', 
   @datasrc=@s
-- По умолчанию будет передаваться текущий контекст безопасности
exec sp_addlinkedsrvlogin @rmtsrvname=N'_LOOPBACK_',@useself=N'True'

-- Включение доступа к данным и возможности запуска процедур
exec sp_serveroption '_LOOPBACK_', 'DATA ACCESS', 'true'
exec sp_serveroption '_LOOPBACK_', 'RPC', 'true'
exec sp_serveroption '_LOOPBACK_', 'RPC Out', 'true'
-- Отключение передачи контекста текущей транзакции
exec sp_serveroption '_LOOPBACK_', 'remote proc transaction promotion', 'false'
GO

--  Создание таблицы для хранения последовательности
if OBJECT_ID(N'dbatools.sequence',N'U') is not null
	drop table dbatools.sequence
go

create table dbatools.sequence(
sequence_name sysname not null,
sequence_start bigint not null,
sequence_step bigint not null,
sequence_current_value bigint not null,
constraint PK_sequence_1 primary key (sequence_name)
)
go

grant select on dbatools.sequence to public;
go

-- создание процедуры для добавления последовательности
if OBJECT_ID(N'dbatools.sp_add_sequence','P') is not null
	drop procedure dbatools.sp_add_sequence
go
create procedure dbatools.sp_add_sequence(
	@sequence_name sysname,
	@sequence_start bigint = 1,
	@sequence_step bigint = 1
)
as
begin
 insert into dbatools.sequence (sequence_name,sequence_start,sequence_step,sequence_current_value)
 values (@sequence_name,@sequence_start,@sequence_step,@sequence_start-@sequence_step);
end;
go

-- создание процедуры для удаления последовательности
if OBJECT_ID(N'dbatools.sp_drop_sequence',N'P') is not null
	drop procedure dbatools.sp_drop_sequence
go

create procedure dbatools.sp_drop_sequence(
	@sequence_name sysname
)
as
begin
 delete from dbatools.sequence 
	where sequence_name = @sequence_name;
end;
go

-- Внутренная процедура получения следующего значения из последовательности (только для 2008/2008R2)
if OBJECT_ID(N'dbatools.sp_next_value_sequence_internal',N'P') is not null
	drop procedure dbatools.sp_next_value_sequence_internal
go
create procedure [dbatools].[sp_next_value_sequence_internal](
	@sequence_name sysname,
	@new_value bigint output 	
	)
as
begin
update [dbatools].[sequence]
set @new_value = sequence_current_value = sequence_current_value + sequence_step 
						  where sequence_name = @sequence_name		  
end;
go

grant execute on dbatools.sp_next_value_sequence_internal to public;
go

-- Внешняя процедура получения следующего значения из последовательности
if OBJECT_ID(N'dbatools.sp_next_value_sequence',N'P') is not null
	drop procedure dbatools.sp_next_value_sequence
go

-- Процедура для 2008/2008R2
create procedure [dbatools].[sp_next_value_sequence](
	@sequence_name sysname,
	@new_value bigint output 	
	)
with execute as caller
as
declare @sql_run nvarchar(1000)		  
begin
 select @sql_run = N'[_LOOPBACK_].'+quotename(db_name())+N'.[dbatools].[sp_next_value_sequence_internal]'
 exec @sql_run @sequence_name = @sequence_name,@new_value = @new_value output
end;
go

-- Процедура для 2005 (старая версия).
create procedure [dbatools].[sp_next_value_sequence](
	@sequence_name sysname,
	@new_value bigint output 	
	)
as
begin
update [dbatools].[sequence]
set @new_value = sequence_current_value = sequence_current_value + sequence_step 
						  where sequence_name = @sequence_name
end;
go


grant execute on dbatools.sp_next_value_sequence to public;
go

Здесь нет ничего сложного. Мы создаем таблицу в которой будем хранить наши последовательности и функции для работы с ней.
Функция dbatools.sp_add_sequence добавляет последовательность в нашу таблицу
 exec dbatools.sp_add_sequence
	@sequence_name = N'MySequence',
	@sequence_start = 10,
	@sequence_step =10

Функция dbatools.sp_next_value_sequence получает следующее значение и записывает в переменную
 declare @seq bigint = 0;
 exec dbatools.sp_next_value_sequence
		@sequence_name = N'MySequence',
		 @new_value =@seq  output
 print @seq


и наконец функция dbatools.sp_drop_sequence удаляет последовательность из таблицы
 exec dbatools.sp_drop_sequence
 @sequence_name = N'MySequence'


Ну вот и все. Вот мой простой алгоритм иммитации последовательности.

Ссылки на связанные темы:
1. dbatools. Часть 1. Общеиспользуемые объекты
2. dbatools. Часть 3. Обслуживание индексов

Комментарии


  • Наивная реализация. Основная ценность sequence состоит в его внетранзакционности: отмена транзакции, которая получала значения последовательности, не должна приводить к возвращению последовательности к значению на начало транзакции, иначе это чревато проблемами с конкурентным доступом. BTW, таблица dbatools.sequence становится "бутылочным горлом" - получение следующего значения скорее всего будет ожидать завершения транзакции, уже получавшее значение последовательности. Нужны автономные транзакции.

  • https://www.sql.ru/blogs/decolores/894

  • 2Der_Guest спасибо за коммент и ссылку как только поправлю скрипт обязательно обновлю.

  • Поправил общий скрипт.



Необходимо войти на сайт, чтобы оставлять комментарии