Cómo implementar una funcionalidad del tipo vector en SQL Server

Introducción

Estaba dando una capacitación a algunos DBAs de Oracle en T-SQL y ellos me preguntaron cómo poder crear vectores en SQL Server.

Les dije que no existen matrices o vectores en SQL Server como las que tenemos en Oracle (varray). Se decepcionaron de esto y me preguntaron cómo se maneja este problema.

Algunos desarrolladores me preguntan lo mismo. ¿Dónde están los vectores en SQL Server?

La respuesta más concreta fue que usamos tablas temporales o TVP (parámetros con valores de tabla) en lugar de vectores. Nosotros utilizamos otras funciones para poder reemplazar los vectores utilizados.

El uso de tablas temporales, TVP y variables table se explican en otro artículo:

La base de datos tempdb, introducción y recomendaciones

En este artículo, vamos a mostrar:

Cómo poder usar una variable de tabla en lugar de un vector
La función STRING_SPLIT, que nos ayudará a reemplazar la funcionalidad de un vector
Cómo empezar a trabajar con versiones anteriores de SQL Server para poder manejar una lista de valores separados por comas

Requisitos

SQL Server 2016 o posterior con SSMS instalado
La base de datos Adventureworks instalada

Empezando

Cómo poder usar una variable de tabla en lugar de un vector

En la primera demostración, mostraremos cómo poder usar una variable de tabla en lugar de un vector.

Crearemos primeramente una variable de tabla usando T-SQL:

DECLARE @myTableVariable TABLE (id INT, name varchar(20))

insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan')

select * from @myTableVariable

Creamos una variable de tabla llamada myTableVariable e insertamos 3 filas, después realizaremos una selección en la variable de tabla.

La selección mostrará los siguientes valores:

Ahora, mostraremos la información de la tabla Person.person de la base de datos adventureworks que coincida con los nombres de la variable de tabla:

DECLARE @myTableVariable TABLE (id INT, name varchar(20))

insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan')

SELECT [BusinessEntityID]

,[PersonType]

,[NameStyle]

,[Title]

,[FirstName]

,[MiddleName]

,[LastName]

FROM [Adventureworks].[Person].[Person] where

FirstName

IN (Select name from @myTableVariable)

Los resultados nos mostrarán los nombres y la información de la tabla Person.person incluyendo los nombres de Roberto, Gail y Dylan:

Tenga en cuenta que, en el SQL Server, es mejor usar sentencias SQL para comparar valores. Esto resulta más eficiente. No usamos bucles (WHILE) en general porque es más lento y no es muy eficiente.

Puede usar la identificación para recuperar valores de una fila específica. Para dar un ejemplo, para Roberto la identificación es 1, para Dylan la identificación es 3 y para Gail la identificación es 2.

En C#, por ejemplo, si desea enumerar el segundo miembro de un vector, debe ejecutar algo como esto:

Array[1];

Se utilizan los corchetes y el número 1 muestra el segundo número del vector (el primero es 0).

En una variable de tabla, puede usar la identificación o ID. Si usted desea enumerar el segundo miembro (id = 2) de la variable de tabla, puede hacer algo como lo siguiente:

DECLARE @myTableVariable TABLE (id INT, name varchar(20))

insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan')

select * from @myTableVariable where id=2

En otras palabras, usted puede utilizar la identificación o id para obtener un miembro específico de la variable de tabla.

El problema con las variables table es que se necesita insertar valores y requiere más código para tener una tabla simple con pocas filas.

En C#, por ejemplo, para crear un vector, solo se necesita escribir los elementos y no necesita insertar datos en la tabla:

1	string[] names = new string[] {"Gail","Roberto","Dylan"};

Requiere una sola línea de código tener el vector con elementos. ¿Podemos hacer algo similar en SQL Server?

La siguiente solución nos ayudará a lograr esto.

La función STRING_SPLIT función

Esta es otra solución que lo que hace es reemplazar las vectores con el uso de la nueva función STRING_SPLIT. Esta función es aplicable en SQL Server 2016 o versiones posteriores y aplicable en Azure SQL.

Si usa la función en una base de datos antigua de Adventureworks o en SQL Server 2014 o anterior, es posible que pueda recibir un mensaje de error. El siguiente ejemplo intentará dividir 3 nombres separados por comas:

1	SELECT value FROM STRING_SPLIT('Roberto,Gail,Dylan', ',');

Un mensaje de error más común sería el siguiente:

Mensaje 208, Nivel 16, Estado 1, Línea 8
Nombre de objeto no válido “STRING_SPLIT”

Si recibe este error en SQL Server 2016, verifique el nivel de compatibilidad de su base de datos:

SELECT compatibility_level

FROM sys.databases WHERE name = 'AdventureWorks';

Si su nivel de compatibilidad es inferior a 130, use la siguiente oración T-SQL para cambiar el nivel de compatibilidad:

1	ALTER DATABASE [Adventureworks] SET COMPATIBILITY_LEVEL = 130

Si no le gusta T-SQL, puede hacer clic derecho en la base de datos en SSMS e ir a las opciones y cambiar el nivel de compatibilidad:

La oración T-SQL convertirá los valores separados por comas en filas:

1	SELECT value FROM STRING_SPLIT('Roberto,Gail,Dylan', ',');

Los valores se convertirán en filas:

En la función STRING_SPLIT, debe especificar el separador.

La siguiente consulta mostrará la información de las personas en la tabla person.person que coincide con los nombres utilizados en la función STRING_SPLIT:

SELECT [BusinessEntityID]

,[PersonType]

,[NameStyle]

,[Title]

,[FirstName]

,[MiddleName]

,[LastName]

FROM [Adventureworks].[Person].[Person] where

FirstName

IN (SELECT value FROM STRING_SPLIT('Roberto,Gail,Dylan', ','));

La consulta nos mostrará la información sobre las personas con los nombres iguales a Roberto o Gail o Dylan:

Si desea tratar de recuperar un miembro específico de la cadena, puede asignar un número de fila, a cada fila de miembros de STRING_SPLIT. El siguiente código nos muestra cómo poder recuperar la información:

WITH fakearray AS

(

SELECT

ROW_NUMBER() OVER(ORDER BY value DESC) AS ID,value FROM STRING_SPLIT('Roberto,Gail,Dylan', ',')

)

SELECT ID, value

FROM fakearray

WHERE ID =3

ROW_NUMBER se usa para agregar una identificación a cada nombre. Por ejemplo, Roberto tiene id = 1, Gail id = 2 y Dylan 3.

Una vez que tenga la consulta en una expresión CTE, puede hacer una instrucción select y usar WHERE para especificar una ID. En este ejemplo, la consulta nos mostrará la información de Dylan (ID = 3). Como puede ver, recuperar un valor de un miembro específico del vector falso no es difícil, pero esto requiere más código que un lenguaje de programación que admita vectores.

Cómo poder trabajar con versiones anteriores de SQL Server

STRING_SPLIT es bastante útil, pero ¿cómo se maneja en versiones anteriores?

Hay muchas formas de resolver esto, pero utilizaremos la solución con XML. El siguiente ejemplo nos va a demostrar cómo poder mostrar los valores que coinciden con los resultados de un vector falso:

DECLARE @oldfakearray VARCHAR(100) = 'Roberto,Gail,Dylan';

DECLARE @param XML;

SELECT @param = CAST('<i>' + REPLACE(@oldfakearray,',','</i><i>') + '</i>' AS XML)

SELECT [BusinessEntityID]

,[PersonType]

,[NameStyle]

,[Title]

,[FirstName]

,[MiddleName]

,[LastName]

FROM [Adventureworks].[Person].[Person]

WHERE FirstName IN

(SELECT x.i.value('.','NVARCHAR(100)') FROM @param.nodes('//i') x(i))

El código hará lo mismo que la solución STRING_SPLIT o la variable de tabla:

En la primera línea, simplemente creamos un nuevo vector falso llamado oldfakearray y asignamos los nombres en la variable:

1	DECLARE @oldfakearray VARCHAR(100) = 'Roberto,Gail,Dylan';

En la segunda línea, estamos declarando una variable XML:

1	DECLARE @param XML;

En la siguiente línea de código, estamos eliminando la coma y creando un XML que contiene los valores de oldfakearray:

1	SELECT @param = CAST('<i>' + REPLACE(@oldfakearray,',','</i><i>') + '</i>' AS XML)

Y finalmente, estamos realizando una selección de la tabla Person.Person de la base de datos Adventureworks donde el nombre está en la variable @param:

SELECT [BusinessEntityID]

,[PersonType]

,[NameStyle]

,[Title]

,[FirstName]

,[MiddleName]

,[LastName]

FROM [Adventureworks].[Person].[Person]

WHERE FirstName IN

(SELECT x.i.value('.','NVARCHAR(100)') FROM @param.nodes('//i') x(i))

Como puede ver, no es un vector, pero nos ayuda a poder comparar una lista de valores con una tabla.

Conclusión

Como se puede ver, SQL Server no incluye vectores. Pero podemos usar variables table, tablas temporales o la función STRING_SPLIT. No obstante, la función STRING_SPLIT es nueva y solo se puede usar en SQL Server 2016 o versiones posteriores.

Si usted no tiene esa versión de SQL Server, hemos mostrado métodos más antiguos para poder dividir cadenas separadas por comas. Mostramos el método que utiliza funciones de XML.

Acerca de
Últimas entradas

Daniel Calbimonte

Daniel Calbimonte es un Microsoft Most Valuable Professional, Microsoft Certified Trainer y Microsoft Certified IT Professional en SQL Server. He is an accomplished SSIS author, Intructor en academias de IT y tienedécadas de experience trabajando con diferentes bases de datos.

Realizó consultorias para empresas gubernamentales, empresas petroleras, sitios web, revistas y universidades alrededor del mundo. Daniel participa regularmente en conferencias de SQL Server y blogs. Escribe material sobre SQL Server y exámenes de certificación.

También realiza la traducción de artículos de SQLShack al español

Ver todas las publicaciones de Daniel Calbimonte

Últimas entradas de Daniel Calbimonte (ver todo)

Cómo construir un cubo desde cero usando SQL Server Analysis Services (SSAS) - December 16, 2019
Fecha de conversión de SQL - December 11, 2019
Funciones frente a los procedimientos almacenados en SQL Server - October 14, 2019

Introducción

Empezando

Conclusión

Related posts: