테이블 변수에 인덱스 생성

테이블 변수에 인덱스 생성

---

index에서 테이블 변수 를 만들 수 있습니까 SQL Server 2000?

즉

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

이름에 색인을 만들 수 있습니까?

---

이 질문에는 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

이름에 색인을 만들 수 있습니까?

짧은 대답 : 그렇습니다.

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 )를 모든 인덱스 키 (중복이 아닌)에 추가하여 다시 고유성을 보장합니다.

SQL Server 2000-2012에서 테이블 변수에 대한 인덱스는 UNIQUE또는 PRIMARY KEY제약 조건을 만들어 내재적으로 만 만들 수 있습니다 . 이 제약 조건 유형의 차이점은 기본 키가 Null을 허용하지 않는 열에 있어야한다는 것입니다. 고유 제한 조건에 참여하는 열은 널 입력 가능할 수 있습니다. ( NULLs가 있는 상태에서 SQL Server의 고유 제약 조건 구현은 SQL 표준에 지정된 고유 제약 조건 이 아닙니다). 또한 테이블에는 하나의 기본 키만 있지만 여러 개의 고유 제한 조건이있을 수 있습니다.

이러한 논리적 제약 조건은 물리적으로 고유 한 인덱스로 구현됩니다. 명시 적으로 지정하지 않으면 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 키에 자동으로 추가 함을 기억하십시오).

A non unique clustered index can also be achieved by manually adding an IDENTITY column to act as a uniqueifier.

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

But this is not an accurate simulation of how a non unique clustered index would normally actually be implemented in SQL Server as this adds the "Uniqueifier" to all rows. Not just those that require it.

---

It should be understood that from a performance standpoint there are no differences between @temp tables and #temp tables that favor variables. They reside in the same place (tempdb) and are implemented the same way. All the differences appear in additional features. See this amazingly complete writeup: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

Although there are cases where a temp table can't be used such as in table or scalar functions, for most other cases prior to v2016 (where even filtered indexes can be added to a table variable) you can simply use a #temp table.

The drawback to using named indexes (or constraints) in tempdb is that the names can then clash. Not just theoretically with other procedures but often quite easily with other instances of the procedure itself which would try to put the same index on its copy of the #temp table.

To avoid name clashes, something like this usually works:

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

This insures the name is always unique even between simultaneous executions of the same procedure.

---

If Table variable has large data, then instead of table variable(@table) create temp table (#table).table variable doesn't allow to create index after insert.

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

1. Create table with unique clustered index

2. Insert data into Temp "#Table" table

3. Create non clustered indexes.

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

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