Arreglos en SQL Server

El uso de arreglos (o arrays) es tan necesario en ciertas ocasiones que ha hecho que su aparición sea tan inmediata como los primeros lenguajes declarativos, y hasta la actualidad, los lenguajes más modernos no los han descartado.

Assember

mov [BX],AX;

C

array = (int *)malloc (N*sizeof(int));

Adda

type Int_Buffer is array (1..10) of Integer;

C++

int *iarray;iarray = new int [10];

Basic

DIM vars$(52)

Microsoft© Visual Basic

Dim c(3 to 82) as Integer

C#

int[] numbers = new int[10];

Entonces ¿podrían ser los arreglos tan necesarios en el T-SQL de Sql Server? La respuesta es que depende para quien, como suelen ser las respuestas a casi todas las preguntas formulables. Quizás esta necesidad no se manifieste tanto dentro del propio motor de base de datos, ya que siempre es posible acceder a los datos necesarios de alguna u otra forma, pero dentro del mundo de los desarrolladores la necesidad es innegable, como lo veremos con un ejemplo posteriormente. Claro que todo este preámbulo no tendría sentido si el T-SQL de Sql Server tuviese arreglos, pero de hecho no los posee (dejemos de lado por ahora la llegada de SQL Server 2008). ¿Entonces como se puede resolver este problema?, plantearemos entonces el siguiente caso que es, como acostumbramos, un caso del mundo real.

Hace un tiempo desarrollamos una aplicación que empleaba SQL Server 2005 como gestor para el soporte de datos. No tardó en aparecer la siguiente situación, entre nuestras tablas se daba, por las reglas de negocios el caso de que cuando se daba de alta un registro en una tabla del tipo «cabecera» debían asociársele unos cuantos registros del tipo «detalle» conjuntamente. Supongamos el caso de las tablas que se muestran a continuación que representan la estructura del problema abstrayendo complicaciones adicionales.

En nuestro caso estábamos empleando C# sobre el Framework 3.5 y ADO.NET para que nuestra capa de datos accediera al motor de base de datos SQL Server 2005, ante el problema analizamos las siguientes opciones:

1. Emplear procedimientos almacenados (haciendo tantas llamadas como fueran necesarias).
2. Emplear consultas dinámicas.
3. Emplear un procedimiento almacenado que resolviera todo de alguna forma.

Recordemos que nuestro problema a resolver era que cuando se creaba un registro cabecera, en conjunto se creaban siempre varios registros detalle.

La primera aproximación fue la más inocente, poseer un procedimiento almacenado que inserta una cabecera y otro que inserta un detalle, llamémoslos ins_cabecera e ins_detalle para darle nombres intuitivos, entonces si había que crear una cabecera con, por ejemplo, 10 detalles. La metodología era llamar a los procedimientos almacenados desde nuestra capa de datos en .NET de la siguiente forma

ins_cabecera "datos cabecera"
ins_detalle "datos detalle 1"
ins_detalle "datos detalle 2"
ins_detalle "datos detalle 3"
ins_detalle "datos detalle 4"
ins_detalle "datos detalle 5"
ins_detalle "datos detalle 6"
ins_detalle "datos detalle 7"
ins_detalle "datos detalle 8"
ins_detalle "datos detalle 9"
ins_detalle "datos detalle 10"

Claro está que funciona perfectamente, pero hay algo que no se ve muy bien y es cada vez que se llama a una inserción en la capa de datos de .NET se produce una larga cantidad de llamadas contra el motor de base de datos, a pesar de ser procedimientos almacenados, claro está que hay una gran pérdida de tiempo. Inmediatamente se pensó que había que evitar hacer tantas llamadas.

Una segunda opción, (que creo que la descartamos incluso desde antes que a alguien se le ocurriera mencionarla), es la del uso de consultas dinámicas. Básicamente es la idea de escribir una larga sentencia T-SQL y pasársela al servidor de una sola vez para que el mismo la ejecute, este tipo de solución haría una sola llamada, claro está, pero hay muchas desventajas al usar consultas dinámicas en contrapartida a procedimientos almacenados.

En SQL Server (y estoy convencido que en cualquier otro motor de base de datos también), el uso de procedimientos almacenados ofrece ventajas en contraste a las consultas dinámicas, un factor clave es por ejemplo la compilación del plan de trabajo (la estrategia que empleará el motor de base de datos para acceder a los datos) que es reutilizada en procedimientos almacenados y no tiene que recalcularse cada vez que se lanza la consulta, un factor que suma puntos a la hora de evaluar la performance.

Además el uso de procedimientos almacenados asegura la inmunidad contra ataques por injection, como ya sabemos es posible crear consultas dinámicas parametrizadas, pero queda en manos de l del programador utilizarlos, quien puede omitir esta práctica y armar las sentencias por simple concatenación, quedando el sitio vulnerable a ataques por injection, (aunque parezca una locura, claramente este problema es más común de lo que uno se imagina), en cambio, en las llamadas a procedimientos almacenados no existe la posibilidad de dejar esa puerta de entrada. Finalmente otra ventaja es que los procedimientos almacenados pueden invocarse unos a otros, lo cual puede permitir atomizar y reutilizar código, otro punto a favor (Exceptuando lamentablemente nuestro caso en SQL Server 2005 donde la opción no está disponible) los procedimientos almacenados pueden depurarse con mucha facilidad, estos son a mi parecer motivos suficientes para desterrar a las consultas dinámicas, aunque hay situaciones en que no es posible utilizar procedimientos almacenados, por ejemplo existen aplicaciones que crean, modifican y destruyen estructuras de datos dentro del motor de base de datos empleando instrucciones DDL, en éstos casos el acceso a datos posiblemente deberá llevarse a cabo mediante consultas dinámicas. Esperemos que los programados sean cautos en esos casos delicados.

Entonces nos quedó nuestra última carta emplear una sola llamada a un procedimiento almacenado que resolviera todo el problema. Siendo algo así como la solución mágica, la misma consideraría pasarle a nuestro procedimiento almacenado, llamémoslo ins_cabdet, los datos de la cabecera y una estructura con todos los detalles (ya que no sabemos cuántos puede haber) para que los inserte juntos ins_cabdet de una sola vez.

Nuestra función desde la capa de datos en .NET tomaría la siguiente forma:

ins_cabdet "datos cabecera", Estructura("datos detalle 1", "datos detalle 2" ... "datos detalle 10");

Pero aquí mismo es donde aparece el problema, un arreglo nos vendría más que bien en este caso, pero ya dijimos que SQL Server no soporta arreglos (para quien recién halla cambiado de canal y se haya perdido el principio, recordemos que estamos omitiendo SQL Server 2008). Claramente ahora deberíamos pensar que los arreglos si eran tan necesarios el T-SQL de Sql Server. De todas formas no todo estuvo perdido, el problema pudimos resolverlo desde otro lugar algo lejano. Una de las características que posee SQL Server 2005 es el uso de XML, SQL Server 2005 posee un tipo de datos XML y es capaz de parsear XML’s empleando incluso XPath, entonces la idea fue clara. si no podemos pasarle arreglos al SQL Server intentemos pasarle un parámetro XML con los datos necesarios para poder realizar las inserciones, el XML es flexible y fácil de usar por lo que este tipo de solución es adaptable a muchos casos similares. El precio es claramente la creación del XML desde .NET y el parseo desde SQL Server, pero hoy día ya casi nada es gratis.

Debimos diseñar primero un formato de XML, creamos algo similar a lo siguiente:

<r>
    <h nm="nombre_cab" />
    <dc>
        <d nm="nombre_de1"/>
        <d nm="nombre_de2"/>
    </dc>
</r>

Con nuestro fragmento XML armamos un procedimiento almacenado, al que llamamos Ins_CabDet el cual haría lo siguiente:

• Extracción de cabecera del xml
• Inserción de cabecera
• Para cada detalle del xml
  • Extracción del detalle del xml
  • Inserción del detalle

  En el mundo real la función tomó la siguiente forma:

  CREATE PROCEDURE Ins_CabDet
   @doc xml
  AS
  BEGIN
   BEGIN TRY
    DECLARE @docHandle int
    EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRAN
    INSERT INTO Cabecera (Nombre)
    SELECT nm 'Nombre'
    FROM
    OPENXML (@docHandle,'r/h',1)
     WITH (
      nm nvarchar(20)
     )
    DECLARE @IDCabecera int
    SET @IDCabecera = @@IDENTITY
    INSERT INTO Detalle (IDCabecera, Nombre, OtroDato1, OtroDato2)
    SELECT @IDCabecera 'IDCabecera', nm 'Nombre', od1 'OtroDato1',  od2 'OtroDato2'
    FROM
    OPENXML (@docHandle,'r/dc/d',1)
     WITH (
      nm nvarchar(20),
      od1 int,
      od2 datetime
     )
    COMMIT
    RETURN 0
   END TRY
   BEGIN CATCH
    IF @@TRANCOUNT > 0
     ROLLBACK
    RETURN -1
   END CATCH
  END

  Básicamente hace lo esperado, parsea el XML y lo inserta en las tablas adecuadamente.

  Finalmente quedaba un último punto, efectuar la llamada desde la capa de datos en .NET, escribimos el código necesario (en nuestro caso estábamos empleando VS2008) para armar el XML y llamar al procedimiento almacenado, creamos una función similar a la siguiente:

  public void Ins_CabDet(string Cab_Nombre, List Detalles) {
   using (SqlCommand objSCmd = new SqlCommand("Ins_CabDet", new SqlConnection("Data  Source=XXX...."))) {
    objSCmd.CommandType = CommandType.StoredProcedure;
    objSCmd.Connection.Open();
    MemoryStream objMS = new MemoryStream();
    using (XmlWriter objXMLW = XmlWriter.Create(objMS))
    {
     objXMLW.WriteStartElement("r");
     objXMLW.WriteStartElement("h");
     objXMLW.WriteAttributeString("nm", Cab_Nombre);
     objXMLW.WriteEndElement();
     objXMLW.WriteStartElement("dc");
     foreach (Detalle objDet in Detalles) {
      objXMLW.WriteStartElement("d");
      objXMLW.WriteAttributeString("nm", objDet.Nombre);
      objXMLW.WriteAttributeString("od1", objDet.OtroDato1.ToString());
      objXMLW.WriteAttributeString("od2", objDet.OtroDato2.ToString("yyyyMMdd", CultureInfo.InvariantCulture));
      objXMLW.WriteEndElement();
     }
     objXMLW.WriteEndElement();
     objXMLW.WriteEndElement();
     objMS.Position = 0;
     objSCmd.Parameters.Add("@doc", SqlDbType.Xml).Value = new SqlXml(objMS);
    }
    objSCmd.ExecuteNonQuery();
   }
  }

  Detalle es la estructura:

  public struct Detalle {
   public string Nombre { set; get; }
   public int OtroDato1 { set; get; }
   public DateTime OtroDato2 { set; get; }
  }

  Con esta simple solución pudimos resolver el problema. Un punto a considerar es que en realidad no estamos pasando arreglos, de alguna forma estamos adaptando algo que fue pensado para otra cosa, como una especie de artificio para conseguir nuestro fin, por lo que este tipo de solución no es una solución universal y debería evaluarse en qué casos simplifica y en que otros complica el problema a resolver. Otra opción que no mencionamos es crear un tipo de datos y un procedimiento CLR, aprovechando las ventajas de poder ejecutar código manejado dentro del motor SQL Server 2005, esta es una opción válida que dependiendo del caso también podría implementarse.

  Arreglos en SQL 2008

  La versión 2008 de Sql Server ha resuelto el problema, finalmente hay arreglos disponibles, aunque los arreglos en T-SQL poseen una visión más cercana al manejo de tablas que al manejo de arreglos de los lenguajes de programación a los que los desarrolladores estamos acostumbrados, esto no provoca ningún problema. La idea aplicada al caso anterior es la siguiente;

  suponiendo que seguimos teniendo las mismas tablas Cabecera y Detalle, lo primero que vamos a hacer es crear un tipo de datos de usuario, en SQL Server 2008 no hay que escribir código CLR, la forma de hacerlo es simplemente escribir un script, para nuestro caso el siguiente:

  create type TipoDetalle as table
  (
   Nombre        nvarchar(20),
   OtroDato1     int,
   OtroDato2     DateTime
  )

  Algo bastante similar a nuestra estructura en C#, una vez creado el tipo de datos ya podemos pasar a crear el procedimiento almacenado

  CREATE PROCEDURE Ins_CabDet
  (
   @Nombre_Cab nvarchar(20),
   @Detalles TipoDetalle  readonly
  )
  AS
  BEGIN
   BEGIN TRY
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRAN
    INSERT INTO Cabecera (Nombre) VALUES (@Nombre_Cab)
    DECLARE @IDCabecera int
    SET @IDCabecera = @@IDENTITY
    INSERT INTO
    Detalle (IDCabecera,Nombre, OtroDato1, OtroDato2)
    SELECT @IDCabecera ,Nombre, OtroDato1, OtroDato2
    FROM
     @Detalles;
    COMMIT
    RETURN 0
   END TRY
   BEGIN CATCH
    IF @@TRANCOUNT > 0
     ROLLBACK
    RETURN -1
   END CATCH
  END

  Claramente puede verse en T-SQL un arreglo es visto como una especie tabla, en cada fila de la tabla hay un elemento (del tipo Detalle en nuestro caso, para eso primero debimos crearlo) y
  cada columna representa una propiedad del elemento (Nombre, OtroDato1, etc.), como mencionamos previamente, es un poco diferente a lo usual, pero no puede negarse que es una visión bastante razonable para un motor de base de datos.

  Finalmente queda ver como efectuar la llamada desde .NET, en C# la llamada tomaría la siguiente forma:

  public void Ins_CabDet(string Cab_Nombre, List Detalles) {
   DataTable dt = new DataTable();
   dt.Columns.Add("Nombre", typeof(string));
   dt.Columns.Add("OtroDato1", typeof(int));
   dt.Columns.Add("OtroDato2", typeof(DateTime));
   foreach (Detalle objDet in Detalles) {
    DataRow dr = dt.NewRow();
    dr[0] = objDet.Nombre;
    dr[1] = objDet.OtroDato1;
    dr[2] = objDet.OtroDato2;
    dt.Rows.Add(dr);
   }
   using (SqlCommand objSCmd = new SqlCommand("Ins_CabDet", new SqlConnection("Data  Source=XXX...."))) {
    objSCmd.CommandType = CommandType.StoredProcedure;
    objSCmd.Connection.Open();
    objSCmd.Parameters.Add("@Nombre_Cab", SqlDbType.NVarChar, 20).Value = Cab_Nombre;
    objSCmd.Parameters.AddWithValue("@Detalles", dt);
    objSCmd.ExecuteNonQuery();
   }
  }

  Un último detalle es que desde en el código C# puede verse como el arreglo es preparado como una tabla, mapeando la estructura que el motor espera recibir.

  SQL Server 2008 trae muchas otras novedades como los campos Date (sin Time) y Time (sin Date), tipos de datos espaciales, la sentencia MERGE y otras cosas que seguramente veremos en algún otro artículo si es que las hemos utilizado para resolver algún problema.