SQL Blog

EVENTARGUMENT, EVENTTARGET i postback w ASP.NET

2012-02-24T01:36:00.001+01:00

Dzisiaj wyjatkowo poruszę temat nie związany z SQL-em.
W ASP.NET do obslugi postback wykorzystywane są dwa ukryte pola na formularzu oraz funkcja javascript. Problem polega na tym , że ASP.NET generuje domyślnie ten kod tylko w przypadku gdy na formularzu występuje kontrolka, która tego wymaga. Może to być np. LinkButton, TextBox, CheckBox, DropDownList (właściwość AutoPostBack musi być ustawiona na true).
W przypadku gdy powyższe jest spełnione to podglądając kod HTML zobaczymy następujący kod

   1: <input type="hidden" name="__EVENTTARGET" id="__EVENTTARGET" value="" />
   2: <input type="hidden" name="__EVENTARGUMENT" id="__EVENTARGUMENT" value="" />

<script type="text/javascript">   1:  
//<![CDATA[
var theForm = document.forms['Form1'];
if (!theForm) {
    theForm = document.Form1;
}
function __doPostBack(eventTarget, eventArgument) {
    if (!theForm.onsubmit || (theForm.onsubmit() != false)) {
        theForm.__EVENTTARGET.value = eventTarget;
        theForm.__EVENTARGUMENT.value = eventArgument;
        theForm.submit();
    }
}
//]]>
</script>

Niestety powyższe nie jest generowane przez kontrolki Button i ImageButton.

W przypadku gdy z jakiegoś powodu jest to wymagane to wtedy trzeba sobie radzić w inny sposób i np w funkcji Page_Load dopisać kod, który doda powyższe dwie ukryte kontrolki jak również javascript.

Przykład implementacji takiego rozwiązania można jest np tutaj.

Dlaczego o tym pisze? A to dlatego, że miałem cały dzień zepsuty przez to, że ktoś próbował wykorzystać EVENTARGUMENT w funkcji, która obługiwała zdarzenie OnClick dla ImageButton.

Wszystko byłoby w porządku, pod warunkiem gdy kod ten nie nadawał się do thedailywtf.com.

Bez kluczy, bez indeksów

2012-02-19T22:22:00.001+01:00

Poprzedni post dotyczący ograniczeń w bazie danych zakończyłem planem wykonania prostego zapytania aktualizującego jeden rekord w tabeli. Na planie tym pojawiło się 13 operacji w tym skanowanie indeksu. Niewątpliwie wszystkie ograniczenia, o których pisałem są bardzo przydatne i w 99% procentach przypadków mają one zastosowanie.

Pozostaje ten 1% kiedy optymalizacja zapisu jest bardziej istotna od integralności. Przykładem takim może być zapisywanie statystyk dostępu do serwisu internetowego. W przypadku dużej liczby odwiedzin, gdyby była potrzeba zapisania informacji o każdej sesji, czy czynności wykonanych przez użytkownika opercje zapisywania danych statystycznych mogłyby mieć negatywny wpływ na podstawową funkcjonalność.
Dane ta bardzo często w środowki produkcyjnym były by tylko danymi do zapisu a odczyt następowałby tylko w sytuacji importowania do jakiegoś magazynu danych. Choć i ta operacja mogłaby być robiona z kopi zapasowej. Oczywiście taka sytuacja miałaby miejce w przypadku zastosowania tylko jednej bazy danych. Alternatywą może być np osobna baza tylko dla danych statystycznych.

Tak więc o ile integralność danych jest bardzo często aspektem kluczowym, warto także na uwadze optymalizację.

Ograniczenia w bazie danych

2012-02-17T00:21:00.001+01:00

Ograniczenia w bazie danych są najlepszym sposobem na zachowanie integralności danych. SQL Server oferuje kilka różnych typów ograniczeń i koszt ich użycia nie jest taki sam. Ograniczenia sprawdzane są w określonej kolejności. W poniższych testach zademonstruje działanie kilku z nich.
Przykładowe zapytania bądą wykonywane na bazie danych, do której skrypt można pobrać z tej lokalizacji.
Poniższy skrypt doda wyzwalacz, który będzie potrzebny do wykonania jednego z testów. Wszystkie testy będą wykonane na tabeli Subdivision.

CREATE TRIGGER TRIG_SUBDIVISION_UPDATE ON Subdivision
AFTER UPDATE
AS
  
IF EXISTS(SELECT * FROM inserted WHERE SubdivisionId like 'XX-%')
    RAISERROR('Invalid SubdivisionId', 1,16)
GO

Poniżej dla przejrzystości deklaracja tabeli Subdivision. Znajduję się ona także w skrypcie, o którym wspomniałem wcześniej.

IF NOT EXISTS(SELECT * FROM SYS.tables WHERE name = 'Subdivision')
CREATE TABLE Subdivision(
    SubdivisionID varchar(6) NOT NULL,
    SubdivisionName nvarchar(50) NOT NULL,
    CountryID  AS CAST(LEFT(SubdivisionID,(2)) AS CHAR(2)) PERSISTED,
    Constraint FK_Subdivisions_Country FOREIGN KEY(CountryID) REFERENCES Country(CountryID),
    Constraint PK_Subdivision PRIMARY KEY(SubdivisionID),
    Constraint SubdivisionLength CHECK(LEN(SubdivisionID) >= 4)
)
GO

Istotnym elementów testów jest tabela Hotel

if not exists(select * from sys.tables where name = 'Hotel')
create table Hotel (
    HotelID int identity not null,
    Name nvarchar(100) not null,
    Address1 nvarchar(100) not null,
    Address2 nvarchar(100) null,
    City nvarchar(255) not null,
    SubdivisionID varchar(6) not null,
    Postcode varchar(10) not null,
    constraint PK_Hotel primary key(HotelID),
    constraint FK_Hotel_Subdivision foreign key(SubdivisionID) references Subdivision (SubdivisionID)
)
go

Kolejności sprawdzania ograniczeń

Ograniczenia w bazie sprawdzane są w następującej kolejności

Wartości domyślne
Wartość NULL
Ograniczenie CHECK
Klucze obce w tabelach zależnych
Klucze tabeli aktualizowanej
Klucz podstawowy / wartość unikalna
Wyzwalacze

Sprawdzanie wartości NULL

W testach pominę pierwszy typ ograniczeń. Osobiście rzadko korzystam z tej funkcjonalności i staram się przenosić ustawianie wartości domyślnych do aplikacji bądź procedur składowych.

Sprawdzanie wartości NULL a raczej NOT NULL jest jednym z moich ulubionych. Ja lubie dane, a jeżeli tych danych nie ma w połowie tabeli to kolumna może powinna zostać przenesiona do innej tabeli.

Kolumna SubdivisionId będąca kluczem podstawowym nie może przyjmować wartości NULL.

Wykonując poniższą kwerendę w wynikach pojawi się błąd.

BEGIN TRAN
UPDATE Subdivision SET SubdivisionId = NULL WHERE SubdivisionName = 'York'
ROLLBACK TRAN

Msg 515, Level 16, State 2, Line 2

Cannot insert the value NULL into column 'SubdivisionID', table 'SQLTest.dbo.Subdivision'; column does not allow nulls. UPDATE fails.

Warto zwrócić uwagę tutaj na fakt, że sprawdzane jest ograniczenie sprawdzające czy nowa wartością nie jest NULL a nie klucz podstawowy.

Organiczenie CHECK

Jest to prawdopodobnie jedno z rzadziej używanych ograniczeń w bazie danych, jednakże jest ono bardzo przydatne w kontekście optymalizacji bazy danych.

W przypadku kodów ISO dla jednostek administracyjnych, pierwsze dwie litery to jest oznaczenie państwa po którym następuje myślnik i kod właściwy jednostki administracyjnej. W przykładzie tym w tabeli Subdivision jest ograniczenie na minimalną długość tekstu, która musi wynosić 4 znaki.

BEGIN TRAN
UPDATE Subdivision SET SubdivisionId = 'XX-' WHERE SubdivisionName = 'York'
ROLLBACK TRAN

Powyższe zapytanie zwróci następujący błąd:

Msg 547, Level 16, State 0, Line 2

The UPDATE statement conflicted with the CHECK constraint "SubdivisionLength". The conflict occurred in database "SQLTest", table "dbo.Subdivision", column 'SubdivisionID'.

Ograniczenie to, podobnie jak wartość domyślna i NOT NULL jest ograniczeniem własnym tabeli. Nie występują tutaj żadne odwołania do innych obiektów. Warto zwrócić tutaj uwagę na kolejność sprawdzania ograniczeń. Pomimo tego, że w tabeli Hotel istnieje rekord dla GB-YOR, który jest aktualizowany przez powyższe zapytanie, klucz obcy nie jest weryfikowany.

Sprawdzanie kluczy obcych w tabelach zależnych

W nastepnej kolejności sprawdzane są klucze obce

BEGIN TRAN
UPDATE Subdivision SET SubdivisionId = 'XX-ZZ' WHERE SubdivisionName = 'York'
ROLLBACK TRAN

Powyższe zapytanie, gdy zakończylo się sukcesem spowodowałoby utratę integralności danych. Z racji tego, że w tabeli Hotel znajduję się klucz obcy zapytanie to zwróci następujący błąd

Msg 547, Level 16, State 0, Line 2

The UPDATE statement conflicted with the REFERENCE constraint "FK_Hotel_Subdivision". The conflict occurred in database "SQLTest", table "dbo.Hotel", column 'SubdivisionID'.

Sprawdzanie kluczy obcych w tabeli aktualizowanej

Aby przeprowadzic kolejny test najpier będzie trzeba zaktualizować tabelę Hotel. W tym celu klucz obcy do tabeli Subdivision będzie musiał zostać tymczasowo wyłączony.

BEGIN TRAN
ALTER TABLE Hotel NOCHECK CONSTRAINT FK_Hotel_Subdivision
UPDATE Hotel SET Subdivisionid = 'XX-ZZ' WHERE HotelId=1
ALTER TABLE Hotel CHECK CONSTRAINT FK_Hotel_Subdivision
  
UPDATE Subdivision SET SubdivisionId = 'XX-ZZ' WHERE SubdivisionName = 'York'
ROLLBACK TRAN

Powyższe zapytanie aktualizujące tabelę Subdivision jest takie samo jak w poprzednim teście, jednakże tym razem zwrócony zostanie następujący błąd:

Msg 547, Level 16, State 0, Line 6

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Subdivisions_Country". The conflict occurred in database "SQLTest", table "dbo.Country", column 'CountryID'.

Sprawdzanie kluczy podstawowych, wartości unikalnych

Przetestowanie sprawdzanie kluczy podstawowych w tabeli Subdivision, przy jednoczesnym zachowaniu sekwencji testów jest niemożliwe.

Wyzwalacze

Ostatnim etapem weryfikacji danych i sprawdzania integralności wbudowanej w SQL Server są wyzwalacze.

W calu wykonania tego testu najpierw trzeba wykonać poniże zapytanie, które spraw, że nie pojawi się błąd związany z kluczami obcymi.

   1: INSERT INTO Country SELECT 'XX', 'Dummy', 'XXX', 0
   2: GO

Teraz ponownie wykonując następujące zapytanie zostanie uruchomiony wyzwalacz

BEGIN TRAN
ALTER TABLE Hotel NOCHECK CONSTRAINT FK_Hotel_Subdivision
UPDATE Hotel SET Subdivisionid = 'XX-ZZ' WHERE HotelId=1
ALTER TABLE Hotel CHECK CONSTRAINT FK_Hotel_Subdivision
  
UPDATE Subdivision SET SubdivisionId = 'XX-ZZ' WHERE SubdivisionName = 'York'
ROLLBACK TRAN

Sprawdza on czy nowa wartość zaczyna się od XX i jeżeli tak jest zwrócony jest błąd.

Invalid SubdivisionId
Msg 50000, Level 1, State 16

Powyższe przykłady pokazały kolejność sprawdzania ograniczeń w bazie danych.

Powyższe rozważania zakończę planem wykonania zapytania aktualizującego dane w tabeli Subdivision. Proste zapytanie a operacji jest nie tak mało. Na planie można między innymi zauważyć kolejność sprawdzania kluczy obcych.

Dalsze rozwzania na temat powyższego planu pozostawie sobie na kolejny post.

Rozmiar tabel i indeksów w bazie danych

2012-02-09T20:00:00.000+01:00

Ostatnio zajmowałem się projektem strony, która hostowana jest na serwerze, gdzie nie było pełnej kontroli nad serwerem baz danych. Jednym z wymagań była możliwość monitorowania wielkości bazy danych. SQL Server umożliwia wykonanie tego zadania poprzez odpytywanie widoków systemowych. Wszystkie dane jakie były potrzebe do wykonania tego zadania znajdują się w 4 widokach.
Poniżej znajduję się skrypt i trochę przykładowych danych do testowania.

   1: create database MyTest   
   2: go 

Baza danych lotnisk

Tutaj możecie pobrać bazę danych lotnisk. Skrypt SQL został stworzony na podstawie bazy, którą można pobrac z tej lokalizacji. Lista nie jest aktualna i nie ma np. na niej lotniska w Warszawa-Modlin.

Baza danych państw

Tutaj możecie pobrać bazę danych państw. Została ona stworzona na podstawie tego arykułu.

Po odpaleniu wcześniej wspomnianych skryptów w bazie danych pojawią się dwie nowe tabele.

Sprawzdanie rozmiaru tabel i indeksów

I teraz odpalając poniższy skrypt uzyskamy rozmiar obiektów w bazie.

DECLARE @TABLENAME VARCHAR(126)   
SET @TABLENAME = NULL   
  
SELECT
    TABLE_NAME,
    INDEX_NAME,
    TYPE_DESC,
    INDEX_ID,
    PARTITION_NUMBER,
    RESERVED_PAGES * 8 AS RESERVED_SPACE,
    USED_PAGES * 8 AS USED_SPACE,
    DATA_PAGES * 8 AS DATA,
    ROWS,
    SUM(RESERVED_PAGES * 8) OVER (PARTITION BY TABLE_NAME) AS TOTAL_SPACE_RESERVED,
    SUM(RESERVED_PAGES * 8) OVER (PARTITION BY TABLE_NAME) - SUM(USED_PAGES * 8) OVER (PARTITION BY TABLE_NAME) AS TOTAL_SPACE_UNUSED
FROM (
    SELECT
        T.NAME AS TABLE_NAME,
        I.NAME AS INDEX_NAME,
        I.TYPE_DESC,
        I.INDEX_ID,
        P.PARTITION_NUMBER,
        SUM(PS.RESERVED_PAGE_COUNT) AS RESERVED_PAGES,
        SUM(PS.USED_PAGE_COUNT) AS USED_PAGES,
        SUM(CASE
            WHEN (PS.INDEX_ID < 2) THEN (IN_ROW_DATA_PAGE_COUNT + LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT)
            ELSE LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT
        END) AS DATA_PAGES,
        SUM (CASE
                WHEN (PS.INDEX_ID < 2) THEN PS.ROW_COUNT
                ELSE 0
            END) AS ROWS
        FROM sys.indexes AS I   
        INNER JOIN sys.tables T
        ON T.OBJECT_ID = I.OBJECT_ID
        INNER join sys.partitions AS P
            ON I.OBJECT_ID = P.OBJECT_ID
            AND I.INDEX_ID = P.INDEX_ID
        INNER JOIN sys.dm_db_partition_stats AS PS
            ON P.PARTITION_ID = PS.PARTITION_ID
        GROUP BY T.NAME, I.NAME, I.TYPE_DESC, I.INDEX_ID, P.PARTITION_NUMBER
) AS stats
WHERE ISNULL(@TABLENAME, TABLE_NAME) = TABLE_NAME

Modyfikując zmienną @TABLENAME uzyskane wyniki będą dotyczyły tylko jednej tabeli.

Sprawdzanie obiektów przed operacją modyfikacji schematu bazy danych

2012-01-24T23:42:00.001+01:00

W poprzednim poście pisałem na temat sprawdzania danych przed ich modyfikacją. Obiecałem także napisać kilka słów na temat pisania skryptów zmieniających schemat bazy danych, które będą mogły być wykonane wielokrotnie.
Poniższy skrypt stworzy przykładową strukturę bazy danych.

USE TEMPDB
GO
  
CREATE TABLE COMPANY (
    COMPANY_ID INT IDENTITY NOT NULL PRIMARY KEY,
    NAME NVARCHAR(20) NOT NULL,
    DELETED BIT
)
GO
  
CREATE TABLE BRANCH (
    BRANCH_ID INT NOT NULL IDENTITY PRIMARY KEY,
    COMPANY_ID INT NOT NULL,
    NAME NVARCHAR(20) NOT NULL,
    CONSTRAINT FK_BRANCH_COMPANY FOREIGN KEY(COMPANY_ID) REFERENCES COMPANY(COMPANY_ID)
)
GO
  
;WITH CTE AS (
SELECT 'Company 1' AS NAME, 0 AS DELETED UNION
SELECT 'Company 2', 0 
)
  
INSERT INTO COMPANY (NAME, DELETED)
SELECT NAME, DELETED FROM CTE 
WHERE NOT EXISTS(SELECT * FROM COMPANY WHERE NAME = CTE.NAME)
  
;WITH CTE AS (
    SELECT 'Branch 1' AS NAME, 'Company 1' AS COMPANY_NAME UNION
    SELECT 'Branch 2', 'Company 1' UNION
    SELECT 'Branch 1', 'Company 2' UNION
    SELECT 'Branch 2', 'Company 2'
), CTE2 AS (
    SELECT CTE.NAME, COMPANY.COMPANY_ID FROM CTE
    INNER JOIN COMPANY
    ON CTE.COMPANY_NAME = COMPANY.NAME
)
  
INSERT INTO BRANCH (COMPANY_ID, NAME)
SELECT COMPANY_ID, NAME FROM CTE2
WHERE NOT EXISTS(SELECT * FROM BRANCH WHERE NAME = CTE2.NAME AND COMPANY_ID = CTE2.COMPANY_ID)

Załóżmy, że wystąpiły zmiany w specyfikacji aplikacji, która korzysta z powyższych tabel i kolumna DELETED powinna zostać przeniesiona z tabeli COMPANY do tabeli BRANCH.

Zadanie to zrealizuje prosty skrypt

   1: ALTER TABLE BRANCH ADD DELETED BIT

W przypadku pierwszego wykonania wszystko zadziała poprawnie i kolumna zostanie dodane do tabeli BRANCH. Jak już wspomniałem w poprzednim poście przeważnie moja kontrola nad wykonywaniem skryptów kończy się w momencie dodania kodu do repozytorium. Z racji tego, że nie przepadam za odpowiadaniem na maile związane z błędami wykonania skryptów zawsze staram się zabezpieczyć przed sytuacją gdzie skrypt wykonany jest ponownie i błąd następującej treści mógłby wystąpić.

Msg 2705, Level 16, State 4, Line 1

Column names in each table must be unique. Column name 'DELETED' in table 'BRANCH' is specified more than once.

Spawdzanie czy dana kolumna już istnieje w tabeli

Tak jak w przypadku operacji DML, opisanych w poprzednim poście, tak i tym razem rozwiązanie wymaga dodania tylko jednego warunku.

Poniższy skrypt może być wykonywany wielokrotnie, bez obaw, że pojawi się błąd jak powyżej.

   1: IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = 'DELETED' AND OBJECT_ID = OBJECT_ID('BRANCH'))
   2: ALTER TABLE BRANCH ADD DELETED BIT

Zapytanie podobnego typu może być wykonane także dla innych typów obiektów. Indeksy, tabele, widoki, wszystkie te elementy istnieją w widokach systemowych. Mogą zostać one odpytane przed dodaniem nowego obiektu.

Sprawdzanie czy kolumna istnieje przed aktualizacją danych

Załóżmy, że nowe wymagania dotyczące projektu mówią także, że wartość DELETED z tabeli COMPANY musi być zapisane kolumnie DELETED w tabeli BRANCH, oraz że kolumna DELETED musi być usunięta z tabeli COMPANY po całej aktualizacji.

Skrypt ten mógłby przyjąć następującą postać

UPDATE BRANCH 
SET DELETED = COMPANY.DELETED
FROM COMPANY
INNER JOIN BRANCH
ON BRANCH.COMPANY_ID = COMPANY.COMPANY_ID
  
IF EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = 'DELETED' AND OBJECT_ID = OBJECT_ID('COMPANY'))
ALTER TABLE COMPANY
DROP COLUMN DELETED
GO

Wszystko będzie działać pod warunkiem, że skrypt nie zostanie wykonany ponownie. Wtedy przy próbie kompilacji wysypie się on bo kolumna DELETED już nie istnieje w tabeli COMPANY.

Problemu tego typu nie da się uniknąć poprzez dodanie tylko i wyłącznie następującego warunku przed aktualizacją danych

   1: IF EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = 'DELETED' AND OBJECT_ID = OBJECT_ID('COMPANY'))

Aby obejść ten problem i mieć święty spokój w momencie kiedy skrypt ponownie byłby uruchomiony trzeba skorzystać z dynamicznego SQL-a.

Zapytanie, które będzie aktualizować dane przyjmie wtedy następującą postać

IF EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = 'DELETED' AND OBJECT_ID = OBJECT_ID('COMPANY'))
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'UPDATE BRANCH 
    SET DELETED = COMPANY.DELETED
    FROM COMPANY
    INNER JOIN BRANCH
    ON BRANCH.COMPANY_ID = COMPANY.COMPANY_ID'
    
    EXEC(@SQL)
END

Warto więc dodać kilka lini kodu więcej, aby mieć pewność, że w momencie wielokrotnego wykonania skryptu nie zostaną zgłoszone jakiekolwiek błędy.

Sprawdzanie danych przed operacjami modyfikacji

2012-01-24T00:23:00.001+01:00

Jednym z najprostrzych sposobów na uniknięcie błedów, które mogą pojawić się podczas wykonywanie skryptów jest weryfikacja danych, które ewentualnie powinny być zmodyfikowane.
W momencie kiedy pisze jakiś skrypt moja kontrola nad nim kończy się w momencie kiedy zostanie dodany on to repozytorium kodu.
Później nie mam już żadnego wpływu na to kto będzie go uruchamiał i jak wiele razy. Tak więc aby zabezpieczyć się przed ewentualnymi błędami, które mogą wyniknąć z wielokrotnego wykonania skryptów zawsze staram się działać w tej kwesti proaktywanie.

Dodawanie danych tylko jeżeli ich nie ma

W przypadku, gdy nowe dane powinny zostać dodane do tabeli najłatwiejszym sposobem unikcięcia błędów jest sprawdzenie czy danych tych przypadkiem w tabeli już nie ma.
Weźmy dla przykładu tabelę z kodami pocztowymi

create table Postcodes (
    postcode char(6) not null primary key,
    city nvarchar(30)
)

Załóżmy teraz, że musimy dodać następujące dane do tabeli

1: insert into Postcodes (postcode, city)

   2: select '30-001', N'Kraków' union

   3: select '30-123', N'Kraków' union

   4: select '00-001', N'Warszawa'

Pierwzze wykonanie skryptu oczywiście się powiedzie, ale jak już wspomniałem wcześniej bardzo często kontrola nad tym kto wykonuje i ile razy skrypt jest niemożliwa.

Wykonując powyższy skryp dodawania danych ponownie pojawi się błąd następującej treści.

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__Postcode__F27E9E8B0DAF0CB0'. Cannot insert duplicate key in object 'dbo.Postcodes'.

The statement has been terminated.

Niespodzianek tego typu łatwo jest uniknąć pod warunkiem, że dane które mają być dodane na wstępie zostaną zweryfikowane pod kątem ich istnienia w tabeli.

Modyfikując zapytanie w następujący sposób problem tego ile razy skrypt zostanie uruchomiony przestaje istnieć.

   1: ;with cte as (

   2: select '30-001' postcode, N'Kraków' city union

   3: select '30-123', N'Kraków' union

   4: select '00-001', N'Warszawa'

   5: )

6:

   7: insert into Postcodes (postcode, city)

   8: select postcode, city from cte where not exists (

   9:     select * from postcodes where postcode = cte.postcode

  10: )

W przypadku SQL Servera 2008 i wyższych zapytanie można by zmodyfikować i użyć MERGE

   1: ;with cte as (

   2: select '30-001' postcode, N'Kraków' city union

   3: select '30-123', N'Kraków' union

   4: select '00-001', N'Warszawa'

   5: )

6:

   7: merge Postcodes p

   8: using cte c

   9: on p.postcode = c.postcode

  10: when not matched then

  11: insert (postcode, city)

  12: values (postcode, city);

Weryfikacja danych przed aktualizacją

Podobny problem może zaistnieć w przypadku aktualizacji danych

Załóżmy, że omyłkowo do tabeli został dodany kod pocztowy 00-000 zamiast 00-001

if not exists(select * from Postcodes where postcode = '00-000')
insert into Postcodes (postcode, city)
values('00-000', N'Warszawa')

Wykonując aktualizację bez sprawdzenie czy poprawny kod pocztowy już istnieje narażamy sie ponownie na błąd

update Postcodes
set postcode = '00-001'
where postcode = '00-000'

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__Postcode__F27E9E8B0DAF0CB0'. Cannot insert duplicate key in object 'dbo.Postcodes'.

The statement has been terminated.

Zabezpieczenie się przed tego typu problemem wymaga tylko jednej dodatkowej lini kodu

   1: if not exists(select * from Postcodes where postcode = '00-001')

   2: update Postcodes

   3: set postcode = '00-001'

   4: where postcode = '00-000'

Jak widac na powyższych przykładach zabezpieczenie się przed błędami, które mogą wyniknąć z wielokrotnego uruchamiania skryptów wymaga niewielkiej ilości dodatkowego kodu.

W kolejnym poście napiszę o operacjach modyfikacji struktury bazy danych i jak się zabezpieczyć przed wpadkami związanymi wielokrotnym wykonywaniem skryptów.

Usuń tabelę z tabel systemowych

2012-01-18T23:45:00.001+01:00

Chciałem dalej kontynuować poprzedni wątek dotyczący blokowania zasobów. Wszystko jednak diabli wzięli. Otóż musiałem interweniować dzisiaj po tym jak do systemu wdarła się poprawka powodująca, że główna funkcjonalność systemu przestała działać. Tak więc dzisiaj będzie o tym jak nie usuwać danych z bazy.
Załóżmy, że mamy następujace dane

CREATE TABLE SYSTEM_TABLES (
    TABLE_NAME VARCHAR(126)
)
  
CREATE TABLE SYSTEM_SETTINGS (
    NAME VARCHAR(100),
    VALUE NVARCHAR(20)
)
  
CREATE TABLE INTEREST_RATES (
    NAME VARCHAR(20),
    VALUE DECIMAL(4,2)
)
  
INSERT INTO SYSTEM_TABLES VALUES('SYSTEM_SETTINGS')
INSERT INTO SYSTEM_TABLES VALUES('INTEREST_RATES')


INSERT INTO SYSTEM_SETTINGS
SELECT 'Language', 'English' UNION
SELECT 'AnonymousUsers', '0' UNION
SELECT 'StrongPassword', '1' UNION
SELECT 'PasswordHistory', '3' 
  
INSERT INTO INTEREST_RATES
SELECT 'Reference', 4.5 UNION
SELECT 'Lombard', 6.0 UNION
SELECT 'Deposit', 3.0 UNION
SELECT 'Rediscount', 4.75

Tabela SYSTEM_TABLES przechowuje nazwy tabel istotnych dla funkcjonowania systemu. Z różnych powodów jedną z tych tabel (INTEREST_RATES) trzeba było usunąć z SYSTEM_TABLES.

Banalne z pozoru zapytanie, a wykonane niepoprawnie spowodowało, że system przestał działać.

DELETE z iloczynem kartezjańskim

Mały quiz teraz. Jaki będzie rezultat poniższego zapytania

   1: DELETE INTEREST_RATES FROM SYSTEM_TABLES

Możliwe odpowiedzi:

Błąd składni
Zapytanie się wykona ale nic nie zostanie usunięte
Rekord INTEREST_RATES zostanie usunięy z SYSTEM_TABLES
Cała zawartość tabeli INTEREST_RATES zostanie usunięta

Zachęcam do komentowania, bez uprzedniego testowania zapytania

No, dobra odpowiem sam na to pytanie. Otóż poprawna odpowiedź to ta oznaczona nr 4.

Składnia zapytania jest jak najbardziej poprawna. Jeżeli byłoby to zapytanie SELECT to jego postać byłaby następująca.

   1: SELECT * FROM INTEREST_RATES, SYSTEM_TABLES

Jest to nic innego jak iloczyn kartezjański.

Z tym, że założenie było takie aby usunąć tylko jeden rekord z SYSTEM_TABLES w następujący sposób

   1: DELETE SYSTEM_TABLES WHERE TABLE_NAME = 'INTEREST_RATES'

Tutaj aż się prosi aby dołączyć referencje do mojego pierwszego postu dotyczącego podstaw SQL. Prawdę mówiąc nie myślałem, że tak szybko będę musiał powracać to tego tematu.

I na koniec jeszcze jedna ciekawostka. W języku angielskim polecenie mogłoby przybrać następującą postać:

You must DELETE INTEREST_RATES FROM SYSTEM_TABLES to make this feature working

Język programowania i naturalny maja dużo ze sobą wspólnego. Tylko, że czasami rezultat może być inny od oczekiwanego.

T-SQL od podstaw cz. 2

2012-01-17T02:00:00.001+01:00

W poprzednim wpisie poruszyłem temat transakcji i jak sie zabezpieczyć przed sytuacją taką, że wiecej danych zostanie zaktualizowanych niż oczekiwano.

Blokowanie zasobów

Pozostawienie otwartej transakcji nawet na chwile ma negatywny wpływ na inne operacje na bazie danych.
W momencie kiedy transakcja jest otwarta i tabela jest aktualizowana inne zapytanie domyślnie nie mogą odczytać danych dopóki transakcja aktualizująca dane nie zostanie zakończona. Ma to zapobiedz sytuacji takiej, że odczytywane danych w innych transakcjach mogą uledz zmianie w przypadku gdy aktualizacja się nie powiedzie z jakiegoś powodu.
Są przypadki, w których nie zależy nam na aptekarskim podejściu do zwróconych danych i pewien stopień błędu jest akceptowalny. Dodatkowo samo odczytywanie danych także ma wpływ na operacje aktualizujące te same dane w innej sesji.
W przypadku analizy danych na środowisku produkcyjnym w zdecydowanej większości przypadków zależy nam na tym by uniknąć sytuacji, w której to co robimy ma negatywny wpływ na pracę systemu.

Hint NOLOCK.

Wykonując polecenie SELECT z hintem NOLOCK nie blokujemy zasobów ale także potęcjalnie możemy mieć taką sytuację, że odczytywane dane niekoniecznie będą takie same przy ponownym uruchomieniu zapytania.
Załóżmy, że mamy tabelę MyTable, z której chcemy pobrać wszystkie rekordy z wartością 1 w kolumnie val

create table MyTable (
    id uniqueidentifier not null,
    val char(1) not null
)
  
declare @i int
set @i = 0
while @i<=100
begin
    insert into MyTable values(newid(), @i%9)
    set @i = @i + 1
end

Rezultat ten możemy uzyskać uruchamiając poniższe zapytanie

   1: select * from MyTable where val = 2

Jednakże w sytuacji, kiedy w innym procesie dane w tej samej tabeli są aktualizowane na odpowiedź może nam przyjść trochę poczekać.

Załóżmy następującą sytuację. W transakcji nr 1 wykonujemy następujące zapytanie aktualizujące wartości w kolumnie val z 1 na 3

begin tran
update MyTable
set val = 3
where val = 1

Teraz wykonując ponownie zapytanie, w transakcji nr 2, pobierające rekordy z wartością 2 w kolumnie val będzie czekać aż pierwsza transakcja się zakończy. Może to trochę potrwać, szczególnie w sytuacji gdy ktoś rozpoczął transakcje a zapomniał ją zakończyć. Taka sytuacja już mi się kilka razy zdarzyła pracując na bazie danych dzielonej z innymi deweloperami.

Z racji tego, że potrzebuje rezultat natychmiast i akceptowanla jest dla mnie sytuacja taka, że jakieś dane mogą zostać zmienione w międzyczasie w innej transakcji, zapytanie SELECT wykonuję z hintem NOLOCK.

Zapytanie uruchamiam w transakcji nr 3.

   1: select * from MyTable WITH (NOLOCK) where val = 2

Wynik w tym przypadku otrzymuję natychmiast, podczas, gdy drugie zapytanie ciągle czeka aż zasoby zostaną zwolnione.

Teraz wracając do pierwszej transakcji zatwierdzam zapytanie

   1: commit tran

W tym samym momencie w transakcji nr 2 dostaję wynik, taki sam jak w transakcji nr 3.

T-SQL od podstaw cz. 1

2012-01-15T22:43:00.001+01:00

Postanowiłem sobie, że w tym roku będę blogował znacznie częściej niż miało to miejsce dotychczas. Jako, że w ostatnim czasie dość sporo czasu spędzam pomagając innym z SQL-em to postanowiłem, że będę pisał trochę praktycznych porad.

Transakcje w T-SQL

Czy zdarzyło się wam kiedyś odpalić jakieś zapytanie aktualizujące tabelę, które przez przypadek zaktualizowało więcej rekordów niż tego oczekiwaliście?

Nie jest to częsty problem ale pewnie każdemu się to kiedyś zdarzyło. W ostatnim czasie miałem przyjemność przywracania fragmentu bazy danych, która została zmieniona przez UPDATE bez klauzuli WHERE. Jednym słowem cała tabela została zaktualizowana i trzeba było ją odtworzyć z innego źródła. W moim przypadku, skopiowałem dane z innej bazy, która zawierała tą samą tabelę.

Problemu by nie było gdyby
· Zapytanie miało poprawną klauzulę WHERE
· Byłoby uruchomione wewnątrz transakcji.

Zawsze zalecam zagnieżdżanie zapytań aktualizujących dane wewnątrz transakcji. Zapytanie jest wtedy wykonywane w trzech krokach.
1. Otwarcie transakcji i zapytanie DML.
2. Sprawdzenie rezultatu
3. Zatwierdzenie lub cofnięcie transakcji w przypadku gdy liczba wierszy zaktualizowanych nie odpowiada oczekiwaniom.

W praktyce wygląda to następująco

begin tran
update aspnet_Roles
set Description = 'System administrators'
where RoleName = 'Admins'

Po wykonaniu powyższego w oknie Messages sprawdzam ilość wierszy, które zostały zaktualizowane i jeżeli jest to 1, czyli tyle ile oczekuję to zatwierdzam transakcję poleceniem

   1: commit tran

W przypadku gdyby rezultat byłby inny to cofam transakcję poleceniem

   1: rollback tran

Zweryfikować rezultat można także poprzez wykonanie zapytanie SELECT w tym samej sesji, bądź SELECT z hintem NOLOCK w dowolnej innej sesji.

Intregralność danych i wartości NULL

2011-09-15T23:45:00.028+02:00

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

DELETE z klauzulą OUTPUT a integralność danych

2011-07-27T18:17:00.012+02:00

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

Wartość minimalna nie mniejsza niż...

2011-06-02T02:28:00.010+02:00

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.

MCTS: Microsoft SQL Server 2008, Database Development

2008-12-13T17:30:00.003+01:00

Wczoraj otrzymałem miłego maila z Microsoftu. Mój wynik beta egzaminu 71-433 został oceniony pozytywnie. Tak więc od 9 paźniernika posiadam tytuł MCTS SQL Server 2008, Database Development.

Czy ten łańcuch znaków jest liczbą?

2008-12-05T01:42:00.001+01:00

Problem z pozoru banalny. Sprawdzenie czy łańcuch znaków jest liczbą. Jak to zrobić? Większość osób z pewnością od razu skorzystałaby z funkcji ISNUMERIC. Problem polega na tym, że ta funkcja sprawdza czy dany łańcuch jest typu numerycznego, a nie czy jest liczbą. Funkcja ISNUMERIC zwróci wartość 1 dla następujących wartości '+', '-', '101.5E9', '$10', oraz innych wartości pieniężnych zawierających odpowiednie znaki reprezentujące waluty (http://msdn.microsoft.com/en-us/library/ms188688.aspx).

Jakie mogą być konsekwencje sprawdzania łańcucha znaków tylko za pomocą ISNUMERIC? Ano takie, że funkcja, procedura czy tam inny kod napisany w SQL się wysypie. Poniżej kilka przykładów, w których ISNUMERIC za każdym razem zawraca wartość 1.

DECLARE @STRING AS VARCHAR(1)
SET @STRING = '+'
IF ISNUMERIC(@STRING) = 1
SELECT CONVERT(DECIMAL,@STRING) + 10
GO

Msg 8115, Level 16, State 6, Line 5
Arithmetic overflow error converting varchar to data type numeric.

DECLARE @STRING AS VARCHAR(3)
SET @STRING = '$10'
IF ISNUMERIC(@STRING) = 1
SELECT CONVERT(INT, @STRING) + 10
GO

Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '$10' to data type int.

DECLARE @STRING AS VARCHAR(10)
SET @STRING = '101.5E9'
IF ISNUMERIC(@STRING) = 1
SELECT CONVERT(INT, @STRING) + 10
GO

Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '101.5E9' to data type int.

Jak więc sprawdzić czy łańcuch znaków jest wartością numeryczną, oraz jest typu, który pożądany jest w dalszych obliczeniach?

Ano można skorzystać w tym celu np. z wyrażeń regularnych. Jako, że wyrażenia regularne nie występują póki co w SQL Server, potrzebna będzie funkcja napisana w CLR.

W poprzednim poście (http://updatedev.blogspot.com/2008/11/transformacja-wyniku-zapytania-do.html) zaprezentowałem bibliotekę CLTToolsSuite. Rozbuduję ją teraz o kolejną funkcję, która będzie weryfikowała łańcuch znaków za pomocą wyrażeń regularnych.

<SqlFunction(isdeterministic:=True, dataaccess:=DataAccessKind.None)> _
Public Shared Function RegEx(ByVal input As SqlString, ByVal pattern As SqlString) As SqlBoolean
Dim RegExpr As New Regex(pattern)
Dim match As Match

If input.IsNull Or pattern.IsNull Then
Return False
End If

match = RegExpr.Match(input)
If match.Success Then
Return True
Else
Return False
End If
End Function

Funkcja ta akceptuje dwa parametry. Łańcuch znaków do zweryfikowania oraz szablon wyrażenia regularnego. W przypadku gdy łańcuch znaków jest poprawny funkcja zwraca 1, w każdym innym przypadku 0.

Po skompilowaniu biblioteki pora ja zarejestrować i utworzyć nową funkcję.

IF OBJECT_ID('fn_RegEx') IS NOT NULL
drop function fn_RegEx

IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'CLRToolsSuite')
drop assembly CLRToolsSuite
go

CREATE ASSEMBLY CLRToolsSuite
from 'C:\CLRToolsSuite\CLRToolsSuite\bin\Release\CLRToolsSuite.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.fn_RegEx(@input as nvarchar(max), @pattern as nvarchar(max))
RETURNS BIT
EXTERNAL name CLRToolsSuite.[CLRToolsSuite.CLRToolsSuite].RegEx;
GO

I teraz krótki test, w którym funkcja ISNUMERIC została zastąpiona przez nową funkcję fn_RegEx.

DECLARE @STRING AS VARCHAR(5)
SET @STRING = '+'
IF dbo.fn_RegEx(@STRING, '^[0-9]+(\.*[0-9]*)$') = 1
SELECT CONVERT(DECIMAL,@STRING) + 10
GO

DECLARE @STRING AS VARCHAR(5)
SET @STRING = '$10'
IF dbo.fn_RegEx(@STRING, '^[0-9]+$') = 1
SELECT CONVERT(INT,@STRING) + 10
GO

DECLARE @STRING AS VARCHAR(10)
SET @STRING = '101.5E9'
IF dbo.fn_RegEx(@STRING, '^[0-9]+$') = 1
SELECT CONVERT(INT,@STRING) + 10
GO

Jak widać z powyższych fragmentów kodu coś co może na pierwszy rzut oka wydawać się banalne, wcale takim w rzeczywistości być nie musi. Warto pamiętać więc co funkcja ISNUMERIC robi i jakie są jej ograniczenia.

Transformacja wyniku zapytania do innego formatu

2008-11-11T23:58:00.005+01:00

Dzisiaj zajmę się tematem formatowania wyniku zapytania. Czasami zachodzi potrzeba aby wynik zapytania był w jakimś formacie, który łatwiej umożliwia wizualizację danych. Tym formatem może być HTML, XHTML. W poście tym przedstawię dwie techniki formatowania / transformowania wyniku zapytania.

HTML w T-SQL

Pierwszą z technik, którą przedstawię zaprezentował na swoim blogu Tony Rogerson. Bardzo ciekawa technika umożliwiająca otrzymanie wyniku zapytania jako dokumentu HTML. Poniższe zapytanie korzysta z bazy danych Northwind.

declare @body varchar(max)

set @body = cast( (
select 'company_name' as "td/@class",td = CompanyName + '</td><td>' + ContactName + '</td><td>' + ContactTitle + '</td><td>' + Address +'</td><td>' + City + '</td><td>' + PostalCode + '</td><td>' + Country
from (
select CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country

from customers
) as d
for xml path( 'tr' ), type ) as varchar(max) )

set @body = '<html><head><style>'
+ 'TABLE {border: 1px; width:600px}'
+ 'TH {border-bottom: 5px solid red; border-top: 5px solid black; white-space:nowrap}'
+ 'TD {font-size: 12px; font-family:verdana; white-space: nowrap}'
+ '.table_header {font-family: Verdana; font-size: 14px;}'
+ '.company_name {color:black; font-weight:bold;}'
+ '</style></head><body>'
+ '<h2>Northwind Customers</h2><table cellspacing="0">'
+'<trclass="table_header"><th>CompanyName</th><th>ContactName</th><th>Title</th><th>Address</th>' +'<th>City</th><th>PostalCode</th><th>Country</th></tr>' + replace( replace( @body, '<', '<' ), '>', '>' ) + '</table></body></html>'

Powyższe zapytanie tworzy łańcuch znaków, będący dokumentem HTML. Jak widać poza znacznikami, można też utworzyć style CSS, które formatują dokument.

Aby zobaczyć dokument wynikowy wystarczy wykonać poniższe zapytanie.

select CONVERT(xml, @body)

Technika ta wykorzystuje tylko i wyłącznie T-SQL. Wszystkie znaczniki oraz style są umieszczone w łańcuchach znaków. Nie ma potrzeby odwołań do zewnętrznych komponentów. Minusem tej techniki jest to, że w przypadku bardziej skomplikowanego dokumentu można bardzo łatwo zapomnieć o domknięciu jakiego znacznika. Poza tym czytelność zapytania także ulega degradacji wraz ze wzrostem jego objętości.

HTML z użyciem CLR, XML, XSLT

Ten sam rezultat można otrzymać korzystając z innej techniki, a mianowicie zwrócić wynik zapytania jako dokument XML i wykonać na nim transformaty za pomocą XSLT. Jako, że SQL Server nie umożliwia wykonania tego typu operacji należy skorzystać z możliwości jakie niesie ze sobą CLR.

W celu wykonania transformacji utworzę funkcję, która jako parametry będzie przyjmowała dokument XML, dokument XSLT. Funkcja ta zwracać będzie dokument w innym formacie. Celowo użyłem sformułowania „w innym formacie”, gdyż wartością zwróconą przez tą funkcję nie musi być dokument HTML. Kilka słów więcej na ten temat będzie na zakończenie artykułu.

Tak więc korzystając z Visual Studio, tworzę nowy projekt, którym jest nowa klasa.

Poniżej cały kod klasy, napisanej w VB.NET, zawierającej funkcję, która transformuje wynik zapytania.
Imports System
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Xml
Imports System.Xml.xsl
Imports Microsoft.SqlServer.Server

Partial Public Class CLRToolsSuite
<SqlFunction(isdeterministic:=True, DataAccess:=DataAccessKind.None)> _
Public Shared Function XSLTransform(ByVal xmlDoc As SqlXml, ByVal xslDoc As SqlXml) As SqlXml

Dim memory_stream As New MemoryStream()
Dim lxmlDoc As New XmlDocument
Dim lxslTransform As New XslCompiledTransform
Dim lxmlWriter As New XmlTextWriter(memory_stream, System.Text.Encoding.UTF8)
Dim xmlReader As XmlTextReader

lxmlDoc.InnerXml = xmlDoc.Value
lxslTransform.Load(xslDoc.CreateReader)
lxslTransform.Transform(lxmlDoc, lxmlWriter)
memory_stream.Seek(0, SeekOrigin.Begin)
xmlReader = New XmlTextReader(memory_stream)
Return New SqlXml(xmlReader)

End Function
End Class

Teraz potrzeba tylko skompilować projekt. Mając utworzoną bibliotekę .dll możemy ją zarejestrować w SQL Server.

Na początek jednak trzeba włączyć możliwość korzystania z CLR. Domyślnie ta usługa, zresztą tak samo jak wiele innych rzeczy, w SQL Server jest wyłączona.

exec sp_configure 'clr enabled', 1
RECONFIGURE
GO

Mając włączony CLR można już zarejestrować naszą bibliotekę a także zadeklarować nową funkcję.

CREATE ASSEMBLY CLRToolsSuite
from 'C:\CLRToolsSuite\CLRToolsSuite\bin\Release\CLRToolsSuite.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.fn_XSLTransform(@xmldoc as xml, @xsldoc as xml)
RETURNS XML
EXTERNAL name CLRToolsSuite.[CLRToolsSuite.CLRToolsSuite].XSLTransform;
GO

Ostatnim elementem, no poza samym zapytaniem, tej układanki jest dokument zawierający style potrzebne do przekształcenia wyniku zapytania.

Nie chcę w tym miejscu wgłębiać się w XSLT. Temat ten mógłby zająć prawdopodobnie kilkanaście artykułów. Poniżej kod dokumentu XSLT, który przekształci wynik zapytania do dokumentu HTML, takiego samego jak zaprezentowany został w poprzedniej technice.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<html>
<head>
<style>
TABLE {border: 1px; width:600px; padding: 2px;}
TH {border-bottom: 5px solid red; border-top: 5px solid black; white-space: nowrap}
TD {font-size: 12px; font-family:verdana; white-space: nowrap}
.table_header {font-family: Verdana; font-size: 14px;}
.company_name {color:black; font-weight:bold;}
</style>
</head>

<body>
<h2>Northwind Customers</h2>
<table cellspacing="0">
<tr class="table_header">
<th>Company Name</th>
<th>Contact Name</th>
<th>Title</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th></tr>

<xsl:for-each select="xmldoc/row">
<tr>
<td class="company_name">
<xsl:value-of select="CompanyName"/>
</td>
<td>
<xsl:value-of select="ContactName"/>
</td>
<td>
<xsl:value-of select="ContactTitle"/>
</td>
<td>
<xsl:value-of select="Address"/>
</td>
<td>
<xsl:value-of select="City"/>
</td>
<td>
<xsl:value-of select="PostalCode"/>
</td>
<td>
<xsl:value-of select="Country"/>
</td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

Tak oto stworzony dokument zapiszę do pliku .xslt. Równie dobrze można by go zapisać gdzieś w jakiejś tabeli w bazie danych, jednakże na potrzeby tego artykułu skorzystam z pliku, który później będzie załadowany do zmiennej.

I teraz już pora przejść do SQL-a i wykonać zapytanie.

DECLARE @XMLDOC AS XML
SELECT @XMLDOC = (SELECT CompanyName, ContactName, ContactTitle, Address, City, PostalCode, CountryFROM Customers FOR XML PATH('row'), ROOT('xmldoc'))

DECLARE @xsldoc AS XMLselect @xsldoc = xd from openrowset(BULK 'c:\xsl.xsl', SINGLE_BLOB) as import(xd)

I ponownie, aby zobaczyć rezultat wystarczy wykonać jeszcze jedno zapytanie.

select dbo.fn_XSLTransform(@xmldoc,@xsldoc)

Podsumowanie

Zarówno pierwsza jak i druga zaprezentowana technika pozwala nam uzyskać zapytanie sformatowane w postaci kodu HTML. O ile w pierwszej technice wykorzystany był tylko T-SQL o tyle druga wykorzystuje CLR i XSLT. Tak jak już wspomniałem w przypadku wykorzystania techniki tylko z T-SQL nie trudno jest o dopuszczenie się błędu w trakcie pisania znaczników HTML. W przypadku drugiej techniki, pomimo tego, że dokument XSLT ma znacznie większą objętość, to korzystając z edytora, takiego jak np. Visual Studio, znacznie trudniej o popełnienie błędu w samym dokumencie.

Inną bardzo dużą zaletą XSLT jest to, że korzystając ze stylów można utworzyć praktycznie dowolny dokument. Sam XSLT jest bardzo potężnym narzędziem, umożliwiającym skomplikowane operacji transformacji danych.

MCP x 8

2008-11-11T19:00:00.002+01:00

No i udało mi się pozytywnie zapoczątkować kolejny rozdział pt. "certyfikacja Microsoftu". Dzisiaj zdałem 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005. Pierwszy z trzech egzaminów wymaganych do uzyskania tytułu MCITP: Database Developer. Mam nadzieję, że czas będzie mi sprzyjał i do końca roku zamknę całą ścieżkę.

Tak na marginesie to był już 8 zdany egzamin.

SSMS 2008

2008-10-27T22:37:00.000+01:00

Jak to często bywa podczas udzielania pomocy innym, musiałem wygenerować trochę danych testowych. Jako, że porównywałem dwa rozwiązanie problemu, wykonałem kwerendy generując jednocześnie ich plany wykonania. I tutaj SSMS w wersji 2008 przychodzi z pomocą. W przypadku gdy wykryje brakujące indeksy, podpowiada aby je założyć.

Bardzo przyjemny dodatek, który czasami może ułatwić pracę.

Kolejność robi różnicę

2008-10-18T18:23:00.001+02:00

Pisząc kwerendy w T-SQL bardzo często decyzja na temat optymalizacji zapytań pozostawiana jest optymalizatorowi. Prawdopodobnie w 9 na 10 przypadków optymalizator wybierze odpowiedni plan wykonania, dzięki któremu zapytanie wykona się w zadowalającym czasie.

Tak jest prawdopodobnie w 9 na 10 przypadków. Jednakże od każdej reguły są wyjątki, z którymi optymalizator póki co sobie nie radzi.

Jednym z takich wyjątków jest kolejność predykatów. Zademonstruje to na przykładzie.
Na początek utworzę funkcje, która będzie miała zauważalny czas wykonania.

CREATE FUNCTION dbo.usn_waitfor()
RETURNS INT
AS
BEGIN
DECLARE @i INT
SET @i = 1
WHILE @i < 10000000
BEGIN
SET @i = @i + 1
END
RETURN 1
END
GO

Funkcja ta zwraca wartość 1, jednakże zanim to zrobi inkrementuje 10000000 razy wartość zmiennej @i. Naturalnie funkcja ta mogłaby wykonywać każdą inną czynność, np. odczytywać dane z kilku tabel czy wykonywać operacje arytmetyczne.

Wykorzystajmy teraz tę funkcję w zapytaniu. Na początek włączę jednakże trochę statystyk.

SET STATISTICS TIME ON

W celu demonstracji potrzebna będzie jeszcze jedna zmienna.

DECLARE @i INT
SET @i = 1

A teraz bardzo proste zapytanie.

SELECT 1
WHERE @i = 1 OR dbo.usn_waitfor() = 0

Wykonując to zapytanie wynik otrzymany jest natychmiast. Statystyki w tym przypadku nic nie wykazały.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Teraz to samo zapytanie, jednakże ze zmienioną kolejnością predykatów. Najpierw będzie sprawdzany wynik funkcji a później wartość zmiennej.

SELECT 1
WHERE dbo.usn_waitfor() = 0 OR @i = 1

Wynik zwrócony przez to zapytanie jest taki sam jak w poprzednim przypadku, natomiast czas jaki upłynął aby go otrzymać już nie. W moim przypadku na drugie zapytanie serwer potrzebował 18 sekund. Poniżej trochę statystyk.

SQL Server Execution Times:
CPU time = 12657 ms, elapsed time = 17921 ms.

Skąd więc taka różnica pomiędzy czasami wykonania obu zapytań? Otóż różnica te leży właśnie w kolejności sprawdzania predykatów. Aby nasz warunek był prawdziwy, i zapytanie zwróciło wynik, przynajmniej jeden predykatów musi być prawdziwy. W pierwszym przypadku najpierw sprawdzona została wartość zmiennej, i warunek @i = 1 był prawdziwy. Jako, że przynajmniej jeden z warunków musi być prawdą, drugi z warunków nie musiał być już sprawdzany.

W przypadku drugiego zapytania to funkcja, która potrzebuje trochę czasu aby została wykonana, brała udział w pierwszym warunku. Dopiero po jej wykonaniu sprawdzany był kolejny warunek, który zwraca prawdę, dzięki czemu otrzymany został wynik zapytania.

Jak widać warto czasami pomyśleć samemu i nie zdawać się na łaskę optymalizatora. Zmienienie kolejności predykatów może mieć kolosalne znaczenie dla czasu w jakim wykonane zostanie zapytanie.

Beta 71-433

2008-10-12T13:07:00.002+02:00

W mijającym tygodniu miałem okazję zdawać beta egzamin Microsoft® SQL Server® 2008, Database Development. Egzamin ten planowałem zdawać tydzień wcześniej, jednakże centrum egzaminacyjne, które wybrałem podczas rejestracji, skutecznie mi to uniemożliwiło. Ale czego można oczekiwać po firmie, która ma jedno stanowisko, na którym można zdawać egzaminy, a jednocześnie jest partnerem zarówno Prometrica jak i Pearson VUE. Czasami warto by było wziąć pod uwagę zadowolenie klientów, a nie tylko ilość potencjalnych złotówek wpływających na konto.

Ostatecznie do egzaminu przystępowałem w Wielkiej Brytanii. Jako, że wcześniej miałem już okazję zdawać kilka egzaminów Microsoftu, nic mnie nie zaskoczyło na 71-433. Jedyne czego żałuje to to, że nie spędziłem więcej czasu z SQL Server 2008. Dla mnie ten egzamin był bardziej rozgrzewką przed planowanymi 3 egzaminami z SQL Server 2005, a niżeli chęcią zdobycia nowych kwalifikacji. Mimo wszystko myślę, że było warto i także Microsoft będzie zadowolony z moich komentarzy do pytań. Teraz pozostaje tylko czekać kolejne 8 tygodni na wyniki.

Dynamiczne obracanie danych

2008-09-25T21:18:00.003+02:00

Początkowo miałem pisać dalej o optymalizacji w wykorzystaniem indeksów. Kontynuacja tematu rozpoczętego wcześniej. Dość długo się do tego zabierałem, a w między czasie pojawił się ciekawy problem na forum na stronie wss.pl.

Zanim jednak przejdę do meritum sprawy, naświetlę trochę temat, wykorzystując banalny przykład obracania danych w SQL 2005/2008 (PIVOT) oraz technikę która umożliwa osiągnięcie tego samego celu w poprzedniej wersji SQL Servera.

Na początek trochę danych.

USE TEMPDB
GO
IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1

CREATE TABLE #T1 (
CLUB NVARCHAR(40) NOT NULL,
ATTRIBUTE NVARCHAR(40) NOT NULL,
VALUE INT
)

ALTER TABLE #T1 WITH NOCHECK
ADD CONSTRAINT PK PRIMARY KEY CLUSTERED(CLUB, ATTRIBUTE)

INSERT INTO #T1
SELECT 'Diving Experience', '# members', 123 UNION ALL
SELECT 'Diving Experience', '# branches', 8 UNION ALL
SELECT 'Diving Experience', '# equipment', 100 UNION ALL
SELECT 'Global Diving', '# members', 125 UNION ALL
SELECT 'Global Diving', '# branches', 9 UNION ALL
SELECT 'My Scuba', '# members', 127 UNION ALL
SELECT 'My Scuba', '# equipment', 128 UNION ALL
SELECT 'Sharks Diving', '# members', 50 UNION ALL
SELECT 'Sharks Diving', '# equipment', 50 UNION ALL
SELECT 'Sharks Diving', '# branches', 2 UNION ALL
SELECT 'Sharks Diving', '# boats', 1

Tabela #T1 zawiera dane w następującej formie:

CLUB ATTRIBUTE VALUE
----------------- ----------- -----------
Diving Experience # branches 8
Diving Experience # equipment 100
Diving Experience # members 123
Global Diving # branches 9
Global Diving # members 125
My Scuba # equipment 128
My Scuba # members 127
Sharks Diving # boats 1
Sharks Diving # branches 2
Sharks Diving # equipment 50
Sharks Diving # members 50

Naszym celem jest przekształcenia tak danych aby każdy z atrybutów był osobą kolumną. Aby osiągnąć zamierzony cel musimy obrócić dane wokół osi, którą będzie kolumna atrybut.

Od SQL Servera w wersji 2005 mamy do dyspozycji klauzulę PIVOT.

Jako kolumny w zapytaniu, podajemy klub dla którego chcemy otrzymać nasze dane, oraz wszystkie dostępne wartości parametrów, a raczej te które nas interesują. Te same wartości parametrów muszą także występować w dalszej części zapytania.

PIVOT wykorzystuje także funkcję agregującą. W tym poniższym przykładzie będzie to MAX.

Wykonując zapytanie:

SELECT
CLUB,
ISNULL([# MEMBERS], 0) AS MEMBERS,
ISNULL([# BRANCHES], 0) AS BRANCHES,
ISNULL([# EQUIPMENT], 0) AS EQUIPMENT,
ISNULL([# BOATS], 0) AS BOATS
FROM #T1
PIVOT (MAX(VALUE) FOR ATTRIBUTE IN ([# MEMBERS], [# BRANCHES], [# EQUIPMENT], [# BOATS])) AS P

Otrzymamy następujący wynik:

CLUB MEMBERS BRANCHES EQUIPMENT BOATS
----------------- ----------- ----------- ----------- -----
Diving Experience 123 8 100 0
Global Diving 125 9 0 0
My Scuba 127 0 128 0
Sharks Diving 50 2 50 1

Każdy z atrybutów stał się teraz osobą kolumną. Teraz w razie potrzeby dane mogą być dalej przetwarzane. Wykorzystanie tabeli w postaci otwartego schematu, gdzie mamy atrybuty i ich wartości w jednym wierszu jest bardzo przydatne w przypadku gdy często zmienia się schemat danych, lub gdy też różne obiekty mają różne wartości parametrów. W SQL Server 2005/2008 ten sam cel można osiągnąć poprzez przechowywanie atrybutów obiektu w postaci XML. Jednakże to temat na inną dyskusję.

W SQL Server 2000 nie mamy do dyspozycji klauzuli PIVOT, jednakże osiągnięcie tego samego celu nie jest bardzo skomplikowane.

Poniższe zapytanie zwróci nam ten sam zbiór wyników

SELECT
CLUB,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# MEMBERS' THEN VALUE END),0) AS MEMBERS,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# BRANCHES' THEN VALUE END),0) AS BRANCHES,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# EQUIPMENT' THEN VALUE END),0) AS EQUIPMENT,
ISNULL(MAX(CASE WHEN ATTRIBUTE = '# BOATS' THEN VALUE END),0) AS BOATS
FROM #T1
GROUP BY CLUB
ORDER BY CLUB

Warto zwrócić tutaj uwagę na fakt, że w przypadku PIVOT grupowanie elementów jest ukryte. Jeżeli spojrzymy co robi optymalizator to zauważymy, że plan wykonania w obu przypadkach jest jednakowy. Co więcej zapytanie kompatybilne z SQL Server 2000 ma nieznacznie mniejszy koszt.

Może trochę nudnawe i przydługie te wprowadzenie do tematu, ale myślę, że warto.

Przejdźmy więc do meritum problemu. Jeden z użytkowników forum zapytał jak przekształcić dane z następującej postaci:

a 123
a 124
a 64
b 125
b 126
c 127
c 128
d 129
d 130
d 131

do

a 123 124 64
b 125 126
c 127 128
d 129 130 131

Na pierwszy rzut oka mogłoby to wyglądać na prosty przykład obracania danych. Jednakże na samym początku pojawią się pierwszy problem. Która kolumna będzie naszą osią. Pierwsza kolumna to unikalne elementy a druga to jakieś wartości. Brak w tym wszystkim jednej kolumny umożliwiającej nam obrócenie danych.

Dugi problem to ilość kolumn w wyniku. Z powyższego przykładu danych nie można wywnioskować ilości docelowych kolumn. Jedną z wad PIVOT jest to, że z góry musimy znać wszystkie możliwe kolumny. PIVOT nie pozwala nam tworzyć ich dynamicznie.

Na początek utwórzmy zbiór danych.

USE TEMPDB
GO

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1

CREATE TABLE #T1 (
ID CHAR(1),
VAL INT
)

INSERT INTO #T1
SELECT 'a', 123 UNION ALL
SELECT 'a', 124 UNION ALL
SELECT 'a', 64 UNION ALL
SELECT 'b', 125 UNION ALL
SELECT 'b', 126 UNION ALL
SELECT 'c', 127 UNION ALL
SELECT 'c', 128 UNION ALL
SELECT 'd', 129 UNION ALL
SELECT 'd', 130 UNION ALL
SELECT 'd', 131

Na początek musimy rozwiązać problem kolumny, wokół której będziemy obracać nasze dane.

W tym celu z pomocą przychodzi nowa w SQL 2005 funkcja ROW_NUMBER.

IF OBJECT_ID('tempdb..#T2') IS NOT NULL
DROP TABLE #T2

;WITH CTE AS (
SELECT
ID,
VAL,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN,
'ATTR' + CONVERT(VARCHAR(2),ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)) AS ATTR
FROM #T1
)

Partycjonujemy nasze dane, dzięki czemu uzyskujemy maksymalną liczbę różnych parametrów. Tworzymy też dodatkową kolumnę, ATTR, która będzie naszą osią. Unikalne parametry tworzymy korzystając ponownie z ROW_NUMBER, tym razem doklejając uzyskaną wartość do attr.

Tak zmodyfikowane dane wstawiamy do tabeli tymczasowej.

SELECT * INTO #T2 FROM CTE

Zawartość naszej tabeli przedstawia się w następujący sposób.

ID VAL RN ATTR
--- ---- --- ------
a 123 1 ATTR1
a 124 2 ATTR2
a 64 3 ATTR3
b 125 1 ATTR1
b 126 2 ATTR2
c 127 1 ATTR1
c 128 2 ATTR2
d 129 1 ATTR1
d 130 2 ATTR2
d 131 3 ATTR3

Mając teraz kolumnę ATTR możemy przejść do obracania danych. Od razu napotykamy na nasz drugi problem. Ile tak naprawdę będziemy mieli różnych rodzajów atrybutów? W powyższym przykładzie jest to 3 ale może równie dobrze być ich znacznie więcej. Tak więc zwykły PIVOT nam tutaj na niewiele się zda. Musimy wykonać go dynamicznie, a raczej skonstruować zapytanie dynamicznie.

Na początek musimy zdobyć maksymalną ilość atrybutów.

W tym celu deklarujemy zmienną i pobieramy maksymalną wartość z kolumny RN

DECLARE @MAXATTR AS INT
SELECT @MAXATTR = MAX(RN) FROM #T2

Deklarujemy także kolejnych kilka zmiennych, które będą wykorzystane przy tworzeniu dynamicznego zapytania.

DECLARE @I AS int
DECLARE @SQL AS NVARCHAR(200)
DECLARE @PIVOTSQL AS NVARCHAR(200)
DECLARE @TABLESQL AS NVARCHAR(400)
DECLARE @FINALSQL AS NVARCHAR(400)

Mając to wszystko możemy wygenerować nasz dynamiczny kod.

WHILE @I <= @MAXATTR
BEGIN

SET @SQL = @SQL + ', attr' + CONVERT(NVARCHAR(4),@I)
SET @PIVOTSQL = @PIVOTSQL + '[ATTR' + CONVERT(NVARCHAR(2),@I) + '],'
SET @TABLESQL = @TABLESQL + 'ALTER TABLE #T3 ADD ATTR' + CONVERT(NVARCHAR(4),@I) + ' INT '
SET @FINALSQL = @FINALSQL + ', MAX(ISNULL(ATTR' + CONVERT(NVARCHAR(4),@I) + ',0)) AS COLUMN' + CONVERT(NVARCHAR(4),@I)
SET @I = @I + 1

END

Kilka słów o tym co dzieję się w pętli, która wykonywana jest tyle razy ile mamy unikalnych atrybutów.

Zmienna SQL przechowuje pierwszą część naszego zapytania, tzn wszystkie kolumny, które będą występowały po SELECT.

Zmienna PIVOTSQL przechowuje wszystkie wartości atrybutów, które docelowo będą naszymi kolumnami.

Zmienna TABLESQL, tworzy kod który będzie modyfikował jedną z tabel docelowych. Kilka słów więcej o niej w dalszej części.

Zmienna FINALSQL przechowuje zapytanie, które zwróci nam ostateczny wynik. Zapytanie utworzone w tej zmiennej grupuje nasze dane.

W pętli tej jednakże tworzymy tylko fragmenty zapytań, z wyjątkiem TABLESQL, które po zakończeniu pętli jest już kompletne.

Tworzymy teraz nasze ostateczne zapytania.

SET @PIVOTSQL = STUFF(@PIVOTSQL,LEN(@PIVOTSQL),1,'')
SET @SQL = @SQL + ' FROM #T2 PIVOT(MAX(VAL) FOR ATTR IN(' + @PIVOTSQL + ')) AS P '
SET @SQL = 'INSERT INTO #T3 SELECT * FROM (' + @SQL + ') AS DSQL'
SET @FINALSQL = @FINALSQL + ' FROM #T3 GROUP BY ID ORDER BY ID'

Tworzymy także kolejną tabelę tymczasową, która będzie przechowywać wynik obracania danych.

IF OBJECT_ID('tempdb..#T3') IS NOT NULL
DROP TABLE #T3

CREATE TABLE #T3 (
ID CHAR(1)
)

Tabela ta na początek posiada tylko jedną kolumnę, która zawiera unikalne elementy. W momencie kiedy ją tworzymy nie wiemy ile będziemy mieli docelowo różnych atrybutów (kolumn). Kolumny te dodamy po uruchomianiu dynamicznie wygenerowanego kodu. W każdej iteracji naszej pętli dodawana była jedna kolumna odpowiadająca wartości naszego atrybutu.

Teraz wykonujemy nasze dynamicznie utworzone zapytania.

EXEC SP_EXECUTESQL @TABLESQL

EXEC SP_EXECUTESQL @SQL

EXEC SP_EXECUTESQL @FINALSQL

Ostatnie z nich zwraca nam nasz pożądany wynik.

ID COLUMN1 COLUMN2 COLUMN3
---- ----------- ----------- -------
a 123 124 64
b 125 126 0
c 127 128 0
d 129 130 131

Jak widać nie ograniczenie jakie niesie za sobą PIVOT idzie łatwo ominąć generując kod dynamicznie.

PS. Po napisaniu oryginalnego rozwiązania postanowiłem je jeszcze trochę zoptymalizować. Oryginalny kod wymagał użycia trzech tabel tymczasowych, a jedna z nich miała dynamicznie dodawane kolumny. Wszystko to za sprawą tego, że tabela #T2 poza danymi wynikowymi posiadała także kolumnę RN, która przechowywała maksymalne wartości atrybutów dla każdej z unikalnych grup. W wyniku przekształcania danych dużo wierszy posiadało wartości NULL. Eliminowane one były dopiero w trzeciej tabeli tymczasowej.

Poniżej cały kod, delikatnie zmodyfikowany. Wynik oczywiście taki sam.

USE TEMPDB
GO

IF OBJECT_ID('tempdb..#T1') IS NOT NULL
DROP TABLE #T1

CREATE TABLE #T1 (
ID CHAR(1),
VAL INT
)

INSERT INTO #T1
SELECT 'a', 123 UNION ALL
SELECT 'a', 124 UNION ALL
SELECT 'a', 64 UNION ALL
SELECT 'b', 125 UNION ALL
SELECT 'b', 126 UNION ALL
SELECT 'c', 127 UNION ALL
SELECT 'c', 128 UNION ALL
SELECT 'd', 129 UNION ALL
SELECT 'd', 130 UNION ALL
SELECT 'd', 131
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
DROP TABLE #T2
;WITH CTE AS (
SELECT
ID,
VAL,
'ATTR' + CONVERT(VARCHAR(2),ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID)) AS ATTR
FROM #T1
)

SELECT * INTO #T2 FROM CTE

DECLARE @MAXATTR AS INT
SELECT @MAXATTR = MAX(RN) FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN
FROM #T1
) AS MAXATTR
DECLARE @I AS int
DECLARE @SQL AS NVARCHAR(400)
DECLARE @PIVOTSQL AS NVARCHAR(200)
SET @SQL = 'SELECT ID '
SET @PIVOTSQL = ''
SET @I = 1
WHILE @I <= @MAXATTR
BEGIN
SET @SQL = @SQL + ', ISNULL(attr' + CONVERT(NVARCHAR(4),@I) + ', 0) AS COLUMN' + CONVERT(NVARCHAR(4),@I)
SET @PIVOTSQL = @PIVOTSQL + '[ATTR' + CONVERT(NVARCHAR(2),@I) + '],'
SET @I = @I + 1
END

SET @PIVOTSQL = STUFF(@PIVOTSQL,LEN(@PIVOTSQL),1,'')
SET @SQL = @SQL + ' FROM #T2 PIVOT(MAX(VAL) FOR ATTR IN(' + @PIVOTSQL + ')) AS P '
EXEC SP_EXECUTESQL @SQL
DROP TABLE #T1
DROP TABLE #T2

Optymalizacja cz.1 – Indeksy

2008-09-03T00:28:00.014+02:00

Wstęp
Pierwszą część dotyczącą optymalizacji baz danych rozpocznę od indeksów. Indeks to uporządkowany zbiór danych. Przykładem indeksu jest książka telefoniczna, która zawiera numery telefonów uporządkowana wg nazwiska czy nazwy firmy.

Korzystając z odpowiednich indeksów można w znaczący sposób skrócić czas wykonywania zapytania do bazy danych. Nic jednak za darmo. Indeksy mają swój koszt i muszą one być odpowiednio przemyślane. Kosztem indeksu jest miejsce na dysku oraz czas związany z operacjami modyfikacji danych (INSERT, UPDATE, DELETE).

Tak jak każda baza danych jest inna tak też nie ma uniwersalnej reguły dotyczącej indeksów. Mając na uwadze fakt, że w przypadku dodania, usunięcia lub modyfikacji danych poza aktualizacją tabeli aktualizowane są także indeksy, byłoby nie rozsądnym dodawanie zbyt wielu indeksów w bazie danych, która jest często aktualizowana. Natomiast w przypadku bazy danych, która jest tylko odczytywana i służy do produkcji raportów większa liczba indeksów niewątpliwie będzie korzystna.

A jak to wygląda w praktyce...
Nie zanudzając teorią przyjdźmy do praktycznego zastosowania. We wszystkich przykładach wykorzystywać będę bazę danych Northwind.
Aby pokazać korzyści płynące z indeksów, usunąłem z tabel istniejące już wcześniej indeksy.
W przypadku braku jakiegokolwiek indeksu skanowana musi być cała tabela.

select orderid, customerid, orderdate from orders
where OrderDate > '19970101'

Powyższe zapytanie generuje następujący plan zapytania

Zapytanie to także wymagało 20 logicznych operacji odczytu.

Utwórzmy więc indeks, który pokryje wszystkie kolumny biorące udział w zapytaniu. W zależności od tego czy baza danych jest na SQL Server 2000 czy na SQL Server 2005 mamy do dyspozycji dwa typy indeksów. W przypadku SQL Server 2000, jeżeli byśmy chcieli utworzyć indeks, który pokryłby wszystkie kolumny biorące udział w zapytaniu, wszystkie te kolumny muszą być kluczami indeksu.

Poniższy kod utworzy nam pożądany indeks.

create index idx_ncl_od on orders(orderdate, orderid, customerid)

Wadą tego indeksu jest to, że w przypadku aktualizacji wszystkie klucze muszą być odpowiednio aktualizowane.

Znacznie bardziej optymalne rozwiązanie umożliwia nam SQL Server 2005, gdzie kluczem indeksu będzie kolumna, która używana jest jako predykat, natomiast pozostałe kolumny dodamy w klauzuli include. W przypadku modyfikacji danych tylko jedna kolumna będzie powodowała dodatkowy koszt. Naturalnie wielkość indeksu na dysku będzie taka sama w przypadku obu rozwiązań. Poniżej zapytanie, które utworzy nam indeks, który pokryje wszystkie kolumny w zapytaniu.

create index idx_ncl_in_od on orders(orderdate) include(orderid, customerid)

Wywołując ponownie nasze zapytanie otrzymujemy poniższy plan wykonania.

Jak widać w tym przypadku nie skanowaliśmy już całej tabeli, a tylko część indeksu.

W przypadku pierwszego zapytania bez indeksu koniecznych było 20 logicznych operacji odczytu. W drugim przypadku było ich tylko 5.

Z uwagi na fakt, że indeks ten nie będzie już nam potrzebny w kolejnych przykładach zostanie on teraz usunięty.

drop index idx_ncl_in_od on orders.

Dotychczas korzystaliśmy tylko z indeksu nieklastrowego. Indeks nieklastrowy charakteryzuje się tym, że posiada on dane tylko z tych kolumn, które są jego kluczami lub kolumn dołączonych za pomocą instrukcji include.

Kolejnym indeksem jaki założymy na naszej tabeli będzie indeks klastrowy. Indeks klastrowy poza danymi pochodzącymi z kolumny, która jest kluczem posiada wszystkie pozostałe dane z tabeli. Indeks klastrowy to tak na prawdę dane. Dane w takim indeksie będą posortowane wg. kolumny klucza.

Poniższe zapytanie utworzy nam indeks klastrowy oraz klucz podstawowy na kolumnie OrderID.

ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED (OrderID)

Wykonując ponownie nasze zapytanie otrzymamy poniższy plan wykonania.

Jak widać w tym przypadku nie skanujemy już zawartości tabeli a indeks klastrowy, który zawiera wszystkie dane posortowane wg. kolumny OrderID. Przed założeniem tego typu indeksu należy się poważnie zastanowić, która z kolumn będzie jego kluczem. W tabeli możemy mieć tylko jeden indeks klastrowy. SQL Server pozwala nam ponadto utworzyć 249 indeksów nieklastrowych.

W przypadku gdy założymy na tabeli także indeks nieklastrowy, nieunikalny, klucz indeksu klastrowego będzie dodatkowym kluczem indeksu nieklastrowego. Niezależnie od tego czy czy indeks nieklastrowy jest unikalny czy nie, kolumna klucza klastrowego będzie także częścią tego indeksu. Ma to związek z faktem z tym, że w przypadku kiedy w tabeli znajduje się indeks klastrowy indeks nieklastrowy posiada do niego wskaźnik. W przypadku braku indeksu klastrowego indeks nieklastrowy zawiera wskaźnik do wiersza i pliku gdzie znajdują się dane.

Aby to zademonstrować założę teraz na tabeli indeks nieklastrowy, którego kluczem będzie kolumna OrderDate.

CREATE INDEX OrderDate ON Orders(OrderDate)

Wykonajmy teraz poniższe zapytanie i przeanalizujmy plan wykonania.

select orderid, orderdate from orders
where OrderDate > '19970101'

Mimo tego, że zakładając indeks nieklastrowy, jako klucz podaliśmy tylko kolumnę OrderDate nasze zapytanie nie wymagało skanowania całej tabeli, a raczej indeksu klastrowego. Zamiast tego wykonany został częściowy skan tabeli poprzedzony odnalezieniem pierwszego wiersza spełniającego predykat.

Dotychczasowe zapytania wykorzystywały tylko jeden z indeksów, choć tak na prawdę w ostatnim przypadku gdybyśmy nie mieli indeksu klastrowego optymalizator wybrałby skan całej tabeli, lub w przypadku wysokiej selektywności zapytania częściowy skan indeksu nieklastrowego w połączeniu z przeszukaniem wszystkich wskaźników do wierszy i plików zawierających dane.

W ten sposób doszedłem do selektywności zapytań. Czym więc jest selektywność zapytań i jak ona wpływa na wydajność? Selektywność jest to iloraz oczekiwanej liczby wierszy do wszystkich jakie znajdują się w tabeli.

Wysoka selektywność oznacza relatywnie małą ilość wyników, natomiast niska selektywność dużą ilość wierszy w wyniku zapytania.

Poniższe zapytanie cechuje się wysoką selektywnością.

select orderid, customerid from orders
where OrderDate > '19980505'

Rezultatem tego zapytania będą tylko 4 wiersze, co stanowi 0,48% wszystkich wierszy w tabeli.

Przyjrzyjmy się teraz planowi wykonania.

Z uwagi na fakt, że nasz indeks nieklastrowy nie pokrywa wszystkich kolumn zapytania optymalizator zdecydował, najpierw o wyszukaniu rekordów spełniających nasz predykat, a później dla każdego ze znalezionych wierszy wykonał przeszukanie indeksu klastrowego w celu pobrania danych z kolumny CustomerID. Wyszukanie danych z kolumny CustomerID jest bardzo kosztowną operacją. Dla każdego ze znalezionych wierszy przeprowadzony musi być skan indeksu. Statystycznie indeks skanowany jest w 50%.

Zmienimy teraz nieznacznie zakres danych

select orderid, customerid from orders
where OrderDate > '19980504'

W wyniku tego zapytania otrzymamy 8 wierszy co stanowi 0,96% wszystkich wierszy w tabeli. Przyjrzyjmy się ponownie planowi wykonania zapytania.

Jak wspomniałem wcześniej przeszukanie całego indeksu klastrowego, lub też tabeli w przypadku jego braku, dla każdego wiersza jest bardzo kosztowną operacją. Dlatego też optymalizator zdecydował, że bardziej korzystne będzie zeskanowanie tylko raz całego indeksu klastrowego.
Na dowód tego wykonam kolejne zapytanie, które wykorzysta indeks nieklastrowy.

select orderid, customerid from orders with (index = 3)
where OrderDate > '19980504'

W wyniku tego zapytania otrzymamy poniższy, znajomy już nam plan.

W moim przypadku koszt tego zapytania to 0,023443, podczas gdy poprzedniego, w którym optymalizator sam wybrał najlepsze rozwiązanie to 0,0182691.

Zakończenie
Indeksy mogą w znacznym stopniu zoptymalizować zapytania do bazy danych. W powyższych zapytaniach nie było może to tak bardzo widoczne, gdyż głównie koncentrowałem się na tym by przedstawić jak działają indeksy i jak optymalizator je wykorzystuje. Temat indeksów poruszę ponownie w przyszłości, wtedy też o wiele bardziej widoczne będą korzyści z nich płynące a także koszt, czego w tym artykule nie poruszyłem.

Usuwanie duplikatów

2008-08-04T08:59:00.005+02:00

Czasami zdarza się tak, że w naszej bazie dancyh występują duplikaty. Nie powinno się to zdarzyc jeżeli są dobre klucze podstawowe w tabelach. Jedkaże kiedy ich nie mamy, lub kiedy dane są importowane z pliku płaskiego, który nie ma żadnej kontroli poprawności danych, musimy sie pozbyć tych nadmiernych informacji.

Istnieje kilka sposobów na usuwanie duplikatów. W zależności od tego czy uważamy za duplikat wiersz, który ma wszystkie wartości w kolumnach takie same jak inny wiersz w tabeli czy duplikatem będzie dla nas wiersz którego jedna kolumna ma taką samą wartość w innym wierszu, trzeba rozważyć różne techiniki usuwania nadmiernych danych.

Na początek stworzę przykładową tabelę, którą wypełnię danymi.

USE TEMPDB
GO

IF OBJECT_ID('tempdb..#KLIENCI') IS NOT NULL
DROP TABLE #KLIENCI

CREATE TABLE #KLIENCI (
ID INT,
NAZWA VARCHAR(50),
TELEFON VARCHAR(9)
)

INSERT INTO #KLIENCI (ID, NAZWA, TELEFON)
SELECT '1', 'CONTOSO LTD', '123456789'
UNION ALL
SELECT '2', 'MERCURY LTD', '987456321'
UNION ALL
SELECT '3', 'NOWY SWIAT', '564789321'
UNION ALL
SELECT '1', 'CONTOSO LTD', '123456789'
UNION ALL
SELECT '2', 'MERCURY LTD', '111111111'

Pobierzmy teraz zawartość tabeli.

SELECT * FROM #KLIENCI

ID NAZWA TELEFON
-------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 987456321
3 NOWY SWIAT 564789321
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111

Jak widać z powyższego listingu mamy dwa rodzaje duplikatów. Pierwszy dla klienta Contoso Ltd ma wszystkie wartości w kolumnach w obu wierszah identyczne. Drugi typ to klient Mercury Ltd (id = 2), który ma dwie różne wartości w kolumnie telefon.

Pierwszy typ duplikatów możemy usunąć korzystając z tabeli tymczasowej, którą wypełniamy unikalnymi wierszami. Następnie usuwamy wszystkie dane z naszej orginalnej tabeli a później kopiujemy zawartość tabeli tymczasowej do naszej tabeli pierwotnej.

SELECT DISTINCT * INTO #DUPLIKATY FROM #KLIENCI

TRUNCATE TABLE #KLIENCI

INSERT INTO #KLIENCI(ID, NAZWA, TELEFON) SELECT ID, NAZWA, TELEFON FROM #DUPLIKATY
DROP TABLE #DUPLIKATY

Pobierając zawartość naszej tabeli otrzymamy teraz następujący rezultat:

ID NAZWA TELEFON
----------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111
2 MERCURY LTD 987456321
3 NOWY SWIAT 564789321

Jak widać pozbyliśmy się jednego wiersza dla klienta Contoso Ltd. Technika ta będzie działać zarówno na SQL Server 2000 jak i nowszych wersjach.

Znacznie bardziej efektywną metodą i pozwalającą nam usuwać duplikaty, gdzie tylko kilka kolumn ma takie same wartości dla różnych wierszy jest technika z wykorzystaniem nowej funkcji, dostępnej od SQL Server 2005, ROW_NUMBER oraz CTE.

Korzystając z funkcji ROW_NUMBER tworzymy numery kolejnych wierszy, jednocześnie partycjonując nasze wyniki. W naszym przypadku uznajemy za duplikat wiersz który ma taką samą wartość identyfikatora klienta w dwóch lub więcej wierszach. Skorzystamy więc z funkcji ROW_NUMBER oraz bedziemy partycjonować nasze wyniki w oparciu o kolumnę id.

WITH DUPLIKATY AS (
SELECT ID, NAZWA, TELEFON, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN FROM #KLIENCI
)

Jeżeli byśmy wyświetlili zawartość naszego CTE, otrzymalibyśmy następujące rezultaty

ID NAZWA TELEFON RN
----------- ------------ --------- --
1 CONTOSO LTD 123456789 1
2 MERCURY LTD 111111111 1
2 MERCURY LTD 987456321 2
3 NOWY SWIAT 564789321 1

Teraz jedyne co musimy zrobić to usunąć nadmiarowe wiersze.

DELETE DUPLIKATY WHERE RN > 1

Pobieramy zawartość naszej tabeli

SELECT * FROM #KLIENCI

ID NAZWA TELEFON
----------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111
3 NOWY SWIAT 564789321

Teraz mając usunięte duplikaty, możemy założyć klucz podstawowy w naszej tabeli.

Importowanie danych z XML do bazy danych.

2008-08-03T10:29:00.004+02:00

Wstęp
Aplikacje biznesowe coraz częściej wykorzystują dane pochodzących z róznych źródeł. Coraz częsniej dane te dostarczane są w formacie XML. SQL Server 2005 oferuje wiele funkcji, które umożliwiają przetwarzanie danych w formacie XML.
SQL Server 2005 potrafi przechowywać dane jako dokumenty XML, lub też przetwarzać je i przechowywac zachowując model relacyjnej bazy danych.

Praktyczne zastosowanie
Nie zanudzając dalej teorią pora przejść do konkretnego zastowanie XML w powiązaniu z SQL Server 2005. Poniższy przykład pokaże z jaką łatwością dane mogą zostać zaimportowane z pliku, lub łancucha znaków w formacie XML do tabeli bazy danych.
Poniższy przykład jako dokument XMl będzie wykorzystywał tabelę kursów walut publikowaną na stronach Narodowego Banku Polskiego.
Fragment dokumentu XML, z którego będę korzystał w dalszej częsci tego artykułu:

<tabela_kursow typ="A">
<numer_tabeli>150/A/NBP/2008</numer_tabeli>
<data_publikacji>2008-08-01</data_publikacji>
<pozycja>
<nazwa_waluty>dolar australijski</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>AUD</kod_waluty>
<kurs_sredni>1,9339</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar Hongkongu</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>HKD</kod_waluty>
<kurs_sredni>0,2649</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar kanadyjski</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>CAD</kod_waluty>
<kurs_sredni>2,0138</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar nowozelandzki</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>NZD</kod_waluty>
<kurs_sredni>1,5061</kurs_sredni>
</pozycja>
</tabela_kursow>

Potrzebne będą jeszcze tabele które będą przechowywać dane.

USE tempdb
GO
CREATE TABLE dbo.WALUTY (
KOD_WALUTY CHAR(3) PRIMARY KEY NOT NULL,
NAZWA_WALUTY VARCHAR(100) NOT NULL
)

CREATE TABLE dbo.KURSY_WALUT (
KOD_WALUTY CHAR(3) NOT NULL,
KURS_SREDNI MONEY NOT NULL,
DATA DATETIME NOT NULL,
PRZELICZNIK SMALLINT NOT NULL,
PRIMARY KEY (KOD_WALUTY, DATA),
FOREIGN KEY (KOD_WALUTY) REFERENCES dbo.WALUTY (KOD_WALUTY)
)

Musimy jeszcze zadeklarować zmienną, która będzie przechowywała nasz dokument XML. Zmienna ta jest typu XML.

DECLARE @XMLDATA AS XML

Aby załadować zawartość pliku XMl do naszej zmeinnej skorzystamy z funkcji OPENROWSET. W SQL Server 2005 funkcja OPENROWSET umożliwia czytanie zawartości pliku bez konieczności korzystania z tabeli. Dzięki korzystamy tylko z jednej istrukcji SELECT.

SELECT @XMLDATA = X FROM OPENROWSET(BULK 'C:\NBP.xml', SINGLE_BLOB) AS IMPORT(X).

Mając utworzone tabele oraz zadeklarowaną zmienna przechowującą zawartość pliku XML możemy przejść do przetwarzania tych danych. W tym celu wykorzystamy procedure, która jako parametr będzie przyjmowała naszą zmienną XML, a raczej dokument XML, który załadowaliśmy wcześniej do zmiennej.
Procedura przyjmuje dokument XML jako swój parametr a nie scieżkę do pliku. Jest to o tyle istotne, że procedura ta może być wykorzystana w dowolnej aplikacji, która będzie przekazyła dokument XML jako łańcuch znaków. Aplikacja taka może np codziennie pobierać dane bezpośrednio ze stron NBP, i przekazywać zawarośc dokumentu do procedury, bez konieczności materializowania pliku na dysku. O tym jak łatwo można zamienić Dataset w aplikacji .NET do łańcucha znaków kilka słów poświęcę na koniec tego dokumentu.
Nazwijmy naszą procedurę IMPORT_NBP

CREATE PROCEDURE IMPORT_NBP(
@XMLDATA XML
)
AS

Na początek musimy zadeklarować zmienną oraz tymczasową tabelę, które pomogą nam w przetwarzaniu danych.

DECLARE @DATA_PUBLIKACJI AS DATETIME

CREATE TABLE #KURSY_WALUT (
NAZWA_WALUTY NVARCHAR(100),
PRZELICZNIK SMALLINT,
KOD_WALUTY CHAR(3),
KURS_SREDNI MONEY
)

Data publikacji występuje w naszym dokumencie XMl tylko raz. Aby kod był bardziej czytelny będziemy tę wartość przechowywać w zmiennej DATA_PUBLIKACJI.
Tymczasowa tabela KURSY_WALUT będzie przechowywała tymczasowo zawartość naszych elementów XML.
W celu przypisania wartości zmeinnej DATA_PUBLIKACJI skorzystamy z funkcji value.

SELECT @DATA_PUBLIKACJI = X.value('(/tabela_kursow/data_publikacji/text())[1]', 'DATETIME') FROM @XMLDATA.nodes('.') AS IMPORT(X)

Proszę zwrócić uwagę na fakt, że funkcja value jak i wszystkiego pozostałe fukcje oraz elementy XML są pisane małymi literami. XML rozróżnia wielkość liter. Funkcja text() odczytuje zawartość elementu data_publikacji. Proszę zwrócić uwagę na index elementu [1]. Oznacza to, że funkcja value pobiera pierwszy element data_publikacji w dokumencie XML. Indeksowanie elementów rozpoczyna się od wartości 1, inaczej jak ma to miejsce w .NET Framework, gdzie indeksowanie rozpoczyna się domyślnie od 0. Jeżeli w naszym dokumecie występowałoby więcej elementów data_publikacji moglibyśmy użyć innej wartości indeksu. Zawartość naszego elementu data_publikacji odczytujemy jako DATETIME. Uzywając funkcji value musimy podać jakiego typu jest nasz element.
Mając już datę publikacji przejdźmy do odczytywanie kolejnych danych. Teoretycznie moglibyśmy to zrobić w ten sam sposób jak odczytaliśmy datę, zmieniając tylko element XML z /tabela_kursow/data_publikacji na /tabela_kursow/pozycja/nazwa_valuty. Z praktycznego punktu widzenia jest to jednak zadanie bardzo karkołomne gdyz najpierw musielibyśmy znać ilość elementów w naszym dokumencie XML a później pobierać każdy z nich z osobna.
Aby sprawić, żeby nasz kod był bardziej uniwersalny skorzystamy z dwóch dodatkowych funkcji, służących do przetwarzania dokumentów XML. Będą to funkcje query i nodes.
Poniższe zapytanie zwróci nam wszystkie elementy pozycja z osobna.

SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)

Teraz korzystając z kolejnego, zewnętrznego zapytania możemy pobrać wartości każdego z elementów naszego dokumentu XML. Wszystkie te dane wstawimy do naszej tabeli tymczasowej KURSY_WALUT.

INSERT INTO #KURSY_WALUT (NAZWA_WALUTY, PRZELICZNIK, KOD_WALUTY, KURS_SREDNI) (
SELECT
NODE.value('(/pozycja/nazwa_waluty/text())[1]', 'NVARCHAR(100)'),
NODE.value('(/pozycja/przelicznik/text())[1]', 'SMALLINT'),
NODE.value('(/pozycja/kod_waluty/text())[1]', 'CHAR(3)'),
REPLACE(NODE.value('(/pozycja/kurs_sredni/text())[1]', 'NVARCHAR(15)'), ',', '.')
FROM (
SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)
) AS ELEMENTY
)

W tym miejscu poświęcę kilka słów średniemu kursowi wymiany. Nasz dokument XML przechowuje tę wartość formie liczby zmienno-pozcyjnej, używając jako separatora dziesiętnego przecinka. SQL Server 2005 domyślnie używa jako separatora kropki. Gdybyśmy odczytali wartośc kurs_sredni jako typ MONEY w naszej tabeli znaleźlibyśmy nie 1,9339 a 19339.00. Dlatego też odczytujemy tę wartość jako typ NVARCHAR, a funkcja REPLACE zatępuje znaki [,] na [.].
W ten sposób mamy już nasze dane z pliku XML w tabeli tymczasowej.
Na koniec pozostaje nam zapisanie wszystkich dancyh do naszych tabel docelowych.
Najpierw zapisujemy dane do tabeli WALUTY, sprawdzając przy okazji czy podane waluta już nie istnieje w naszej bazie danych.

INSERT INTO dbo.WALUTY (KOD_WALUTY, NAZWA_WALUTY) (
SELECT KOD_WALUTY, NAZWA_WALUTY FROM #KURSY_WALUT
WHERE KOD_WALUTY NOT IN (SELECT KOD_WALUTY FROM dbo.WALUTY)
)

Mając uzupełnioną tabelę z walutami możemy zapisać kursy walut, przy okazji sprawdzając czy kurs waluty dla danego dnia już nie istnieje.

INSERT INTO dbo.KURSY_WALUT (KOD_WALUTY, KURS_SREDNI, DATA, PRZELICZNIK) (
SELECT KOD_WALUTY, KURS_SREDNI, @DATA_PUBLIKACJI, PRZELICZNIK FROM #KURSY_WALUT AS KW
WHERE NOT EXISTS (
SELECT KOD_WALUTY, DATA FROM KURSY_WALUT WHERE KOD_WALUTY = KW.KOD_WALUTY AND DATA = @DATA_PUBLIKACJI
)
)

Na koniec pozostaje nam usunięcie tabeli tymczasowej

DROP TABLE #KURSY_WALUT

A także ostatnie

GO

Pora teraz sprawidzić naszą procedurę.

DECLARE @XMLDATA AS XML
SELECT @XMLDATA = X FROM OPENROWSET(BULK 'C:\NBP.xml', SINGLE_BLOB) AS IMPORT(X)

EXEC IMPORT_NBP @XMLDATA

SELECT * FROM dbo.KURSY_WALUT
SELECT * FROM dbo.WALUTY

Jak już wspomniałem wcześniej procedura ta może zostać wykorzystana w aplikacji, która pobiera dane ze strony www, a później przekazuje cały dataset jako parametr w procedurze.
Zamiana całego dataset w .NET na łańcuch znaków w formacie XML to tylko jedna instrukcja:

Dataset1.GetXml

Podsumowanie
Jak widać na powyższym przykładzie w SQL Server 2005 bardzo łatwo możemy pzretwarzać dane w formacie XML. Dane nie muszą być przetwarzane w aplikacji, tylko wszystko przekazane zostaje na ‘barki’ serwera bazy danych.

Na koniec kompletny kod naszej procedury.

CREATE PROCEDURE IMPORT_NBP(
@XMLDATA XML
)
AS

DECLARE @DATA_PUBLIKACJI AS DATETIME

CREATE TABLE #KURSY_WALUT (
NAZWA_WALUTY NVARCHAR(100),
PRZELICZNIK SMALLINT,
KOD_WALUTY CHAR(3),
KURS_SREDNI MONEY
)

SELECT @DATA_PUBLIKACJI = X.value('(/tabela_kursow/data_publikacji/text())[1]', 'DATETIME') FROM @XMLDATA.nodes('.') AS IMPORT(X)

INSERT INTO #KURSY_WALUT (NAZWA_WALUTY, PRZELICZNIK, KOD_WALUTY, KURS_SREDNI) (
SELECT
NODE.value('(/pozycja/nazwa_waluty/text())[1]', 'NVARCHAR(100)'),
NODE.value('(/pozycja/przelicznik/text())[1]', 'SMALLINT'),
NODE.value('(/pozycja/kod_waluty/text())[1]', 'CHAR(3)'),
REPLACE(NODE.value('(/pozycja/kurs_sredni/text())[1]', 'NVARCHAR(15)'), ',', '.')
FROM (
SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)
) AS ELEMENTY
)

INSERT INTO dbo.WALUTY (KOD_WALUTY, NAZWA_WALUTY) (
SELECT KOD_WALUTY, NAZWA_WALUTY FROM #KURSY_WALUT
WHERE KOD_WALUTY NOT IN (SELECT KOD_WALUTY FROM dbo.WALUTY)
)

INSERT INTO dbo.KURSY_WALUT (KOD_WALUTY, KURS_SREDNI, DATA, PRZELICZNIK) (
SELECT KOD_WALUTY, KURS_SREDNI, @DATA_PUBLIKACJI, PRZELICZNIK FROM #KURSY_WALUT AS KW
WHERE NOT EXISTS (
SELECT KOD_WALUTY, DATA FROM KURSY_WALUT WHERE KOD_WALUTY = KW.KOD_WALUTY AND DATA = @DATA_PUBLIKACJI
)
)

DROP TABLE #KURSY_WALUT

GO