
/******************************************************************
▣ Index
*******************************************************************/

---------------------------------------------------------------------------------
-- ▶ 인덱스 관련 Dynamic Management Objects  (6페이지)
---------------------------------------------------------------------------------

-- 사용된 인덱스와 인덱스 사용 빈도 확인
select * from sys.dm_db_index_usage_stats;
go


-- 인덱스의 i/o 및 잠금 정보
select * from sys.dm_db_index_operational_stats 
	(db_id('adventureworks')
	,object_id('adventureworks.person.address')
	, null
	, null);
go


-- 인덱스의 조각화 정보
select * 
from sys.dm_db_index_physical_stats
	(db_id('adventureworks')
	, object_id('adventureworks.person.address')
	, null
	, null
	, default)
go

---------------------------------------------------------------------------------
-- ▶ 인덱스 정보 확인 (7~10,15~19 페이지)
---------------------------------------------------------------------------------
use adventureworks;
go


-- 1. 테이블 생성
if objectproperty(object_id('salesorderdetail_test'), 'isusertable') = 1
	drop table salesorderdetail_test;
go


select * into salesorderdetail_test from sales.salesorderdetail;
go


-- 2. 인덱스 생성 
alter table dbo.salesorderdetail_test
add constraint pk_salesorderdetail_test
	primary key clustered (salesorderid,salesorderdetailid);
go


-- 3. 인덱스 및 통계 정보 확인 
exec sp_helpindex 'dbo.salesorderdetail_test';
go


select * from sys.indexes 
where object_id = object_id('salesorderdetail_test'); -- catalog views
go


select * from sys.stats 
where object_id = object_id('salesorderdetail_test'); -- catalog views
go


-- 4. 인덱스 조각화 정보
dbcc showcontig('salesorderdetail_test') with tableresults;
                                      -- with fast, limited scan
go


--입력 가능한 모드 : default, null, limited, sampled, detailed
--		             default = null = limited 
--입력 가능한 인덱스id : null, 0, 1, 2-250
--		                 null = all index, 0 = heap, 1 = clustered, 2-250 = non-clustered
select * 
from sys.dm_db_index_physical_stats
	(db_id(), object_id('adventureworks.dbo.salesorderdetail_test')
	, 1, null, 'sampled');
go


select * 
from sys.dm_db_index_physical_stats
	(db_id(), object_id('adventureworks.dbo.salesorderdetail_test')
	, 1, null, null);  -- 'limited';
go


select * 
from sys.dm_db_index_physical_stats
	(db_id(), object_id('adventureworks.dbo.salesorderdetail_test')
	, 1, null, 'detailed');
go


---------------------------------------------------------------------------------
-- ▶ 인덱스 재구성 (12~14 페이지)
---------------------------------------------------------------------------------
use adventureworks;
go


-- 1. 테이블 생성
if objectproperty(object_id('salesorderdetail_test'), 'isusertable') = 1
	drop table salesorderdetail_test;
go


select * into salesorderdetail_test from sales.salesorderdetail;
go


-- 2. 인덱스 생성 
alter table dbo.salesorderdetail_test
add constraint pk_salesorderdetail_test
	primary key clustered (salesorderid,salesorderdetailid);
go


-- dbcc indexdefrag(내부 잠금 발생, 논리적 재구성)
dbcc indexdefrag (adventureworks, 'dbo.salesorderdetail_test', pk_salesorderdetail_test)
go


-- alter index ~ reorganize(내부 잠금 없음, 논리적 재구성)
alter index pk_salesorderdetail_test on dbo.salesorderdetail_test
reorganize;
go

---------------------------------------------------------------------------------
-- ▶ 인덱스 비활성화 및 재구성 (20~27 페이지)
---------------------------------------------------------------------------------
-- 클러스터드 인덱스 비활성화한 후, 재작성
use adventureworks;
go


select * from dbo.salesorderdetail_test;
go


exec sp_helpindex 'dbo.salesorderdetail_test';
go


alter index pk_salesorderdetail_test on dbo.salesorderdetail_test
disable ;
go
select * from dbo.salesorderdetail_test;
go
/*
메시지 8655, 수준 16, 상태 1, 줄 1
테이블 또는 뷰 'salesorderdetail_test'의 인덱스 'pk_salesorderdetail_test'이(가) 비활성화되었으므로
 쿼리 프로세서에서 계획을 생성할 수 없습니다.
*/


select is_disabled from sys.indexes 
where object_id = object_id('salesorderdetail_test'); 
go


alter index pk_salesorderdetail_test on dbo.salesorderdetail_test
rebuild with (online=on, fillfactor = 90);
go
/*
메시지 1988, 수준 16, 상태 1, 줄 1
클러스터형 인덱스 'pk_salesorderdetail_test'은(는) 비활성화되어 있으므로 온라인 상태에서 다시 작성할 수 없습니다.
*/


alter index all on dbo.salesorderdetail_test
rebuild;
go


select * from dbo.salesorderdetail_test;
go


select is_disabled from sys.indexes 
where object_id = object_id('salesorderdetail_test'); 

-- 클러스터드 인덱스를 온라인상에서 재작성하기
-- 온라인 인덱스 작업은 enterprise edition에서만 가능
alter index pk_salesorderdetail_test on dbo.salesorderdetail_test
rebuild with (online=on, fillfactor = 90);
go


-- 넌클러스터드 인덱스를 온라인상에서 재구성하기
create index ix_productid on dbo.salesorderdetail_test (productid);
go


alter index ix_productid on dbo.salesorderdetail_test
rebuild with (online=on, fillfactor = 90);
go


-- alter index의 옵션 설정하기
if objectproperty(object_id('optiontestignoredupkey'), 'isusertable') = 1
	drop table optiontestignoredupkey;
go


create table optiontestignoredupkey (col1 int not null, col2 varchar(10));
go


create unique clustered index idx_col1 
	on optiontestignoredupkey (col1);
go


alter index idx_col1 on optiontestignoredupkey
set (ignore_dup_key = on);
go


select name, ignore_dup_key, allow_page_locks 
from sys.indexes where object_id=object_id('optiontestignoredupkey');
go

---------------------------------------------------------------------------------
-- [참고] FK 제약 조건이 있는 클러스터드 인덱스 
-- 비활성화한 후, 활성화하기
---------------------------------------------------------------------------------

use tempdb;
go


if objectproperty(object_id('cities'), 'isusertable') = 1
	drop table cities;
go


if objectproperty(object_id('states'), 'isusertable') = 1
	drop table states;
go


-- 테스트 테이블 생성
create table states (state char(2) not null);
go


create table cities (
		 city varchar(30) not null,
		 state char(2) ,
	     zip int);
go


-- pk 제약 조건과 fk 제약 조건 생성
alter table states
add constraint pk_states primary key clustered (state);
go


alter table cities
add constraint pk_cities primary key clustered (city);
go


alter table cities
add constraint fk_cities_states_state foreign key (state)
references states (state);
go


-- cities 테이블에 데이터 insert 
-- states 테이블에 없는 ‘pr’ 값은 cities 테이블에 insert 불가능
insert into states select 'ca';
go


insert into cities select 'los angles', 'ca',111;
go


insert into cities select 'san juan', 'pr' ,222;
go
/*
메시지 547, 수준 16, 상태 0, 줄 1
insert 문이 foreign key 제약 조건 "fk_cities_states_state"과(와) 충돌했습니다. 데이터베이스 "tempdb", 테이블 "dbo.states", column 'state'에서 충돌이 발생했습니다.
문이 종료되었습니다.
*/


-- states 테이블의 클러스터드 인덱스 비활성화 
-- fk 제약 조건도 동시에 비활성화된다
alter index pk_states on states
disable;
go
/*
경고: 인덱스 'pk_states'을(를) 비활성화한 결과 테이블 'states'을(를) 참조하는 테이블 'cities'의 외래 키 'fk_cities_states_state'이(가) 비활성화되었습니다.
*/
 

-- cities 테이블에 데이터 insert 
-- fk 제약 조건이 비활성화되었으므로 
-- states 테이블에 없는 ‘pr’ 값은 cities 테이블에 insert 가능
insert into cities select 'san juan', 'pr' ,222;
go


-- states 테이블의 클러스터드 인덱스 재구성
alter index pk_states on states
rebuild;
go


-- cities 테이블에 데이터 insert 
-- fk 제약 조건은 여전히 비활성화 상태이므로 
-- states 테이블에 없는 ‘pr’ 값은 cities 테이블에 insert 가능
insert into cities select 'detroit', 'pr' ,333;
go


-- fk 제약 조건 재생성하기
alter table  cities 
check constraint fk_cities_states_state;


-- cities 테이블에 데이터 insert 
-- states 테이블에 없는 ‘pr’ 값은 cities 테이블에 insert 불가능
insert into cities select 'kenmore', 'pr' ,444;
go
/*
msg 547, level 16, state 0, line 1
the insert statement conflicted with the foreign key constraint "fk_cities_states_state". the conflict occurred in database "adventureworks", table "dbo.states", column 'state'.
the statement has been terminated.
*/

---------------------------------------------------------------------------------
-- ▶ 포괄 열이 있는 인덱스 (32 페이지)
---------------------------------------------------------------------------------
use adventureworks;
go


if objectproperty(object_id('address_test'), 'isusertable') = 1
	drop table address_test;
go


select * into address_test from person.address;
go


alter table address_test 
	add constraint pk_address_test primary key clustered (addressid);
go


create nonclustered index ix_address_postalcode
    on dbo.address_test (postalcode)
    include (addressline1, addressline2, city, stateprovinceid);
go


select addressline1, addressline2, city, stateprovinceid, postalcode
from dbo.address_test
where postalcode between n'98000' and n'99999';
go


-- included column 용도 확인
create procedure get_index_columns (@object sysname, @index sysname = null)
as
declare @oid int
select @oid = object_id(@object)


select c.name as [column], 
	case ic.is_included_column when 0 then 'key'
		else 'included'
	end as [column usage]
from sys.index_columns ic join sys.columns c
		on ic.object_id = c.object_id
		and ic.column_id = c.column_id
	join sys.indexes i	
		on i.object_id = ic.object_id
		and i.index_id = ic.index_id
where i.object_id = @oid  and i.name = @index
and i.index_id > 0
go	


exec dbo.get_index_columns 'dbo.address_test','ix_address_postalcode';
go


exec sp_columns address_test;
go


alter table address_test 
alter column addressline1 nvarchar(100) not null;
go


---------------------------------------------------------------------------------
-- ▶ DBCC SHOW_STATISTICS (37 페이지)
---------------------------------------------------------------------------------
use adventureworks;
go


dbcc show_statistics ('person.address',ix_address_postalcode);
go


dbcc show_statistics ('person.address',ix_address_postalcode)
with stat_header;
--with density_vector;
--with histogram;
go



/********************************* 끝 ************************************/