-----------------------------------------------------------------------------
/*
SQL Server در Execution Plan آشنایی با مفهوم  
Site:        http://www.NikAmooz.com
Instagram:   https://instagram.com/nikamooz/
Telegram:	 https://telegram.me/nikamooz
Created By:  Iman Bagheri
*/
-----------------------------------------------------------------------------
/*
Execution Plan مقایسه 
*/
--ایجاد بانک اطلاعاتی تستی
USE master
GO
IF DB_ID('TestDB')>0
BEGIN
	ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE TestDB
END
GO
CREATE DATABASE TestDB
GO
USE TestDB
GO
--ایجاد یک جدول تستی و درج دیتا نسبتا زیاد در آن
DROP TABLE IF EXISTS SalesOrderWithoutIndex
DROP TABLE IF EXISTS SalesOrderWithIndex

GO
CREATE TABLE  SalesOrderWithoutIndex(
	SalesOrderID int NOT NULL,
	SalesOrderDetailID int NOT NULL,
	UnitPriceDiscount money NOT NULL,
	ModifiedDate datetime NOT NULL
	) ON [PRIMARY]
GO
CREATE TABLE  SalesOrderWithIndex(
	SalesOrderID int NOT NULL,
	SalesOrderDetailID int NOT NULL,
	UnitPriceDiscount money NOT NULL,
	ModifiedDate datetime NOT NULL
	) ON [PRIMARY]
GO
INSERT INTO SalesOrderWithoutIndex(SalesOrderID, SalesOrderDetailID, UnitPriceDiscount, ModifiedDate)
 SELECT   SalesOrderID, SalesOrderDetailID, UnitPriceDiscount, ModifiedDate
  FROM AdventureWorks2019.Sales.SalesOrderDetail 
GO

INSERT INTO SalesOrderWithIndex(SalesOrderID, SalesOrderDetailID, UnitPriceDiscount, ModifiedDate)
 SELECT   SalesOrderID, SalesOrderDetailID, UnitPriceDiscount, ModifiedDate
  FROM AdventureWorks2019.Sales.SalesOrderDetail 
GO
--بررسی رکوردهای درج شده
SP_SPACEUSED SalesOrderWithoutIndex
GO
SP_SPACEUSED SalesOrderWithIndex
GO
 
--ایجاد یک ایندکس  
CREATE NONCLUSTERED INDEX Non_SalesOrderID ON SalesOrderWithIndex(SalesOrderID)
GO

SP_SPACEUSED SalesOrderWithoutIndex
GO
SP_SPACEUSED SalesOrderWithIndex
GO
--------------------------------------------------------------------
--In Place مقایسه به صورت 

--کوئری اول
SELECT 
	*
FROM SalesOrderWithoutIndex  
WHERE SalesOrderID = '52827'
 
--کوئری دوم
SELECT 
	*
FROM SalesOrderWithIndex  
WHERE SalesOrderID = '52827'
GO

---------------------------------------------------------
/* Clusterd  ایجاد ایندکس  */
--In Place مقایسه به صورت 

CREATE CLUSTERED INDEX IX_SalesOrderID ON SalesOrderWithIndex(SalesOrderID)
GO

--کوئری اول
SELECT 
	*
FROM SalesOrderWithoutIndex  
WHERE SalesOrderID = '52827'
 
--کوئری دوم
SELECT 
	*
FROM SalesOrderWithIndex  
WHERE SalesOrderID = '52827'
GO

--------------------------------------------------------------------
/* نمایش جزییات اجرای کوئری  */
SET STATISTICS IO ON
GO
SELECT 
	*
FROM SalesOrderWithoutIndex  
WHERE SalesOrderID = '52827'
 
--کوئری دوم
SELECT 
	*
FROM SalesOrderWithIndex  
WHERE SalesOrderID = '52827'
GO
 

 --------------------------------------------------------------------
--Compare ShowPlan استفاده از 
SELECT 
	*
FROM SalesOrderWithoutIndex  
WHERE SalesOrderID = '52827'
 
--کوئری دوم
SELECT 
	*
FROM SalesOrderWithIndex  
WHERE SalesOrderID = '52827'
GO
 