---------------
--- Indexes ---
---------------

USE [AdventureWorks2012]

Drop table emp1 
 
CREATE TABLE [dbo].[Emp1]
(
	[EmployeeID] [int] NOT NULL,
	[Name] [nchar](10) NULL,
	[Department_ID] [int] NULL
)

Insert Emp1 
select 2,'John',1
union
select 3,'Nick',2
union
select 1,'Ben',3
union
select 4,'Keren',4
order by 2 desc 

select * from emp1

--Create UNIQUE CONSTRAINT and Nunclusterd index

ALTER TABLE Emp1
ADD UNIQUE (EmployeeID)
 
select * from emp1

-- Try To insert a new employees with id 4:
Insert into Emp1 values (4,'Joe',5)

--Create primary key and clusterd index 
ALTER TABLE emp1 ADD CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED 
(
	EmployeeID ASC
)

select * from emp1
 
ALTER TABLE [dbo].[Emp1] 
DROP CONSTRAINT [PK_EmployeeID]

select * from emp1

--Create primary key and nonclustered index 

ALTER TABLE emp1 ADD CONSTRAINT [PK_EmployeeID] PRIMARY KEY nonclustered 
(
	EmployeeID ASC
)

select * from emp1
 
 --------------------------------------------------
-- CLUSTERED INDEX

CREATE CLUSTERED INDEX [Clus_Emp1] ON [dbo].[Emp1] 
(
	[EmployeeID] ASC
)
 
select * from [Emp1]
where EmployeeID between 1 and 5
 
--Drop clustered index 
DROP INDEX [Clus_Emp1] ON [dbo].[Emp1] WITH ( ONLINE = OFF )

--And the results now...

 select * from [Emp1]
 where EmployeeID between 1 and 5
 
 --Table scan

 select * from [Emp1]
 where Name = 'Nick' 

DROP INDEX [Nun_Clus_Emp1] ON [dbo].[Emp1] WITH ( ONLINE = OFF )

CREATE NONCLUSTERED INDEX [Nun_Clus_Emp1] ON [dbo].[Emp1] 
(
	[Name] ASC
)
INCLUDE ( [EmployeeID],[Department_ID])

--The results now...

 select * from [Emp1]
 where Name = 'Nick' 
 
 /*==================================*/
 --Filterd index

 SELECT he.BusinessEntityID, he.LoginID, he.JobTitle
 FROM HumanResources.Employee he
 WHERE he.jobTitle = 'Marketing Manager'
 
Drop Index NCI_Department On HumanResources.Employee

CREATE NONCLUSTERED INDEX NCI_Department ON HumanResources.Employee(JobTitle)
WHERE JobTitle= 'Marketing Manager'
 
 SELECT he.BusinessEntityID, he.LoginID, he.JobTitle
 FROM HumanResources.Employee he
 WHERE he.jobTitle = 'Marketing Manager'
 
-------------------------------------
 --- sys.tables - System Table To see All DB tables
select * from sys.tables

select * from sys.tables
where name='emp1'

select * from sys.objects
where object_id=679673469

select * from sys.indexes
where name='NCI_Department'

select * from sys.partitions

select * from sys.indexes
where name='NCI_Department'

select * from sys.objects
where object_id=679673469

--- Fragmentation ---

select * from sys.dm_db_index_physical_stats
(database_id, object_id , index_id, partition_number, mode)

-- Can use null to find all 

select * from sys.databases where name = 'northwind'
select * from sys.objects where name like 'Order Details'
select * from sys.indexes

select * from sys.dm_db_index_physical_stats
(7, null , null, null, null)
order by avg_page_space_used_in_percent desc

select * from sys.dm_db_index_physical_stats
(7, 325576198 , null, null, null)
order by avg_page_space_used_in_percent desc

select * from sys.indexes where index_id=2 and object_id=325576198

--- Index Rebuild & Reorganize ---

ALTER INDEX PK_Order_Details ON [dbo].[order details] REBUILD 

ALTER INDEX OrderID ON [dbo].[order details] REORGANIZE 

--- Additional Queries for Fragmentation ---

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
and indexstats.avg_fragmentation_in_percent > 40
ORDER BY indexstats.avg_fragmentation_in_percent desc

OR

SELECT OBJECT_NAME(OBJECT_ID) as obj_name, index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'Northwind'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

OR

SELECT OBJECT_NAME(OBJECT_ID) as obj_name, index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(),object_id(679673469),NULL,NULL,'SAMPLED')
where index_id=9;