-- drop table dbo.New_SalesOrderHeader

-- Tworzenie kopii tabeli Sales.SalesOrderHeader, co pozwala wykonać
-- wszystkie dalsze operacje na nowej tabeli o nazwie New_SalesOrderHeader.

SELECT * INTO dbo.New_SalesOrderHeader
FROM Sales.SalesOrderHeader

-----------------------------------------------------
--- Skanowanie tabeli
SET STATISTICS PROFILE ON
GO
SELECT SalesOrderID, OrderDate, CustomerID
FROM dbo.New_SalesOrderHeader
GO
SET STATISTICS PROFILE OFF
GO

DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderID, OrderDate, CustomerID
FROM dbo.New_SalesOrderHeader
GO
SET STATISTICS IO OFF

SELECT dpages, *
FROM sys.sysindexes
WHERE ID = OBJECT_ID('New_SalesOrderHeader')
-----------------------------------------------------
-- Skanowanie indeksu klastrowanego (nieuporządkowanego)
CREATE CLUSTERED INDEX IXCU_SalesOrderID ON New_SalesOrderHeader(SalesOrderID)

DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderID,RevisionNumber, OrderDate, DueDate  
FROM New_SalesOrderHeader
GO
SET STATISTICS IO OFF

-----------------------------------------------------
-- Skanowanie indeksu nieklastrowanego (nieuporządkowanego)
CREATE NONCLUSTERED INDEX IXNC_SalesOrderID ON New_SalesOrderHeader(OrderDate) 
INCLUDE(RevisionNumber, DueDate)
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderID,RevisionNumber, OrderDate, DueDate  
FROM New_SalesOrderHeader
GO
SET STATISTICS IO OFF

-----------------------------------------------------
-- Skanowanie indeksu klastrowanego (uporządkowanego)
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderID,RevisionNumber, OrderDate, DueDate  
FROM New_SalesOrderHeader
ORDER BY SalesOrderID
GO
SET STATISTICS IO OFF

-----------------------------------------------------
-- Skanowanie indeksu nieklastrowanego (uporządkowanego)
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderID,RevisionNumber, OrderDate, DueDate  
FROM New_SalesOrderHeader
ORDER BY OrderDate
GO
SET STATISTICS IO OFF

-----------------------------------------------------
-- Przeszukiwanie indeksu nieklastrowanego z częściowym skanowaniem

DROP INDEX New_SalesOrderHeader.IXCU_SalesOrderID
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderID,RevisionNumber, OrderDate, DueDate, SalesOrderNumber
FROM New_SalesOrderHeader
WHERE OrderDate BETWEEN '2007-10-08 00:00:00.000' AND '2007-10-10 00:00:00.000'
GO
SET STATISTICS IO OFF

----------
CREATE CLUSTERED INDEX IXCU_SalesOrderID ON New_SalesOrderHeader(SalesOrderID)
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderID,RevisionNumber, OrderDate, DueDate, SalesOrderNumber
FROM New_SalesOrderHeader
WHERE OrderDate BETWEEN '2007-10-08 00:00:00.000' AND '2007-10-10 00:00:00.000'
GO
SET STATISTICS IO OFF

-----------------------------------------------------
-- Przeszukiwanie indeksu klastrowanego z częściowym skanowaniem

DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderID,RevisionNumber, OrderDate, DueDate, SalesOrderNumber
FROM New_SalesOrderHeader
WHERE SalesOrderID BETWEEN 43696 AND 45734
GO
SET STATISTICS IO OFF

-- Określanie głębokości indeksu
SELECT INDEXPROPERTY (OBJECT_ID('New_SalesOrderHeader'), 'IXCU_SalesOrderID', 'IndexDepth') 

-- Dodawanie indeksu w celu zmiany planu i sprawdzenia statystyk operacji wejścia-wyjścia

CREATE NONCLUSTERED INDEX IXNC_SalesOrderID_2 ON New_SalesOrderHeader(SalesOrderID, OrderDate) 
INCLUDE(RevisionNumber, DueDate, SalesOrderNumber)

-- Usuwanie indeksu po wykonaniu ćwiczenia
DROP INDEX New_SalesOrderHeader.IXNC_SalesOrderID_2
-----------------------------------------------------
-- Określanie fragmentacji indeksu tabeli New_SalesOrderHeader

SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.New_SalesOrderHeader'), NULL, NULL, NULL)

-------------------------------------------------------
-- Odbudowywanie indeksu w trybie online
ALTER INDEX IXNC_SalesOrderID ON dbo.New_SalesOrderHeader REBUILD WITH (ONLINE = ON)
--------------------------------------------------------

-- Usuwanie z planem opartym na wierszu
DELETE FROM New_SalesOrderHeader
WHERE OrderDate = '2007-07-01 00:00:00.000'

-- Usuwanie z planem opartym na indeksie
DELETE FROM Sales.SalesOrderHeader
WHERE OrderDate < '2006-07-01 00:00:00.000'
--------------------------------------------------------
