Variables Tipo Tabla

Como usar variables tipo tabla

  • Las Variables Tipo Tabla son tipos de datos que generalmente son utilizados en un lote T-SQL,  procedimiento almacenado o función definida por el usuario.
  • Las variables tipo tabla se crea y definen igual a las tablas con la diferencia que tienen una alcance de vida definido.
  • Se debe evaluar usando los planes de ejecución de cada opción entre Variables tipo tabla y tablas temporales.
  • Use las variables tipo tabla o las tablas temporales con conjunto de datos pequeños.

Beneficios

  • Duración o alcance. estos objetos viven únicamente durante la ejecución del lote, función o procedimiento almacenado.
  • Tiempos de bloqueo más cortos.
  • Cuando se usan en procedimientos almacenados realizan menos re compilaciones.

Consideraciones

El rendimiento de las variable tipo tabla no es óptimo cuando el resultado es demasiado grande.

Sintaxis

Declare @NombreVariableTipoTabla Table
(
Campo1 TipoDatos, …
)

 

Limitaciones del uso de variables tipo tabla

  • No se puede usar la opción Into en un Select (Ver Consultas Opciones)
  • No se puede truncar la tabla (Truncate)
  • Una vez creadas no se pueden modificar (Ver Alter Table)
  • Sólo permite clave primaria y restricción Unique.
  • No se puede usar funciones definidas por el usuario (UDF) en un check constraint, columna calculada o default constraint. (Ver FDU)
  • No se puede usar tipos de datos definidos por el usuario (Ver Tipos de datos definidos por el usuario)
  • No se puede eliminar la tabla usando Drop Table
  • Las variables de tipo tabla no se pueden acceder en procedimientos anidados

 

Ejemplos

Usando Northwind
use Northwind
go

Ejercicio 01
Crear una tabla de tipo variable y asignar los datos de los productos de categoría 1

Declare @ProductosCategoria1 Table
( Codigo nchar(3) Primary key,
Descripcion nvarchar(50)
)
Insert into @ProductosCategoria1 select ProductID, ProductName from Products
where CategoryID = 1
select * from @ProductosCategoria1
go

El resultado se muestra en la siguiente imagen




Ejercicio 02
Crear una tabla con los datos de los clientes y la cantidad de órdenes generadas en un año determinado
Ejemplo: Para 1997

Declare @TotalOrdenesClientes1997 Table
(CodigoCliente nchar(5), NombreCliente nvarchar(100),
CantidadOrdenes Numeric(9,0))
Insert into @TotalOrdenesClientes1997
select C.CustomerID, C.CompanyName , COUNT(O.OrderID)
from Customers As C
join Orders As O on C.CustomerID = O.CustomerID
where YEAR(O.OrderDate) = 1997
Group by C.CustomerID, C.CompanyName
select * from @TotalOrdenesClientes1997 order by CantidadOrdenes desc
go

Resultado:

Usando tabla temporal, el bloque anterior puede ser como sigue

Create table #TotalOrdenesClientes1997
(CodigoCliente nchar(5), NombreCliente nvarchar(100),
CantidadOrdenes Numeric(9,0))
Insert into #TotalOrdenesClientes1997
select C.CustomerID, C.CompanyName , COUNT(O.OrderID)
from Customers As C
join Orders As O on C.CustomerID = O.CustomerID
where YEAR(O.OrderDate) = 1997
Group by C.CustomerID, C.CompanyName
select * from #TotalOrdenesClientes1997 order by CantidadOrdenes desc
go

En este caso, queda la tabla temporal creada y se tendrá que eliminar explícitamente usando Drop o cuando el usuario se desconecte se eliminará de manera automática.
Si analiza los costos de cada opción del plan de ejecución podrá notar que para este conjunto de resultados es igual al usar variables de tipo tabla o tablas temporales.

Ejercicio 03
Crear una variable tipo tabla e insertar los registros manualmente

Declare @Areas table
(AreasCodigo nchar(5), AreasNombre nvarchar(100),
AreasFechaCreacion Date)
Insert into @Areas
values (‘98653′,’Gerencia General’,’15/10/2014′), (‘45732′,’Producción’,’16/11/1996′),
(‘57794′,’Ventas’,’21/11/2015′),(‘35795′,’Logística’,’01/10/2000′),
(‘90569′,’Contabilidad’,’10/02/1999′),(‘34947′,’Proyectos’,’24/02/1996′)
select * from @Areas order by AreasNombre
go

Importante
Si ejecuta la consulta para mostrar los datos de la tabla @Areas fuera del lote

select * from @Areas order by AreasNombre
go

Aparece un mensaje que falta declarar la variable de tabla @Areas