czwartek, 15 września 2011

Intregralność danych i wartości NULL

Pracowałem nad projektem, gdzie musiałem zapewnić integralność danych w tabeli, której kolumny mogą przyjmować wartość NULL. Do tego wartości w jednej kolumnie były zależne od danch w innej kolumnie. Do rozwiązania były następujące problemy:
  • Wartości w kolumnach x i y muszą unikatowe z założeniem, że x i y mogą wielokrotnie przyjmować wartość NULL
  • Jeżeli wartość w kolumnie x jest NULL to wartość w kolumnie y także musi być NULL i na odwrót, y może mieć wartość NULL tylko w przypadku gdy x jest NULL
Poniższy fragment kodu posży nam do utworzenia przykładowych tabel.

IF OBJECT_ID('SOURCES') IS NOT NULL
DROP TABLE SOURCES
GO

CREATE TABLE SOURCES (
SOURCE_ID INT NOT NULL primary key,
SOURCE_NAME NVARCHAR(100) NOT NULL
)
GO

INSERT INTO SOURCES
SELECT '1', 'Source 1' UNION
SELECT '2', 'Source 2'
GO

IF OBJECT_ID('ARTICLES') IS NOT NULL
DROP TABLE ARTICLES
GO

CREATE TABLE ARTICLES (
ID INT IDENTITY NOT NULL PRIMARY KEY,
TITLE NVARCHAR(100) NOT NULL,
FOREIGN_ID NVARCHAR(10) NULL,
SOURCE_ID INT NULL,
CONSTRAINT FK_AS FOREIGN KEY(SOURCE_ID) REFERENCES SOURCES(SOURCE_ID)
)


Postawione wcześniej warunki odnoszą się do kolumn FOREIGN_ID oraz SOURCE_ID w tabeli ARTICLES.

Pierwszym orzechem do zgryzienia był problem zapewnienia by FOREIGN_ID oraz SOURCE_ID były unikatowe, jednakże dopuszczalna jest sytuacja, gdy artykuł nie posiada żadnego źródła. Na początku pomyślałem, że UNIQUE Cosntraint rozwiąże problem, ale od razu zapaliła mi się w głowie lampka. Te rozwiązanie zadziałałoby tylko dla pierwszego rekordu z podaną kombinacją danych a to nie było to czego potrzebowałem. Tak więc skorzystać musiałem z innego rozwiązania.

Unikatowy indeks na kolumnach zawierających wartoci NULL

SQL Server 2008 umożliwia zakładanie indeksów zawierających filter. Filtrem w tym przypadku była wartość NULL w obu kolumnach.

CREATE UNIQUE INDEX IDX_UNIQUE ON ARTICLES(FOREIGN_ID, SOURCE_ID)
WHERE FOREIGN_ID IS NOT NULL AND SOURCE_ID IS NOT NULL

Tak zadeklarowany indeks sprawi, że będzie on składał się tylko z tych rekordów, które przyjmują wartości w obu kolumnach.

Poniższe zapytania przetestują utworzony indeks

insert into articles (title)
select 'Article 1' union
select 'Article 2'


insert into articles (title, foreign_id, source_id)
select 'Article 3', '1', 1 union
select 'Article 4', '1', 1


Pierwszy INSERT wykona się bez błędów, natomiast drugi zwróci następujący błąd

Cannot insert duplicate key row in object 'dbo.ARTICLES' with unique index 'IDX_UNIQUE'.

Tak więc pierwszy problem został rozwiązany. Pozostaje jeszcze drugi aby rekordy nie zawierały źródła i klucza obcego, lub też by zawierały obie wartości jednocześnie.

Wartość NULL w kolumnie z warunkiem

Tutaj z pomocą przychodzi CHECK constraint. Umożliwia on dodanie danych, tylko w przypadku spełnienia jakiegoś warunku.

W tym przypadku warunkiem jest, że albo obie kolumny przyjmują wartość NULL albo żadna z nich.

ALTER TABLE ARTICLES ADD CHECK ((FOREIGN_ID IS NULL AND SOURCE_ID IS NULL) OR (FOREIGN_ID IS NOT NULL AND SOURCE_ID IS NOT NULL))

Powyższy fragment kodu spełni postawiony wcześniej warunek.

Z kolei poniższe 3 zapytania przetestują, czy wszystko działa tak jak powinno

insert into articles (title, foreign_id, source_id) values ('Article 3', NULL, 1)

insert into articles (title, foreign_id, source_id) values ('Article 3', '1', NULL)

insert into articles (title, foreign_id, source_id) values ('Article 3', '1', 1)

Pierwsze dwa zapytania zwrócą nastepujący błąd. Jedna z kolumn przyjmuje wartość NULL

The INSERT statement conflicted with the CHECK constraint "CK__ARTICLES__1D7B6025". The conflict occurred in database "tempdb", table "dbo.ARTICLES"

Natomiast ostatnie zapytanie pomyślnie zostanie wykonane.

Na koniec jeszcze krótki fragment kodu, który usunie utworzone wcześniej tabele.

IF OBJECT_ID('ARTICLES') IS NOT NULL
DROP TABLE ARTICLES
GO

IF OBJECT_ID('SOURCES') IS NOT NULL
DROP TABLE SOURCES
GO

środa, 27 lipca 2011

DELETE z klauzulą OUTPUT a integralność danych

Dzisiaj natrafiłem na ciekawą właściwość klauzuli OUTPUT. Miałem za zadanie napisać procedurę, która usuwa rekord z jednej tabeli i zapisuje go to innej. Prosty audyt, bez wykorzystywania triggerów.

Problem trywialny. Stworzyłem sobie tabelę do której zapisywane są usuwane recordy. Do tego dodałem kilka kluczy obcych. W zapytaniu, które usuwa recordy dodałem klauzulę OUTPUT, tak by zapisać skasowane dane do mojej nowej tabeli. Proste, nieprawdaż? Otóż nie. W momencie uruchomienia mojej procedure od razu sie ona wysypała. Niestety w klauzuli OUTPUT ... INSERT nie może występować tabela, która jest powiązana z innymi tabelami kluczami obcymi. I co tu wybrać teraz integralnośćdanych czy wygodę?

Wole integralność mimo wszystko kosztem bardziej złożonego zapytania. Tyle teorii, teraz praktyka. Poniższy przykład w bardzo prosty sposób pokazuje istotę problemu.

Najpierw tabele:

USE TEMPDB
GO

CREATE TABLE PRODUCTS (
ID INT IDENTITY NOT NULL PRIMARY KEY,
[DESCRIPTION] NVARCHAR(100)
)
GO

CREATE TABLE PROFILES (
PROFILE_ID INT IDENTITY NOT NULL PRIMARY KEY,
FULLNAME NVARCHAR(100)
)
GO

CREATE TABLE PRODUCTS_ARCHIVE (
ID INT IDENTITY NOT NULL PRIMARY KEY,
[DESCRIPTION] VARCHAR(100),
DELETEDBY INT,
ORIGINAL_ID INT
)
GO

Teraz trochę danych testowych.

INSERT INTO PRODUCTS([DESCRIPTION])
SELECT 'Brick' UNION
SELECT 'Knife' UNION
SELECT 'Spanner' UNION
SELECT 'Nails'
GO

INSERT INTO PROFILES (FULLNAME)
SELECT 'John Peacock' UNION
SELECT 'Steven Smith' UNION
SELECT 'Jason Brown'
GO

Teraz dwie procedure, które pomogą zobrazować problem.
CREATE PROCEDURE SP_DELETE_PRODUCT
@ID INT,
@DELETEDBY INT
AS
DELETE FROM PRODUCTS
OUTPUT DELETED.[DESCRIPTION], @DELETEDBY, DELETED.ID INTO PRODUCTS_ARCHIVE([DESCRIPTION],DELETEDBY,ORIGINAL_ID)
WHERE ID = @ID
GO

CREATE PROCEDURE SP_DELETE_PRODUCT_ALT
@ID INT,
@DELETEDBY INT
AS
DECLARE @TAB TABLE (
[DESCRIPTION] VARCHAR(100),
ORIGINAL_ID INT
)

DELETE FROM PRODUCTS
OUTPUT DELETED.[DESCRIPTION], DELETED.ID INTO @TAB([DESCRIPTION],ORIGINAL_ID)
WHERE ID = @ID
INSERT INTO PRODUCTS_ARCHIVE([DESCRIPTION],DELETEDBY,ORIGINAL_ID)
SELECT [DESCRIPTION], @DELETEDBY, ORIGINAL_ID FROM @TAB
GO

Żadna z tabel nie jest powiązana kluczami obcymi, przeważnie niezbyt dobre to rozwiązanie.

EXEC SP_DELETE_PRODUCT 1,1

Wykonując pierwszą procedurę, która wykonuje zadanie z wykorzystaniem zaledwie 3 lini kodu otrzymujemy pożądany rezultat. Rekord został usunięty z tabeli PRODUCTS i dodany do PRODUCTS_ARCHIVE. Wszystko pięknie, ładnie i przyjemnie ale przydałoby się zapewnienie trochę integralności danch.

W tym celu połączymy tabele PROFILES and PRODUCTS_ARCHIVE kluczem obcym.

ALTER TABLE PRODUCTS_ARCHIVE
ADD CONSTRAINT FK_P_PA FOREIGN KEY(DELETEDBY) REFERENCES PROFILES(PROFILE_ID)
GO

Teraz poniższe zapytanie zwróci błąd.

EXEC SP_DELETE_PRODUCT 2,1

Msg 332, Level 16, State 1, Procedure SP_DELETE_PRODUCT, Line 6
The target table 'PRODUCTS_ARCHIVE' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_P_PA'.

Niestety DELETE ... INTO nie lubi relacji między tabelami.

Rozwiązaniem problemu w tym przypadku jest utworzenie tabeli tymczasowej lub zmiennej w celu przechowania usuwanych danych.

Problem można rozwiązać na dwa sposoby. Albo wykonać zapytanie SELECT, które skopiuje usuwane dane do tabeli tymczasowej, przed wykonaniem polecenia DELETE, albo też skorzystać z klauzuli OUTPUT ... INTO.

Mając już usunięte dane z pierwszej tabeli, należy teraz skopiować zawartość tabeli tymczasowej do tabeli docelowej.

Te rozwiązanie wykorzystuje druga procedura, poniższe zapytanie wykona sie bez błędów.

EXEC SP_DELETE_PRODUCT_ALT 2,1

Na koniec jeszcze dla formalności troche kodu czyszczącego utworzone obiekty.

DROP PROCEDURE SP_DELETE_PRODUCT
GO
DROP PROCEDURE SP_DELETE_PRODUCT_ALT
GO
DROP TABLE PRODUCTS_ARCHIVE
GO
DROP TABLE PRODUCTS
GO
DROP TABLE PROFILES
GO

czwartek, 2 czerwca 2011

Wartość minimalna nie mniejsza niż...

Czy zastanawialiście sie kiedys jak znaleść najmniejszą wartość w zbiorze, jednakże tak była ona większa od pewnej wartości a w dodatku by rozwiązanie to było dobrze zoptymalizowane?

Problem można rozwiązać na kilka sposobów.

Pierwsza rzecz jaka kazdemu przychodzi zapewne do głowy to:

SELECT ID, MIN(COL) FROM MyTABLE WHERE COL > x GROUP BY ID

Cel osiągniety bez większego nakładu sił. Co jednak w przypadku kiedy potrzebne są wartości minimalne dla kilku kolumn?

Można to zrobić na kilka sposobów pewnie. Żeby nie teoretyzować za dużo poniższe rozwiązanie wykorzystuje fragment notowań dwóch spółek z GPW.

Przykładowa dane zapiszemy w tabeli tymczasowej

SET NOCOUNT ON

IF OBJECT_ID('TEMPDB..#QUOTES') IS NOT NULL
DROP TABLE #QUOTES

;WITH CTE AS (
SELECT 'URLOPYPL' AS NAZWA,'04 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','05 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','06 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','09 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','10 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','11 May 201' AS DATE,187000 AS VOLUME, 41140.00 AS VALUE, 24 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','12 May 201' AS DATE,345662 AS VOLUME, 90760.00 AS VALUE, 36 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','13 May 201' AS DATE,268563 AS VOLUME, 99930.00 AS VALUE, 51 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','16 May 201' AS DATE,765218 AS VOLUME, 425420.00 AS VALUE, 263 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','17 May 201' AS DATE,378111 AS VOLUME, 307780.00 AS VALUE, 170 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','18 May 201' AS DATE,1502701 AS VOLUME, 1485030.00 AS VALUE, 833 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','19 May 201' AS DATE,1397675 AS VOLUME, 1441540.00 AS VALUE, 784 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','20 May 201' AS DATE,191155 AS VOLUME, 203610.00 AS VALUE, 132 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','23 May 201' AS DATE,159533 AS VOLUME, 152170.00 AS VALUE, 132 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','24 May 201' AS DATE,204692 AS VOLUME, 173250.00 AS VALUE, 139 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','25 May 201' AS DATE,246006 AS VOLUME, 187250.00 AS VALUE, 152 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','26 May 201' AS DATE,175840 AS VOLUME, 151150.00 AS VALUE, 113 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','27 May 201' AS DATE,113403 AS VOLUME, 92440.00 AS VALUE, 87 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','30 May 201' AS DATE,221718 AS VOLUME, 176730.00 AS VALUE, 149 AS TRANSACTIONS UNION ALL
SELECT 'URLOPYPL','31 May 201' AS DATE,669521 AS VOLUME, 679490.00 AS VALUE, 383 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','04 May 201' AS DATE,991 AS VOLUME, 496280.00 AS VALUE, 23 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','05 May 201' AS DATE,537 AS VOLUME, 267640.00 AS VALUE, 13 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','06 May 201' AS DATE,966 AS VOLUME, 474170.00 AS VALUE, 29 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','09 May 201' AS DATE,1390 AS VOLUME, 682390.00 AS VALUE, 22 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','10 May 201' AS DATE,89 AS VOLUME, 44370.00 AS VALUE, 8 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','11 May 201' AS DATE,57 AS VOLUME, 28430.00 AS VALUE, 15 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','12 May 201' AS DATE,286 AS VOLUME, 140850.00 AS VALUE, 23 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','13 May 201' AS DATE,367 AS VOLUME, 183830.00 AS VALUE, 24 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','16 May 201' AS DATE,79 AS VOLUME, 39590.00 AS VALUE, 11 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','17 May 201' AS DATE,946 AS VOLUME, 466280.00 AS VALUE, 18 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','18 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','19 May 201' AS DATE,144 AS VOLUME, 72630.00 AS VALUE, 9 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','20 May 201' AS DATE,1412 AS VOLUME, 713060.00 AS VALUE, 10 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','23 May 201' AS DATE,1727 AS VOLUME, 856060.00 AS VALUE, 26 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','24 May 201' AS DATE,70 AS VOLUME, 34580.00 AS VALUE, 9 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','25 May 201' AS DATE,20 AS VOLUME, 9860.00 AS VALUE, 1 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','26 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','27 May 201' AS DATE,38 AS VOLUME, 18740.00 AS VALUE, 5 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','30 May 201' AS DATE,0 AS VOLUME, 0.00 AS VALUE, 0 AS TRANSACTIONS UNION ALL
SELECT 'WAWEL','31 May 201' AS DATE,526 AS VOLUME, 262780.00 AS VALUE, 16 AS TRANSACTIONS
)

SELECT * INTO #QUOTES FROM CTE

CREATE NONCLUSTERED INDEX IDX_QUOTES ON #QUOTES(NAZWA,[DATE]) INCLUDE(VOLUME, VALUE, TRANSACTIONS)

Powiedzmy, że potrzebujemy wartości minimalne dla ilości transakcji, wolumenu oraz wartości obrotu, jednakże tylko z sesji gdzie wystąpiły transakcje.

SELECT
NAZWA,
MIN(TRANSACTIONS) MIN_TRANSACTIONS,
MIN(VOLUME) MIN_VOLUME,
MIN(VALUE) MIN_VALUE,
'Excluded sessions without transactions' NOTES
FROM #QUOTES
WHERE TRANSACTIONS > 0

Powyższe zapytanie zwróci oczekiwane wyniki. Przykład jest dość trywialny, gdyż wartości w kolumnach, z który pobierane są wartości minimalne są skorelowane z ilością transakcji. Jeżeli nie bylo transakcji to zarówno wolumen jak i wartość obrotu wynoszą 0.

Powyższe zapytanie zwróci następujące wyniki:
NAZWA MIN_TRANSACTIONS MIN_VOLUME MIN_VALUE NOTES
-------- ---------------- ----------- --------------------------------------- --------------------------------------
URLOPYPL 24 113403 41140.00 Excluded sessions without transactions
WAWEL 1 20 9860.00 Excluded sessions without transactions

Przy okazji cel został osiągnięty przy jednym skanie całej tabeli.

Co jednak w przypadku gdybyśmy chcieli obliczyć dodatkowo średnią ilość transakcji, ale biorąc pod uwagę także sesje bez transakcji?

Poniższe zapytanie, z warunkiem na ilość transakcji niestety, ale zwróci niepoprawne wyniki
SELECT
NAZWA,
MIN(TRANSACTIONS) MIN_TRANSACTIONS,
MIN(VOLUME) MIN_VOLUME,
MIN(VALUE) MIN_VALUE,
AVG(TRANSACTIONS) AVG_TRANSACTIONS,
'Excluded sessions without transactions' NOTES
FROM #QUOTES
WHERE TRANSACTIONS > 0
GROUP BY NAZWA

Problem można by rozwiązać za pomocą skorelowanego zapytania lub CROSS APPLY, jednakże będzie się to wiązać z dodatkowym skanem tabeli.

Czy komuś przychodzi do głowy inne rozwiązanie, takie które by zwrócilo pożądane wyniki z wykorzystaniem tylko jednego skanu tabeli?

Otóż z pomocą przychodzi tutaj CASE WHEN ... END.

SELECT
NAZWA,
MIN(CASE WHEN TRANSACTIONS = 0 THEN NULL ELSE TRANSACTIONS END) MIN_TRANSACTIONS,
MIN(CASE WHEN VOLUME = 0 THEN NULL ELSE VOLUME END) MIN_VOLUME,
MIN(CASE WHEN VALUE = 0 THEN NULL ELSE VALUE END) MIN_VALUE,
AVG(TRANSACTIONS) AVG_TRANSACTIONS,
'Included sessions without transactions' NOTES
FROM #QUOTES
GROUP BY NAZWA

W przypadku gdy nie wystąpiły żadne transakcje w ciągu danej sesji, funkcja agregująca przyjmuje NULL w pozostałym przypadku wartość dla danej sesji. Rozwiązanie jest to o tyle uniwersalne, że możemy pobrac wartość minimalną nie mniejszą niż, i może być to zarazem różna wartość dla każdej z kolumn.