Category Archives: SQL Server

T-SQL, klauzula OUTPUT

OUTPUT pozwala na zwrócenie wierszy d0danych, zaktualizowanych lub usuniętych za pomocą komend INSERT, UPDATE  i DELETE. Zwrócony wynik możemy wyświetlić po prostu na ekranie lub wstawić do innej tabeli.  Wyobraźmy sobie, że mamy tabele Employee oraz EmployeeLog zawierającą logi. W celu wykonania loga możemy:

INSERT INTO Employee (ID_EMPLOYEE,FirstName,LastName)
OUTPUT getdate(),'wstawiono wiersz',inserted.ID_EMPLOYEE INTO EmployeeLog
values(1,'Jakies imie,','Jakies nazwisko');

Równoważny efekt można osiągnąć wykorzystując wyzwalacz. Nawet podobnie do dyspozycji mamy tabele inserted oraz deleted.

W sposób analogiczny realizujemy logi dla polecenia UPDATE:

UPDATE Employee SET FirstName='nowe imie'
OUTPUT getdate(),'aktualizacja wiersza',inserted.ID_EMPLOYEE,inserted.FirstName,deleted.FirstName INTO EmployeeLog
WHERE ID_EMPLOYEE=1

W powyższym przykładzie inserted.FirstName zawiera imię po aktualizacji a deleted.LastName przechowuje starą wartość (przed aktualizacją). Ostatni przypadek to polecenie DELETE:

DELETE FROM Employee (ID_EMPLOYEE,FirstName,LastName)
OUTPUT getdate(),'usunieto wiersz',inserted.ID_EMPLOYEE INTO EmployeeLog
where ID_EMPLOYEE=1

Operator Apply (T-SQL)

W SQL Server 2005 wprowadzono operator Apply. Służy on do łączenia tabeli z funkcją zwracającą również tabelę (table-valued function). Po lewej stronie operatora mamy więc zwykłą tabelę (np. "Employees”) a  po prawej jakąś funkcję (np. “GetContactInfo”). Apply wywoła GetContactInfo dla każdego wiersza z Employee. W T-SQL występują dwie odmiany operatora: CROSS APLY i OUTER APPLY. Pierwszy  z nich złączy wyłącznie te wiersze dla których funkcja zwraca jakiś wynik. Z kolei OUTER APPLY złączy wiersze zastępując brakujące pola wartością NULL. Przykład:

SELECT tbl.FirstName, tbl.LastName, func.Email, func.Phone FROM Employess as tbl
CROSS APPLY
GetContactInfo(tbl.ID_EMPLOYEE) as func;

Jak już wspomniałem, GetContactInfo wywoływany jest dla każdego wiersza zwróconego przez pierwsze zapytanie SELECT.

Warunkowe wyświetlanie kolumn w T-SQL

Załóżmy, że mamy tabelę składającą się z 4 kolumn: FirstName, LastName, CompanyName oraz PersonType. Pole  PersonType przyjmuje ‘C’ gdy dany wiersz przedstawia firmę oraz ‘N’ gdy reprezentuje osobę fizyczną. Następnie chcemy wyświetlić w zależności od typu osoby nazwę firmy (C) lub imię+nazwisko(N). Zadanie można zrealizować za pomocą klauzuli case when:

select (case when PersonType='C' then CompanyName else FirstName+' '+LastName end) Name from Persons

Powyższy przykład miał tylko pokazać zastosowanie case when. W praktyce powyższa tabela nie jest zgodna z trzecią postacią normalną (3NF) i powinna zostać rozdzielona na dwie tabele (osoby fizyczne oraz firmy).

Kolumny SPARSE w SQL Server

Definiując tabelę np. za pomocą T-SQL istnieje możliwość opatrzenia kolumny atrybutem SPARSE. Kolumny SPARSE są specjalnymi kolumnami zoptymalizowanymi na przechowanie wartości NULL. W sytuacji gdy dana kolumna często ma wartość NULL(a dokładniej pole w wierszu), warto zastanowić się nad użyciem tego parametru ponieważ oszczędności pamięciowe są dość znaczące. Z drugiej jednak strony, jeśli będziemy chcieli zapisać wartość, która jest różna od NULL w kolumnie typu SPARSE zużyjemy więcej pamięci. Oszacowane oszczędności, zależne od typu danych znajdziecie tutaj. Aby stworzyć więc tabelę z kolumną SPARSE wystarczy opatrzyć ją atrybutem:

CREATE TABLE Example
(
    ID int PRIMARY KEY,
    SparseColumnExample smallint SPARSE NULL
) ;

Ze względu na charakterystykę kolumn SPARSE kolumna oczywiście musi być również typu NULLABLE. Podobnie kolumna taka nie może posiadać wartości domyślnej co jest uzasadnione(po co optymalizacja NULL skoro zawsze będzie wstawiona wartość domyślna). Dodatkowo niektóre typy danych nie mogą być opatrzone SPARSE a dokładniej: geography, text, geometry, timestamp, image, ntext  oraz wszelkie typy zdefiniowane przez użytkownika.