El Bruno

Buenas,

mientras sigo con la duda sobre quien gana si Mickey Mouse se enfrenta al maestro Yoda en un combate de sables laser; también pienso en algunos escenarios donde aprovechar algunas características de Microsoft SQL Server 2005.

Por ejemplo, supongamos que tenemos 1 tabla con varios millones de registros con la siguiente estructura:

y necesitamos agregar 1 nueva columna basada en el cálculo de otras columnas de la tabla (por ejemplo; necesitamos una nueva columna que sume que sume las columnas colX + colY). Una opción simple para resolver este problema es crear una vista que procese el resultado que necesitamos; otra opción es crear una nueva columna en la tabla y luego un trigger que frente a las acciones INSERT o UPDATE sobre un registro actualize el valor de esta columna.

Seguramente hay más alternativas para tener en cuenta; pero una muy interesante es agregar una nueva columna que surja de una función de SQL y que persista los valores de la misma (Computed Column).

Lo primero que debemos hacer es crear la función en nuestra base de datos; este es el script de creación de la misma:

 1 -- =============================================
 2 -- Author:    Bruno Capuano
 3 -- Create date: 24-01-2007
 4 -- Description:  Return param x + param y
 5 -- =============================================
 6 ALTER function [dbo].[tsql_Add] (@x int, @y int)
 7 returns int
 8 with schemabinding
 9 as 
10 begin
11           return @x + @y
12 end

La función simplemente recibe 2 parametros y retorna la suma de los mismos, Luego aqui está la parte interesante podemos modificar la estructura de la tabla para que posea un nuevo campo que procese los datos de esta función. El script de creación de la tabla sería:

 1 USE [SampleLabs]
 2 GO
 3 /****** Object:  Table [dbo].[ComputedColumnTable]    Script Date: 01/24/2007 14:23:43 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 CREATE TABLE [dbo].[ComputedColumnTable](
 9   [colX] [int] NOT NULL,
10   [colY] [int] NOT NULL,
11   [comp_col]  AS ([dbo].[tsql_Add]([colX],[colY])),
12  CONSTRAINT [PK_ComputedColumnTable] PRIMARY KEY CLUSTERED 
13 (
14   [colX] ASC,
15   [colY] ASC
16 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
17 ) ON [PRIMARY]

Hemos creado una nueva columna comp_col y los valores que alimentarán a la misma se obtendrán de la ejecución de la funcion tsql_Add. Cuando agreguemos algunos valores de prueba podremos ver como esta columna se completa automaticamente tanto cuando agregamos nuevos registros o cuando modificamos los existentes.

Las columnas calculadas ademas pueden ser parte de los índices de una tabla; sin embargo para lograr que los mismos sean indexados hay que modificar un poco la estructura de la misma para que aproveche todas la capacidades de integracion CLR en Microsoft SQL Server 2005. Lo primero que debemos hacer es crear un assembly .Net que contenga la funcionalidad que deseamos; por ejemplo:

 1 using System;
 2 using Microsoft.SqlServer.Server;
 3 
 4 public class SampleComputedColumn
 5 {
 6      [SqlFunction(IsDeterministic=true, IsPrecise=true)]
 7      public static int tsql_Add(int x, int y)
 8      {
 9           return x + y;
10      }
11 }
12 

Aplicando los atributos IsDeterministics and IsPrecise ya podemos agregar la columnna a los índices de la tabla. Para mas referencia les recomiendo darse una vuelta por:

• Using CLR Integration in SQL Server 2005
  http://msdn2.microsoft.com/en-us/library/ms345136.aspx
• Create Function (T-SQL)
  http://msdn2.microsoft.com/en-us/library/ms186755.aspx
• Create Index (T-SQL)
  http://msdn2.microsoft.com/en-us/library/ms188783.aspx

Actualización 25-01-2007: El script de la DB con el ejemplo se puede descargar desde aqui. 

Saludos

El Bruno