Создание индекса для табличной переменной

вы можете создать index на табличной переменной в SQL Server 2000?

т. е. 

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

могу ли я создать индекс по имени?

2 148
sql sql-server tsql indexing table-variable

2 ответа:

вопрос помечен 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 молча добавляет uniqueifier для любых дубликатов ключей, чтобы сделать их уникальными.

некластеризованные индексы также могут быть явно объявлены уникальными. В противном случае для не уникального случая SQL Server добавляет указатель на строку (ключ кластеризованного индекса или удалить для кучи) для всех ключей индекса (а не только дубликатов) это снова гарантирует, что они уникальны.

в SQL Server 2000 - 2012 индексы табличных переменных могут быть созданы неявно только путем создания UNIQUE или PRIMARY KEY ограничения. Разница между этими типами ограничений заключается в том, что первичный ключ должен находиться в Столбцах, не допускающих значения null. Столбцы, участвующие в уникальном ограничении, могут быть обнулены. (хотя реализация SQL Server уникальных ограничений при наличии NULLs не соответствует тому, что указано в стандарте SQL). Также таблица может иметь только один первичный ключ, но несколько уникальных ограничения.

оба этих логических ограничения физически реализованы с уникальным индексом. Если явно не указано иначе PRIMARY KEY станет кластеризованным индексом и уникальными ограничениями некластеризованными, но это поведение можно переопределить, указав CLUSTERED или NONCLUSTERED явно с объявлением ограничения (пример синтаксиса)

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

в результате вышеизложенного следующие индексы могут быть неявно созданы на табличных переменных в SQL Сервер 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 столбец to действуйте как уникальный человек.

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

но это не точная имитация того, как не уникальный кластеризованный индекс обычно будет реализован в SQL Server, поскольку это добавляет "Uniqueifier" ко всем строкам. Не только те, которые этого требуют.

289

следует понимать, что с точки зрения производительности нет никаких различий между таблицами @temp и таблицами #temp, которые благоприятствуют переменным. Они находятся в одном и том же месте (tempdb) и реализуются одинаково. Все различия проявляются в дополнительных функциях. Смотрите эту удивительно полную запись: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

хотя там в тех случаях, когда временная таблица не может быть использована, например, в табличных или скалярных функциях, для большинства других случаев до v2016 (где даже отфильтрованные индексы могут быть добавлены в переменную таблицы) вы можете просто использовать таблицу #temp.

недостатком использования именованных индексов (или ограничений) в базе данных tempdb является то, что имена могут конфликтовать. Не только теоретически с другими процедурами, но часто довольно легко с другими экземплярами самой процедуры, которые попытаются поместить тот же индекс на свою копию временная таблица.

чтобы избежать столкновения имен, обычно работает что-то вроде этого:

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

это гарантирует, что имя всегда уникально даже между одновременными выполнением одной и той же процедуры.

11