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

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 ISNULL

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 CLUSTEREDINDEX 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 CLUSTEREDINDEX 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

 
출처 : http://gdbt.tistory.com/69