Capitulo 7 - Lección 2: Extender la funcionalidad de Microsoft SQL Server con SQLCLR y Filestream

SQLCLR se refiere a la utilización de la ejecución de CLR en el motor de base de datos SQL Server. CLR también se refiere a menudo como el Framework. NET ,como . NET, o por el nombre del lenguaje de programación que podrían ser utilizados, tales como Visual Basic. NET o C #. El uso de CLR dentro de SQL Server es un aspecto importante de hacer más extensible de SQL Server, permitiendo a los desarrolladores a hacer más de lo que es posible sólo con el lenguaje T-SQL y las funciones del sistema. También vamos a ver cómo los procedimientos almacenados, triggers, funciones, tipos y agregados pueden ser creados usando CLR. Al final de esta lección, vamos a ver cómo se pueden utilizar objetos FileStream para almacenar objetos binarios grandes (BLOB) en SQL Server 2008.

Los aspectos básicos del uso de SQLCLR
Para utilizar el CLR en la base de datos, debe realizar los siguientes pasos:

1. Se debe establecer la instancia de SQL Server para permitir que el código CLR para ejecutar.
2. Usted debe escribir el código que utiliza el objeto con un lenguaje. NET (en este capítulo, se incluye C # y Visual Basic. NET ejemplos de código de referencia).
3. Debe compilar el código para un assembly (un archivo ejecutable de CLR).
4. Usted debe cargar el ensamblado en SQL Server.
5. Por último, debe crear el objeto base de datos y el punto a el assembly de usando Data Definition Language (DDL).

Ahora seguiremos cada uno de estos pasos para crear un procedimiento almacenado CLR muy simple que sólo debe ejecutar la instrucción SQL “SELECT * FROM Sales.Customer WHERE CustomerID = @CustomerID”. A pesar de que nunca usaría el CLR para este procedimiento en particular, es un buen ejemplo ya que le permite ver con claridad la diferencia entre crear un procedimiento almacenado mediante T-SQL para ejecutar una consulta con parámetros y el uso de SQLCLR para ejecutar una consulta parametrizada . Como referencia, la versión de T-SQL del procedimiento almacenado se muestra aquí:
CREATE PROCEDURE Sales.spCustomerGet

@CustomerID INT
    AS
BEGIN
    SELECT * FROM Sales.Customer
    WHERE CustomerID = @CustomerID;
END

Para ejecutar la misma consulta con SQLCLR, primero permitimos que el código SQLCLR que se ejecutará dentro de esta instancia de SQL Server ejecute el código T-SQL se muestra aquí. Tenga en cuenta que los tipos de sistemas y funciones que utilizan SQLCLR (tales como los tipos de datos geográficos y la geometría espacial) no requieren que el CLR este habilitado, sino que sólo es necesario para el código de usuario:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
y deberemos ver algo parecido a esto:



La primera línea de código cambia la configuración de 'clr enabled' a 1 (true) y la segunda línea de código indica a SQL Server para empezar a utilizar los ajustes de configuración ha cambiado.
Ahora es el momento de escribir el código. NET (C # o Visual Basic Net.) Para el procedimiento almacenado. En este ejemplo, podemos crear un solo archivo que contiene el código. NET y luego compilarlo con el símbolo del sistema.

TIPS: Si tiene Microsoft Visual Studio a mano, se puede utilizar para crear los archivos.Utilice la plantilla de proyecto de SQL Server, que puede encontrarse en el tipo de proyecto de base de datos, para crear objetos de base de datos de CLR. Visual Studio puede incluso desplegar objetos CLR base de datos directamente a SQL Server (lo que significa que Visual Studio se realiza para que el resto de los pasos que se describen aquí).

Creamos un archivo llamado CLRStoredProc.cs (si usted está usando C #) oCLRStoredProc.vb (si está utilizando Visual Basic NET.) Y agregue el siguiente código:

'VB:
Imports System
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Namespace TK433.Clr
Public Class Demo
Public Shared Sub CustomerGetProcedure(ByVal customerId As SqlInt32)
Using conn As SqlConnection = New SqlConnection("context connection=true")
Dim cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM Sales.Customer " & vbCrLf & _
"WHERE CustomerID = @CustomerID;"
cmd.Parameters.AddWithValue("@CustomerID", customerId)
conn.Open()
' Ejecutar el comando y enviar los resultados a el que llama.
SqlContext.Pipe.ExecuteAndSend(cmd)
End Using
End Sub
End Class
End Namespace
//C#:
using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
namespace TK433.Clr
{
  public class Demo
  {
   static public void CustomerGetProcedure(SqlInt32 customerId)
    {
     using (SqlConnection conn = new SqlConnection("context connection=true"))
     {
      SqlCommand cmd = conn.CreateCommand();
      cmd.CommandText = @"SELECT * FROM Sales.Customer
      WHERE CustomerID = @CustomerID";
      cmd.Parameters.AddWithValue("@CustomerID", customerId);
      conn.Open();
      // Execute the command and send the results to the caller.
      SqlContext.Pipe.ExecuteAndSend(cmd);
     }
   }
  }
}

Tenga en cuenta los siguientes puntos en este ejemplo:

• La cadena de conexión "context connection = true" le dice al objeto SqlConnection como conectarse a la instancia de SQL Server en el que ya está ejecutando. Tenga en cuenta que todavía están en la misma sesión que usted estaba en fuera del código de CLR. Esto significa que usted no está bloqueada por los bloqueos que se llevan a cabo en la reunión que se ejecuta este código CLR.
• Los espacios de nombres adicionales que se utilizan son los siguientes:
  • El espacio de nombres System.Data.SqlClient contiene las clases SqlConnection y SqlCommand utilizadas en el ejemplo. Estas clases se utilizan para conectarse a SQL Server y ejecutar la consulta. Tenga en cuenta que estas clases son las mismas que se utiliza cuando se conecta desde una aplicación.
  • El espacio de nombres System.Data.SqlTypes contiene el tipo SqlInt32 utilizado en el ejemplo.
    Este tipo imita el comportamiento de un entero en SQL Server. Usted puede usar la propiedad Value del tipo SqlInt32 para recuperar un número entero de CLR.
  • El espacio de nombres Microsoft.SqlServer.Server contiene las clases SqlContext y SqlPipe (necesario para la llamada SqlContext.Pipe) utilizado en el ejemplo. Estas clases son específicos a la aplicación SQLCLR y se utilizan para comunicar a la sesión de SQL Server en el que se está ejecutando.
• El código es muy similar a cualquier otro código que tiene acceso a SQL Server desde una aplicación.
• Esto parece bastante complicado de ejecutar sólo esta sentencia SELECT. Como se puede ver claramente, no se tendría que utilizar SQLCLR para simples procedimientos como los de este ejemplo.

El siguiente paso es compilar el código en un ensamblado CLR. Esto se logra mediante la ejecución del compilador de C # (csc.exe) o el Visual Basic. NET(vbc.exe). Ambos compiladores normalmente se pueden encontrar en el directorio C:\Windows\Microsoft.NET\Framework\v3.5. Para compilar el código desde la línea de comandos, ejecute el comando siguiente en el directorio donde se almacena el código fuente. (Tenga en cuenta que debe tener la ruta de modo que apunte al directorio que contiene el compilador.)
'Comando modelo para compilar VB:
vbc.exe /target:library /out:CLRStoredProc.dll CLRStoredProc.vb
//Comando modelo para compilar C#:
csc.exe /target:library /out:CLRStoredProc.dll CLRStoredProc.cs

Bueno, ahora vamos a hacer un paréntesis respecto del libro, porque probablemente no puedan ejecutar esto tal cual se explica, así que detallaremos un poco con imágenes:
Copiamos el codigo detallado mas arriba, y yo por ejemplo lo guarde en E:\70433\CLRStoredProc.cs para C#
 
Debemos asegurarnos que se guarda como archivo .cs y o .cs.txt, pueden hacerlo eligiendo en el desplegable Tipo que diga “todos los archivos” o escribiendo el nombre entre comillas dobles al guarda. EJ: “CLRStoredProc.cs”
Lo siguiente es ubicar C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe y poder ejecutar el compilador, lo cual debemos hacerlo desde una ventana de MSDOS.
Primero abrimos una ventana de MSDOS desde Windows+R y escribimos cmd
 
Después nos dirigimos a la carpeta donde guardamos el archivo anterior
 
yo para poder compilarlo use este comando porque si lo ponía sin la ruta completa me daba error
C:\Windows\Microsoft.NET\Framework\v3.5\csc.exe /target:library /out:CLRStoredProc.dll CLRStoredProc.cs
y en el directorio apareció la DLL que buscamos
 
bueno, ya podemos continuar normalmente!
Ahora expliquemos el porque de los comandos usados:
El /target:library le dice al compilador que está compilando un ensamblado sin un punto de partida, es decir, el assembly de no se puede ejecutar directamente. Se trata simplemente de una biblioteca de tipos, y por lo tanto, se debe usar la extensión. Dll. El modificador /out simplemente le dice al compilador lo que debe el nombre del ensamblado creado.
Ahora es el momento para cargar el ensamblado creado en la base de datos. Esto se logra mediante la instrucción CREATE ASSEMBLY. Cuando se ejecuta la instrucción, el ensamblado se copia físicamente en la base de datos al que se adjuntan. Por lo tanto, puede eliminar el archivo de ensamblado. Dll después de que se ha cargado porque SQL Server no lo necesita. Tener el assembly de almacenados en la base de datos es muy útil cuando se está en movimiento una base de datos a otra instancia, porque todas las assembly dentro de la base de datos se mueven con la base de datos. Para ver qué assembly existen dentro de una base de datos, puede consultar la vista de catálogo sys.assemblies.
El siguiente código T-SQL carga el ensamblado en la base de datos AdventureWorks y muestra sus propiedades mediante la consulta de la vista de catálogo sys.assemblies:

USE AdventureWorks;

CREATE ASSEMBLY TK433ClrDemo
FROM 'E:\70433\CLRStoredProc.dll';

SELECT assembly_id, name
FROM sys.assemblies
WHERE name = 'TK433ClrDemo';

  

Finalmente creamos el procedimiento almacenado de manera que pueda ser ejecutado por los usuarios. El siguiente código T-SQL crea un procedimiento almacenado llamado Sales.spCustomerGetClr que apunta al método CLR llamado CustomerGetProcedure en la clase TK433. Después de crear el procedimiento almacenado en el siguiente ejemplo, también se consulta la vista de catálogo sys.assembly_modules para obtener información respecto a este procedimiento almacenado CLR.
T-SQL módulos se pueden encontrar en la vista de catálogo sys.sql_modules:

CREATE PROCEDURE Sales.spCustomerGetClr
@CustomerID INT
AS
EXTERNAL NAME TK433ClrDemo."TK433.Clr.Demo".CustomerGetProcedure;
GO
SELECT assembly_id, assembly_class, assembly_method
  FROM sys.assembly_modules
  WHERE object_id = OBJECT_ID('Sales.spCustomerGetClr');

y Obtenemos este resultado de la ultima sentencia:
 
Después de que el procedimiento almacenado se ha creado, lo podemos ejecutar.Tenga en cuenta que la ejecución del procedimiento almacenado se ve exactamente como la ejecución de un procedimiento almacenado de T-SQL:
EXEC Sales.spCustomerGetClr @CustomerID = 1;
 
Antes de continuar mirando más detalles de la creación de los diferentes tipos de objetos SQLCLR, vale la pena señalar que también se puede almacenar el código fuente de CLR en la base de datos.
SQL Server no utiliza el código fuente, pero es muy útil para almacenar el código en la base de datos, ya que es entonces posible para recuperarlo a partir de la base de datos más adelante (que puede haber perdido el código fuente real para ese entonces...). Para agregar un archivo de código fuente a la base de datos, ejecute la siguiente instrucción:

 

Para ver qué archivos se han añadido a un ensamblado, se puede consultar la vista de catálogo sys.assembly_files. Tenga en cuenta que el montaje real (. Dll), también se pueden encontrar a través de esta  vista.

Objetos que pueden ser creados con SQLCLR
Los siguientes tipos de objetos pueden ser creados usando SQLCLR:

• Procedimientos almacenados
• Funciones escalares UDFs que devuelvan un solo valor
• Funciones escalares UDFs que devuelvan una tabla y pueden ser llamadas desde un FROM, JOIN, o clausula APPLY.
• Triggers (DML, DDL, y logon triggers)
• Funciones de agregado o agrupación definidas por el usuario.
• Tipos definidos por el usuario (UDTs).

Vale la pena señalar que los dos últimos objetos sólo se pueden crear utilizando SQLCLR, no pueden ser creados usando T-SQL.
Porque ya hemos cubierto cómo crear un procedimiento almacenado CLR, empezamos por buscar la manera de crear una función UDF escalar.

Creación de una UDF escalar

Al igual que el procedimiento almacenado CLR, una UDF escalar se compone de un único método. La diferencia es que para la UDF, el método debe devolver un valor, mientras que para el procedimiento almacenado, el método debe devolver void (para C #) o un Sub (para Visual Basic. NET). UDF es donde realmente sobresale SQLCLR porque usted puede crear fácilmente archivos UDF que le permiten utilizar básicamente cualquier cosa dentro del. NET Framework. Por lo general, debe usar una UDF CLR sólo para realizar los cálculos, es decir, no acceder a ninguna tabla.Sus funciones son entonces normalmente se utiliza en una sentencia SQL DML. En este ejemplo, creamos una UDF que le permite utilizar expresiones regulares en SQL Server. Uso de expresiones regulares es una característica que frecuentemente es necesario que no existe de forma nativa en SQL Server, pero que fácilmente se puede "robar" a partir de. NET Framework con SQLCLR. En los siguientes ejemplos, que no cubren la compilación o la carga de la assembly porque los pasos fueron cubiertos anteriormente.
Para crear la nueva UDF, se usa el siguiente código de CLR. Por abreviación,sólo el propio método y los espacios de nombres importados se incluyen:

'VB:
Imports System
Imports System.Data.SqlTypes
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Server
. . .
<SqlFunction(IsPrecise:=True, Isdeterministic:=True)> _
Public Shared Function IsRegExMatch(ByVal input As SqlString, ByVal pattern As
SqlString) _
As SqlBoolean
If input.IsNull Or pattern.IsNull Then
Return SqlBoolean.Null ' Return NULL if either parameter is NULL.
End If
Return CType(Regex.IsMatch(input.Value, pattern.Value), SqlBoolean)
End Function
. . .
//C#:
using System;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
. . .
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
static public SqlBoolean IsRegExMatch(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull)
return SqlBoolean.Null; // Return NULL if either parameter is NULL.
return (SqlBoolean)Regex.IsMatch(input.Value, pattern.Value);
}
. . .
Observe el atributo SqlFunction que se utiliza para definir la función. Este atributo indica a SQL Server que la función es a la vez preciso (es decir, los cálculos de coma flotante no afectan a su valor de retorno) y determinista (es decir,no devuelve valores diferentes cuando se llama varias veces con los mismos valores de entrada) . Debido a esto, el resultado de la función puede ser persistido en una columna calculada e indexado.
Después de compilar y cargar el ensamblado en SQL Server, el siguiente código T-SQL se ejecuta para crear la función:
CREATE FUNCTION dbo.fnIsRegExMatch
(
@Input NVARCHAR(MAX)
,@Pattern NVARCHAR(100)
)
RETURNS BIT
AS
EXTERNAL NAME TK433ClrDemo."TK433.Clr.Demo".IsRegExMatch;

Ahora bien, esta función se puede utilizar cuando se quiere aplicar una expresión regular en una consulta. En el siguiente ejemplo, T-SQL, la consulta devuelve el número de filas en la tabla Sales.CreditCard que no tienen un número de tarjeta de crédito con exactamente 14 números. Esto se hace usando la expresión regular ^ [0-9] {14} $:

SELECT
COUNT(*) AS InvalidCreditCardNumbers
FROM Sales.CreditCard
WHERE dbo.fnIsRegExMatch(CardNumber, N'^[0-9]{14}$') = 0;
y el resultado seria:

Otro aspecto interesante de CLR (y T-SQL) UDF es que pueden ser utilizados en las restricciones.
El siguiente ejemplo muestra cómo podemos añadir una restricción de comprobación a la tabla de Sales.CreditCards de modo que sólo permite un número de tarjeta de crédito que sea nulos o que coincidan con la expresión regular a partir del ejemplo anterior:

ALTER TABLE Sales.CreditCard
ADD CONSTRAINT CKCreditCardNumber
CHECK(dbo.fnIsRegExMatch(CardNumber, N'^[0-9]{14}$') = 1 OR CardNumber IS NULL);

-- Al intentar actualizar un numero invalido de tarjeta de credito.
UPDATE Sales.CreditCard SET CardNumber = '1234' WHERE CreditCardID = 1;

Obtenemos este resultado:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CKCreditCardNumber". The conflict occurred in database "AdventureWorks", table "Sales.CreditCard", column 'CardNumber'.

Debido a que se marcó la función como precisa (IsPrecise) y determinista (IsDeterministic) en el código CLR, ambos persisten en el índice de una columna calculada que utiliza la función. Para indexar el resultado de la función, primero se debe colocar en la expresión de una columna calculada. En el siguiente ejemplo T-SQL, una columna calculada utilizando la función en la tabla y se marca con el atributo persistente. El atributo persistente indica a SQL Server que debería calcular el resultado de la función sólo cuando se escribe en la columna o columnas subyacentes y almacenar el resultado de la función física en la fila de la tabla. Después de agregar la columna, también creamos un índice sobre la misma. Tenga en cuenta que la columna calculada no tiene que ser marcada como persistente para que usted sea capaz hacer el índice. La consulta que se ejecuta en contra de la nueva columna al final de este ejemplo utiliza el índice:

ALTER TABLE Sales.CreditCard
ADD IsValidCardNumber AS dbo.fnIsRegExMatch(CardNumber, N'^[0-9]{14}$')
PERSISTED;
GO

CREATE NONCLUSTERED INDEX IsValidCardNumberIdx
ON Sales.CreditCard (IsValidCardNumber);
GO

-- Esta consulta utiliza el índice de IsValidCardNumberIdx y realiza
-- Un índice de operación de búsqueda contra él.
SELECT COUNT(*)
FROM Sales.CreditCard
WHERE IsValidCardNumber = 1;

Crear una tabla con valores de UDF CLR
Debido a que un valor de tabla UDF devuelve varios valores (o mejor dicho, varias filas), que es un poco más complejo que una UDF escalar. Mientras que una UDF escalar consiste en un método único, con valores de tabla UDF consta de dos métodos:

• Un método que actúa como repetidor o máquina de estados, recorrer los valores que deben ser devueltos en las filas.
• Un método que se ejecuta para cada fila y rellena la fila actual que se devuelven al motor de ejecución de SQL Server.

Considere el siguiente código de CLR, que define estos dos métodos para una tabla de valores de UDF.
Esta función especial se puede utilizar para dividir cadenas delimitadas por comas.

¿Por qué no hay ejemplos en Visual Basic. NET ?
En este ejemplo se utiliza la palabra clave yield en C # para crear un iterador. La palabra clave yield no existe en Visual Basic. NET, por lo que los ejemplos en Visual Basic. NET no estáran incluidos. Usted puede crear valores de tabla de funciones utilizando Visual Basic. NET, pero esto requiere que usted cree una clase que actue como repetidor y se ocupe de recorrer los valores.

//C#:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
. . .
 [SqlFunction(FillRowMethodName = "SplitStringFillRow",
 TableDefinition = "ValueIndex INT, Value NVARCHAR(100)")]
 static public IEnumerator SplitString(SqlString stringToSplit)
{
// Salir si la cadena a dividir es NULL.
 if (stringToSplit.IsNull)
  yield break;
  int valueIndex = 0;
  foreach (string s in stringToSplit.Value.Split(new char[] { ',' },
  StringSplitOptions.RemoveEmptyEntries))
 {
  yield return new KeyValuePair<int, string>(valueIndex++, s.Trim());
 }
}



static public void SplitStringFillRow(object oKeyValuePair,
out SqlInt32 valueIndex, out SqlString value)
{
// Buscar el par de valores clave del primer parámetro.
KeyValuePair<int, string> keyValuePair = (KeyValuePair<int, string>)oKeyValuePair;
 // Set each output parameter's value.
 valueIndex = keyValuePair.Key;
 value = keyValuePair.Value;
 }
. . .

En el ejemplo anterior, se puede ver que hemos creado los dos métodos SplitStringy SplitStringFillRow. Cuando se ejecuta la UDF de SQL Server, el método SplitString se ejecuta primero. Cada instrucción yield return que se ejecuta dentro de este método llama al método SplitStringFillRow (es decir, el método de línea de relleno especificado en el atributo SqlFunction).
Como puede ver, el conjunto de resultados que devuelve la función SplitString se define mediante el parámetro TableDefinition del atributo SqlFunction.
El método de relleno de las filas siempre toma un parámetro de tipo System.Object que contiene una referencia a yield return, así como uno de parámetros para cada columna que debe ser devuelto por la función de acuerdo con el parámetro TableDefinition. En el siguiente ejemplo, se puede ver el código T-SQL para crear y consultar la tabla de valores de UDF:

CREATE FUNCTION dbo.fnSplitString
(
  @StringToSplit NVARCHAR(max)
)
RETURNS TABLE (ValueIndex INT, Value NVARCHAR(100))
AS
  EXTERNAL NAME TK433ClrDemo."TK433.Clr.Demo".SplitString;
GO
SELECT ValueIndex ,Value
FROM dbo.fnSplitString('Hi,how,are,you?') AS a;

lo que nos dará como resultado:


Crear un desencadenador CLR


Crear un desencadenador CLR es muy similar a la creación de un procedimiento almacenado CLR. El código CLR está formado por un solo método o Sub que realiza las acciones que desea que el disparador para llevar a cabo. Al igual que un trigger de T-SQL, un desencadenador CLR tiene acceso a las tablas triggers específicas inserted y deleted. En el siguiente ejemplo de un desencadenador CLR, la transacción se cancela si la declaración que provocó la activación elimina más de una fila:

'Veamos el codigo en VB:
Imports System.Data.SqlClient
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Server
. . .
Public Shared Sub ClrTrigger()
' Si esto no era una instrucción de eliminación, hay que salir.
If SqlContext.TriggerContext.TriggerAction <> TriggerAction.Delete Then
Return
End If
Using conn As SqlConnection = New SqlConnection("context connection=true")
Dim cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = "SELECT COUNT(*) FROM deleted"
conn.Open()
' Comprobamos el número de filas que se encuentran en la tabla deleted.
If (CType(cmd.ExecuteScalar(), Integer)) > 1 Then
cmd.CommandText = _
"RAISERROR('Too many rows deleted, rolling back " & _
"transaction.', 16, 1);" & vbCrLf & _
"ROLLBACK TRAN;"
' Este try / catch se necesita con el fin de omitir el error que se
' causa si RAISERROR es ejecutado
Try
SqlContext.Pipe.ExecuteAndSend(cmd)
Catch
End Try
End If
End Using
End Sub
. . .

//Ahora en C#:
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
. . .
static public void ClrTrigger()
{
// Si esto no era una instrucción de eliminación, hay que salir.
 if (SqlContext.TriggerContext.TriggerAction != TriggerAction.Delete)
 return;
 using (SqlConnection conn = new SqlConnection("context connection=true"))
{
 SqlCommand cmd = conn.CreateCommand();
 cmd.CommandText = "SELECT COUNT(*) FROM deleted;";
 conn.Open();
// Comprobamos el número de filas que se encuentran en la tabla deleted.
 if (((int)cmd.ExecuteScalar()) > 1)
{
 cmd.CommandText = @"RAISERROR('Too many rows deleted, " +
 "rolling back transaction.', 16, 1);" +
 "ROLLBACK TRAN;";
// Este try / catch se necesita con el fin de omitir el error que se
// causa si RAISERROR es ejecutado.
 try
  {
   SqlContext.Pipe.ExecuteAndSend(cmd);
  }
  catch { }
  }
 }
}

El siguiente código se utiliza para crear el desencadenador

CREATE TRIGGER dbo.ClrTrigger
ON dbo.MyTable
AFTER DELETE
AS
EXTERNAL NAME TK433ClrDemo."TK433.Clr.Demo".ClrTrigger;
GO

Al igual que con los procedimientos almacenados CLR, la creación de este desencadenador de un modo simple se convierte en muy complicado en comparación con la creación de un desencadenador de T-SQL, ya que es necesario utilizar un objeto SqlCommand y SqlConnection cada vez que desee ejecutar una sentencia SQL.
Como puede ver, hay una propiedad de la clase SqlContext llamado TriggerContext(de tipo SqlTriggerContext), que puede ser utilizado para llevar a cabo controles específicos de desencadenador del código CLR.
Los desencadenadores CLR debe utilizarse sólo cuando sea necesario para realizar tareas que no se puede realizar utilizando disparadores regulares T-SQL, o cuando las tareas son en gran medida de cálculo intensivo y el desencadenador CLR supera el desencadenador T-SQL.

Creación de funciones CLR de agregado o agrupación definidas por el usuario.

La posibilidad de crear funciones personalizadas de agregación puede ser muy útil cuando se quiere crear un agregado que no se incluye con SQL Server (por ejemplo,un agregado de productos) o para crear una función de agregado que puede manejar un tipo personalizado de UDT CLR.
Al crear un CLR de agregado definido por el usuario , es necesario crear todo un tipo CLR (ya sea de clase o estructura) en lugar de sólo uno o dos métodos, como con los anteriores objetos CLR. Esto se debe a el conjunto tiene que ser capaz de hacer algunas cosas, entre ellas las siguientes:

• Inicializarse: Esto se realiza mediante el método Init del conjunto del tipo de CLR.
• Añadir otro valor para el cálculo: Para cada valor que debe ser añadido al cálculo, el método Accumulate es llamado.
• Combinarse a si mismo con otra instancia de la función de agregado: esto se realiza mediante una llamada al método Merge.
• Devolver su resultado: Esto se realiza mediante una llamada al método Terminate.

Antes de explorar más detalles de los agregados definido por el usuario, tome unos momentos para leer el siguiente ejemplo de código CLR, que define un conjunto de productos matemáticos.
El objetivo de este agregado es que todas las entradas se multipliquen entre sí (por ejemplo, 5 * 10 * 15 = 750). Para añadir alguna funcionalidad extra para este agregado, se ha incluido un segundo parámetro que dice a el agregado o no de incluir ceros en el cálculo (por la inclusión de un cero siempre se traduciría en un total de cero). La posibilidad de tener múltiples parámetros definido por el usuario en los agregados se agregó en SQL Server 2008. Hay muy pocas observaciones que hacer sobre este código:

• En este ejemplo, el atributo SqlUserDefinedAggregate indica a SQL Server de forma manual que se encargará de la serialización de los agregados (si es necesario) mediante la especificación del formato que es definido por el usuario(Format.UserDefined). Los atributos también indican a SQL Server que debe devolver NULL si no hay valores en la agregación (IsNullIfEmpty = True), asi como el agregado no se preocupa por duplicados (IsInvariantToDuplicates = False), no se preocupa por los valores NULL (IsInvariantToNulls = True), y no importa el orden de la entrada (IsInvariantToOrder = True). Por último, en los atributos también se indica a SQL Server que el espacio de almacenamiento máximo utilizado cuando se serializa este agregado es de 19 bytes (16 bytes para el valor decimal de 1 byte y cada uno de los tres valores Boolean).
• El atributo SqlFacet se utiliza un par de veces en todo el código para especificar la precisión y la escala del tipo de SqlDecimal.
• La interfaz IBinarySerialize debe implementarse ya que se utilizó el formato definido por el usuario en la definición de los agregados. También puede optar por utilizar el formato nativo, sin embargo, no funciona en este ejemplo porque el tipo de datos decimal aquí no es compatible con la serialización nativa.
• El método IBinarySerialize.Write se llama por SQL Server para serializar la instancia durante la ejecución, si es necesario.
• El método IBinarySerialize.Read se llama por SQL Server para deserializar la instancia durante la ejecución, si es necesario.

'Primero el codigo en VB:Imports System
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports Microsoft.SqlServer.Server
. . .
<SqlUserDefinedAggregate(Format.UserDefined, IsNullIfEmpty:=True, _
IsInvariantToDuplicates:=False, IsInvariantToNulls:=True, _
IsInvariantToOrder:=True, MaxByteSize:=19)> _
Public Structure DecimalProductAggregate
Implements IBinarySerialize
Private m_Value As Decimal '16 bytes storage. 

Private m_ValueIsNull As Boolean '1 byte storage. 

Private m_SkipZeros As Boolean '1 byte storage. 

Private m_SkipZerosIsNull As Boolean '1 byte storage. 

Public Sub Init()
Me.m_ValueIsNull = True
Me.m_SkipZerosIsNull = True
End Sub
Public Sub Accumulate(<SqlFacet(Precision:=38, Scale:=5)> ByVal value _
As SqlDecimal, ByVal skipZeros As SqlBoolean)
If skipZeros.IsNull Then
Throw New InvalidOperationException( _
"The @SkipZeros parameter cannot be null.")
End If
If value.IsNull Then
Return
End If
' Init omite los ceros si la bandera no se ha establecido.
If (Me.m_SkipZerosIsNull) Then
Me.m_SkipZeros = skipZeros.Value
Me.m_SkipZerosIsNull = False
ElseIf Me.m_SkipZeros <> skipZeros.Value Then
' No permitimos cambiar la configuración acerca de los ceros durante la ejecución.
Throw New InvalidOperationException( _
"The @SkipZeros parameter cannot be changed.")
End If
' Saltarse valores cero si la configuración nos dice que debemos hacerlo
' y el valor actual es cero.
If Me.m_SkipZeros And value.Value = 0 Then
Return
End If
' Si este es el primer valor, nos limitamos a setearlo.
If Me.m_ValueIsNull Then
Me.m_Value = value.Value
Me.m_ValueIsNull = False
Return
End If
Me.m_Value *= value.Value
End Sub
Public Sub Merge(ByVal other As DecimalProductAggregate)
Dim skipZeros As SqlBoolean
If other.m_SkipZerosIsNull Then
skipZeros = SqlBoolean.Null
Else
skipZeros = other.m_SkipZeros
End If
Dim otherValue As SqlDecimal = other.Terminate()
Me.Accumulate(otherValue, skipZeros)
End Sub
Public Function Terminate() As <SqlFacet(Precision:=38, Scale:=5)> SqlDecimal
If Me.m_ValueIsNull Then
Return SqlDecimal.Null
End If
Return Me.m_Value
End Function
' Leemos el agregado de SQL Server.
Sub Read(ByVal r As System.IO.BinaryReader) Implements IBinarySerialize.Read
Me.m_ValueIsNull = r.ReadBoolean()
Me.m_Value = r.ReadDecimal()
Me.m_SkipZerosIsNull = r.ReadBoolean()
Me.m_SkipZeros = r.ReadBoolean()
End Sub
' Escribimos el agregado a SQL Server.
Sub Write(ByVal w As System.IO.BinaryWriter) Implements IBinarySerialize.Write
w.Write(Me.m_ValueIsNull)
w.Write(Me.m_Value)
w.Write(Me.m_SkipZerosIsNull)
w.Write(Me.m_SkipZeros)
End Sub
End Structure
. . .

//Y Ahora el equivalente en C#:
using System;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;
. . .
[SqlUserDefinedAggregate(Format.UserDefined, IsNullIfEmpty=true,
IsInvariantToDuplicates=false, IsInvariantToNulls=true,
IsInvariantToOrder=true, MaxByteSize=19)]
public struct DecimalProductAggregate : IBinarySerialize
{
 private decimal m_Value; // 16 bytes storage. private bool m_ValueIsNull; // 1 byte storage. private bool m_SkipZeros; // 1 byte storage. private bool m_SkipZerosIsNull; // 1 byte storage.public void Init()
{
  this.m_ValueIsNull = true;
  this.m_SkipZerosIsNull = true;
}
 public void Accumulate([SqlFacet(Precision = 38, Scale = 5)]SqlDecimal value,
 SqlBoolean skipZeros)
{
  if (skipZeros.IsNull)
{
  throw new InvalidOperationException(
  "The @SkipZeros parameter cannot be null.");
}
  if (value.IsNull)
  return;
// Init omite los ceros si la bandera no se ha establecido.
  if (this.m_SkipZerosIsNull)
{
  this.m_SkipZeros = skipZeros.Value;
  this.m_SkipZerosIsNull = false;
}
// No permitimos cambiar la configuración acerca de los ceros durante la ejecución.
  else if (this.m_SkipZeros != skipZeros.Value)
{
  throw new InvalidOperationException(
  "The @SkipZeros parameter cannot be changed.");
}
// Saltarse valores cero si la configuración nos dice que debemos hacerlo
// y el valor actual es cero.
  if (this.m_SkipZeros && value.Value == 0M)
 {
  return;
 }
// Si este es el primer valor, nos limitamos a setearlo.
  if (this.m_ValueIsNull)
{
  this.m_Value = value.Value;
  this.m_ValueIsNull = false;
  return;
}
  this.m_Value *= value.Value;
}
  public void Merge(DecimalProductAggregate other)
{
  SqlBoolean skipZeros = other.m_SkipZerosIsNull ? SqlBoolean.Null
  : other.m_SkipZeros;
  SqlDecimal otherValue = other.Terminate();
  this.Accumulate(otherValue, skipZeros);
}
  [return: SqlFacet(Precision = 38, Scale = 5)]
  public SqlDecimal Terminate()
{
  if (this.m_ValueIsNull)
 {
  return SqlDecimal.Null;
 }
  return this.m_Value;
}
// Leemos el agregado de SQL Server.
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
  this.m_ValueIsNull = r.ReadBoolean();
  this.m_Value = r.ReadDecimal();
  this.m_SkipZerosIsNull = r.ReadBoolean();
  this.m_SkipZeros = r.ReadBoolean();
}
// Escribimos el agregado a SQL Server.
void IBinarySerialize.Write(System.IO.BinaryWriter w)
 {
   w.Write(this.m_ValueIsNull);
   w.Write(this.m_Value);
   w.Write(this.m_SkipZerosIsNull);
   w.Write(this.m_SkipZeros);
 }
}
. . .

Después de cargar el ensamblado en SQL Server, puede ejecutar el siguiente código T-SQL para crear la función de agregado definida por el usuario:

CREATE AGGREGATE dbo.PRODUCT_DECIMAL_38_5
(
@Input DECIMAL(38,5)
,@SkipZeros BIT
)
RETURNS DECIMAL(38,5)
EXTERNAL NAME TK433ClrDemo."TK433.Clr.DecimalProductAggregate";

En este caso, hemos llamado a el agregado PRODUCT_DECIMAL_38_5 para que refleje el tipo real al que se puede calcular el producto. Usted puede ver cómo podemos usar funcion de agregado en una consulta SQL en el siguiente código T-SQL:
WITH Numbers AS (
SELECT * FROM (VALUES
(0)
,(5.5)
,(10.5)
,(15.5)
) AS a(Number)
)
SELECT
dbo.PRODUCT_DECIMAL_38_5(Number, 1) AS ProductExcludingZeros
,dbo.PRODUCT_DECIMAL_38_5(Number, 0) AS ProductIncludingZeros
FROM Numbers;

y Obtendremos esto como resultado

En este ejemplo, la consulta se ejecuta en una CTE, pero se puede utilizar contra tablas también.

La creación de un UDT CLR

El último objeto de CLR que es compatible con SQL Server es un UDT. La creación de UDT personalizado es similar a la creación de agregados definidos por el usuario. El tipo se compone de un tipo CLR (clase o estructura) que utiliza el atributoSqlUserDefinedType para informar a SQL Server de sus diferentes comportamientos. En el siguiente ejemplo, se puede ver el código de CLR que se utiliza para definir un UDT CLR. Este tipo, llamado CURRENCY_VALUE, se puede utilizar para almacenar un valor monetario (un decimal) y su código de moneda (una cadena) 

Al igual que con los agregados definidos por el usuario, hay algunas observaciones que hacer sobre este código:

• En este ejemplo, el atributo SqlUserDefinedType indica a SQL Server manualmenteque se encargará de la serialización del tipo mediante la especificación del formato que se definidas por el usuario (Format.UserDefined). El atributo también indica a SQL Server que un método llamado Validate se debe ejecutar para comprobar la integridad del tipo cada vez que un valor binario se convierte a este tipo(ValidationMethodName) y que el tipo no está ordenada por bytes (IsByteOrdered =False). Debido a que la clasificación de los UDT sólo compatibles con SQL Server es la ordenación de bytes, que debe ser muy cuidadoso en cómo ponerlo en práctica.Recuerde que un valor entero de -1 es mayor que 1 cuando se comparan las estructuras de byte por el bit más significativo en el número entero se pone a 1 si es negativo y cero si es positivo. Las otras dos opciones que se establecen son IsFixedLength, que indica a SQL Server si este tipo siempre utiliza el mismo número de bytes de almacenamiento, y MaxByteSize, que, como un agregado definido por el usuario, le dice a SQL Server el número máximo de bytes necesarios para almacenar una instancia serializada de este tipo.
• El atributo SqlFacet se utiliza un par de veces en todo el código para especificar la precisión y la escala del tipo de SqlDecimal, así como la longitud máxima del tipoSqlString.
• El atributo SqlMethod se utiliza en dos lugares para especificar que tanto la currencyCode y el valor de las propiedades deterministas y precisas, por lo que puede ser indexado y persistente.
• El método Parse se utiliza para convertir una cadena a la UDT. Se llama automáticamente por SQL Server, tanto implícita como explícitamente, cuando sucede la conversión de una cadena a este tipo.
• El método ToString es utilizada por SQL Server cuando se convierte de la UDT en una cadena. Tenga en cuenta que normalmente es una muy buena idea que los métodos Parse y ToString utilicen la misma representación de cadena de este tipo.
• La interfaz IBinarySerialize debe implementarse ya que se utilizó el formato definido por el usuario en la definición del tipo. También puede optar por utilizar el formato nativo, sin embargo, no funcionará en este ejemplo porque ni los decimales ni los tipos de datos de cadena que se utilizan en este ejemplo son compatibles con la serialización nativa.
• El método IBinarySerialize.Write se llama por SQL Server para serializar el tipo.
• El método IBinarySerialize.Read se llama por SQL Server para deserializar el tipo.

'VB:
Imports System
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports Microsoft.SqlServer.Server
. . .
<SqlUserDefinedType(Format.UserDefined, ValidationMethodName:="Validate", _
IsByteOrdered:=False, IsFixedLength:=True, MaxByteSize:=20)> _
Public Structure CurrencyValueType
Implements IBinarySerialize, INullable
Private m_Value As Decimal ' 16 bytes storage.
Private m_CurrencyCode As String ' 4 bytes storage.
Private m_IsNull As Boolean
Public Sub New(ByVal value As Decimal, ByVal currencyCode As String)
Me.m_Value = value
Me.m_CurrencyCode = currencyCode.ToUpper()
Me.m_IsNull = False
End Sub
' Obtener una instancia nula del tipo CurrencyValueType.
Public Shared ReadOnly Property Null() As CurrencyValueType
Get
Dim currValue As CurrencyValueType = New CurrencyValueType()
currValue.m_IsNull = True
Return currValue
End Get
End Property
<SqlFacet(MaxSize:=3)> _
Public Property CurrencyCode() As SqlString
<SqlMethod(IsPrecise:=True, IsDeterministic:=True)> _
Get
If Me.m_IsNull Then
Return SqlString.Null
End If
Return Me.m_CurrencyCode
End Get
Set(ByVal value As SqlString)
Me.m_CurrencyCode = value.Value.ToUpper()
If Me.Validate() = False Then
Throw New InvalidOperationException( _
"The currency code is invalid.")
End If
End Set
End Property
<SqlFacet(Precision:=38, Scale:=5)> _
Public Property Value() As SqlDecimal
<SqlMethod(IsPrecise:=True, IsDeterministic:=True)> _
Get
If Me.m_IsNull Then
Return SqlDecimal.Null
End If
Return Me.m_Value
End Get
Set(ByVal value As SqlDecimal)
Me.m_Value = value.Value
End Set
End Property
' Llamado de SQL Server para validar el valor de la moneda.
Public Function Validate() As Boolean
Return System.Text.RegularExpressions.Regex.IsMatch( _
Me.m_CurrencyCode, "^[A-Z]{3}$")
End Function
' Convertir una cadena en un valor de la moneda.
Public Shared Function Parse(ByVal input As SqlString) As CurrencyValueType
If input.IsNull Then
Return CurrencyValueType.Null
End If
Dim space As Integer = input.Value.IndexOf(" ")
If space <> 3 Then
Throw New InvalidOperationException( _
"The input string cannot be converted to a currency value.")
End If
Dim currencyCode As String = input.Value.Substring(0, 3)
Dim value As Decimal = SqlDecimal.Parse(input.Value.Substring( _
4, input.Value.Length - 4)).Value
Return New CurrencyValueType(value, currencyCode)
End Function
' Convertir un valor de la moneda a una cadena.
Public Overrides Function ToString() As String
If Me.m_IsNull Then
Return Nothing
End If
Return String.Format("{0} {1}", Me.CurrencyCode.Value, _
Me.Value.ToString())
End Function
' Leer el tipo desde SQL Server.
Public Sub Read(ByVal r As System.IO.BinaryReader) _
Implements IBinarySerialize.Read
Me.m_Value = r.ReadDecimal()
Me.m_CurrencyCode = r.ReadString()
Me.m_IsNull = False
End Sub
' Escribir el tipo a SQL Server.
Public Sub Write(ByVal w As System.IO.BinaryWriter) _
Implements IBinarySerialize.Write
w.Write(Me.m_Value)
w.Write(Me.m_CurrencyCode)
End Sub
Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
Get
Return Me.m_IsNull
End Get
End Property
End Structure
. . .

//Ahora en C#:
using System;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;
. . .
[SqlUserDefinedType(Format.UserDefined, ValidationMethodName="Validate",
IsByteOrdered=false, IsFixedLength=true, MaxByteSize=20)]
public struct CurrencyValueType : IBinarySerialize, INullable
{
private decimal m_Value; // 16 bytes storage.
private string m_CurrencyCode; // 4 bytes storage.
private bool m_IsNull; // Not stored. . .
public CurrencyValueType(decimal value, string currencyCode)
{
this.m_Value = value;
this.m_CurrencyCode = currencyCode.ToUpper();
this.m_IsNull = false;
}
// Obtener una instancia nula del tipo CurrencyValueType.
static public CurrencyValueType Null
{
  get
  {
    return new CurrencyValueType() { m_IsNull = true };
  }
}
[SqlFacet(MaxSize = 3)]
public SqlString CurrencyCode
{
 [SqlMethod(IsPrecise = true, IsDeterministic = true)]
 get
{
  if (this.m_IsNull)
  return SqlString.Null;
  return this.m_CurrencyCode;
}
set
  {
   this.m_CurrencyCode = value.Value.ToUpper();
   if (!this.Validate())
  {
   throw new InvalidOperationException("The currency code is invalid.");
 }
}
}
  [SqlFacet(Precision = 38, Scale = 5)]
  public SqlDecimal Value
{
  [SqlMethod(IsPrecise=true, IsDeterministic=true)]
  get
{
  if (this.m_IsNull)
  return SqlDecimal.Null;
  return this.m_Value;
}
  set
 {
  this.m_Value = value.Value;
 }
}
// Llamado de SQL Server para validar el valor de la moneda.
private bool Validate()
{
return System.Text.RegularExpressions.Regex.IsMatch(
this.m_CurrencyCode, "^[A-Z]{3}$");
}
// Convertir una cadena en un valor de la moneda.
static public CurrencyValueType Parse(SqlString input)
{
 if (input.IsNull)
 return CurrencyValueType.Null;
 int space = input.Value.IndexOf(' ');
 if (space != 3)
 throw new InvalidOperationException(
 "The input string cannot be converted to a currency value.");
 string currencyCode = input.Value.Substring(0, 3);
 decimal value = SqlDecimal.Parse(input.Value.Substring(
 4, input.Value.Length - 4)).Value;
 return new CurrencyValueType(value, currencyCode);
}
// Convertir un valor de la moneda a una cadena.
 override public string ToString()
{
  if (this.m_IsNull)
  return null;
  return string.Format("{0} {1}", this.CurrencyCode.Value,
  this.Value.ToString());
}
// Leer el tipo desde SQL Server.
 void IBinarySerialize.Read(System.IO.BinaryReader r)
{
  this.m_Value = r.ReadDecimal();
  this.m_CurrencyCode = r.ReadString();
  this.m_IsNull = false;
}
// Escribir el tipo a SQL Server.
  void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
  w.Write(this.m_Value);
  w.Write(this.m_CurrencyCode);
}
  bool INullable.IsNull
{
  get
{
  return this.m_IsNull;
}
}
}
. . .

Después de cargar el ensamblado en SQL Server, puede ejecutar el siguiente código T-SQL para crear el UDT:

CREATE TYPE dbo.CURRENCY_VALUE
EXTERNAL NAME TK433ClrDemo."TK433.Clr.CurrencyValueType";

En el ejemplo T-SQL se muestra aquí, una nueva tabla es creada y se rellena con filas de la tabla Production.Product. Tenga en cuenta que son convertidos los precios de la tabla Product a CURRENCY_VALUE cuando les insertan en la nueva tabla. También tenga en cuenta que podemos utilizar las propiedades públicas del tipo de lectura y escritura de datos:

CREATE TABLE Production.TestProducts
( Name NVARCHAR(100) NULL
,Price dbo.CURRENCY_VALUE NULL );
INSERT Production.TestProducts (Name, Price)
SELECT Name
,CAST('SEK ' + CAST(ListPrice AS NVARCHAR(100)) AS dbo.CURRENCY_VALUE)
FROM Production.Product;
UPDATE Production.TestProducts SET
Price.CurrencyCode = 'USD'
WHERE Price.Value = 3578.27000;
SELECT Name ,Price.CurrencyCode
,Price.Value ,Price
FROM Production.TestProducts
WHERE Price.CurrencyCode = 'USD';

Y Obtenemos este resultado:

Como puede ver, cuando se consulta la UDT sin llamar a una propiedad, se ve la forma de bytes bruto de este tipo. Para el valor 0xC375050000000000000000000000020003555344, los primeros 16 bytes en negrita representan el valor decimal. Se coloca antes de la cadena, simplemente porque lo escribió antes de la cadena en el método de escritura. Por supuesto, es muy importante leer los datos en el mismo orden en que se escribe en el método de lectura, de lo contrario, puede acabar con algunos errores realmente desagradable. Si usted fuera a utilizar el formato nativo, no tendría que preocuparse por esto. Sin embargo, debido a que el formato nativo es muy limitado en cuanto a qué tipos de datos se puede utilizar, por lo general tendrá que utilizar el formato definido por el usuario. Los últimos 4 bytes son del tipo de la cadena que representa el código de moneda 03.555.344 (03 = tres caracteres en la cadena, seguido por la representación hexadecimal de cada personaje: 55H/85D = U, 53H/83D = S, y44H/68D = D).

Al igual que con UDF, el resultado de un método UDT se puede guardar e indexados (en este caso, debe ser persistente para que se indexe) utilizando una columna calculada. Considere lo siguiente ejemplo, T-SQL en el que cree una columna calculada persistente al índice del código de moneda del precio de la tabla Production.TestProducts y consulta la columna para el número de precios que se indican en coronas suecas (SEK):

ALTER TABLE Production.TestProducts
ADD ComputedCurrencyCode AS Price.CurrencyCode
PERSISTED; -- Debe conservarse para el índice.
CREATE NONCLUSTERED INDEX ComputedCurrencyCodeIdx
ON Production.TestProducts (ComputedCurrencyCode );
GO
SELECT COUNT(*)
FROM Production.TestProducts
WHERE ComputedCurrencyCode = 'SEK';
-- SQL Server realiza una operación de Index Seek con el
-- índice ComputedCurrencyCodeIdx.

Una nota interesante es que el optimizador de ComputedCurrencyCodeIdx existe para la expresión utilizada en la última consulta y utiliza el índice, incluso si no se consulta la columna calculada directamente, como en este ejemplo (el cambio se muestra en negrita):

SELECT COUNT(*)
FROM Production.TestProducts
WHERE Price.CurrencyCode = 'SEK';
-- SQL Server sigue realizando una operación de Index Seek con el
-- indice ComputedCurrencyCodeIdx.


Que me permite hacer el codigo CLR?

El código de CLR que se utiliza dentro de su base de datos se pueden colocar en uno de los tres "conjuntos de permisos" diferentes: SAFE, EXTERNALACCESS o UNSAFE. El conjunto de permisos por defecto es SAFE.
SAFE es el conjunto de permisos que se ha utilizado para todos los ejemplos de esta lección, y es también el conjunto de permisos que se debe tratar de usar en todo momento para minimizar los problemas de seguridad, así como el impacto potencial de los bugs. El significado de cada conjunto de permisos se explica a continuación:

• SAFE: un assembly SAFE no se le permite acceder a los recursos fuera de la base de datos a los que se ha implementado, sólo se puede realizar cálculos y el acceso a la base de datos local.
• EXTERNAL ACCESS : Un ensamblado EXTERNAL ACCESS se le permite acceder a los recursos fuera de la instancia local de SQL Server a la que se despliega, como otra instancia de SQL Server, el sistema de archivos, o incluso un recurso de red como un servicio Web.
• UNSAFE: A un ensamblado UNSAFE se les permite ir aún más lejos que el acceso externo. Se permite ejecutar no CLR (también llamada no administrado) de código como una API de Win32 o un componente COM.

Usar FILESTREAM


FILESTREAM es una opción que se puede especificar para las columnas del tipo varbinary (max).
En esencia, hace que SQL Server almacenar los datos en estas columnas en archivos separados en el sistema de archivos en lugar de dentro de los archivos de base de datos actual. El uso de Filestream pueden mejorar tanto la lectura y el rendimiento de escritura de este tipo de datos. FILESTREAM se recomienda generalmente si los datos que se almacenan en la columna de al menos 1 megabyte (MB) de tamaño. Filestream puede perjudicar el rendimiento si tiene inserciones muy frecuente de los datos BLOB pequeños.
Para poder utilizar FILESTREAM, deberá activarlo en SQL Server mediante el establecimiento de la opción de sp_configure en 1, 2, o 3. Configuración de la opción de configuración para 1 solo permite T-SQL de acceso a los datos FILESTREAM, y establecer la opción a 2 también permite acceso directo a archivos con los datos a través del sistema de archivos. Por último, el establecimiento de la opción 3 permite el acceso a los datos de FILESTREAM a través de un archivo (de red) de acciones. Para utilizar FILESTREAM, también debe crear un grupo de archivos que contiene un archivo de base de datos de FILESTREAM. El archivo de base de datos de FILESTREAM no es realmente un archivo, es un directorio donde los archivos se almacenan los datos FILESTREAM. Un grupo de archivos FILESTREAM sólo puede tener un "archivo de base de datos."
El siguiente ejemplo muestra cómo agregar un grupo de archivos FILESTREAM a la Base de datos AdventureWorks (la cadena FILENAME se ha formateado para que quepa en la página impresa):

ALTER DATABASE AdventureWorks
ADD FILEGROUP FileStreamPhotosFG
CONTAINS FILESTREAM;
ALTER DATABASE AdventureWorks
ADD FILE
( NAME = 'FileStreamPhotosDF'
,FILENAME = 'C:\Program Files\Microsoft SQL Server\
MSSQL10.MSSQLSERVER\MSSQL\DATA\FileStreamPhotosDF')
TO FILEGROUP FileStreamPhotosFG;

Cuando el grupo de archivos se ha añadido, varbinary (max) FILESTREAM se puede crear.
Para ver una tabla que contiene columnas FILESTREAM, debe tener una columna uniqueidentifier marcado con la propiedad ROWGUIDCOL y tener una restricción única definida en él. El siguiente ejemplo muestra cómo agregar un ROWGUIDCOL a la tabla Production.ProductPhoto:

ALTER TABLE Production.ProductPhoto
ADD RowGuid UNIQUEIDENTIFIER NOT NULL
ROWGUIDCOL
CONSTRAINT DFProductPhotoRowGuid DEFAULT NEWSEQUENTIALID()
CONSTRAINT UQProductPhotoRowGuid UNIQUE;

Ahora podemos añadir un varbinary (max) FILESTREAM y copiar datos en ella utilizando normalmente

T-SQL:
ALTER TABLE Production.ProductPhoto
ADD ThumbNailPhotoAsFileStream VARBINARY(MAX) FILESTREAM NULL;
GO
UPDATE Production.ProductPhoto SET
ThumbNailPhotoAsFileStream = ThumbNailPhoto;

Si se examina el directorio C:\Archivos de programa\Microsoft SQL Server \MSSQL10.MSSQLSERVER\MSSQL\Data\FileStreamPhotosDF, nos encontramos con los siguientes elementos en ella:

• El directorio de registro $FSLOG de los datos de FileStream.
• El archivo Filestream.hdr almacena metadatos sobre el grupo de archivos FILESTREAM.
• Todos los directorios con nombres GUID, como 09A42544-450A-4932-B25F-5E33F117C179, son los directorios donde se almacenan los datos reales.

Al eliminar los datos de FILESTREAM (ya sea mediante una actualización o una sentencia DELETE), SQL Server no elimina inmediatamente el archivo. En cambio, los archivos se eliminan cuando el proceso de recolección de basura Filestream se ejecuta. Este proceso, a su vez se ejecuta cuando el proceso de control de base de datos se ejecuta.

Resumen de la lección

• Para utilizar los objetos definidos por el usuario sobre la base de SQLCLR,SQLCLR debe estar habilitado en la instancia de SQL Server.
• Los objetos más adecuados para el desarrollo con SQLCLR son las UDF y los agregados definidos por el usuario.
• Si crea UDT sobre la base de SQLCLR, asegúrese de que usted les prueba a fondo.
• Considere el uso de FILESTREAM si la información relevante sobre todo implica el almacenamiento de secuencias de más de 1 MB.