DOMYŚLNE OGRANICZENIE, warto?

Zwykle projektuję swoje bazy danych według następujących zasad:

• Nikt inny niż db_owner i sysadmin nie ma dostępu do tabel bazy danych.
• Role użytkowników są kontrolowane na poziomie aplikacji. Zwykle używam jednej roli db, aby przyznać dostęp do widoków, procedur przechowywanych i funkcji, ale w niektórych przypadkach dodam drugą regułę, aby chronić niektóre procedury przechowywane.
• Używam TRIGGERS do początkowej weryfikacji ważnych informacji.

CREATE TRIGGER <TriggerName>
ON <MyTable>
[BEFORE | AFTER] INSERT
AS
    IF EXISTS (SELECT 1 
               FROM   inserted
               WHERE  Field1 <> <some_initial_value>
               OR     Field2 <> <other_initial_value>)
    BEGIN
        UPDATE MyTable
        SET    Field1 = <some_initial_value>,  
               Field2 = <other_initial_value>  
        ...  
    END

• DML jest wykonywany przy użyciu procedur przechowywanych:

sp_MyTable_Insert(@Field1, @Field2, @Field3, ...);
sp_MyTable_Delete(@Key1, @Key2, ...);
sp_MyTable_Update(@Key1, @Key2, @Field3, ...);

Czy uważasz, że w tym scenariuszu warto użyć DOMYŚLNYCH OGRANICZEŃ, czy dodam dodatkową i niepotrzebną pracę do serwera DB?

Aktualizacja

Rozumiem, że używając ograniczenia DOMYŚLNEGO podaję więcej informacji komuś innemu, kto musi zarządzać bazą danych. Ale najbardziej interesuje mnie wydajność.

Zakładam, że baza danych sprawdza zawsze wartości domyślne, nawet jeśli podam prawidłową wartość, dlatego wykonuję to samo zadanie dwukrotnie.

Na przykład Czy istnieje sposób na uniknięcie ograniczenia DOMYŚLNEGO w ramach wykonania wyzwalacza?

Zakładam, że baza danych sprawdza zawsze wartości domyślne, nawet jeśli podam prawidłową wartość, dlatego wykonuję to samo zadanie dwukrotnie.

Dlaczego miałbyś to założyć? ;-). Biorąc pod uwagę, że Domyślne istnieją, aby zapewnić wartość, gdy kolumna, do której są dołączone, nie jest obecna w INSERTinstrukcji, zakładam, że jest dokładnie odwrotnie: są one całkowicie ignorowane, jeśli powiązana kolumna jest obecna w INSERTinstrukcji.

Na szczęście żadne z nas nie musi niczego zakładać z powodu tego stwierdzenia w pytaniu:

Najbardziej interesuje mnie wydajność.

Pytania dotyczące wydajności prawie zawsze można przetestować. Musimy więc wymyślić test, który pozwoli SQL Serverowi (tutaj prawdziwemu autorytetowi) odpowiedzieć na to pytanie.

USTAWIAĆ

Uruchom raz:

SET NOCOUNT ON;

-- DROP TABLE #HasDefault;
CREATE TABLE #HasDefault
(
  [HasDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NULL,
  [SomeDate] DATETIME NOT NULL DEFAULT (GETDATE())
);

-- DROP TABLE #NoDefault;
CREATE TABLE #NoDefault
(
  [NoDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NULL,
  [SomeDate] DATETIME NOT NULL
);

-- make sure that data file and Tran Log file are grown, if need be, ahead of time:
INSERT INTO #HasDefault ([SomeInt])
  SELECT TOP (2000000) NULL
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;

Testy 1A i 1B należy wykonywać osobno, a nie razem, ponieważ zniekształca to taktowanie. Uruchom każdy z nich kilka razy, aby poznać średnie czasy dla każdego z nich.

Test 1A

TRUNCATE TABLE #HasDefault;
GO

PRINT '#HasDefault:';
SET STATISTICS TIME ON;
INSERT INTO #HasDefault ([SomeDate])
  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;
SET STATISTICS TIME OFF;
GO

Test 1B

TRUNCATE TABLE #NoDefault;
GO

PRINT '#NoDefault:';
SET STATISTICS TIME ON;
INSERT INTO #NoDefault ([SomeDate])
  SELECT TOP (1000000) '2017-05-15 10:11:12.000'
  FROM   [master].sys.[all_columns] ac1
  CROSS JOIN [master].sys.[all_columns] ac2;
SET STATISTICS TIME OFF;
GO

Testy 2A i 2B należy wykonywać osobno, a nie razem, ponieważ zmienia to czas. Uruchom każdy z nich kilka razy, aby poznać średnie czasy dla każdego z nich.

Test 2A

TRUNCATE TABLE #HasDefault;
GO

DECLARE @Counter INT = 0,
        @StartTime DATETIME,
        @EndTime DATETIME;

BEGIN TRAN;
--SET STATISTICS TIME ON;
SET @StartTime = GETDATE();
WHILE (@Counter < 100000)
BEGIN
  INSERT INTO #HasDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
  SET @Counter = @Counter + 1;
END;
SET @EndTime = GETDATE();
--SET STATISTICS TIME OFF;
COMMIT TRAN;
PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);

Test 2B

TRUNCATE TABLE #NoDefault;
GO

DECLARE @Counter INT = 0,
        @StartTime DATETIME,
        @EndTime DATETIME;

BEGIN TRAN;
--SET STATISTICS TIME ON;
SET @StartTime = GETDATE();
WHILE (@Counter < 100000)
BEGIN
  INSERT INTO #NoDefault ([SomeDate]) VALUES ('2017-05-15 10:11:12.000');
  SET @Counter = @Counter + 1;
END;
SET @EndTime = GETDATE();
--SET STATISTICS TIME OFF;
COMMIT TRAN;
PRINT DATEDIFF(MILLISECOND, @StartTime, @EndTime);

Powinieneś zobaczyć, że nie ma prawdziwej różnicy w czasie między testami 1A i 1B, ani między testami 2A i 2B. Więc nie, nie ma ograniczenia wydajności, które ma DEFAULTokreślone, ale nie jest używane.

Poza dokumentowaniem zamierzonego zachowania należy również pamiętać, że to w głównej mierze zależy Ci na tym, aby instrukcje DML były całkowicie zawarte w procedurach przechowywanych. Ludzie wsparcia nie dbają. Przyszli programiści mogą nie zdawać sobie sprawy z twojego pragnienia, aby wszystkie DML były zawarte w tych procedurach przechowywanych, lub dbać o to, nawet jeśli wiedzą. A ktokolwiek utrzymuje tę bazę danych po twojej nieobecności (inny projekt lub zadanie), może się tym nie przejmować lub może nie być w stanie zapobiec użyciu ORM bez względu na to, ile protestuje. Tak więc, Defaults, może pomóc w tym, że dają ludziom „out” podczas robienia INSERT, zwłaszcza ad-hoc INSERTwykonywany przez przedstawiciela wsparcia, ponieważ jest to jedna kolumna, której nie trzeba uwzględniać (dlatego zawsze używam wartości domyślnych podczas audytu kolumny dat).

I właśnie przyszło mi do głowy, że można raczej obiektywnie pokazać, czy a DEFAULTjest sprawdzane, gdy powiązana kolumna jest obecna w INSERTinstrukcji: po prostu podaj niepoprawną wartość. Poniższy test właśnie to robi:

-- DROP TABLE #BadDefault;
CREATE TABLE #BadDefault
(
  [BadDefaultID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeInt] INT NOT NULL DEFAULT (1 / 0)
);


INSERT INTO #BadDefault ([SomeInt]) VALUES (1234); -- Success!!!
SELECT * FROM #BadDefault; -- just to be sure ;-)



INSERT INTO #BadDefault ([SomeInt]) VALUES (DEFAULT); -- Error:
/*
Msg 8134, Level 16, State 1, Line xxxxx
Divide by zero error encountered.
The statement has been terminated.
*/
SELECT * FROM #BadDefault; -- just to be sure ;-)
GO

Jak widać, gdy DEFAULTpodana jest kolumna (i wartość, a nie słowo kluczowe ), wartość domyślna jest w 100% ignorowana. Wiemy to, ponieważ się to INSERTudaje. Ale jeśli zostanie użyta wartość domyślna, wystąpi błąd, ponieważ jest ona w końcu wykonywana.

Czy istnieje sposób na uniknięcie DOMYŚLNEGO ograniczenia w wykonywaniu wyzwalacza?

Chociaż potrzeba uniknięcia domyślnych ograniczeń (przynajmniej w tym kontekście) jest całkowicie niepotrzebna, dla zachowania kompletności można zauważyć, że możliwe byłoby „uniknięcie” domyślnego ograniczenia w ramach INSTEAD OFwyzwalacza, ale nie w obrębie AFTERwyzwalacza. Zgodnie z dokumentacją CREATE TRIGGER :

Jeśli istnieją ograniczenia w tabeli wyzwalaczy, są one sprawdzane po wykonaniu WYZWALANIA wyzwalacza i przed wykonaniem PO WYKONANIU wyzwalacza. Jeśli ograniczenia zostaną naruszone, akcje INSTEAD OF wyzwalacza zostaną wycofane, a wyzwalacz AFTER nie zostanie uruchomiony.

Oczywiście użycie INSTEAD OFwyzwalacza wymagałoby:

1. Wyłączanie domyślnego ograniczenia
2. Tworzenie AFTERwyzwalacza, który włącza ograniczenie

Jednak nie poleciłbym tego robić.

Nie widzę żadnej istotnej szkody z powodu domyślnych ograniczeń. W rzeczywistości widzę jedną szczególną zaletę - zdefiniowałeś domyślną wartość na tym samym poziomie logiki, co sama definicja tabeli. Jeśli masz wartość domyślną podaną w procedurze przechowywanej, ktoś musi tam pójść, aby dowiedzieć się, jaka jest wartość domyślna; i nie jest to coś oczywistego dla kogoś nowego w systemie, koniecznie (jeśli, na przykład, jutro odziedziczysz miliard dolarów, kupisz własną tropikalną wyspę, zrezygnujesz i się tam przeprowadzisz, pozostawiając innego biednego sokowirówkę do wymyślenia różnych rzeczy) na własną rękę).