Sprawdzanie ilości wolnego miejsca na dysku w SQL Server

Poniżej najprostsze zapytanie zwracajace ilość miejsca na dysku na którym znajdują się bazy danych. UWAGA! Zapytanie to nie działa w wersji SQL2008 !

EXEC MASTER..xp_fixeddrives

drive MB free
----- -----------
C     43948
D     16985
E     34184
F     6148
G     1514

(5 rows affected)

Innym zapytaniem możemy sprawdzić miejsce na dyskach powiązanych z naszymi bazami danych. Dyski na których nie ma baz nie będą wyświetlane.

SELECT DISTINCT
SUBSTRING(dovs.logical_volume_name,1,20) AS LogicalName,
SUBSTRING(dovs.volume_mount_point,1,20) AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

LogicalName          Drive                FreeSpaceInMB
-------------------- -------------------- -------------
SYSTEM               C:\                  43956

(1 row affected)

Po modyfikacji wyświetla ilość wolnego miejsca w powiązaniu z naszymi bazami.

SELECT DISTINCT
SUBSTRING(DB_NAME(dovs.database_id),1,25) DBName,
SUBSTRING(dovs.logical_volume_name,1,20) AS LogicalName,
SUBSTRING(dovs.volume_mount_point,1,20) AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

DBName                    LogicalName          Drive                FreeSpaceInMB
------------------------- -------------------- -------------------- -------------
baza_testowa              SYSTEM               C:\                  43954
AdventureWorks2012        SYSTEM               C:\                  43954
master                    SYSTEM               C:\                  43954
ReportServerTempDB        SYSTEM               C:\                  43954
msdb                      SYSTEM               C:\                  43954
ReportServer              SYSTEM               C:\                  43954
tempdb                    SYSTEM               C:\                  43954
Northwind                 SYSTEM               C:\                  43954
DataWarehouse             SYSTEM               C:\                  43954
FGRestoreTEST             SYSTEM               c:\                  43954
TriggerDatabase           SYSTEM               C:\                  43954
CDCDatabase               SYSTEM               C:\                  43954
SSISDB                    SYSTEM               C:\                  43954
model                     SYSTEM               C:\                  43954

(14 rows affected)

Mozemy też wyświetlić jeszcze bardziej szczegółowo w rozbiciu na konkretne pliki

SELECT DISTINCT
SUBSTRING(DB_NAME(dovs.database_id),1,25) DBName,
SUBSTRING(mf.physical_name,1,70) as PhysicalFileLocation,
SUBSTRING(dovs.logical_volume_name,1,15) AS LogicalName,
SUBSTRING(dovs.volume_mount_point,1,10) AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

DBName                    PhysicalFileLocation                                                   LogicalName     Drive      FreeSpaceInMB
------------------------- ---------------------------------------------------------------------- --------------- ---------- -------------
SSISDB                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf                   SYSTEM          C:\        43953
ReportServer              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServer.mdf             SYSTEM          C:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf                  SYSTEM          C:\        43953
SSISDB                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf                   SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2012.ndf                                                  SYSTEM          c:\        43953
ReportServerTempDB        C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB_log.ldf   SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FGRestoreTEST.mdf                                           SYSTEM          c:\        43953
msdb                      C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf                  SYSTEM          C:\        43953
AdventureWorks2012        C:\SQLData\AdventureWorks2012_log.ldf                                  SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2013.ndf                                                  SYSTEM          c:\        43953
Northwind                 C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\Northwind.ldf                SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FGRestoreTEST_log.ldf                                       SYSTEM          c:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf           SYSTEM          C:\        43953
DataWarehouse             C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\DataWarehouse_log.ldf        SYSTEM          C:\        43953
TriggerDatabase           C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\TriggerDatabase.mdf          SYSTEM          C:\        43953
baza_testowa              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\baza_testowa.mdf             SYSTEM          C:\        43953
ReportServerTempDB        C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServerTempDB.mdf       SYSTEM          C:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf                   SYSTEM          C:\        43953
AdventureWorks2012        C:\SQLData\AdventureWorks2012_Data.mdf                                 SYSTEM          C:\        43953
CDCDatabase               C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\CDCDatabase.mdf              SYSTEM          C:\        43953
model                     C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf                 SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2011.ndf                                                  SYSTEM          c:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf           SYSTEM          C:\        43953
msdb                      C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf                 SYSTEM          C:\        43953
DataWarehouse             C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\DataWarehouse.mdf            SYSTEM          C:\        43953
model                     C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf                    SYSTEM          C:\        43953
tempdb                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf           SYSTEM          C:\        43953
Northwind                 C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\Northwind.mdf                SYSTEM          C:\        43953
ReportServer              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\ReportServer_log.ldf         SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2010.ndf                                                  SYSTEM          c:\        43953
baza_testowa              C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\baza_testowa_log.ldf         SYSTEM          C:\        43953
FGRestoreTEST             c:\SQLData\FG2014.ndf                                                  SYSTEM          c:\        43953
master                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf                   SYSTEM          C:\        43953
CDCDatabase               C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\CDCDatabase_log.ldf          SYSTEM          C:\        43953
master                    C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf                  SYSTEM          C:\        43953
TriggerDatabase           C:\SQLData\MSSQL14.MSSQLSERVER\MSSQL\DATA\TriggerDatabase_log.ldf      SYSTEM          C:\        43953

(36 rows affected)
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

SQLCMD czyli łączymy się do zdalnego serwera SQL

Często podczas tworzenia zapytań musimy skorzystać z danych znajdujących się na jakimś innym serwerze SQL. Z reguły wykonujemy połączenie za pomocą Management Studio wykonujemy zapytanie a następnie wracamy do naszego serwera na którym obecnie pracujemy. Aby ułatwić pracę w Management Studio utworzono funkcję SQLCMD. Najpierw musimy ją uruchomić. Aby to zrobić otwieramy nowe okno Query a następnie z górnego menu Query wybieramy SQLCMD MODE.

Nim uruchomimy ten tryb zapytania korzystające z tej funkcji będą zwracać błędy

Po uruchomieniu zostaną one podświetlone kolorem szarym.

Jak już uruchomiliśmy nasze SQLCMD możemy próbować podłączyć się do serwera.
Polecenie

:connect localhost

spowoduje połączenie się do lokalnego serwera (tak jakbyśmy wykonywali zwykłego selecta bez włączania SQLCMD.

Polecenie

:CONNECT SQLSERVER1\INSTANCE1

Łączy się do serwera SQLSERVER1 do instancji INSTANCE1
Możemy po tym poleceniu wpisać np. jakiegoś selecta który wykona się właśnie na tym serwerze i jego bazach

Jeśli chcemy użyć uwierzytelnienia za pomocą konta i hasła w bazie danych wykonujemy:

:CONNECT SQLSERVER1 -U MyLoginName -P MyPassword

Jeśli chcemy wykonać polecenie w systemie operacyjnym np wyświetlić zawartość folderu wpisujemy

!!DIR
!!:GO

lub

!!%SystemRoot%\system32\cmd.exe /c dir c:\
!!:GO

Wykonanie selecta wygląda podobnie

!!sqlcmd /Q "exit(SELECT COUNT(*) FROM [Northwind].[dbo].[Employees])"

Niedługo podam jeszcze kilka innych przykładów. CDN… 🙂

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Extended Events w SQL Server

Poniżej omówię w skrócie kolejne narzędzie monitorujące zdarzenia w naszej bazie danych jakim jest Extended Events. Uruchamiamy Management Studio i znajdujemy narzędzie w zakładce Management. Rozwijamy je i w środku odnajdujemy gałąź Sessions.

Możemy teraz uruchomić kreatora naszej sesji jak to ładnie nazwali a co będzie naszym procesem monitorującym.

Albo uruchomić prostszą wersję

My uruchomimy wizarda

Wpisujemy nazwę naszej „sesji”.Mozemy zaznaczyć aby uruchamiała się razem z nasza instancją.

Na razie nie posiadamy żadnego szablonu więc wybieramy drugą opcję

Teraz wybieramy jakie zdarzenia w naszej instancji chcemy monitorować. W naszym przykładzie będą to podstawowe operacje na bazie danych (tworzenie, zmiana rozmiaru plików i usunięcie).

Po wybraniu przy użyciu strzałki dodajemy go do prawego panelu.

Następnie wybiramy jakie dane mają być wyświetlone po wykryciu zdarzenia.

Tu na razie nic nie zmieniamy. Możemy tu ustawić opcję filtrowania danych.

Tutaj ustawimy by zarejestrowane dane zostały zapisane do pliku na dysku w celu późniejszej analizy. Określamy oprócz ścieżki do pliku, jego wielkość, ilość plików (po wykorzystaniu maksymalnej liczny zostaną one nadpisane).

Małe podsumowanie

Przy pomocy przycisku SCRIPT możemy utworzyć skrypt do utworzenia naszej sesji


CREATE EVENT SESSION [MySession_1] ON SERVER
ADD EVENT sqlserver.database_created(
ACTION(sqlos.task_time,sqlserver.client_hostname,sqlserver.database_id,
sqlserver.database_name,sqlserver.nt_username,
sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.database_dropped(
ACTION(sqlos.task_time,sqlserver.client_hostname,sqlserver.database_id,
sqlserver.database_name,sqlserver.nt_username,
sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.database_file_size_change(
ACTION(sqlos.task_time,sqlserver.client_hostname,sqlserver.database_id,
sqlserver.database_name,sqlserver.nt_username,
sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.databases_log_file_size_changed(
ACTION(sqlos.task_time,sqlserver.client_hostname,sqlserver.database_id,
sqlserver.database_name,sqlserver.nt_username,
sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file
(SET filename=N'C:\SQLData\MySession_1.xel',max_file_size=(30))
WITH (STARTUP_STATE=OFF)
GO

No i kończymy tworzenie. Na koniec zaznaczamy aby sesja została uruchomiona zaraz po utworzeniu oraz aby zdarzenie zostało wyświetlone jak tylko się wydarzy.

Po zamknięciu kreatora otworzy się okno w którym będą wyświetlane wykryte zdarzenia.

W drzewie Session pojawi się nasz nowy element

Jakby okno ze zdarzeniami nie było otwarte możemy go zawsze otworzyć z menu

Tak więc mamy otwarte okno monitorujące nasze zdarzenia (na razie puste)

Pora na kilka „zdarzeń”. Na początek utworzymy nową bazę.

Zmienimy rozmiar pliku MDF

Zmienimy rozmiar pliku LDF

A teraz zalogujemy się na innego użytkownika i usunięmy naszą testową bazę.

Jak widać wszystko jest rejestrowane (kto usunął, kiedy i jakiego polecenia użył).
Tak jak wcześniej skonfigurowaliśmy wszystkie zdarzenia zostały zapisane do pliku XEL.

Naszą sesję możemy w każdej chwili zatrzymać aby dalej nie monitorowała zdarzeń.

W momencie kiedy otwarte jest okno wyświetlające nasze zdarzenia w górnym menu pojawia się dodatkowa opcja „Extended Events”.

Jest tu kilka opcji do skonfigurowania.

Jeśli naciśniemy poniższy przycisk, możemy wybrać plik XEL do wczytania do okna ze zdarzeniami.

Możemy też wyeksportować nasze ustawienia sesji jako szablon XML do wykorzystania przy tworzeniu nowych sesji.

Przy tworzeniu nowej sesji p oprostu wybieramy w template nasza szablon

Jak już wszystko przetestowaliśmy, zatrzymujemy sesję a następnie możemy ją usunąć poleceniem

DROP EVENT SESSION [MySession_1] ON SERVER

lub z menu

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Mirroring w SQL Server

Nie będę się rozpisywał do czego to służy, bo od tego są książki. Pokażę jak w prosty sposób to skonfigurować.
Po pierwsze na obu serwerach (Primary i Secondary czyli Mirror) serwisy SQL muszą być uruchamiane z kont lokalnych (w naszym przykładzie SQLService). Najlepiej stworzyć specjalne konta dla naszych serwisów.

Pamiętajmy że po zmianie konta z którego będzie uruchamiany serwis należy je zrestartować.

Utworzymy mirror bazy. Logujemy się na oba serwery. Na serwerze PRIMARY uruchamiamy konfigurację Mirroringu.

lub w inny sposób wchodząc we właściwości bazy

i pierwssze problemy 🙂

Zwróciło błąd bo baza jest w trybie Recowery Simple. Musimy zmienić na Recovery Full we właściwościach bazy.

lub poleceniem

USE [master]
GO
ALTER DATABASE [Northwind] SET RECOVERY FULL WITH NO_WAIT
GO

Commands completed successfully.

Teraz trzeba wykonać pełny backup naszej bazy oraz drugi backup logów transakcyjnych (najlepiej do tego samego pliku z funkcję Append). Następnie nalezy przenieść plik backupu na nasz drugi serwer i tam odtworzyć bazę.
UWAGA !!! bazę odtwarzamy z trybie NORECOVERY

Nasze bazy będą po tym wyglądały tak:


Jak widać druga baza jest w trybie RESTORING.

Pora uruchomić mirroring.

Uruchomi się kreator mirroringu


kreator pyta nas czy będziemy wykorzystywali „świadka”. Jest to specjalny serwer który będzie monitorował nasze serwery z mirrorem. Jeśli tylko straci kontakt z serwerem automatycznie przełączy mirror na drugi serwer. Aby połaczyć serwery w mirroringu potrzebna jest wersja Stabdard lub Enterprise. Na świadku musi być zainstalowany SQL ale może to być nawet wersja Express (SQL służy tylko do komunikacji więc nie ma specjalnych wymagań).

Świadek będzie wiec zostawiamy ptaszka 🙂

Tu nic nie zmieniamy (w sumie to tak tylko informacyjnie jest podane).

Tu łączymy się z serwerem SECONDARY

A tu z serwerem „świadka” WITNESS

W tym oknie wpisujemy konto za pomocą którego będziemy autoryzowani na naszych trzech serwerach (Primary,Secondary i Witness). Może to być jedno wspólne konto domenowe lub dla każdego inne konto (te same z którego uruchamialiśmy serwisy SQL).

No i kończymy

Pora uruchomić nasz skonfigurowany Mirror

Potrwa to kilka chwil ale jeśli wszystko jest OK powinno się pojawić takie okno.

Jak można zauważyć mirroring komunikuje się z serwerami za pomocą portów 5022, 5023 i 5024. Taka konfiguracja występuje gdy uruchamiamy ją na jednym serwerze (jak w naszym przykładzie). Nie jest to dobre rozwiązanie bo jak padnie nam serwer znikają obie bazy wraz ze świadkiem. Najlepiej to skonfigurować na trzech różnych serwerach i wtedy mirror będzie się komunikował po porcie 5022.

Nasze bazy powinny zmienić swój status na Principal/Synchronized i Secondary/Synchronized/Restoring.

Jest OK ale dla pewności możemy uruchomić Database Mirroring Monitor.

Proszę się przerazić jak zobaczycie czerwone ikonki alarmu. Po kilku chwilach gdy bazy się zsynchronizują wszystko się ustabilizuje.

Bazy teraz w momencie awarii powinny się przełączyć na drugą. Oczywiście bazy można ręcznie przestawiać. Do tego służy przycisk FAILOVER

Po jego naciśnięciu otrzymamy komunikat że połączenia z bazą PRIMARY zostaną zerwane i czy chcemy tego.

I już mamy bazę na serwer 2 jako PRIMARY

Jesli byśmy wyłączyli serwis SQL na serwerze 1 to baza na serwerze 2 (obecnie PRIMARY) straci połączenie.

UWAGA!!! Trzeba na to szybko reagować, gdyż na bazie PRIMARY będą zbierane logi transakcyjne do czasu usunięcia awarii. Więc jeśli planujemy kilkudniową awarię to lepiej jest usunąć mirroring na tej bazie i po naprawie serwera 1 ponownie ją skonfigurować.

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

LinkedServer czyli podłączamy się do drugiego serwera

Utworzymy sobie linked server

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'MICEK1968\MSSQLSERVER1',
@srvproduct=N'SQL Server' ;
GO

Commands completed successfully.

SELECT FirstName, LastName, BirthDate
FROM [MICEK1968\MSSQLSERVER1].[Northwind1].[dbo].[Employees]
GO

Taki linked server będzie działał jeśli posiadamy na drugim serwerze uprawnienia.

Możemy do tego też wykorzystać specjalnie utworzone na serwerze SQL konta dla linked server. Za ich pomocą możemy kontrolować co użytkownik będzie widział za pomocą udostępnionego linked serwera.

usuwamy utworzonego linked serwera

USE [master]
GO
EXEC master.dbo.sp_dropserver @server=N'MICEK1968\MSSQLSERVER1', @droplogins='droplogins'
GO

Commands completed successfully.

Najpierw zakładamy użytkownika bazodanowego na naszym drugim serwerze (z którego będziemy zbierać dane).

Na razie nie dajemy mu żadnych uprawnień.

Na pierwszym serwerze dodajemy Linked Server

Wpisujemy dane naszego serwera z danymi

wpisujemy naszego utworzonegop usera

Sprawdzamy co widzi nasz Linked Server 🙂

Niewiele widzi więc pora mu udostępnić naszą bazę (w tym przykładzie to TSQL2012). Nadajemy uprawnienia dla usera LINK_USER. Zaznaczamy naszą bazę ale nie przyznajemy żadnych uprawnień na niej gdzyż będziemy chcieli udostępnić tylko jedna tabelę z tej bazy dla naszego Linked Servera

Linked Server widzi już naszą bazę ale bez tabel

Na drugim serwerze wchodzimy do bazy TSQL2012 i wybieramy naszą tabelę.

We właściwościach nadajemy uprawnienia SELECT dla LINK_USER

Sprawdzamy czy po odświeżeniu w Linked Server pojawi się nasza tabela

Teraz sprawdzimy czy Linked Server działa.

SELECT FirstName, LastName, BirthDate
FROM [MICEK1968\MSSQLSERVER1].[TSQL2012].[HR].[Employees]
GO

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Synonimy w SQL Server

Synonimy to nic innego jak odnośniki (skrócone nazwy) do naszych obiektów w bazie. Ułatwiają one nam pracę np. przy częstym wykorzystywaniu tych samych długich nazw baz, schematów i tabel.

SELECT TOP (10) [BusinessEntityID]
,[FirstName]
,[LastName]
,[ModifiedDate]
FROM [AdventureWorks2012].[Person].[Person]

Zamiast wpisywać za każdym razem [AdventureWorks2012].[Person].[Person] utworzymy synonim [OSOBY_2012], które będą się odwoływać do naszej tabeli

Możemy to zrobić poleceniem:

USE [AdventureWorks2012]
GO
CREATE SYNONYM [dbo].[OSOBY_2012] FOR [MICEK1968].[AdventureWorks2012].[Person].[Person]
GO

Commands completed successfully.

Lub w Managemenct Studio

a następnie wypełniamy odpowiednio:

Pozostałe zakładki zostawiamy bez modyfikacji.

Po utworzeniu synonimu sprawdzamy czy działa 🙂

SELECT TOP (10) [BusinessEntityID]
,[FirstName]
,[LastName]
,[ModifiedDate]
FROM OSOBY_2012;

Działa więc możemy go usunąć

USE [AdventureWorks2012]
GO
DROP SYNONYM [dbo].[OSOBY_2012]
GO

Commands completed successfully.
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Triggery DML w SQL Server

DML triggery (Data Manipulation Language – czyli INSERT/UPDATE/DELETE) są to obiekty bazodanowe w MS SQL Server, których implementacja jest wykonywana po lub przed wystąpieniem któregoś z poniższych trzech poleceń (lub ich kombinacji):
INSERT – wstawianie danych
DELETE – usunięcie danych
UPDATE – aktualizacja danych

Niezależnie od ilości modyfikowanych wierszy przez poszczególne polecenia, trigger do obsługi danego zdarzenia będzie wywołany tylko raz.Jeżeli żaden wiersz w tabeli nie będzie zmodyfikowany trigger obsługujący dane zdarzenie też zostanie wywołany.

Triggery można tworzyć (CREATE TRIGGER), usuwać (DROP TRIGGER), deaktywowac (ENABLE TRIGGER), aktywować (DISABLE TRIGGER).

Triggery DML w MS SQL Server mogą być powiązane zarówno z tabelą jak i widokiem.W kontekście triggerów DML, czyli powiązanych z modifikacją danych możemy wyróżnić dwa typy triggerów:
INSTEAD OF i AFTER

Typ Triggera Obsługa tabeli Obsługa widoku
AFTER Tak Nie
INSTEAD OF Tak Tak

Informacje o statusie DML triggera, jego typie i powiązaniach można odczytać z dwóch tabel systemowych sys.triggers i sys.trigger_events.

SELECT t.*, te.*
FROM sys.triggers t
INNER JOIN sys.trigger_events te
ON t.object_id = te.object_id

Tabele Inserted i Deleted nie mogą być modyfikowane, mogą być wykorzystywane tylko do odczytu.

Inserted – zawiera dane wstawione do tabeli/widoku poleceniem INSERT, lub nową wesję danych zmodyfikowanych poleceniem UPDATE.

Tabela Deleted – zawiera dane usunięte z tabeli/widoku poleceniem DELETE, lub oryginalne wartości danych przed zmianami wprowadzonymi poleceniem UPDATE.

Tabel Updated – NIE ISTNIEJE

Na początek stworzymy jakiegoś małego i prostego triggera typu AFTER

use master;
GO
CREATE DATABASE TriggerDatabase;
GO

Commands completed successfully.

use TriggerDatabase;
GO
CREATE TABLE dbo.Customers
(
CustomerName Varchar(10) NOT NULL
)
GO

Commands completed successfully.

CREATE TRIGGER dbo.Customers_AfterDelete
ON dbo.Customers
AFTER DELETE
AS
BEGIN
PRINT 'After Delete'
END
GO

Commands completed successfully.

DELETE FROM Customers
WHERE 1=0
GO
DROP TRIGGER dbo.Customers_AfterDelete
GO

After Delete

(0 rows affected)

To był trigger typu AFTER. Teraz przykład trigera typu INSTEAD OF.

CREATE VIEW dbo.vCustomers
AS
SELECT CustomerName FROM dbo.Customers
GO

Commands completed successfully.

CREATE TRIGGER dbo.vCustomers_AfterDelete
ON dbo.vCustomers
INSTEAD OF DELETE
AS
BEGIN
DELETE c
FROM dbo.Customers c
INNER JOIN deleted d
ON c.CustomerName = d.CustomerName
END
GO

Commands completed successfully.

INSERT INTO dbo.Customers(CustomerName)
VALUES('Jef')
GO

(1 row affected)

select * from dbo.Customers

CustomerName
------------
Jef

(1 row affected)

DELETE vCustomers WHERE CustomerName = 'Jef'
GO

(1 row affected)

(1 row affected)

DROP TRIGGER dbo.vCustomers_AfterDelete
GO

Commands completed successfully.
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Funkcje w SQL Server

SELECT [BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[JobTitle]
,[BirthDate]
,[HireDate]
FROM [AdventureWorks2012].[HumanResources].[Employee]

Tworzymy funkcję wyszukującą tylko rekordy z podanym zawodem (JobTitle):

CREATE FUNCTION Sales.ufn_MojaFunkcja (@Job_Title varchar(20))
RETURNS TABLE
AS
RETURN
(
SELECT [BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[JobTitle]
,[BirthDate]
,[HireDate]
FROM [AdventureWorks2012].[HumanResources].[Employee]
WHERE [JobTitle] = @Job_Title
);
GO

Commands completed successfully.

Wywołujemy funckję:

SELECT * FROM Sales.ufn_MojaFunkcja ('Marketing Specialist');

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Sekwencje w SQL Server

Od wersji SQL Server 2012 udostępniono mechanizm sekwencji. Sekwencja jest mechanizmem podobnym do mechanizmu Identity (AutoIncrement wierszy przy tworzeniu tabel). Sekwencje jednak nie są związane z tabelą i możemy z nich korzystać w dowolnym momencie pobierając kolejną wartość.

Aby utworzyć sekwencję należy wydać polecenie:

CREATE SEQUENCE dbo.MojaSekwencja
AS INT
MINVALUE 1
NO MAXVALUE
START WITH 1;

Commands completed successfully.

Założona sekwencja ma minimalną wartość 1 i będzie się powiększała o 1 do nieskończoności.
Sekwencję możemy również założyć korzystając z Management Studio.

Ja już jest stworzona aby pobrać wartość wykonujemy:

use [baza_testowa]
SELECT NextOrderID = NEXT VALUE FOR dbo.MojaSekwencja;

NextOrderID
-----------
1

(1 row affected)

Każde kolejne uruchomienie powoduje zwrócenie kolejnej wartości zgodnie z zdefiniowaną sekwencją.

Teraz utworzymy tabelę i spróbujemy za pomocą sekwencji wstawić kilka wierszy:

create table testowa_SEQ (id int not null primary key,opis varchar(255));

Commands completed successfully.

insert into testowa_SEQ (id,opis) values (NEXT VALUE FOR [MojaSekwencja],'jakis opis');
go 10

Beginning execution loop

(1 row affected)
...
(1 row affected)
Batch execution completed 10 times.

SELECT [id],[opis] FROM [baza_testowa].[dbo].[testowa_SEQ]

id          opis
----------- --------------------------
1           jakis opis
2           jakis opis
3           jakis opis
4           jakis opis
5           jakis opis
6           jakis opis
7           jakis opis
8           jakis opis
9           jakis opis
10          jakis opis

(10 rows affected)

Wartość sekwencji możemy „wyzerować” do dowolnej wartości

use [baza_testowa]
SELECT NextOrderID = NEXT VALUE FOR dbo.MojaSekwencja

NextOrderID
--------------------
3

(1 row affected)

Jeśli będziemy chcieli ustawić wartość powyżej zadeklarowanej maksymalnej wartości sekwencji otrzymamy komunikat

ALTER SEQUENCE [MojaSekwencja]
RESTART WITH 198;

Msg 11703, Level 16, State 1, Line 8
The start value for sequence object 'MojaSekwencja' must be between the minimum and maximum value of the sequence object.

Przy ustawieniu nowej wartości powyżej „obecnej” otrzymamy

ALTER SEQUENCE [MojaSekwencja]
RESTART WITH 98;

The sequence object 'MojaSekwencja' cache size is greater than the number of available values.

Ustawione mamy maksymalną wartość na 100 więc po kilku uruchomieniach Selecta otrzymamy

SELECT NextOrderID = NEXT VALUE FOR [MojaSekwencja];

Msg 11728, Level 16, State 1, Line 19
The sequence object 'MojaSekwencja' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Możemy wtedy zresetować wartość lub ustawić zapętlenie (opcja CYCLE) lub

CREATE SEQUENCE dbo.MojaSekwencja
AS INT
MINVALUE 1
MAXVALUE 100
cycle
START WITH 1;

Commands completed successfully.

Niestety po wykonaniu insertów do tabeli a następnie usunięciu wierszy (podobnie jak w Identity) powstaną „dziury”. Nie ma możliwości sprawdzenia brakujących wartości. Możemy jedynie sprawdzić aktualną wartość sekwencji wykonując:

SELECT current_value
FROM sys.sequences
WHERE name = 'MojaSekwencja';

current_value
-------------------------
43

(1 row affected)

więcej o sekwencjach znajdziemy na
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

SQL Raport Builder – krok drugi

Jak już umiemy wykonywać proste tabeli i wykresy możemy pobawić się w łączenie tych elementów oraz nauczyć się wykorzystywać zaawansowane funkcje Raport Buildera. Np spróbujemy zrobimy tabelkę pokazującą graficznie zajętość logów transakcyjnych bazy.

Po uruchomieniu Report Buildera tworzymy DataSource (do serwera localhost i bazy master) oraz DataSet gdzie w oknie query wpisujemy polecenie zwracające zajętość plików logów transakcyjnych czyli DBCC SQLPERF(logspace).

Polecenie zwraca cztery kolumny w tym jedną „status” której nie będziemy wykorzystywali.

Usuwamy ją a w jej miejsce dodajemy nowe pole (Field) wybierając z menu „Add Calculated Field”.


Polem tym będzie wyliczona procentowa wartość wolnej przestrzeni, gdyż polecenie zwraca tylko procentową ilość użytego miejsca w pliku. Wpisujemy nazwę naszego nowego pola oraz treść wyrażenia obliczającego nasze pole.



Następnie wstawiamy tabelkę i wklejamy do niej nasze dane. Sprawdzamy jak to wygląda.

Trochę nieciekawie pokazuje nasze dane. Trzeba usunąć miejsca po przecinku.
Zmieniamy to we właściwościach pola tekstowego w tabelce.


Sprawdzamy jak to wygląda po zmianie.

Jest lepiej. Pora wkleić trochę kolorów do naszej tabelki. najpierw dodajemy jeszcze jedną kolumnę w naszej tabeli w której dodamy kolorowe wykresiki 🙂

Następnie dodajemy poziomy wykres „DATA BAR”

Wybieramy typ wykresu.


Zostanie on wklejony poniżej tabelki więc musimy go przeciągnąć do niej. następnie przeciągamy nasze pola do okien parametrów naszego DATA BAR’a

Wchodzimy do właściwości wykresu

i ustawiamy maksymalną wartość wykresu.


I mamy piękne kolorowe wykresiki. Możemy im we właściwościach pozmieniać kolory, obramowania i inne wizualne ustawienia.

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz