Monthly Archives: Grudzień 2011

Role serwerowe a resource governor w SQL 2012

W SQL 2012 została wprowadzona możliwość deklarowania ról użytkownika na poziomie serwera. Założeniem tej funkcjonalności  jest podwyższenie poziomu bezpieczeństwa i ułatwienie zarządzania relacyjnym silnikiem danych dostarczanym przez Microsoft. Nie jest to jednak jedyne jej zastosowanie z jakiego możemy skorzystać 🙂

Zacznijmy więc od przedstawienia nowej funkcjonalności

Zadeklarowanie nowej roli na poziomie serwera jest bardzo podobne do stworzenia roli na poziomie bazy danych. Jedyną różnicą jest słowo SERVER użyte podczas  deklaracji:

CREATE SERVER ROLE NazwaRoliSerwerowej

Po stworzeniu roli możemy dodać do niej wybranie loginy SQL Server . Aby tego dokonać należy skorzystać z instrukcji ALTER SERVER ROLE:

ALTER SERVER ROLE NazwaRoliSerwerowej
ADD LOGIN LoginTestowy

Nadawanie uprawnień także jest bardzo intuicyjne.  Przykład tego typu instrukcji przedstawiam poniżej:

GRANT VIEW SERVER STATE
TO NazwaRoliSerwerowej

Gdzie tej nowości możemy użyć?

Niestandardowym zastosowaniem nowej funkcjonalności jest jej użycie w RESOURCE GOVERNOR, a dokładniej w ciele funkcji klasyfikującej.

Dla tych z was, którzy nie korzystali z możliwości RESOURCE GOVERNOR-a  (jest on dostępny jedynie w wersji ENTERPRISE) dobrym początkiem do zapoznania się z jego możliwościami jest artykuł dostępny na technecie:

http://technet.microsoft.com/pl-pl/library/akademia-sql—czesc-2-resource-governor.aspx

Podstawą jego działania RESOURCE GOVERNOR-a jest odpowiednio napisana funkcja klasyfikująca. Jednym z możliwych scenariuszy w jakim RESOURCE GOVERNOR ma zastosowanie jest przydzielanie zasobów serwera poszczególnym osobom.

Zaznaczam w tym miejscu słowo osobom, gdyż do tej pory w tego typu przypadkach jedyną możliwością dla umiejscowienia loginów SQL w odpowiedniej puli było zawarcie sztywnego mapowania LoginSQL -> GrupaZasobów w ciele procedury:

CREATE FUNCTION dbo.fnFunkcjaKlasyfikujaca()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @w sysname

 SET @w = CASE USER_NAME()
           WHEN 'LoginA' THEN 'GrupaA'
           ELSE 'default'
          END
 RETURN @w
END

Zapewne część z was powie, że można  stworzyć odpowiednią tabelkę z mapowaniem, zamienić instrukcję SET na SELECT-a itp. Tego typu podejście nadal nie jest optymalne z administracyjnego punktu widzenia.

SQL Server 2012 pozwala ominąć ten problem 🙂

Stworzenie odpowiedniej roli na poziomie serwera i użycie funkcji IS_SRVROLEMEMBER jest rozwiązaniem, które wydaje mi się o wiele lepsze:

CREATE SERVER ROLE RolaREsourceA
GO
ALTER SERVER ROLE RolaResourceA
ADD LOGIN LoginA
GO
CREATE FUNCTION dbo.fnFunkcjaKlasyfikujaca()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @w sysname

  SET @w = CASE WHEN IS_SRVROLEMEMBER('RolaResourceA') = 1 THEN 'GrupaA'
             ELSE 'default'
           END
  RETURN @w
END
GO

Oczywiście rozwiązanie to ma także pewne wady, np. jeden login może być przypisany do wielu grup. Powinniśmy tę ewentualność uwzględnić w naszej funkcji klasyfikującej i odpowiednio ją oprogramować.

Podsumowanie 

Role serwerowe są nowością, która zostanie wprowadzona wraz z SQL Server 2012. Dzięki jej wprowadzeniu możemy nadawać uprawnienia na poziomie instancji o wiele przejrzyściej niż dotychczas.  Co więcej możemy tej nowości używać w połączeniu z innymi możliwościami jakie daje relacyjna baza danych firmy Microsoft by uzyskać większą gamę rozwiązań dla napotkanych problemów.

Reklamy

Właśnie ukazał się mój artykuł na temat FILETABLE w SQL Server 2012

Na portalu wss.pl ukazał się artykuł mojego autorstwa. Jest on wprowadzeniem do nowości nazwie FILETABLE, która zostanie wprowadzenia w SQL Server 2012 i pozwala na łatwiejszy dostęp do danych binarnych przechowywanych w bazie.

Jeżeli chcielibyście dowiedzieć się na ten temat nieco więcej, to zachęcam do lektury 🙂

A oto link do wpomnianego artykułu

Pięć najfajniejszych funkcji skalarnych wprowadzonych w SQL Server 2012

Microsoft wraz z każdą kolejną premierą swojej platformy bazodanowej wprowadza szereg nowości i udoskonaleń. SQL Server 2012 nie odbiega od tej reguły. W poniższym wpisie chciałbym przedstawić ranking pięciu najfajniejszych (moim zdaniem) nowinek w funkcjach skalarnych języka T-SQL. Ranking ten jest całkowicie subiektywny, tak więc jeżeli ktoś z was ma inne zdanie na temat tego która z funkcji powinna być na miejscu pierwszym, a która na piątym – nie będę się spierał, lecz wpisu nie zmienię 🙂

Przejdźmy zatem do zestawienia:

Miejsce 5 – IFF

IFF jest funkcją, której działanie polega na zwróceniu jednej z dwóch zadanych wartości w zależności od tego czy pierwszy z wymaganych parametrów przyjmuje wartość logiczną TRUE czy FALSE. Składnia tej funkcji jest następująca:

IIF ( boolean_expression, true_value, false_value )

  boolean_expression - wyrażenie logiczne zwracające prawdę lub fałsz.
  true_value - wartość zwracana gdy wyrażenie boolean_expression jest prawdą
  false_value - wartość zwracana, gdy wyrażenie boolean_expression jest fałszem

Przykład jej wykorzystania może wyglądać w ten sposób:

-- IFF zwracające prawdę
SELECT IIF(1=1, 'PRAWDA', 'FAŁSZ')

Należy w tym miejscu powiedzieć, że kod ten można bardzo łatwo zastąpić zwykłą instrukcją CASE, która nie będzie o wiele dłuższa:

-- alternatywa kodu z poprzedniego przykładu
SELECT CASE WHEN 1=1 THEN 'PRAWDA' ELSE 'FAŁSZ' END

Z uwagi małą oszczędność związaną z pisaniem kodu uplasowałem IIF na piątym miejscu. Chciałbym jednak dodać, że jej duża czytelność oraz prostota użycia jest na tyle zachęcająca, że prawdopodobnie będę używał jej dość często 🙂

Miejsce 4 – FORMAT

Całkiem przyjemna funkcja formatująca podaną wartość i zwracająca ją jako ciąg znaków nvarchar. Jej składnię przedstawiam poniżej:

FORMAT (value, format [, culture ])

  value - wartość, którą chcemy przedstawić w postaci ciągu znaków nvarchar
  format - zadeklarowany przez nas format zwracanej wartości,
           powinien on być zgodny z konwencją formatowania platformy .NET 4.0
  culture - opcjonalny parametr związany z opcjami językowymi

Sposób użycia tej funkcji przedstawiam poniżej:

SELECT FORMAT(GETDATE(), 'yyyyMMdd (dddd)', 'pl') AS DzisiejszaData

Trzeba przyznać, że funkcja ta jest dość wygodna w użyciu. Alternatywa kodu zwracającego ten sam rezultat, lecz bez użycia funkcji FORMAT, nie jest już tak przyjemna dla naszych palców:

SET LANGUAGE 'polish'
SELECT CONVERT(nvarchar(8), GETDATE(), 112)
        + ' (' + DATENAME(WEEKDAY, GETDATE()) + ')' AS DzisiejszaData

Pewnym niuansem alternatywnego kodu jest to, że jeżeli zechcemy otrzymać nazwę dnia tygodnia w języku innym niż sesja użytkownika, to niestety nie da się tego w prosty sposób zrobić (stąd SET LANGUAGE ‚polish’). Używając funkcji FORMAT mamy dostępny specjalny parametr, który pomaga nam pozbyć się tego problemu.

Miejsce 3 – DATEFROMPARTS

Jeżeli musieliście kiedykolwiek składać datę z trzech parametrów typu rok, miesiąc dzień, to DATEFROMPARTS na pewno się wam spodoba. Jej składnia jest następująca:

DATEFROMPARTS ( year, month, day )

  year - rok w postaci liczby typu int
  month - miesiąc w postaci liczby typu int
  day - dzień miesiąca w postaci liczby typu int

Przykład użycia:

SELECT DATEFROMPARTS(2012, 12, 4) As NaszaData

O ile w alternatywnych kodzie dla poprzednich funkcji trzeba było się trochę napisać, to tym razem mamy prawdziwe „dzieło sztuki” :):

DECLARE @rok int = 2012, @miesiac int =12, @dzien int = 4
SELECT CAST (
 CAST(2012 AS varchar)
 + ( CASE WHEN LEN(@miesiac) > 1 THEN CAST(@miesiac AS varchar)
     ELSE '0' + CAST(@miesiac AS varchar) END)
 + ( CASE WHEN LEN(@dzien) > 1 THEN CAST(@dzien AS varchar)
     ELSE '0' + CAST(@dzien AS varchar) END)
 AS date) AS NaszaData

Z uwagi na to, że preferuję dość zwięzłe formułowanie myśli w językach programowania, to DATEFROMPARTS ma u mnie miejsce trzecie.

Miejsce 2 – EOMONTH

Nie jestem w stanie stwierdzić ile razy musiałem przeliczać czy wybrany miesiąc ma 31, 30 czy 28 (lub 29) dni. Jedno jest pewne – nie będę już musiał więcej tego robić, gdyż EOMONTH zrobi to za mnie. Funkcja ta w zależności od podanych parametrów zwraca ostatni dzień wybranego miesiąca.

EOMONTH ( start_date [, month_to_add ] )

  start_date - dowolna data, dla której zostanie podany ostatni dzień miesiąca
  month_to_add - ilośc miesięcy jaka zostanie dodana do daty startowej

Sposób wykorzystania jest następujący:

SELECT EOMONTH('20111205', 2) As OstatniDzienLutego

Alternatywny kod przeliczający ten wynik we wcześniejszych wersjach SQL Server wyglądałby podobnie do tego:

SELECT DATEADD(dd, -DATEPART(dd, '20111205'),
         DATEADD(mm, 3, '20111205')) AS OstatniDzienLutego

Kod ten może nie jest może bardzo długi, ale w moich oczach nie jest on do końca czytelny. Użycie funkcji EOMONTH na tyle go porządkuje, że zasłużyła ona na miejsce drugie mego zestawienia.

Miejsce 1- TRY_PARSE

TRY_PARSE jest funkcją, która pobiera napis i „tłumaczy” go na zmienną wybranego przez nas typu. Gdy tłumaczenie to się nie uda funkcja zwraca wartość NULL. Składnia tej funkcji jest następująca:

TRY_PARSE (string_value AS data_type [ USING culture ])

 string_value - napis, który ma zostać sparsowany
 data_type - typ danych, do którego parsujemy
 culture - ustawienia językowe podanego napisu

Prosty przykład jej wykorzystania przedstawiam poniżej:

SELECT TRY_PARSE ('2011' AS int) As rok

Zamiast alternatywnego kodu, tym razem pokażę drugi przykład – bardziej życiowy, który ma na celu pokazać za co TRY_PARSE objęła zaszczytne miejsce w moim zestawieniu.

Załóżmy, że macie na szybko zaimportować do waszej bazy informacje znajdujące się w pliku CSV. Ja zazwyczaj w takich sytuacjach nie bawię się w tworzenie paczki SSIS, tylko używam opcji „Import data” dostępnej wprost z Management Studio. W pierwszym kroku importowym przenoszę zawartość pliku całość do tabeli, która ma zadeklarowane wszystkie typy kolumn jako varchar a następnie przepisuję do dane to tabeli drugiej, z odpowiednimi typami danych dla kolumn, gdzie dokonuje dalszej weryfikacji.

Dlaczego importuję dane w ten sposób?

Bo zawsze w plikach otrzymanych na szybko jest coś nie tak, np tam gdzie miały być liczby są liczby i zdanie „BRAK DANYCH”, lub jakieś kreski, gwiazdki itp. Jest to idealne miejsce na użycie funkcji TRY_PARSE:

-- cte udające tabelę z brzydkimi wartościami kolumny wartA
WITH ctePrzykladowaTabela
AS (
 SELECT *
 FROM ( VALUES ('1', '5'), ('2', '*'),
 ('3', '3+1'), ('4', '+'), ('5', 'BRAK DANYCH') ) t(id, wartA)
)
-- pokaż wszystkie rekordy, gdzie wartA nie jest liczbą
SELECT *
FROM ctePrzykladowaTabela
WHERE TRY_PARSE(wartA AS int) IS NULL

Dzięki TRY_PARSE   mogę wylistować wszystkie wartości, które nie dadzą się konwertować na inny typ danych. Co więcej mogę to zrobić za pomocą jednego prostego zapytania. Jakby nie patrzeć TRY_PARSE w moich oczach rządzi 🙂

Podsumowanie

SQL Server 2012 przynosi wiele nowości, w tym także nowe funkcje skalarne. Mam nadzieję, że stworzony przez mnie ranking przybliżył wam część z nich na tyle, by powiększyć wachlarz konstrukcji T-SQL z których korzystacie.