SQL 2000 VS SQL2005 의 인덱스 관련 비교

SQL 2005 부터 Index 관련 어떤것이 바뀌었고 어떤식으로 사용 되는지에 대해서 한번 보고 넘어
가는 것이 어떨까 해서 정리해 보았습니다.

1. 포괄 열 인덱스

- 키가 아닌 열을 포함한 비클러스터형 인덱스
- 인덱스 커버링 가능성을 높여 줌 - 쿼리의 성능 개선 효과
- text, ntext, image, varchar(max), nvarchar(max), varbinary(max),xml 데이터 형식 제외
- SQL 2008 부터 varchar(max), nvarchar(max), varbinary(max) 가능

USE AdventureWorks;

GO

-- 포괄 열을 추가한 인덱스 생성

CREATE NONCLUSTERED INDEX IX_Address_PostalCode

    ON Person.Address (PostalCode)

    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

GO

-- 포괄 열 인덱스로 인해 성능이 향상되는 쿼리 예

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode

FROM Person.Address

WHERE PostalCode BETWEEN N'98000' and N'99999';

GO

-- SQL 2008 이상 VARCHAR(MAX) 도 포괄열 가능
IF OBJECT_ID ('TBLX') IS NOT NULL

DROP TABLE TBLX

GO

 

CREATE TABLE TBLX

(IDX INT

,C1 CHAR(10)

,C2 VARCHAR(10)

,C3 TEXT)

GO

 

ALTER TABLE TBLX

ALTER COLUMN C3 VARCHAR(MAX)

GO

 

CREATE NONCLUSTERED INDEX NC_TBLX_03 ON TBLX (IDX) INCLUDE (C3)
GO

 

2. 인덱스 옵션 - IGNORE_DUP_KEY

- 기본 모드가 중복 키 무시인 이기종 DBMS에서 SQL Server 2005로 마이그레이션하는 경우에 유용하게 사용

- IGONORE_DUP_KEY 옵션을 활성화하면 다중 행 INSERT 작업 시 중복 키 값이 들어올 때 오류가 아닌 경고 메시지를 반환하고 고유 인덱스에 위배되는 행만 INSERT에서 제외

- 사용 방법

CREATE UNIQUE INDEX AK_Index ON #Test (C2)

     WITH (IGNORE_DUP_KEY = ON);

     GO

 

3. 인덱스 조각화 확인

- SQL 2000
DBCC SHOWCONTIG('Person.Address')

 

- SQL 2005
DECLARE @db_id SMALLINT;

DECLARE @object_id INT;

 

SET @db_id = DB_ID(N'AdventureWorks');

SET @object_id = OBJECT_ID(N'Person.Address');

 

IF @db_id IS NULL

BEGIN;

    PRINT N'Invalid database';

END;

ELSE IF @object_id IS NULL

BEGIN;

    PRINT N'Invalid object';

END;

ELSE

BEGIN;

    SELECT *

       FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , NULL);

    SELECT *

    FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

END;

GO

4. 인덱스 사용현황 확인하기

SELECT * FROM sys.dm_db_index_usage_stats

WHERE database_id = db_id('Adventureworks');

GO

  

5. 인덱스 사용구문 비교

SQL Server 2000	SQL Server 2005
CREATE INDEX with DROP_EXISTING - Creates a new index with the same name and drops the current index while ensuring the nonclustered indexes are not rebuilt twice.   CREATE CLUSTERED INDEX au_id_clidx ON Authors (au_id) WITH DROP_EXISTING GO	CREATE INDEX with DROP_EXISTING - Creates a new index with the same name and drops the current index while ensuring the nonclustered indexes are not rebuilt twice.   CREATE CLUSTERED INDEX au_id_clidx ON dbo.Authors (au_id) WITH (DROP_EXISTING = ON); GO
DROP INDEX and CREATE INDEX - Removes the au_id_ind index on the authors table.   DROP INDEX authors.au_id_ind GO CREATE INDEX au_id_ind ON Authors (au_id ASC) GO	DROP INDEX and CREATE INDEX - Removes the au_id_ind index on the authors table, which is the equal functionality as SQL Server 2000.   DROP INDEX au_id_ind ON authors GO CREATE INDEX au_id_ind ON Authors (au_id ASC); GO
DBCC DBREINDEX - Rebuild all of the indexes on the authors table with 80% fill factor.   DBCC DBREINDEX (authors, '', 80) GO	ALTER INDEX - Rebuild all of the indexes on the Authors table with 80% fill factor, sort the intermediary data in TempDB and automatic updating of the statistics are enabled.   ALTER INDEX ALL ON Authors REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF); GO
DBCC INDEXDEFRAG - Defragments the au_id_ind index on the Authors table. DBCC INDEXDEFRAG (Pubs, Authors, au_id_ind) GO	ALTER INDEX - Defragment the au_id_ind index on the Authors table which is intended to be a truly online operation. ALTER INDEX au_id_ind ON dbo.Authors REORGANIZE; GO