Jan 08

Aktualizowanie rekordów w pętli while – TSQL

Poniżej przykład aktualizowania dużej liczby rekordów z wykorzystaniem pętli WHILE w TSQL

Dlaczego tak? Dlatego, że chcemy podzielić rekordy na paczki i aktualizować paczka po paczce. W przypadku błędu wycofana zostanie jedna paczka a nie całość.

Przykład na podstawie bazy AdventureWorks2008R2

SELECT COUNT(*) FROM Production.Product WHERE FinishedGoodsFlag = 1

Wynik zapytania 295 rekordów

DECLARE @isFinished BIT
DECLARE @packageSize INT
SET @isFinished = 0
SET @packageSize = 50

WHILE @isFinished = 0 
    BEGIN
        UPDATE TOP ( @packageSize ) Production.Product
            SET     FinishedGoodsFlag = 0
        WHERE FinishedGoodsFlag = 1
        
        IF ( @@ROWCOUNT < @packageSize ) 
        BEGIN
            SET @isFinished = 1
        END
    END

Wynik zapytania:

(50 row(s) affected)

(50 row(s) affected)

(50 row(s) affected)

(50 row(s) affected)

(50 row(s) affected)

(45 row(s) affected)

Należy zwrócić szczególną uwagę na warunek wyjścia z pętli WHILE w połączeniu z warunkiem WHERE w wyrażeniu UPDATE. Jeśli nie dodamy klauzuli WHERE z odpowiednim warunkiem, możemy uruchomić pętlę, która nigdy się nie skończy . W powyższym przykłądzie aktualizowalibyśmy pierwsze 50 rekordów z tabeli Production.Product

Dec 24

Import pliku CSV do SQL Server

Podczas analizy olbrzymiej ilości danych zdarza się, że edytory tekstu i regexp nie dają, wtedy, jedną z metod jest bulk insert danych z pliku do tabeli. Metoda bardzo wydajna, pozwalająca po imporcie swobodnie korzystać z dobrodziejstw SQL Server.


BULK INSERT TempTable FROM 'C:\Data\data.csv'
WITH(
    FIRSTROW =2,
    LASTROW =10,
    FIELDTERMINATOR =',',--CSV field delimiter
    ROWTERMINATOR ='\n',--Use to shift the control to next row
    ERRORFILE ='C:\data_errorRows.csv',
    TABLOCK
    )

Gdzie,

  • FIRSTROW – oznacza od którego wiersza dane będą importowane. Pozwala to ominąć nagłówek danych albo, w połączeniu z opcją LASTROW dokonać importu wybranej części danych
  • LASTROW – wskazuje na ostatni importowany wiersz
  • FIELDTERMINATOR – separator kolejnych pól
  • ROWTERMINATOR – znak końca linii w importowanym pliku
  • ERRORFILE – w tym pliku znajdą się rekordy, których nie udało się zaimportować.
  • TABLOCK – zakłada blokadę na tabeli, do czasu wykonania importu. Znacząco przyspiesza import danych

Oct 21

Mapowanie typów danych SSIS i SQL Server

Kto nigdy nie napotkał tego problemu, to znaczy, że nie pracował z SSIS`ami :)

 

SSIS Data Type SSIS Expression SQL Server
single-byte signed integer (DT_I1)
two-byte signed integer (DT_I2) smallint
four-byte signed integer (DT_I4) int
eight-byte signed integer (DT_I8) bigint
single-byte unsigned integer (DT_UI1) tinyint
two-byte unsigned integer (DT_UI2)
four-byte unsigned integer (DT_UI4)
eight-byte unsigned integer (DT_UI8)
float (DT_R4) real
double-precision float (DT_R8) float
string (DT_STR, «length», «code_page») char, varchar
Unicode text stream (DT_WSTR, «length») nchar, nvarchar, sql_variant, xml
date (DT_DATE) date
Boolean (DT_BOOL) bit
numeric (DT_NUMERIC, «precision», «scale») decimal, numeric
decimal (DT_DECIMAL, «scale») decimal
currency (DT_CY) smallmoney, money
unique identifier (DT_GUID) uniqueidentifier
byte stream (DT_BYTES, «length») binary, varbinary, timestamp
database date (DT_DBDATE) date
database time (DT_DBTIME)
database time with precision (DT_DBTIME2, «scale») time(p)
database timestamp (DT_DBTIMESTAMP) datetime, smalldatetime
database timestamp with precision (DT_DBTIMESTAMP2, «scale») datetime2
database timestamp with timezone (DT_DBTIMESTAMPOFFSET, «scale») datetimeoffset(p)
file timestamp (DT_FILETIME)
image (DT_IMAGE) image
text stream (DT_TEXT, «code_page») text
Unicode string (DT_NTEXT) ntext

 

Dla innych serwerów baz danych mapowania znajdują się na MSDN

Aug 21

Transakcje i poziomy izolacji

Transakcja

Transakcja jest zbiorem operacji na bazie danych, które wykonują się zgodnie z zasadą “wszystko albo nic”. Transakcyjność gwarantuje spójność danych w przypadku wystąpienia błędu w trakcie wykonania zapytania na bazie danych.  Jest ona jedną z zasad ACID

Zasada ACID

Atomicityatomowość, Consistencyspójność, Isolationizolacja, Durabilitytrwałość.

  • atomowość transakcji oznacza, iż każda transakcja albo wykona się w całości, albo w ogóle, czyli np. jeżeli w ramach jednej transakcji odbywać ma się przelew bankowy (zmniejszenie wartości jednego konta i powiększenie innego o tę samą kwotę), to nie może zajść sytuacja, że z jednego konta ubędzie pieniędzy a kwota na docelowym będzie bez zmian: albo przelew zostanie wykonany w całości, albo w ogóle.
  • spójność transakcji oznacza, że po wykonaniu transakcji system będzie spójny, czyli nie zostaną naruszone żadne zasady integralności.
  • trwałość danych oznacza, że system potrafi uruchomić się i udostępnić spójne, nienaruszone i aktualne dane zapisane w ramach zatwierdzonych transakcji, na przykład po nagłej awarii zasilania.
  • izolacja transakcji oznacza, iż jeżeli dwie transakcje wykonują się współbieżnie, to zazwyczaj (zależnie od poziomu izolacji) nie widzą zmian przez siebie wprowadzanych. Poziom izolacji w bazach danych jest zazwyczaj konfigurowalny i określa, jakich anomalii możemy się spodziewać przy wykonywaniu transakcji. Przykładowe typy izolacji to (model ANSI):
  1. read uncommitted – najniższy poziom izolacji, jedna transakcja może odczytywać wiersze, na których działają inne transakcje,
  2. read committed – transakcja może odczytywać tylko wiersze zapisane,
  3. repeatable read – transakcja nie może czytać, ani zapisywać, na wierszach odczytywanych, bądź zapisywanych w innej transakcji,
  4. serializable – pełna izolacja, wyniki współbieżnie realizowanych zapytań muszą być identyczne z wynikami tych samych zapytań realizowanych szeregowo (ang. nazwa oznacza szeregowalne)

(źródło: wikipedia)

Poziomy izolacji w MS Sql Server

Read uncommited – jest to najmniej restrykcyjne ustawienie, które praktycznie powoduje ignorowanie założonych blokad. Jeżeli jedna transakcja nie została zatwierdzona (commit) ale zmieniła dane w bazie to w tym samym czasie inny proces może te dane odczytać. Jeżeli wspomniana transakcja zostanie wycofana to w efekcie okaże się, że drugi proces pobrał dane, które zostały wycofane.

Read commited – jest to domyślna opcja, która powoduje, że we wspomnianym przykładzie zostaną odczytane dane sprzed rozpoczęcia pierwszej transakcji. Podstawową wadą tej opcji jest oczywiście odwrotna sytuacja niż poprzednio – w momencie zatwierdzenia transakcji dane zostaną zmienione czyli nasz pierwotny odczyt będzie nieaktualny.

Repeatable read – w tym przypadku odczytywane są jedynie dane z zatwierdzonych transakcji, a żadna z transakcji nie może zmodyfikować danych, które zostały odczytane

Snapshot – w tym przypadku każda transakcja w momencie jej utworzenia tworzy sobie snapshot danych i na nim pracuje do czasu jej zakończenia. W ten sposób inne transakcje nie są w stanie zmodyfikować danych, które zostały użyte. Nawet jeżeli inna transakcja zmodyfikuje dane to oryginalna transakcja cały czas pracuje na danych z momentu jej utworzenia. W ten sposób upraszczamy dostęp do danych ale powoduje to dodatkowe obciążenie tabeli tempdb.

Serializable – odczyt danych z tabeli za pośrednictwem instrukcji select powoduje zablokowanie danego zakresu. W efekcie żadna inna transakcja nie będzie miała możliwości zmiany danych w tym okresie.

Jul 08

Debuggowanie SSIS Script Component

Debugging is not possible untill 2012, but there are some ‘workarounds’

http://microsoft-ssis.blogspot.com/2011/04/breakpoint-does-not-work-within-ssis.html

Dobrym rozwiązaniem jest debuggowanie z wykorzystaniem Trace loga

// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    System.Diagnostics.Trace.WriteLine("SomeMessage: " + Row.YourColumn);
}

Programik do podglądania logów DebugView v4.81

http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx