tipo - tabla temporal sql

Creando un índice en una variable de tabla

Question

¿Puedes crear un index en una variable de tabla en SQL Server 2000 ?

es decir

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

¿Puedo crear un índice en Name?

Answer 1

Debe entenderse que, desde el punto de vista del rendimiento, no existen diferencias entre las tablas @temp y las tablas #temp que favorecen las variables. Residen en el mismo lugar (tempdb) y se implementan de la misma manera. Todas las diferencias aparecen en características adicionales. Vea este informe increíblemente completo: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

Aunque hay casos en los que no se puede usar una tabla temporal, como en tablas o funciones escalares, en la mayoría de los casos anteriores a v2016 (donde incluso los índices filtrados se pueden agregar a una variable de tabla) simplemente puede usar una tabla #temp.

El inconveniente de usar índices con nombre (o restricciones) en tempdb es que los nombres pueden entrar en conflicto. No solo teóricamente con otros procedimientos, sino a menudo con bastante facilidad con otras instancias del procedimiento en sí que tratarían de poner el mismo índice en su copia de la tabla #temp.

Para evitar conflictos de nombres, algo como esto generalmente funciona:

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

Esto asegura que el nombre sea siempre único incluso entre ejecuciones simultáneas del mismo procedimiento.

Answer 2

La pregunta está etiquetada como SQL Server 2000, pero para beneficio de las personas que están desarrollando la última versión, la abordaré primero.

SQL Server 2014

Además de los métodos para agregar índices basados ​​en restricciones que se describen a continuación, SQL Server 2014 también permite que los índices no únicos se especifiquen directamente con la sintaxis en línea en las declaraciones de variables de tabla.

La sintaxis de ejemplo para eso está debajo.

/*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*/
);

Los índices e índices filtrados con columnas incluidas no se pueden declarar actualmente con esta sintaxis; sin embargo, SQL Server 2016 relaja esto un poco más. Desde CTP 3.1 ahora es posible declarar índices filtrados para variables de tabla. Por RTM puede ser que las columnas incluidas también estén permitidas, pero la posición actual es que "probablemente no lleguen a SQL16 debido a limitaciones de recursos".

/*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

¿Puedo crear un índice en Name?

Respuesta corta: Sí.

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

Una respuesta más detallada está debajo.

Las tablas tradicionales en SQL Server pueden tener un índice agrupado o están estructuradas como heaps .

Los índices agrupados pueden declararse como exclusivos para no permitir valores de clave duplicados o predeterminados como no exclusivos. Si no es exclusivo, SQL Server agrega silenciosamente un uniqueifier a las claves duplicadas para que sean únicas.

Los índices no agrupados también se pueden declarar explícitamente como únicos. De lo contrario, para el caso no único, SQL Server agrega el localizador de filas (clave de índice agrupado o RID para un montón) a todas las claves de índice (no solo a las duplicadas), esto de nuevo asegura que son únicas.

En SQL Server 2000 - 2012, los índices de las variables de la tabla solo pueden crearse implícitamente al crear una restricción UNIQUE o PRIMARY KEY . La diferencia entre estos tipos de restricciones es que la clave primaria debe estar en columna (s) no anulables. Las columnas que participan en una restricción única pueden ser anulables. (aunque la implementación de SQL Server de restricciones únicas en presencia de NULL s no es por lo especificado en el estándar SQL). Además, una tabla solo puede tener una clave principal pero múltiples restricciones únicas.

Ambas limitaciones lógicas se implementan físicamente con un índice único. Si no se especifica explícitamente, la PRIMARY KEY se convertirá en el índice agrupado y las restricciones exclusivas no agrupadas, pero este comportamiento se puede anular al especificar CLUSTERED o NONCLUSTERED explícita con la declaración de restricción (sintaxis de ejemplo)

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

Como resultado de lo anterior, los siguientes índices pueden crearse implícitamente en variables de tabla en 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                                 |
+-------------------------------------+-------------------------------------+

El último requiere un poco de explicación. En la definición de variable de tabla al principio de esta respuesta, el índice no agrupado no exclusivo en Name es simulado por un índice único en Name,Id (recuerde que SQL Server agregaría silenciosamente la clave de índice agrupado a la clave NCI no única de todos modos).

También se puede lograr un índice agrupado no exclusivo al agregar manualmente una columna de IDENTITY para que actúe como un único elemento.

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

Pero esta no es una simulación precisa de cómo un índice agrupado no exclusivo normalmente se implementaría en SQL Server, ya que esto agrega el "Uniqueifier" a todas las filas. No solo aquellos que lo requieren.