Category Archives: SQL Server

tSQLt – testowanie wyzwalaczy

O tSQLt pisałem już wiele razy. Dzisiaj chciałbym pokazać jak testować wyzwalacze (triggers). Załóżmy, że mamy następującą tabelę:


CREATE TABLE [dbo].[Articles](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Price] [int] NULL,
 CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Jest to prosta tabela z kluczem głównym oraz jedną kolumną (cena). Napiszmy wyzwalacz, w którym za każdym razem, gdy dodajemy nowy wiersz, cena jest podwajana i kolejny wiersz jest dodawany:

CREATE TRIGGER [dbo].[OnArticleInserted]  
ON [dbo].[Articles]  
AFTER INSERT
AS    
  INSERT INTO Articles (Price) (SELECT Price*2 from inserted)

Nasz test z kolei będzie wyglądać następująco:

ALTER PROCEDURE [sampleTests].[testTrigger]
AS
BEGIN
  --Assemble
  exec tSQLt.FakeTable 'Articles'
  exec tSQLt.ApplyTrigger 'Articles', 'OnArticleInserted'

  create table ExpectedValues
  (
     Price integer
  )

  INSERT INTO ExpectedValues (Price) Values (6),(12)
  
  --Act
  INSERT INTO Articles (Price) Values (6)
  
  --Assert
  EXEC tSQLt.AssertEqualsTable @Expected = N'ExpectedValues',  @Actual = N'Articles'
  
END

Zawsze chcemy odizolowywać tabele. Z tego względu używamy FakeTable na “Articles”. W naszym przypadku, wyzwalacz operuje tylko na tej tabeli więc to wystarczy. W praktyce izolujemy wszystkie tabele z danego wyzwalacza.
Następnie wywołujemy ApplyTrigger. Wiemy, że FakeTable powoduje stworzenie mock’a, zatem wszystkie wyzwalacze domyślnie będą odizolowane. Wywołanie ApplyTrigger umożliwia selektywną aktywację wyzwalaczy.

Dalsza część kodu już wygląda analogicznie do innych testów. Wykonujemy INSERT INTO na Article, co spowoduje uruchomienie naszego wyzwalacza. Na końcu sprawdzamy, czy nowy wiersz faktycznie został dodany.

Typ Geography w Dapper

W poprzednim wpisie zajęliśmy się typem Geography, który jest przydatny na operacjach związanych z współrzędnymi geograficznymi. Jak wiemy, zapytania SQL wyglądają dość skomplikowanie, tzn.:

INSERT INTO Locations (Location)  
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326)); 

Na szczęście Dapper ma wsparcie dla Geography i nie trzeba samemu tworzyć powyższych zapytań. Przede wszystkim należy skorzystać z klasy SqlGeography, która znajduje się w Microsoft.SqlServer.Types.dll. Jeśli ta biblioteka nie jest dołączona do projektu, należy dodać odpowiednią referencję:
1

Następnie, dodanie nowej wartości wygląda następująco:

            using (var connection = new SqlConnection(connectionString))
            {
                var geography = SqlGeography.STLineFromText(
                    new SqlChars(new SqlString("LINESTRING(-122.360 47.656, -122.343 47.656 )")), 4326);

                var pars = new { geo = geography };

                connection.Execute("INSERT INTO [Locations] (Location) values (@geo)", pars);
            }

Jak widać, Dapper rozpozna typ SqlGeography i wygeneruje odpowiedni kod SQL. Nie musimy ręcznie wstrzykiwać żadnego zapytania i korzystamy z parametrów tak jak to ma miejsce z innymi typami.

SQL Server – typ geography

W ostatnim w poście pokazałem jak odczytać współrzędne geograficzne. Kolejne możliwe pytanie, to jak je przechowywać w bazie danych. Najprostsze podejście to stworzenie dwóch kolumn typu float, dla szerokości i długości geograficznej.

W SQL Server istnieje jednak lepsze rozwiązanie, a mianowicie typ “geography”. Służy on do przechowywania lokalizacji i nie koniecznie  wyłącznie pojedynczego punktu. Możliwe jest zapisywanie całych poligonów. Utworzenie tabeli z taką kolumną, nie różni się niczym od pozostałych typów:

CREATE TABLE Locations   
    ( Id int IDENTITY (1,1),  
    Location geography );  
GO  

Następnie dodanie lokalizacji, opisującej poligon wygląda następująco:

INSERT INTO Locations (Location)  
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));  

W celu dodania lokalizacji w formie pojedynczego punktu, należy stworzyć obiekt POINT zamiast powyższego POLYGON:

INSERT INTO Locations (Location)  
VALUES (geography::STGeomFromText('geography::Point(47.65100, -122.34900, 4326))', 4326));

Zaletą korzystania z geography jest, że wszelkie operacje takie jak znajdowanie odległości między dwoma punktami, są już zaimplementowane. Jeśli chcemy znaleźć części wspólne dwóch lokalizacji wtedy:

DECLARE @g geometry;  
DECLARE @h geometry;  
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);  
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);  
SELECT @g.STIntersection(@h).ToString();  

Zwracanie odległości między punktami wygląda następująco:

DECLARE @g geography;  
DECLARE @h geography;  
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);  
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);  
SELECT @g.STDistance(@h);  

Ponadto, SQL Server management studio, ma pewne wsparcie dla typu geography. Wykonując prosty SELECT zobaczymy wyłącznie binarne wartości:
1

Przechodząc jednak do zakładki “Spatial Results”, zobaczymy całą mapkę:
2

W przyszłym wpisie, pokażę jak korzystać z tego typu w C# (a konkretnie w Dapper).

Fragmentacja indeksów

To jest nie blog o SQL Server, ale myślę, że podstawowe informacje o bazach danych są przydatne dla każdego programisty.

Wszyscy używamy indeksów i wiemy, że dzielą się one m.in. na clustered index oraz non-clustered index. Odpowiednie stworzenie ich może okazać się dość trudnym zadaniem. Niestety nasza rola nie ogranicza się wyłącznie do ich utworzenia ponieważ wcześniej czy później będziemy mieli do czynienia z fragmentacją indeksów.

Co to jest fragmentacja? W informatyce jest to znane pojęcie i oznacza, że dane nie są rozmieszczone równomiernie w pamięci. Indeks jest reprezentowany jako drzewo. Jeśli jego węzły nie są rozmieszczone równomiernie (w spójnych blokach), wtedy odczytywanie danych jest trudniejsze ponieważ musimy skakać z jednego adresu na drugi.

Tak na prawdę w bazach danych musimy być świadomi dwóch typów fragmentacji. Pierwszy z nich to fragmentacja fizyczna dysku. Jeśli dane na dysku nie są spójne, to naturalnie może zdarzyć się, że plik bazy danych również zostanie podzielony na różne bloki w pamięci. Ten typ fragmentacji nie ma nic wspólnego z SQL Server, ale jeśli ma miejsce, spowoduje spowolnienie wszystkich zapytań. Wyobraźmy sobie, że plik bazy danych jest umieszczony na tym samym dysku, gdzie umieszczone są jakieś inne pliki. Jeśli tylko jest tam dużo operacji Input\Output, wtedy fragmentacja może mieć miejsce dosyć często. Rozwiązaniem jest zwykła defragmentacja dysku wykonywana przez Windows. Dodatkowo warto umieszczać plik bazodanowy na osobnym, niezależnym dysku.

Drugi typ fragmentacji, to fragmentacja indeksu i jest ściśle związana z silnikiem baz danych. Wchodząc szczegóły, wyróżnia się fragmentację wewnętrzną oraz zewnętrzna. Jeśli usuniemy dane ze środka tabeli, wtedy pojawią się puste fragmenty na stronach pamięci. Wykonując DELETE wielokrotnie, możemy doprowadzić zatem do fragmentacji wewnętrznej, czyli pustych fragmentów (“dziur”) na stronach pamięci.

Z kolei zewnętrzna fragmentacja ma miejsce, gdy logiczna kolejność stron nie odpowiada fizycznej kolejności. Przytrafia się to, gdy wiersze są dodawane do środkowych stron, w których nie ma już miejsca. Załóżmy, że mamy strony Page1, Page2, Page3, które odpowiadają fizycznej kolejności Page1->Page2->Page3. Załóżmy, że wykonujemy INSERT, który powinien dodać dane na końcu strony Page1. Jeśli nie będzie tam wystarczającao miejsca, wtedy zostanie dodana nowa strona (nazwijmy ją Page1A) i fizyczny porządek będzie wyglądał Page1->Page2->Page3->Page1A, a logiczny z kolei Page1->Page1a->Page2->Page3. Innymi słowy, zewnętrzna fragmentacja to “dziury” pomiędzy stronami, a wewnętrzna to “dziury” w obrębie pojedynczych stron.

Wniosek taki, że indeksy trzeba przebudowywać od czasu do czasu. W  SQL Management Studio można wybrać z menu kontekstowego opcję Rebuild:

1

Na ekranie pojawi się informacja jak bardzo dany indeks jest sfragmentowany:

2

Oczywiście w praktyce automatyzuje się takie zadania. Administratorzy baz danych zwykle definiują skrypty, które wykonają się, gdy poziom fragmentacji osiągnie dany próg.

Na przykład, podany skrypt wyświetli informacje o wszystkich indeksach wraz z ich fragmentacją (źródło):

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

DbUp – generowanie silnie typowanych nazw procedur za pomocą T4

O bibliotece DbUp pisałem tutaj:
DBUP – AKTUALIZACJA BAZ DANYCH

Z kolei o szablonach T4  stworzyłem cały mini-cykl, który można znaleźć tutaj.

Problem z DbUp jest taki, że wywołuje on po prostu kolejne skrypty. Następnie sami za pomocą np. Dapper, musimy podać nazwę procedury czy tabeli na której chcemy operować. DbUp + Dapper to częsta kombinacja narzędzi. Jeśli korzystamy z rozbudowanego ORM, takiego jak EntitityFramework, wtedy wszystkie tabele czy nazwy procedur i tak są silnie typowane ponieważ framework je wygeneruje. Za pomocą  EF Migrations, można uzyskać to samo co robi DbUp.

Jeśli jednak korzystamy tylko z Dapper+DbUp, wciąż możemy mieć silnie typowane nazw. Załóżmy że w projekcie DbUp mamy następującą strukturę skryptów:

-> StoredProcs
—>1. GetFirstNameById
—>2. GetData
—>3. UpdateData

->Functions:
—>1. Split
—>2. MaxData

->Types
—>1.  CustomType1
—>2.  CustomType2

Wtedy można pokusić się o następujący szablon T4:

<#@ template debug="false" hostspecific="true" language="C#" #> <#@ assembly name="System.Core" #> <#@ import namespace="System.Linq" #>
<#@ import namespace="System.IO" #> <#@ import namespace="System.Text" #> <#@ import namespace="System.Collections.Generic" #> <#@ output extension=".cs" #>
namespace Data.Sql
{
<#
	 string[] foldersToGenerate = new [] { "StoreProcs", "Functions","Types" };
	 string scriptsPath = Host.ResolvePath(@"..\Database\Scripts");

	 foreach(var folderName in foldersToGenerate)
	 {
	     WriteLine(string.Format("\tinternal static class {0}\n\t{{",folderName));

		 foreach(var directory in System.IO.Directory.GetDirectories(scriptsPath, "*" + folderName, SearchOption.AllDirectories))
		 {
			foreach(var fileName in Directory.GetFiles(directory))
			{
				string name = Path.GetFileNameWithoutExtension(fileName).Split(' ').Last();
				string declaration = string.Format("\t\tinternal const string {0} = \"{0}\";", name);

				WriteLine(declaration);
			}
		 }

		 WriteLine("\t}");
	 }
#>
}

Szablon przejdzie automatycznie po wszystkich plikach i wygeneruje statyczną klasę zawierającą stałe, np.:

internal static class StoreProcs
{
    internal const string GetFirstNameById="GetFirstNameById";
    internal const string GetData="GetData";
    internal const string UpdateData="UpdateData";
}

Rozwiązanie zakłada, że nazwa pliku pokrywą się z nazwą zasobu (czyli procedury, funkcji czy typu). Analogicznie sprawa wygląda z tabelami. Myślę, że jest to lepsze rozwiązanie niż hardcodowanie za każdym razem procedury.

Parameters sniffing – SQL Server

Kilka ostatnich wpisów poświęciłem na temat SQL Server. Ostatnio pisałem o SQL Statistics, które wpływa na wygenerowanie planu wykonania. Jak wiemy, plan jest zawsze buforowany, co wpływa zwykle pozytywnie na wydajność. Analiza zapytania i wygenerowanie planu są dość czasochłonne, dlatego zwykle plan jest zapisywany.

Problem w tym, że nie zawsze plan przechowywany w cache jest najbardziej optymalny. Zwykle pierwsze wykonanie danego zapytania powoduje zapis planu w pamięci. Co jeśli kolejne zapytania będą używały innych parametrów, a co za tym idzie, inny plan może być skuteczniejszy?

Problem pokażę, na przykładzie bazy AdventureWorks, którą można ściągnąć z stąd.
Do dyspozycji mamy tam tabelę Address.

Zapytanie:

SELECT* FROM [AdventureWorks2014].[Person].[Address]

Wynik:

1

Jeden z indeksów pokrywa kolumnę StateProvinceID. Wykonajmy poniższe zapytanie, aby dowiedzieć się trochę więcej o dystrybucji danych:

SELECT StateProvinceID,count(*)
  FROM [AdventureWorks2014].[Person].[Address] group by StateProvinceID order by count(*) desc

Wynik:

2

Widzimy, że bardzo dużo wierszy ma StateProvinceId równy 9. Z kolei 119 ma tylko jedno wystąpienie. Wykonajmy zatem  poniższe zapytania na tej kolumnie, aby zobaczyć wygenerowane plany wykonania:

 SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=9
 GO
 SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=119

Plany wykonania:

3

Widzimy, że pierwsze zapytanie nie korzysta z indeksu, ponieważ jest mało selektywne i LookUp kosztował by więcej niż skanowanie. Drugie zapytanie, które zwraca wyłącznie jeden wiersz jest dużo lepszym kandydatem na przeszukiwanie indeksu.
Plan nie został zbuforowany bo to fizycznie dwa różne zapytanie.

Stwórzmy teraz procedurę, która wykonuje analogiczne zapytanie:

CREATE PROCEDURE GetAddressByStateId
	@stateId int
AS
BEGIN
  
    SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=@stateId
END
GO

Wykonajmy również procedurę przekazując dwa różne identyfikatory stanów:

  exec GetAddressByStateId 9
  GO
  exec GetAddressByStateId 119

Plany wykonania:

4

Co się stało? Pierwsze wykonanie zbuforowało plan wykonania. Drugie wywołanie zatem nie będzie próbowało znaleźć optymalnego planu, a skorzysta z tego zawartego w cache. Problem w tym, że dla skomplikowanych procedur nie zawsze pierwszy plan wykonania jest najlepszy.

Jednym z “obejść” jest rekompilacja procedury za każdym razem za pomocą WITH RECOMPILE:

ALTER PROCEDURE GetAddressByStateId
	@stateId int  WITH RECOMPILE
AS
BEGIN
  
    SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=@stateId
END
GO

Innym rozwiązaniem może być skorzystanie z Query Hints. Za pomocą wskazówek można ułatwić SQL Server wygenerowanie najlepszego planu. W praktyce jednak, jeśli mamy problemy z parameter sniffing oznacza to, że procedura jest zbyt skomplikowana i część logiki powinna być wykonywana np. w osobnej funkcji.

SQL Server Statistics – podgląd danych

W poprzednim poście, wyjaśniłem do czego służą statystyki w SQL Server. Do dyspozycji MSSQL ma gęstość oraz dystrybucję danych. Dzisiaj pokażę, jak przeglądać te dane, aby przekonać się samemu, że faktycznie powyższe metryki istnieją.

Załóżmy, że mamy tabele Articles z dwoma kolumnami, ID oraz Price. Następnie tworzymy indeks, czyli zarazem statystykę na kolumnie Price. W Sql Server studio będzie to wyglądać następująco:

1

Dodajmy również kilka wierszy z tymi samymi cenami. W celu wyświetlenia statystyk należy wywołać DBCC SHOW_STATISTICS:

DBCC SHOW_STATISTICS('dbo.Articles',PRICE_INDEX)

Wynik będzie wyglądać następująco:

2

Pierwsza tabela przedstawia nazwę statystyki wraz z podsumowaniem. Widzimy m.in. datę ostatniej aktualizacji czy liczbę wierszy, które brały udział w obliczaniu metryk.

Druga tabela to gęstość. Wartość 1 oznacza, że nie ma unikalnych wierwszy. 0.125 to z kolei gęstość klucza.Mamy osiem wierszy i klucz główny zawsze ma unikalną wartość, stąd 1/8 równa się 0.125.

Ostatnia tabela to dystrybucja danych w formie histogramu. Histogram opisany jest za pomocą kilku kolumn tzn.:

RANGE_HI_KEY
RANGE_ROWS
EQ_ROWS
DISTINCT_RANGE_ROWS
AVG_RANGE_ROWS

RANGE_HI_KEY to wartość pojedynczego progu w hisogramie. Kolejne metryki opisują ile wartości przypada na ten próg (m.in. włączając granice progu, przypadające dokładnie w miejsce progu, liczba unikalnych wartość).

Szczegóły można znaleźć w dokumentacji m.in. tutaj. W praktyce oczywiście nie musimy analizować tych danych. Bardzo ważne jednak jest, aby mieć świadomość o SQL Statistics ponieważ statystyki mają ogromny wpływ podczas generowania planu wykonania.

SQL Server Statistics

SQL Server buduje statystyki opisujące przechowywane dane. Dzięki tym statystkom łatwiej jest wygenerować optymalny “execution plan”.  Jak wiemy,  plan wykonania zależy m.in. od stworzonych indeksów oraz przekazanych parametrów do danego zapytania\procedury.  Problem w tym, że bez wiedzy o konkretnych danych w bazie, trudno wygenerować optymalny plan. Jeśli wiemy, że zapytanie zwróci wszystkie wiersze, wtedy nie ma sensu korzystać z żadnych indeksów, ponieważ skanowanie będzie i tak szybsze.

Dzięki statystykom, SQL Server wie m.in. o dystrybucji i gęstości konkretnych danych\kolumn. Jeśli kolumna B ma jedną wartość we wszystkich wierszach, wtedy filtrowanie po niej nie jest zbyt selektywne. Takie informacje są bardzo przydatne w czasie tworzenia planu. W momencie, gdy wykonujemy jakiekolwiek zapytanie czy procedurę następuje kilka ważnych etapów:

  1. Zapytanie jest przekazane do SQL Server query optimizer.
  2. Jeśli plan zapytania istnieje w cache, wtedy po prostu jest zwracany.
  3. Tworzony jest nowy plan na podstawie dostępnych indeksów oraz typu przechowywanych danych.  To tutaj SQL Optimizer decyduje czy warto użyć indeksu czy lepsze może okazać się standardowe skanowanie. Jeśli optymizer zdecyduje się na użycie indeksu, wtedy musi również wybrać ten najbardziej optymalny.
  4. Nowo stworzony plan jest umieszczany w cache, wiec następne zapytania nie będą musiały być ponownie analizowane.

Do dyspozycji SQL Server ma dwie metryki, zagęszczenie danych oraz ich dystrybucję.

  • Gęstość  danych(Density)

Pierwszą zbieraną metryką jest zagęszczenie danych, czyli współczynnik pokazujący jak wiele unikalnych wartości jest w danej kolumnie. Można to opisać następującym wzorem:

Density of X = 1 / liczba unikalnych wartości w kolumnie X

Wysoka gęstość oznacza zatem, że jest tam mało unikalnych wartości, a co za tym idzie, zapytanie na takiej kolumnie będzie mało selektywne i nie ma sensu korzystać z indeksu.

  • Dystrybucja danych

Dystrybucja danych również opisuje zagęszczenie danych, ale pokazuje jakie konkretne dane występują najczęściej. Innymi słowy, dystrybucja danych to histogram. Dane są zatem dzielone na odpowiednie przedziały (oś X), a potem na osi Y mamy liczbę wystąpień znajdujących się w danym przedziale.   Jeśli w jakimś zapytaniu mamy “WHERE X> 200 AND X < 500, wtedy za pomocą dystrybucji można określić ile w przybliżeniu zapytanie zwróci wierszy. Jeśli histogram w tych przedziałach ma wysokie wartości, wtedy zapytanie jest bardzo selektywne, a co za tym idzie, indeks jest dobrym rozwiązaniem.

W SQL Server występuje 200 wspomnianych przedziałów. Trochę upraszczam tutaj, bo SQL Server przechowuje kilka innych wartości histogramu, ale główną ideą jest posiadanie opisu gęstości konkretnych przedziałów danych.

Kiedy są zatem tworzone statystyki? Domyślnie są tworzone dla indeksów, w tym klucza głównego (clustered index). Jeśli AUTO_CREATE_STATISTICS jest uaktywnione na bazie, wtedy również będą tworzone dla kolumn w zapytaniach, które używane są do filtrowania danych (klauzula WHERE, JOIN).

W następnym wpisie przyjrzyjmy się jak obejrzeć powyższe statystyki w SQL Server.

tSQLt – jak testować “constraints”

Załóżmy, że mamy następującą tabelę:

CREATE TABLE [dbo].[Articles](
	[Id] [int] NOT NULL,
	[Price] [int] NULL,
 CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Articles]  WITH CHECK ADD  CONSTRAINT [CK_Articles] CHECK  (([Price]>(5)))
GO

ALTER TABLE [dbo].[Articles] CHECK CONSTRAINT [CK_Articles]
GO

Oprócz tabeli, dodaliśmy walidację danych. Cena zawsze musi być większą niż 5. Jeśli spróbujemy dodać wartość mniejszą niż 5, zakończy się to błędem.

INSERT INTO Articles Values(1,4)

Błąd:

The INSERT statement conflicted with the CHECK constraint "CK_Articles". The conflict occurred in database "testdb", table "dbo.Articles", column 'Price'.

Spróbujmy teraz wykonać taką samą operację w teście:

EXEC tSQLt.FakeTable 'dbo.Articles'
INSERT INTO Articles Values(1,4)

Dodanie wartości 4 nie spowoduje żadnego błędu. Wynika to z tego, że każdy stub pozbawiony jest jakichkolwiek restrykcji. Zwykle jest to bardzo korzystne, ponieważ jeśli testujemy tylko jedną kolumnę, nie musimy martwić się o resztę wartości.
Jeśli z kolei chcemy, aby nasze testy pokryły również walidację danych (constraints), wtedy mamy do dyspozycji metodę ApplyConstraint:

EXEC tSQLt.FakeTable 'dbo.Articles'
EXEC tSQLt.ApplyConstraint @TableName='dbo.Articles', @ConstraintName="CK_Articles"

INSERT INTO Articles Values(1,4)

Dzięki ApplyConstraint możemy dodawać ograniczenia jedno po drugim, co umożliwia prawidłową implementację testów jednostkowych, gdzie chcemy testować wyłącznie pojedyncze rzeczy. Prawidłowa implementacja testu wygląda zatem następująco:

EXEC tSQLt.FakeTable 'dbo.Articles'
EXEC tSQLt.ApplyConstraint @TableName='dbo.Articles', @ConstraintName="CK_Articles"

EXEC tSQLt.ExpectException

INSERT INTO Articles Values(1,4)

Analogicznie, powinniśmy przetestować wartości, które są zgodne z wymaganiami:

EXEC tSQLt.FakeTable 'dbo.Articles'
EXEC tSQLt.ApplyConstraint @TableName='dbo.Articles', @ConstraintName="CK_Articles"

EXEC tSQLt.ExpectNoException

INSERT INTO Articles Values(1,10)

Testowanie kluczy obcych odbywa się również za pomocą ApplyContraint. Bardzo podobną metodą jest ApplyTrigger:

tSQLt.ApplyTrigger [@TableName = ] 'table name'
                    , [@TriggerName = ] 'trigger name'

tSQLt – integracja z TeamCity

Kilka postów poświęciłem już na temat pisania testów jednostkowych dla SQL Server. Tak samo jak ze zwykłymi testami w C#, chcemy je wykonywać jako etap w CI. Ostatnio zwykle pracuję z TeamCity, dlatego w tym wpisie pokażę plugin właśnie dla TC.

Integracja TeamCity z tSQLt sprowadza się do instalacji następującego plugina:

https://github.com/cprieto/tsqlt-teamcity

Plugin umiezczamy w “C:\ProgramData\JetBrains\TeamCity\plugins”. Następnie, przy tworzeniu kolejnego etapu w CI, do dyspozycji będziemy mieli tSQLt runner (screen z oficjalnej dokumentacji):

Analogicznie do testów jednostkowych nUnit, będziemy mogli przeglądać, które testy zostały wykonane. Konfiguracja zatem sprowadza się do instalacji plugina oraz podania connection string.