Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 CHECK для диапазона времени  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
Есть таблица с логином оператора, датой начала и датой завершения некоторого процесса. Как сделать ограничение, чтобы при добавлении новой записи у оператора не было пересечения периодов? Написал скалярную функцию, но мне кажется должно быть другое, более простое (и более скоростное) решение.
+
CREATE TABLE [dbo].[OperList](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Operator] [varchar](10) NOT NULL,
	[StartDate] [smalldatetime] NOT NULL,
	[EndDate] [smalldatetime] NOT NULL,
	[Stamp] [datetime] NOT NULL CONSTRAINT [DF_OperList_Stamp]  DEFAULT (getdate()),
)

ALTER TABLE [dbo].[OperList]  WITH CHECK ADD  CONSTRAINT [CK_OperList_Date] CHECK  (([Startdate]<[EndDate]))

ALTER TABLE [dbo].[OperList]  WITH CHECK ADD  CONSTRAINT [CK_OperList_Period] CHECK  (([dbo].[fnCheckOperPeriod]()=(0)))


ALTER FUNCTION [dbo].[fnCheckOperPeriod]
(
	-- Add the parameters for the function here
)
RETURNS int
AS
BEGIN
	DECLARE @res int = 0
	set @res = (
					SELECT count(*) as Total
					FROM [dbo].[OperList] O1
					inner join [dbo].[OperList] O2
						on O1.id <> O2.id
							and O1.Operator = O2.Operator
							and (
								(O2.StartDate > O1.Startdate and O2.StartDate < O1.EndDate)
									or
								(O2.EndDate > O1.Startdate and O2.EndDate < O1.EndDate)
									or
								(O2.StartDate > O1.StartDate and O2.EndDate < O1.EndDate)
									or
								(O2.StartDate < O1.StartDate and O2.EndDate > O1.EndDate)
								)
				)

	RETURN @res

END
30 ноя 16, 09:08    [19949916]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
aleks2
Guest
and (
								(O2.StartDate > O1.Startdate and O2.StartDate < O1.EndDate)
									or
								(O2.EndDate > O1.Startdate and O2.EndDate < O1.EndDate)
									or
								(O2.StartDate > O1.StartDate and O2.EndDate < O1.EndDate)
									or
								(O2.StartDate < O1.StartDate and O2.EndDate > O1.EndDate)
								)

вот те "более скоростное"

O2.StartDate < O1.EndDate and  O1.Startdate < O2.EndDate
30 ноя 16, 09:18    [19949938]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
aleks2
Guest
+ не надо объявлять и присваивать переменную внутри функции
+ не надо считать пересечения - те какая разница скока их?
30 ноя 16, 09:22    [19949947]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Для определения наличия пересечений интервалов достаточно произвести два сравнения.
30 ноя 16, 11:20    [19950376]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Владислав Колосов
Для определения наличия пересечений интервалов достаточно произвести два сравнения.
Алекс же уже произвёл?
30 ноя 16, 11:21    [19950382]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
virtuOS
более простое (и более скоростное) решение.
Триггер instead of insert с соответствующей проверкой.
30 ноя 16, 11:32    [19950464]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
а от самосоединения таблицы никак не избавиться?
30 ноя 16, 11:33    [19950472]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 266
автор
а от самосоединения таблицы никак не избавиться?

По аналогии с inserted.operator, inserted.StartDate, inserted.EndDate
30 ноя 16, 11:34    [19950478]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
aleks2
Guest
invm
virtuOS
более простое (и более скоростное) решение.
Триггер instead of insert с соответствующей проверкой.

Констрэйнт (правильный, ибо функция тредстартера ужасна) быстрее триггера.
И MS рекомендует.
30 ноя 16, 11:57    [19950618]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
aleks2
Констрэйнт (правильный, ибо функция тредстартера ужасна) быстрее триггера.
А "правильный" это какой?
30 ноя 16, 12:16    [19950714]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
invm
aleks2
Констрэйнт (правильный, ибо функция тредстартера ужасна) быстрее триггера.
А "правильный" это какой?
C EXISTS(), наверно.
Однако, проверка будет проходить построчно, если я правильно понимаю?
А в триггере можно проверить всё сразу.
30 ноя 16, 12:22    [19950741]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Чек констрейты проверяются после выполнения команды перед закрытием транзакции, насколько я помню. Иначе может необоснованно "выбить" проверку до внесения всех полагающихся изменений в данные.
30 ноя 16, 13:00    [19951038]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
aleks2
Guest
iap
invm
пропущено...
А "правильный" это какой?
C EXISTS(), наверно.
Однако, проверка будет проходить построчно, если я правильно понимаю?
А в триггере можно проверить всё сразу.

Да. За "построчно" можно нипариться.
MS за "построчно".
30 ноя 16, 13:31    [19951229]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
iap
C EXISTS(), наверно.
Ага. Но у алекса может быть свое мнение В данном оно совпало с общепринятым.
Владислав Колосов
Чек констрейты проверяются после выполнения команды перед закрытием транзакции
Нет.
aleks2
Констрэйнт ... быстрее триггера.
Как ни странно, но это так.
+
use tempdb;
go

create table dbo.t (id int identity primary key, op int not null, ds datetime not null, de datetime not null);

with a as
(
 select top (1000000)
  row_number() over (order by (select 1)) as n,
  (row_number() over (order by (select 1)) - 1) / 100 as op
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b
)
insert into dbo.t
 (op, ds, de)
select
 op, dateadd(mi, n * 4, '17530101'), dateadd(mi, n * 4 + 3, '17530101')
from
 a;

create index IX_t on dbo.t (op, de) include (ds);
go

create function dbo.fnIsIntersected
(
 @id int,
 @op int,
 @ds datetime,
 @de datetime
)
returns int
as
begin
 return case when exists(select 1 from dbo.t where op = @op and @ds < de and @de > ds and id <> @id) then 1 else 0 end;
end;
go

create trigger dbo.tr_t__ioi
on dbo.t
instead of insert
as
begin
 set nocount on;

 if exists(
   select
    1
   from
    inserted i
   where
    exists(select 1 from dbo.t where op = i.op and ds < i.de and de > i.ds)
 )
 begin
  raiserror('Intersection occurred', 16, 1);
  rollback;
  return;
 end;

 insert into dbo.t (op, ds, de) select op, ds, de from inserted;
end;
go

if object_id('tempdb..#t', 'U') is not null
 drop table #t;

select top (10000)
 op, dateadd(year, 1000, ds) as ds, dateadd(year, 1000, de) as de
into
 #t
from
 dbo.t;
go

declare @r table (descr varchar(100), elapsed_time int);
declare @dt datetime = getdate();

begin tran;
select @dt = getdate();
insert into dbo.t (op, ds, de) select op, ds, de from #t;
insert into @r values ('trigger instead of insert', datediff(ms, @dt, getdate()));
rollback;

drop trigger tr_t__ioi;
alter table dbo.t with nocheck add constraint C_t check (dbo.fnIsIntersected(id, op, ds, de) = 0);

begin tran;
select @dt = getdate();
insert into dbo.t (op, ds, de) select op, ds, de from #t;
insert into @r values ('check constraint based on function', datediff(ms, @dt, getdate()));
rollback;

select * from @r;
go

drop table dbo.t;
drop function dbo.fnIsIntersected;
go
descrelapsed_time
trigger instead of insert656
check constraint based on function526
30 ноя 16, 15:27    [19951996]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
aleks2
Guest
Если функцию написать по-человечески - выйдет еще быстрее.
1 дек 16, 06:42    [19953730]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
invm,

как это нет, недавно ссылку кто-то кидал на блок MSDN, что MS этим разруливает парадоксы чеков.
1 дек 16, 11:35    [19954400]     Ответить | Цитировать Сообщить модератору
 Re: CHECK для диапазона времени  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
Владислав Колосов
как это нет
А вот так. Смотрите в плане выполнения где находится Assert.
1 дек 16, 11:43    [19954463]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить