MSSQL INDEX 인덱스

1. 인덱스 조회

sp_helpindex Table1

2. 인덱스 생성

CREATE INDEX 인덱스이름 ON 테이블이름(컬럼이름)

CREATE INDEX 인덱스이름 ON 테이블이름(컬럼이름 DESC, 컬럼이름 DESC)

CREATE INDEX IDX_OUTDATE_DESC ON Table1(OutDate DESC)

3. 인덱스 삭제

DROP INDEX 테이블이름.인덱스이름

DROP INDEX Table1.IDX_OUTDATE_DESC

4. SQL DB내부 전체 INDEX 검색조회

SELECT

    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,

    OBJECT_NAME(i.OBJECT_ID) AS TableName,

    i.name AS IndexName,

    i.index_id AS IndexID,

    CASE i.index_id WHEN 1 THEN 'Clustered Index' ELSE 'NonClustered Index' END AS IndexType,

    SUM(au.used_pages) * 8 AS 'Indexsize(KB)'

FROM

    sys.indexes AS i

    INNER JOIN sys.partitions AS p

       ON p.OBJECT_ID = i.OBJECT_ID

      AND p.index_id = i.index_id

    INNER JOIN sys.allocation_units AS au

      ON au.container_id = p.partition_id

WHERE

    i.index_id != 0                               -- not heap

    and OBJECT_SCHEMA_NAME(i.object_id) != 'sys'  -- not system table

GROUP BY

    OBJECT_SCHEMA_NAME(i.object_id),

    OBJECT_NAME(i.OBJECT_ID),

    i.OBJECT_ID,

    i.index_id,

    i.name

ORDER BY

    OBJECT_SCHEMA_NAME(i.object_id),

    OBJECT_NAME(i.OBJECT_ID),

    i.index_id