[MSSQL]MS-SQL 4일차 - Trigger / Index

 ### 트리거 (Trigger)

 1) 목적 : 데이터 무결성과 일관성 유지

 

 2) 종류:

- DML 트리거 : insert, update, delete

- DDL 트리거 : create, alter, drop

 3) 옵션:

- After : 실행 후(트랜잭션 로그가 계속 남음)

- Instead of : 실행 전(트랜잭션 로그가 안남음)

 4) 참고: 가상 테이블 (inserted, deleted)

use sqldb

create Table TA

 ( id int identity Primary key,

name char(8))

create Table TB

 ( id int identity Primary key,

name char(8))

insert into TA values ('jhAn')

insert into TB values ('jhAn')

select * from TA

select * from TB

create Trigger Trg_A on TA

 instead of insert

 as

rollback

go

create Trigger Trg_B on TB

 after insert

 as

rollback

go

drop Trigger Trg_A

drop Trigger Trg_B

Instead Vs. After 차이점

## Procedure Compile

drop Table spTable

select * into spTable 

from AdventureWorks.Sales.Customer

order by rowguid

select * from spTable

create index idx_spTbl on spTable (customerid)

Exec sp_helpindex spTable

--set statistics time off

set statistics IO on

select * from spTable where customerID < 10

select * from spTable where customerID < 1000

create Proc usp_1

@id int

as

select * from spTable where customerID < @id

Exec usp_1 10 --with recompile

Exec usp_1 1000 --with recompile

DBCC Freeproccache

alter Proc usp_1

@id int

with recompile

as

select * from spTable where customerID < @id

## 데이터 형식 우선순위 ##

1.     사용자 정의 데이터 형식(가장 높음)

2.     sql_variant

3.     xml

4.     datetimeoffset

5.     datetime2

6.     datetime

7.     smalldatetime

8.     date

9.     time

10.   float

11. real

12.   decimal

13.   money

14.   smallmoney

15.   bigint

16.   int

17.   smallint

18.   tinyint

19.   bit

20.   ntext

21.   text

22.   image

23.   timestamp

24.   uniqueidentifier

25.   nvarchar(nvarchar(max) 포함)

26.   nchar

27.   varchar(varchar(max) 포함)

28.   char

29.   varbinary(varbinary(max) 포함)

30.   binary(가장 낮음)

### 인덱스 생성 테스트 ###

use AdventureWorks

select * from AdventureWorks.sales.customer

create database indb

use indb

select * into Cust_A from AdventureWorks.sales.customer

select * into Cust_c from AdventureWorks.sales.customer

select * into Cust_n from AdventureWorks.sales.customer

select Top(5)* from Cust_A

select Top(5)* from Cust_C

select Top(5)* from Cust_N

Exec sp_helpindex cust_A

Exec sp_helpindex cust_C

Exec sp_helpindex cust_N

create Clustered index idx_cust_C

on cust_C (customerid)

create nonclustered index idx_cust_N

on cust_N (customerid)

set statistics io on

set statistics time on

select * from Cust_A where customerID < 1000

select * from Cust_C where customerID < 1000

select * from Cust_N where customerID < 1000

select * from Cust_A where customerID < 1000

select * from Cust_C where customerID < 1000

select * from Cust_N with(index(idx_Cust_N)) 

where customerID < 1000

### 형 변환 ##

use indb

go

select * from master.dbo.syscolumns

select * from tempdb.dbo.syscolumns

select * from model.dbo.syscolumns

select * from msdb.dbo.syscolumns

if OBJECT_ID('t_columns') is not null

drop table t_columns

go

select t.*

into t_columns

from 

(select CAST(1 as smallint)as dbid,* from master.dbo.syscolumns

union all

select CAST(2 as smallint)as dbid,* from tempdb.dbo.syscolumns

union all

select CAST(3 as smallint)as dbid,* from model.dbo.syscolumns

union all

select CAST(4 as smallint)as dbid,* from msdb.dbo.syscolumns

) t

go

select * from t_columns

go

select COUNT(*) as cnt from t_columns

go

create index idx_t_01 on t_columns (dbid,name)

go

Exec sp_helpindex t_columns

-----------------------------------------------

set statistics profile on

set statistics io on

set statistics time on

go

select * 

from t_columns 

-- Index Scan --

select * 

from t_columns 

where name = 'addr'

-- Index Seek --

select * 

from t_columns 

where name = 'addr'

and dbid in (1,2,3,4)

-- Index 제거 --

sp_helpindex t_columns

drop index t_columns.idx_t_01

go

-- index 추가 --

create index idx_t_02 on t_columns(name)

go

-- column 추가

select * from t_columns

go

alter table t_columns -- 자동증가값

add no_tmp int identity not null

go

alter table t_columns -- null 값

add no varchar(10)

go

-- 값 변경 null --> 자동증가값

update t_columns

set no = no_tmp

go

-- Index 추가

create index idx_t_03 on t_columns (no)

go

select * from t_columns

where name = 'rowsetid'

---------------------------------------

-- Explicit 형 변환 --

select * from t_columns

where SUBSTRING(name,1,8) = 'rowsetid'

go

select * from t_columns

where name like 'rowsetid'

go

-- Implicit 형변환 --

select * from t_columns

where no=1 --varchar(10)

go       

--> 데이터 우선순위 (Int > varchar) 

--> 변환 (varchar --> Int)

select * from t_columns

where no = CONVERT(varchar(10),1)

go

--> 암시적 변환 예

--> 날짜 char(8)

--> where 계약일자 = 20130311

--> 데이터 우선순위 (Int > char(8)) 

--> 변환 (char(8) --> Int)

### 인덱스 기본키 생성

use xDB

go

create table T1 (

a int,

b int,

c int)

go

Exec sp_help T1

Exec sp_helpindex T1

go

create table T2 (

a int Primary key,

b int,

c int)

go

Exec sp_help T2

Exec sp_helpindex T2

go

create table T3 (

a int Primary key,

b int Unique,

c int Unique)

go

Exec sp_helpindex T3

go

create table T4(

a int Primary key nonclustered,

b int Unique clustered,

c int Unique)

go

Exec sp_helpindex T4

go

create table T5(

a int Primary key nonclustered,

b int Unique clustered,

c int Unique clustered) --< 불가능

go

Exec sp_helpindex T5

go

create table T6(

a int Primary key nonclustered,

b int Unique nonclustered,

c int Unique nonclustered)

go

Exec sp_helpindex T6

go

## 인덱스 생성

exec sp_helpdb indb

go

if OBJECT_ID('empTbl') is not null

drop table empTbl

go

use inDB

create table empTbl(

empID int not null,

LastName nvarchar(20) null,

FirstName nvarchar(10) null,

HireDate datetime null

)

insert into empTbl

select EmployeeID, LastName, FirstName, HireDate

from Northwind.dbo.Employees

order by EmployeeID

go

select * from empTbl

go

-- Table에 Index 확인 --

Exec sp_helpindex empTbl

go

-- Index 생성 --> LastName

create index idx_empTbl_LastName on empTbl(LastName)

with (fillfactor = 1, pad_index = on)

go

-- index 아이디 확인 --

select index_id, name

from sys.indexes 

where object_id = object_id('empTbl')

go

-- ind ('Database 명','Table 명','Index 아이디')

--> Page Type : DataPage(1),IndexPage(2),IAM(10)

--> Index Level : 루트(2),브랜치(1),리프(0)

dbcc ind('inDB','empTbl',2)

go

-- dbcc page 앞에 실행해야 함

--> dbcc 명령들은 기본적으로 출력을 로그에만 남김

dbcc traceon(3604)

go

-- page('Database 명','파일번호','페이지번호','출력옵션[0~3]')

dbcc page('indb',1,118,3)

go

------- Index 생성 기초 ------------------

-----------------------------------------

-- Clustered Index 생성 --

Exec sp_helpindex empTbl

SP_SPACEUSED [empTbl] 

GO

alter table empTbl 

add constraint idx_emp_pk

primary key clustered (empID)

go

alter table empTbl

drop constraint idx_emp_pk

go

### 인덱스 정보 확인 ###

USE ADVENTUREWORKS

GO

SP_HELP [PERSON.ADDRESS] 

GO

SP_HELPINDEX [PERSON.ADDRESS] 

GO

USE ADVENTUREWORKS

GO

SELECT * FROM SYS.INDEXES 

WHERE OBJECT_ID = OBJECT_ID('PERSON.ADDRESS')

GO

SELECT * FROM SYS.INDEX_COLUMNS 

WHERE OBJECT_ID = OBJECT_ID('PERSON.ADDRESS')

GO

USE ADVENTUREWORKS

GO

SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS 

(DB_ID(N'ADVENTUREWORKS'), OBJECT_ID(N'PERSON.ADDRESS'),

NULL, NULL , 'DETAILED')

GO

SELECT * FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS 

(DB_ID(N'ADVENTUREWORKS'), OBJECT_ID(N'PERSON.ADDRESS'), NULL,

NULL)

GO

SELECT * FROM SYS.DM_DB_INDEX_USAGE_STATS 

WHERE OBJECT_ID = OBJECT_ID(N'PERSON.ADDRESS')

GO

SELECT INDEXPROPERTY(OBJECT_ID('PERSON.ADDRESS'), 

'PK_Address_AddressID','IsClustered')AS [Is Clustered],

INDEXPROPERTY(OBJECT_ID('PERSON.ADDRESS'),

'PK_Address_AddressID','IndexDepth') AS [Index Depth],

INDEXPROPERTY(OBJECT_ID('PERSON.ADDRESS'),

'PK_Address_AddressID','IndexFillFactor') AS [Fill Factor];

GO

### 인덱스 활용

USE ADVENTUREWORKS

GO

Exec sp_helpindex [SALES.SALESORDERDETAIL]

select * from SALES.SALESORDERDETAIL

drop index SALES_QTY_IDX on SALES.SALESORDERDETAIL

GO

set statistics io on

SELECT * FROM SALES.SALESORDERDETAIL 

WHERE ORDERQTY >= 35

GO

Exec sp_helpindex [SALES.SALESORDERDETAIL]

CREATE INDEX SALES_QTY_IDX ON SALES.SALESORDERDETAIL(ORDERQTY) 

GO

SELECT * FROM SALES.SALESORDERDETAIL 

WHERE ORDERQTY >= 35

GO

SELECT * FROM SALES.SALESORDERDETAIL

WHERE ORDERQTY - 5 >= 30

GO

USE ADVENTUREWORKS

GO

SP_HELPINDEX [PRODUCTION.PRODUCT] 

GO

SELECT NAME FROM PRODUCTION.PRODUCT 

WHERE NAME LIKE '%Blade%'

GO

SELECT NAME FROM PRODUCTION.PRODUCT

WHERE NAME LIKE 'Blade%'

GO

### 인덱스 비활성화

USE ADVENTUREWORKS

GO

select * from Person.Address

SP_HELPINDEX [PERSON.ADDRESS] 

GO

SP_SPACEUSED [PERSON.ADDRESS] 

GO

set statistics io on

select * from Person.Address

where StateProvinceID < 3

ALTER INDEX IX_ADDRESS_STATEPROVINCEID ON PERSON.ADDRESS 

rebuild -- 활성화

DISABLE  -- 비활성화

GO

SP_HELPINDEX [PERSON.ADDRESS] 

GO

SP_SPACEUSED [PERSON.ADDRESS]

GO

ALTER INDEX PK_Address_AddressID ON PERSON.ADDRESS 

DISABLE

GO

SP_SPACEUSED [PERSON.ADDRESS]

GO

ALTER INDEX IX_ADDRESS_STATEPROVINCEID ON PERSON.ADDRESS 

REBUILD WITH (ONLINE=ON)

GO

ALTER INDEX ALL ON PERSON.ADDRESS REBUILD 

GO

SP_SPACEUSED [PERSON.ADDRESS]

GO

ALTER INDEX IX_ADDRESS_STATEPROVINCEID ON PERSON.ADDRESS 

DISABLE

GO

SELECT IS_DISABLED FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID('PERSON.ADDRESS')

AND NAME = 'IX_ADDRESS_STATEPROVINCEID'

GO

ALTER INDEX IX_ADDRESS_STATEPROVINCEID ON PERSON.ADDRESS

REBUILD WITH (ONLINE=ON)

-- ONLINE=ON (온라인 상태, 즉 Table Lock을 안 걸고 실행)

GO