/*

Włączenie mechanizmu śledzenia zmian w bazie danych

*/

ALTER DATABASE AdventureWorks2008
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO

/*

Włączenie mechanizmu śledzenia zmian w tabeli

*/

USE AdventureWorks2008
GO
ALTER TABLE HumanResources.Employee
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

/*

Wyłączenie mechanizmu śledzenia zmian 

*/

USE AdventureWorks2008
GO
ALTER TABLE HumanResources.Employee
DISABLE CHANGE_TRACKING;
GO
ALTER DATABASE AdventureWorks2008
SET CHANGE_TRACKING = OFF;


/*

Inicjalizacja  danych - mechanizm sledzenia zmian

*/

--aktualna wersja zmian uzyskana przy pomocy funkcji CHANGE_TRACKING_CURRENT_VERSION
DECLARE @Wersja_Zmian INT = CHANGE_TRACKING_CURRENT_VERSION();
--aktualne dane z tablicy źródłowej
SELECT
  E.BusinessEntityID as [Klucz główny]
 ,E.LoginID  as [Login]
 ,E.VacationHours as [Pozostałe dni wakacji]
 ,E.SickLeaveHours as [Pozotałe dni zwolnienia lekarskeigo]
FROM HumanResources.Employee E
WHERE E.JobTitle = 'Sales Representative'
ORDER BY E.LoginID;


/*

Pobieranie zmian 

*/
--wykonujemy zmiany w tablicy HumanResources.Employee
UPDATE HumanResources.Employee 
SET VacationHours+=2
WHERE LoginID='adventure-works\david8';
GO
UPDATE HumanResources.Employee 
SET SickLeaveHours=0
WHERE LoginID='adventure-works\garrett1';
GO
--pobieramy informacje o zmianach
SELECT
    CT.BusinessEntityID as [Klucz glowny]
    ,CASE CT.SYS_CHANGE_OPERATION
         WHEN 'I' THEN 'insert'
         WHEN 'U' THEN 'update'
         WHEN 'D' THEN 'delete'
  END as [Operacja]
    , CT.SYS_CHANGE_COLUMNS as [Mapa bitowa zmienionej kolumny] 
FROM CHANGETABLE(CHANGES HumanResources.Employee, @Wersja_Zmian) CT;
GO


--pobieramy aktualną wersję zmian
DECLARE @Aktualna_Wersja_Zmian INT = CHANGE_TRACKING_CURRENT_VERSION();
--pobieramy informacje , wykorzystując POPRZEDNIO uzyskaną wersję zmian
SELECT
  E.LoginID
  ,E.VacationHours
  ,E.SickLeaveHours
  ,CT.BusinessEntityID as [Klucz glowny]
  ,CASE CT.SYS_CHANGE_OPERATION
         WHEN 'I' THEN 'insert'
         WHEN 'U' THEN 'update'
         WHEN 'D' THEN 'delete'
  END as [Operacja]
 ,CT.SYS_CHANGE_COLUMNS as [Mapa bitowa zmienionej kolumny]
FROM HumanResources.Employee E JOIN
  CHANGETABLE(CHANGES HumanResources.Employee, @Wersja_Zmian) AS CT
ON E.BusinessEntityID = CT.BusinessEntityID
WHERE E.JobTitle = 'Sales Representative'
ORDER BY E.LoginID; 
--zamieniamy wersje zmian, bo w następnym  zapytaniu należy skorzystać z 
--aktualnej wersji
SET @Wersja_Zmian  = @Aktualna_Wersja_Zmian;
GO


/*

Sprawdzanie ważności numeru zmian

*/

-- sprawdzamy, czy możemy wykorzystać dane zawarte w tablicy zmian  
IF (@Wersja_Zmian < 
  CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('HumanResources.Employee'))
BEGIN
-- aplikacja kliencka nie moze może  pobrać zmian
-- musimy ponownie wykonać inicjalizację danych
END;
GO

/*

Śledzenie zmian w kolumnach

*/
DECLARE @VacationHoursID INT = COLUMNPROPERTY(
    OBJECT_ID('HumanResources.Employee'),'VacationHours', 'ColumnId')

SELECT
  E.LoginID
  ,E.VacationHours
  ,E.SickLeaveHours
  ,CASE
         WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(@VacationHoursID, CT.SYS_CHANGE_COLUMNS) = 1      THEN VacationHours
         ELSE NULL
         END AS CT_PozostaleWakacje
  ,CHANGE_TRACKING_IS_COLUMN_IN_MASK(@VacationHoursID, CT.SYS_CHANGE_COLUMNS) AS CT_PozostaleWakacje_Zmienione
  , CT.SYS_CHANGE_COLUMNS
FROM HumanResources.Employee E JOIN
  CHANGETABLE(CHANGES HumanResources.Employee, @Wersja_Zmian) AS CT
ON E.BusinessEntityID = CT.BusinessEntityID
WHERE E.JobTitle = 'Sales Representative'
AND CT.SYS_CHANGE_OPERATION = 'U'
ORDER BY E.LoginID; 


/*

Konfiguracja mechanizmu przechwytywania zmian

*/

use AdventureWorks2008
GO
EXEC sys.sp_cdc_enable_db;

/* -- opis składni polecenia sys.sp_cdc_enable_table 
sys.sp_cdc_enable_table 
    [ @source_schema = ] 'source_schema', 
    [ @source_name = ] 'source_name' ,
    [ @role_name = ] 'role_name'
    [,[ @capture_instance = ] 'capture_instance' ]
    [,[ @supports_net_changes = ] supports_net_changes ]
    [,[ @index_name = ] 'index_name' ]
    [,[ @captured_column_list = ] 'captured_column_list' ]
    [,[ @filegroup_name = ] 'filegroup_name' ]
*/

EXEC sys.sp_cdc_enable_table 
    @source_schema = N'Production'
  , @source_name = N'Product'
  , @role_name = NULL


EXEC sys.sp_cdc_enable_table
    @source_schema = N'Production'
  , @source_name = N'Product'
  , @role_name = NULL
  ,@capture_instance = 'Production_Product2'

/*-- opis składni polecenia sys.sp_cdc_disable_table 
sys.sp_cdc_disable_table [ @source_schema = ] 'source_schema', 
      [ @source_name = ] 'source_name' ,
      [ ,[@capture_instance = ] 'capture_instance' | 'all'
*/

USE AdventureWorks2008
GO
EXEC sys.sp_cdc_disable_db;


/*

Praca z mechanizmem przechwytywania zmian

*/
--zmienne @from_lsn oraz @to_lsn  określają  zakres zmian
use AdventureWorks2008
GO
DECLARE @from_lsn binary(10),@to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources.Employee');
SET @to_lsn = sys.fn_cdc_get_max_lsn();

--funkcja sys.fn_cdc_get_column_ordinal zwróci numer kolumny, dla której należy  
--sprawdzić, czy były w niej zmiany
DECLARE @nr_kolumny bit;
SET @nr_kolumny = sys.fn_cdc_get_column_ordinal('HumanResources.Employee', 'Status');
SELECT @nr_kolumny

--funkcja fn_cdc_is_bit_set sprawdza, czy zmieniono wartość w kolumnie , bazując na zapisanej masce
SELECT * 
,sys.fn_cdc_is_bit_set (@nr_kolumny, __$update_mask) as [Zmieniono]
FROM  cdc.fn_cdc_get_net_changes_dbo_Employee (@from_lsn, @to_lsn, 'all with mask') 


DECLARE @from_lsn binary(10) BIGINT,@to_lsn binary(10) BIGINT;
--wyznacz najmniejszy i najmniejszy zarejestrowany wpis do tablicy zmian
SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources.Employee');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
-- wykonaj zapytanie
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all');


DECLARE @from_lsn binary(10),@to_lsn binary(10);
--wyznacz najmniejszy wpis, ale więekszy  od poprzedniego maksimum. Funkcja 
--sys.fn_cdc_increment_lsn dodaje 1 do numeru ostatniego LSN
SET @from_lsn = sys.fn_cdc_increment_lsn(@to_lsn); 
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn,@to_lsn,'all');



DECLARE @to_time datetime, @max_time datetime;
SET @to_time = '12:00:00 31-10-2008';
SET @from_lsn = sys.fn_cdc_increment_lsn(@to_lsn);
RETRY:
SELECT @max_time = sys.fn_map_lsn_to_time (sys.fn_cdc_get_max_lsn());
    IF (@to_time > @max_time)
        BEGIN
            WAITFOR DELAY '00:10:00'
            GOTO RETRY
        END
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @to_time);
SELECT * from cdc.fn_cdc_get_all_changes_dbo_Pracownicy(@from_lsn, @to_lsn, 'all');

/*-- opis operatora użytego w funkcji mapującej czas na LSN
<relational_operator> ::=
{  largest less than
 | largest less than or equal
 | smallest greater than
 | smallest greater than or equal
}
*/

sys.sp_cdc_get_ddl_history [ @capture_instance = ] 'capture_instance'


/*

Kompresja wierszy

*/

USE R04
GO
CREATE TABLE Tab(col1 int, col2  char(1000) )
WITH (DATA_COMPRESSION = ROW);

USE R04
GO
ALTER TABLE Tab
REUBUILD WITH (DATA_COMPRESSION=ROW);


/*

Kompresja stron danych

*/
USE R04
GO
CREATE TABLE Tab2(col1 int, col2  char(1000) )
WITH (DATA_COMPRESSION = PAGE);
GO  
ALTER TABLE Tab
REUBUILD WITH (DATA_COMPRESSION=PAGE);

/*

Szacowanie stopnia kompresji 

*/

/*-- opis składni procedury sp_estimate_data_compression_savings 
sp_estimate_data_compression_savings 
  [@schema_name = ] 'schema_name'  
  , [@object_name = ] 'object_name' 
  , [@index_id = ] index_id 
  , [@partition_number = ] partition_number 
  , [@data_compression = ] 'data_compression' 
[;]
*/

USE AdventureWorks2008
GO
EXEC sp_estimate_data_compression_savings 'Sales', 'SalesOrderDetail', NULL, NULL, 'PAGE' ;


/*

Indeksy pełnotekstowe

*/

USE AdventureWorks2008
CREATE FULLTEXT CATALOG ftKatalog;


/*

Predykaty CONTAINS i FREETEXT

*/

SELECT FirstName 
FROM Person.Person
WHERE CONTAINS(*,'"additional phone"'); 


SELECT FirstName 
FROM Person.Person
WHERE CONTAINS(*,'phone')
AND Title = 'Mr.'; 


SELECT FirstName 
FROM Person.Person
WHERE CONTAINS(*,'"family" AND "bicycles"');


SELECT FirstName 
FROM Person.Person
WHERE CONTAINS(*,'phone NEAR urgent');


SELECT FirstName 
FROM Person.Person
WHERE CONTAINS(*,'FORMSOF(INFLECTIONAL,bicycle)');


SELECT FirstName 
FROM Person.Person
WHERE FREETEXT(*,'"additional phone"'); 

/*

Funkcje CONTAINSTABLE i FREETEXTTABLE

*/

SELECT * 
FROM CONTAINSTABLE(Person.Person,*,'"additional phone"')
ORDER BY RANK DESC; 


SELECT P.FirstName , CT.RANK
FROM CONTAINSTABLE(Person.Person,*,'"additional phone"') AS CT
JOIN Person.Person AS P ON CT.[KEY]=P.BusinessEntityID
WHERE CT.RANK >100; 


SELECT P.FirstName , CT.RANK
FROM CONTAINSTABLE(Person.Person,*,'ISABOUT (bicycles WEIGHT (.6), road WEIGHT (.2), Mountain WEIGHT (.2))') AS CT
JOIN Person.Person AS P ON CT.[KEY]=P.BusinessEntityID
ORDER BY CT.RANK DESC; 

/*

Lista pustych słów i wybór języka

*/

SELECT special_term,display_term 
FROM sys.dm_fts_parser (' "I am an experienced and versatile" ', 1033, 0, 0);


SELECT special_term,display_term 
FROM sys.dm_fts_parser (' "I am an experienced and versatile" ', 1033, NULL, 0);


SELECT special_term,display_term 
FROM sys.dm_fts_parser (' "multi-million" ', 1033, 0, 0);


SELECT special_term,display_term
FROM sys.dm_fts_parser (' "multi-million" ', 1040, 0, 0);


/*

Atrybut SPARSE

*/
  USE AdventureWorks2008
  GO
  ALTER TABLE Sales.SalesOrderHeader
  ALTER COLUMN Comment nvarchar(128) SPARSE NULL;
  GO


SELECT  SC.name as [Nazwa kolumny]
         , is_SPARSE 
FROM sys.columns SC
JOIN sys.tables ST 
         ON ST.object_id = SC.object_id
WHERE ST.name= 'SalesOrderHeader';
GO

/*

Praca z kolumnami z atrybutem column set 

*/
USE tempdb
GO
CREATE TABLE Table1
(
  i int SPARSE null 
  ,j int
  ,k int SPARSE null 
  ,d char(5) 
  ,col_set xml column_set FOR ALL_SPARSE_COLUMNS
);
GO

INSERT INTO Table1  (i,j,k,d)
VALUES  (1,1,15,'abcde'),
        (NULL,2,15,'abcde'),
        (NULL,3,NULL,'abcde'),
        (2,4,15,'abcde');

INSERT INTO Table1 (j,d,col_set)
VALUES (10,'ex.1','<i>7</i>');

SELECT i,j,k,d
FROM Table1;


SELECT *
FROM Table1;
GO


--UWAGA, było: i=1,k=15: będzie i=100 i k=
UPDATE Table1
SET col_set='<k>200</k><i>100</i>' 
WHERE i=1;

SELECT i,k,col_set
FROM Table1;


/*

Tworzenie indeksu filtrowanego

*/
USE AdventureWorks2008
GO
CREATE NONCLUSTERED INDEX [idx_ncl_FICarrierTrackingNumber] 
ON [Sales].[SalesOrderDetail] 
(
  [SalesOrderID] ASC
)
INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount]) 
WHERE ([CarrierTrackingNumber] IS NOT NULL)
ON [PRIMARY];
GO


use tempdb
GO
CREATE TABLE Tabela
(
  a  int
  ,b int
);
GO
CREATE NONCLUSTERED INDEX Index1 
ON dbo.Tabela(a)
WHERE b = '1';

CREATE NONCLUSTERED INDEX Index1 
ON dbo.Tabela(a)
WHERE b = CAST('1' as int);

/*

Indeksy filtrowane a pełnotablicowe – krótkie porównanie

*/

USE AdventureWorks2008
GO
CREATE NONCLUSTERED INDEX [idx_ncl_FICarrierTrackingNumber] 
ON [Sales].[SalesOrderDetail] 
(
  [SalesOrderID] ASC
)
INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount]) 
WHERE ([CarrierTrackingNumber] IS NOT NULL)
ON [PRIMARY];
GO


use AdventureWorks2008
GO
CREATE NONCLUSTERED INDEX [idx_ncl_CarrierTrackingNumber] 
ON [Sales].[SalesOrderDetail] 
(
  [CarrierTrackingNumber] ASC,
  [SalesOrderID] ASC
)
INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount]) 
ON [PRIMARY];
GO



SELECT 
  [SalesOrderID] 
  ,[ProductID]
  ,[UnitPrice]
  ,[UnitPriceDiscount]
FROM  [Sales].[SalesOrderDetail]
WHERE [CarrierTrackingNumber] IS NOT NULL;
GO

/*

Rekomendacje dotyczące tworzenia indeksu filtrowanego

*/
SELECT 
  [SalesOrderID]
  ,[ProductID]
  ,[UnitPrice]
  ,[UnitPriceDiscount]
FROM [Sales].[SalesOrderDetail]
WHERE [CarrierTrackingNumber] IS NOT NULL
AND SalesOrderID='63155';



SELECT
   [SalesOrderID] 
  ,[ProductID]
  ,[UnitPrice]
  ,[UnitPriceDiscount]
  ,ModifiedDate -- TA KOLUMNA NIE JEST ZAWARTA W INDEKSIE FILTROWANYM 
FROM [Sales].[SalesOrderDetail]
WITH ( INDEX ( idx_ncl_FICarrierTrackingNumber) )
WHERE [CarrierTrackingNumber] IS NOT NULL
AND SalesOrderID='63155';
GO


CREATE NONCLUSTERED INDEX [idx_ncl_FICarrierTrackingNumber] 
ON [Sales].[SalesOrderDetail] 
(
  [SalesOrderID] ASC
)
INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount]) 
WHERE ([CarrierTrackingNumber] IS NOT NULL)
ON [PRIMARY]
GO


SELECT 
  [SalesOrderID] 
  ,[ProductID]
  ,[UnitPrice]
  ,[UnitPriceDiscount]
  ,[CarrierTrackingNumber]
FROM [Sales].[SalesOrderDetail]
WHERE [CarrierTrackingNumber] IS NOT NULL;
GO


WHERE ([CarrierTrackingNumber] IS NOT NULL) -- WARUNEK FILTRA INDEKSU
WHERE ([CarrierTrackingNumber] ='000A-434D-BC') -- WARUNEK W ZAPYTANIU

CREATE NONCLUSTERED INDEX [idx_ncl_FICarrierTrackingNumber] 
ON [Sales].[SalesOrderDetail] 
(
  [SalesOrderID] ASC
  ,[CarrierTrackingNumber]  
)
INCLUDE ( [ProductID],[UnitPrice],[UnitPriceDiscount]) 
WHERE ([CarrierTrackingNumber] IS NOT NULL)
WITH (DROP_EXISTING = ON)
ON [PRIMARY]
GO



SELECT 
  [SalesOrderID] 
  ,[ProductID]
  ,[UnitPrice]
  ,[UnitPriceDiscount]
  ,[CarrierTrackingNumber]
FROM [Sales].[SalesOrderDetail]
WHERE [CarrierTrackingNumber] IS NOT NULL;
GO


/*

Statystyki filtrowane  

*/

USE [AdventureWorks2008]
GO
--filtrowany indeks
CREATE NONCLUSTERED INDEX [IDX_NCL_LoginID_Filtered_SRP] 
ON [HumanResources].[Employee] 
(
  [LoginID] ASC
)
WHERE ([JobTitle]='Sales Representative')
ON [PRIMARY];
GO
-- definicja automatycznie wygenerowanej statystyki dla powyższego indeksu
CREATE STATISTICS IDX_NCL_LoginID_Filtered_SRP
ON [HumanResources].[Employee](LoginID)
WHERE ([JobTitle]='Sales Representative');
GO



SELECT name, has_filter, filter_definition
FROM sys.stats
WHERE name IN ('AK_Employee_LoginID','IDX_NCL_LoginID_Filtered_SRP')



SELECT OBJECT_NAME(s.object_id) AS [Obiekt]
     ,COL_NAME(sc.object_id, sc.column_id) AS [Kolumna]
     ,s.name AS [Statystyka]
     ,s.filter_definition AS [Definicja filtra]
FROM sys.stats AS s JOIN  sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
WHERE s.has_filter=1
ORDER BY s.name;

/*

Tworzenie partycji

*/

USE AdventureWorks2008
CREATE PARTITION FUNCTION udfFunkcjaPatycjonujaca (INT)
AS RANGE RIGHT FOR VALUES (7000, 16000);


SELECT $partition.udfFunkcjaPatycjonujaca(600), $partition.udfFunkcjaPatycjonujaca(7000), $partition.udfFunkcjaPatycjonujaca(15999), $partition.udfFunkcjaPatycjonujaca(55000);


CREATE PARTITION SCHEME SchematPartycjonowania
AS PARTITION udfFunkcjaPatycjonujaca
ALL TO ([PRIMARY]);

CREATE TABLE TabPartycjonowanie ( 
	i INT PRIMARY KEY, 
	s VARCHAR(500) DEFAULT 'Rozmieszczenie partycji w różnych, znajdujących się na osobnych dyskach, grupach plików pozwala zrównoważyć obciążenie tych dysków.') 
ON SchematPartycjonowania (i);


SELECT OBJECT_NAME(object_id), partition_id ,partition_number, index_id 
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('TabPartycjonowanie');


DECLARE @a INT = 1;
WHILE (@a < 17000)
BEGIN
  INSERT INTO TabPartycjonowanie (i)
  VALUES (@a);
  SET @a = @a + 1;
END;

SELECT $partition.udfFunkcjaPatycjonujaca(i), COUNT(*)
FROM TabPartycjonowanie
GROUP BY $partition.udfFunkcjaPatycjonujaca(i)
ORDER BY $partition.udfFunkcjaPatycjonujaca(i);


/*

Zarządzanie partycjami

*/

ALTER PARTITION FUNCTION udfFunkcjaPatycjonujaca () 
SPLIT RANGE (20000);
GO
SELECT $partition.udfFunkcjaPatycjonujaca(55000);



SELECT OBJECT_NAME(object_id), partition_id ,partition_number, index_id, rows 
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('TabPartycjonowanie');



CREATE TABLE TabNowaPartycja ( 
	i INT PRIMARY KEY CHECK (i >= 55000), 
	s VARCHAR(500) ); 
GO
INSERT INTO TabNowaPartycja (i)
VALUES (55000);
GO
ALTER TABLE TabNowaPartycja 
SWITCH TO TabPartycjonowanie PARTITION 4;
GO
SELECT partition_number, rows 
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('TabPartycjonowanie');



ALTER PARTITION FUNCTION udfFunkcjaPatycjonujaca () 
MERGE RANGE (20000);
GO
SELECT partition_number, rows 
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('TabPartycjonowanie');


/*

Eskalacja blokad

*/
BEGIN TRAN;
UPDATE TabPartycjonowanie 
SET I = i 
WHERE i < 4500;

SELECT COUNT (*) 
FROM TabPartycjonowanie
WHERE i > 16500;

SELECT resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE [resource_type] <> 'DATABASE';

ROLLBACK;

/*

Zakleszczenia

*/
BEGIN TRAN;
UPDATE TabPartycjonowanie 
SET I = i 
WHERE i BETWEEN 7500 AND 15500;


SELECT COUNT (*) 
FROM TabPartycjonowanie
WHERE i > 16500;


SELECT OBJECT_NAME(resource_associated_entity_id),request_mode, request_status 
FROM sys.dm_tran_locks
WHERE [resource_type] <> 'DATABASE';


ALTER TABLE TabPartycjonowanie
SET (LOCK_ESCALATION = AUTO);


SELECT resource_associated_entity_id,request_mode, request_status 
FROM sys.dm_tran_locks
WHERE [resource_type] <> 'DATABASE';


BEGIN TRAN;
UPDATE TabPartycjonowanie 
SET I = i 
WHERE i < 7000;


BEGIN TRAN;
UPDATE TabPartycjonowanie 
SET I = i 
WHERE i BETWEEN 7500 AND 15500;



SELECT resource_associated_entity_id,request_mode, request_status 
FROM sys.dm_tran_locks
WHERE [resource_type] <> 'DATABASE';


SELECT  * 
FROM TabPartycjonowanie
WHERE i = 500;


SELECT  * 
FROM TabPartycjonowanie
WHERE i = 8500;

/*

Wskazówka OPTIMIZE FOR

*/
USE AdventureWorks2008
GO
--pierwsza procedura nie używa wskazówki OPTIMIZE FOR
CREATE PROC P1
(
  @Nazwisko varchar(50) 
  ,@Inicjal varchar(50)
)
AS BEGIN
SELECT  
  [LastName]
  ,[MiddleName]
  ,[FirstName]
FROM [Person].[Person]
WHERE LastName = @Nazwisko
AND MiddleName = @Inicjal
END;
GO
--druga procedura używa wskazówki OPTIMIZE FOR
CREATE PROC P2
(
  @Nazwisko varchar(50) 
  ,@Inicjal varchar(50)
)
AS BEGIN
SELECT  
  [LastName]
  ,[MiddleName]
  ,[FirstName]
FROM [Person].[Person]
WHERE LastName = @Nazwisko
AND MiddleName = @Inicjal
OPTION (OPTIMIZE FOR (@Inicjal='A', @Nazwisko UNKNOWN) );
END;
GO
--wywołanie procedur 
DECLARE @Nazwisko varchar(50) = 'Diaz';
DECLARE @Inicjal char(2) = 'Z.';

EXEC P1 @Nazwisko,@Inicjal;
EXEC P2 @Nazwisko,@Inicjal;


/*

Wskazówka FORCESEEK

*/
USE AdventureWorks2008;
GO
CREATE NONCLUSTERED INDEX [IDX_NCL_TerritoryID] ON [Sales].[SalesPerson] 
(
  [TerritoryID] ASC
) ON [PRIMARY];


SELECT *
FROM [Sales].[SalesPerson] P  JOIN
[Sales].[SalesPersonQuotaHistory] PH
ON P.BusinessEntityID = PH.BusinessEntityID
WHERE PH.QuotaDate >'2002-01-01'
AND P.TerritoryID = 1;


SELECT *
FROM [Sales].[SalesPerson] P WITH (FORCESEEK) 
JOIN  [Sales].[SalesPersonQuotaHistory] PH
ON P.BusinessEntityID = PH.BusinessEntityID
WHERE PH.QuotaDate >'2002-01-01'
AND P.TerritoryID = 1;


/*

Sugerowany plan wykonania zapytania

*/

-- przykładowe zapytanie
USE AdventureWorks2008;
GO
SELECT 
  BusinessEntityID
  ,LoginID
  ,VacationHours
  ,SickLeaveHours
FROM HumanResources.Employee
WHERE HireDate < '20050101';
GO
--  wykonanie  planu w postaci XML
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT BusinessEntityID
                                                     ,LoginID
                                                     ,VacationHours
                                                     ,SickLeaveHours
                                              FROM HumanResources.Employee
                                              WHERE HireDate < ''20050101'';%');

--wykonanie sugerowanego planu wykonania zapytania
EXEC sp_create_plan_guide 
    @name = N'Sugerowany plan wykonania zapytania z XML_showplan', 
    @stmt = N'SELECT BusinessEntityID
                               ,LoginID
                               ,VacationHours
                               ,SickLeaveHours
                        FROM HumanResources.Employee
                        WHERE HireDate < ''20050101'';', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints =@xml_showplan;
GO


DECLARE @plan_handle VARBINARY(64);
DECLARE @offset INT;
--uzyskanie informacji o identyfikatorze zapytania oraz pozycji zapytania z widoków
--dynamicznych sys.dm_exec_text_query_plan oraz sys.dm_exec_query_stats
SELECT @plan_handle = plan_handle
  , @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT BusinessEntityID
                               ,LoginID
                               ,VacationHours
                        FROM HumanResources.Employee
                        WHERE HireDate %';
--wykonanie sugerowanego planu wykonania zapytania
EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Sugerowany plan wykonania zapytania nr 2',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;
GO


USE AdventureWorks2008;
GO
SELECT 
plan_guide_id as [Identyfikator planu]
, msgnum as [kod błędu]
, severity 
, state
, message as [opis błędu]
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);
GO


/*

Parametryzowane zapytania

*/

  SELECT [ProductID]
           ,[Name]
          ,[ProductNumber]
           ,[MakeFlag]
           ,[Color]
           ,[StandardCost]
           ,[Size]
           ,[Weight]
           ,[DaysToManufacture]
           ,[ProductLine]
           ,[FinishedGoodsFlag]
    FROM [Production].[Product]
    WHERE 
    (
         (Name LIKE @Name+'%' OR @Name IS NULL)
         AND
         (ProductNumber LIKE @ProductNumber+'%' OR @ProductNumber IS NULL)
         AND
         (MakeFlag = @MakeFlag OR @MakeFlag IS NULL)
         AND
         (Color = @Color OR @Color IS NULL)
         AND
         (Size > @Size OR @Size IS NULL)
         AND
         ([Weight] >= @Weight OR @Weight IS NULL)
         AND
         (DaysToManufacture = @DaysToManufacture OR @DaysToManufacture IS NULL)
         AND
         (StandardCost >= @StandardCost OR @StandardCost IS NULL)
    );


