표 변수에 색인 작성

표 변수에 색인 작성

SQL Server 2000의 테이블 변수에 인덱스를 작성할 수 있습니까?

예.

DECLARE @TEMPTABLE TABLE (
     [ID] [int] NOT NULL PRIMARY KEY
    ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL 
)

Name

질문에는 SQL Server 2000이라는 태그가 붙어 있습니다만, 최신 버전으로 개발하시는 분들의 편의를 위해 먼저 대응하겠습니다.

SQL Server 2014

아래 SQL Server 2014에서 설명하는 제약조건 기반 인덱스를 추가하는 방법 외에 테이블 변수 선언에 인라인 구문을 사용하여 고유하지 않은 인덱스를 직접 지정할 수도 있습니다.

그 구문 예는 다음과 같습니다.

/*SQL Server 2014+ compatible inline index syntax*/
DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

필터링된 인덱스 및 열이 포함된 인덱스는 현재 이 구문을 사용하여 선언할 수 없지만 SQL Server 2016은 이 구문을 조금 더 완화합니다.CTP 3.1에서 테이블 변수의 필터링된 인덱스를 선언할 수 있게 되었습니다.RTM에 의해 포함된 컬럼도 허용될 수 있지만 현재 입장은 "리소스 제약으로 인해 SQL16으로 만들 수 없을 것"입니다.

/*SQL Server 2016 allows filtered indexes*/
DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

---

SQL Server 2000 - 2012

Name에 인덱스를 만들 수 있습니까?

단답: 네.

DECLARE @TEMPTABLE TABLE (
  [ID]   [INT] NOT NULL PRIMARY KEY,
  [Name] [NVARCHAR] (255) COLLATE DATABASE_DEFAULT NULL,
  UNIQUE NONCLUSTERED ([Name], [ID]) 
  ) 

자세한 답변은 다음과 같습니다.

SQL Server의 기존 테이블은 클러스터된 인덱스를 가질 수도 있고 힙으로 구성될 수도 있습니다.

클러스터된 인덱스는 중복 키 값을 허용하지 않도록 고유하다고 선언하거나 기본적으로 고유하지 않음으로 선언할 수 있습니다.고유하지 않은 경우 SQL Server는 중복 키에 고유 식별자를 자동으로 추가하여 고유하게 만듭니다.

클러스터되지 않은 인덱스는 고유하다고 명시적으로 선언할 수도 있습니다.그렇지 않으면 SQL Server가 행 로케이터(클러스터된 인덱스 키 또는 힙의 RID)를 모든 인덱스 키(복제된 키뿐 아니라)에 추가합니다.

2000 - 변수에 는 SQL Server 2000 - 2012를 해야만 암묵적으로 할 수 .UNIQUE ★★★★★★★★★★★★★★★★★」PRIMARY KEY약이있있 있있있다다이러한 제약 조건 유형의 차이는 기본 키가 null이 아닌 열에 있어야 한다는 것입니다.SQL Server가 (SQL Server)의 존재 하고 있는데도) (는 (SQL Server)의 존재에 따라) null을 구현하고 .NULLs' SQL Standard's 'SQL Standard' (SSQL Standard)또한 테이블에는 1개의 프라이머리 키만 사용할 수 있지만 여러 개의 고유한 제약 조건을 설정할 수 있습니다.

이러한 논리적 제약 조건은 모두 고유한 인덱스를 사용하여 물리적으로 구현됩니다.되어 있지 않은 는, 「」를 참조해 주세요.PRIMARY KEY만, 이 은 「」를 해 수 .CLUSTERED ★★★★★★★★★★★★★★★★★」NONCLUSTERED으로 제약의 예).약조건 선언을 명시적으로 사용(구문의 예)

DECLARE @T TABLE
(
A INT NULL UNIQUE CLUSTERED,
B INT NOT NULL PRIMARY KEY NONCLUSTERED
)

위의 결과로 SQL Server 2000 - 2012의 테이블 변수에 다음과 같은 인덱스를 암묵적으로 생성할 수 있습니다.

+-------------------------------------+-------------------------------------+
|             Index Type              | Can be created on a table variable? |
+-------------------------------------+-------------------------------------+
| Unique Clustered Index              | Yes                                 |
| Nonunique Clustered Index           |                                     |
| Unique NCI on a heap                | Yes                                 |
| Non Unique NCI on a heap            |                                     |
| Unique NCI on a clustered index     | Yes                                 |
| Non Unique NCI on a clustered index | Yes                                 |
+-------------------------------------+-------------------------------------+

마지막은 조금 설명이 필요합니다.이 답변의 시작 부분에 있는 테이블 변수 정의에서 고유한 클러스터되지 않은 인덱스는 다음과 같습니다.Name에 대한 고유 인덱스에 의해 시뮬레이션됩니다.Name,Id(SQL Server가 클러스터된 인덱스 키를 고유하지 않은NCI 키에 자동으로 추가하도록 호출합니다).

인덱스는 수 .IDENTITY고유 식별자 역할을 하는 컬럼입니다.

DECLARE @T TABLE
(
A INT NULL,
B INT NULL,
C INT NULL,
Uniqueifier INT NOT NULL IDENTITY(1,1),
UNIQUE CLUSTERED (A,Uniqueifier)
)

그러나 이것은 "Uniqueifier"가 모든 행에 추가되기 때문에 SQL Server에서 실제로 구현되는 비고유 클러스터 인덱스의 정확한 시뮬레이션은 아닙니다.필요한 사람들만 그런 게다

성능의 관점에서 변수를 선호하는 @temp 테이블과 #temp 테이블 사이에는 차이가 없음을 이해해야 합니다.같은 장소(tempdb)에 상주하며 동일한 방법으로 구현됩니다.모든 차이는 추가 기능으로 나타납니다.이 놀라운 완전한 문서를 참조하십시오.https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

table이나 scalar 함수 등 temp 테이블을 사용할 수 없는 경우가 있지만 v2016 이전(필터링된 인덱스까지 테이블 변수에 추가할 수 있는 경우)에는 #temp 테이블을 사용할 수 있습니다.

tempdb에서 이름 있는 인덱스(또는 제약조건)를 사용할 경우 이름이 충돌할 수 있다는 단점이 있습니다.이론적으로는 다른 프로시저뿐만 아니라 #temp 테이블의 복사본에 동일한 인덱스를 붙이려고 하는 프로시저 자체의 다른 인스턴스에도 쉽게 접근할 수 있습니다.

이름 충돌을 피하기 위해 일반적으로 다음과 같은 작업을 수행합니다.

declare @cmd varchar(500)='CREATE NONCLUSTERED INDEX [ix_temp'+cast(newid() as varchar(40))+'] ON #temp (NonUniqueIndexNeeded);';
exec (@cmd);

이것에 의해, 같은 순서를 동시에 실행하는 경우에서도, 이름은 항상 일의입니다.

Table 변수에 큰 데이터가 있는 경우 table 변수(@table) 대신 temp table(#table)을 작성합니다.table 변수에서는 삽입 후 인덱스를 작성할 수 없습니다.

 CREATE TABLE #Table(C1 int,       
  C2 NVarchar(100) , C3 varchar(100)
  UNIQUE CLUSTERED (c1) 
 ); 

1. 고유한 클러스터 인덱스로 테이블 생성

2. Temp "#Table" 테이블에 데이터 삽입

3. 클러스터되지 않은 인덱스를 만듭니다.

    CREATE NONCLUSTERED INDEX IX1  ON #Table (C2,C3);
   

언급URL : https://stackoverflow.com/questions/886050/creating-an-index-on-a-table-variable