태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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

Posted at 2009.12.11 14:43 // in MSSQL/SQL Server 2005 new Feature // by ㏈ª ☞ β┖υΕJini.κR

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

 

'MSSQL > SQL Server 2005 new Feature' 카테고리의 다른 글

조인힌트  (0) 2010.01.07
Partitioned Table 고찰 Part 2  (3) 2010.01.06
SQL 2000 VS SQL2005 의 인덱스 관련 비교  (0) 2009.12.11
온라인 인덱스 구성(Online Index)  (0) 2009.12.11
미러 백업의 개요  (0) 2009.12.11
Partitioned Table 고찰 part-1  (0) 2009.12.11

댓글을 남겨주세요.