online - Ventaja de SQL SERVER CLR

sql server documentation (4)

¿Qué ventajas ofrece SQLServer CLR sobre T-SQL? ¿Usar la sintaxis .NET es más fácil que T-SQL? Veo que puede definir tipos de usuario, pero no tengo muy claro por qué es mejor. Por ejemplo, podría definir un tipo de correo electrónico y tendría una propiedad de prefijo y una propiedad de dominio. Luego puede buscar en el dominio o prefijo o ambos. Sin embargo, no veo cómo eso es diferente de agregar un par de columnas llamadas prefijo y un dominio llamado y buscarlas individualmente. Tal vez alguien tiene razones reales de por qué esto es mejor.

Daré un buen ejemplo: CLR tiene un objeto RegEx incorporado, que carece de SQL Server. Ahora es trivial escribir funciones para hacer restricciones / reparaciones de validación basadas en expresiones regulares.

Propósitos diferentes Un procedimiento almacenado de CLR es útil para cosas en las que resultaría beneficioso escribir código altamente de procedimientos o utilizar las funciones del sistema a las que no se puede acceder desde T-SQL. Aunque no existe una razón inherente por la cual no se puedan escribir aplicaciones en su contra, generalmente no se verán las secuencias de CLR como un lenguaje meramente diferente para escribir sprocs de aplicaciones. Por lo general, la mayoría de los usos de un sproc CLR serían para fines del sistema en lugar de componentes de la aplicación, aunque esto no es una regla rígida.

La capa de integración de CLR ofrece algunos recursos que no están disponibles directamente en los procedimientos almacenados de T-SQL, como las funciones de agregado personalizadas. También ofrece acceso a bibliotecas .Net, que pueden ser útiles para acceder a capacidades que T-SQL no puede admitir.

T-SQL hace cosas de bases de datos tradicionales, y se integra con el optimizador de consultas, por lo que sigue siendo el más apropiado para el código de base de datos orientada a conjuntos. Hay enlaces API para los scripts de CLR para proporcionar información al optimizador de consultas, pero esto agrega cierta complejidad.

También se puede usar la integración CLR para definir funciones que son accesibles para el código T-SQL. En algunos casos, estos pueden ser más rápidos y más eficientes en cuanto a la memoria que las funciones de T-SQL. El libro de prensa de Wrox sobre la integración de CLR analiza esto con cierta profundidad.

También puede, por ejemplo, llamar a un servicio web externo desde un método SQLCLR, que no es exactamente posible en T-SQL :-)

Bagazo

La integración SQLCLR / CLR dentro de SQL Server es solo otra herramienta para ayudar a resolver ciertos (no todos) los problemas. Hay algunas cosas que hace mejor que lo que se puede hacer en T-SQL puro, y hay algunas cosas que solo se pueden hacer a través de SQLCLR. Escribí un artículo para SQL Server Central, Stairway to SQLCLR Level 1: ¿Qué es SQLCLR? (Se requiere registro gratuito para leer los artículos allí), que aborda esta cuestión. Los conceptos básicos son (ver el artículo vinculado para más detalles):

Funciones Streaming con valores de tabla (sTVF)
SQL dinámico (dentro de las funciones)
Mejor acceso a recursos externos / Reemplace xp_cmdshell
- Pasar datos es más fácil
- Obtener más columnas de un resultado atrás es más fácil
- Sin dependencias externas (por ejemplo, 7zip.exe)
- Mejor seguridad mediante suplantación
Posibilidad de Multi-hilo
Manejo de errores (dentro de las funciones)
Agregados personalizados
Tipos personalizados
Modificar estado (dentro de una función y sin OPENQUERY / OPENROWSET )
Ejecutar un procedimiento almacenado (solo lectura; dentro de una función y sin OPENQUERY / OPENROWSET )
Rendimiento ( nota: esto no es significativo en todos los casos, pero definitivamente en algunos casos depende del tipo y la complejidad de la operación)
Puede capturar el resultado (es decir, lo que se envía a la pestaña Mensajes en SSMS) (por ejemplo, PRINT y RAISERROR con una gravedad = 0 a 10) - Olvidé mencionarlo en el artículo ;-).

Otra cosa a tener en cuenta es que a veces es beneficioso compartir código entre la aplicación y el DB para que el DB tenga una idea de cierta lógica empresarial sin tener que crear pantallas personalizadas e internas solo para acceder a ese código de aplicación. Por ejemplo, he trabajado en un sistema que importó archivos de datos de clientes y usé un hash personalizado de la mayoría de los campos y guardé ese valor en la fila en el DB. Esto permitió omitir filas fácilmente al importar sus datos nuevamente, ya que la aplicación compararía los valores del archivo de entrada y los compararía con el valor de hash almacenado en la fila. Si fueran iguales, sabíamos al instante que ninguno de los campos había cambiado, así que pasamos a la siguiente fila, y fue una simple comparación INT. Pero ese algoritmo para hacer el hash solo estaba en el código de la aplicación, ya sea para depurar un caso del cliente o buscar formas de descargar parte del procesamiento a servicios de back-end marcando filas que tenían al menos un campo con cambios (cambios provenientes de nuestra aplicación) en lugar de buscar cambios dentro de un archivo de importación más nuevo), no había nada que pudiera hacer. Esa hubiera sido una gran oportunidad para tener un poco de lógica empresarial en el DB, incluso si no fuera por el procesamiento normal; tener lo que equivale a un valor codificado en el DB sin la capacidad de comprender su significado hace que sea mucho más difícil resolver problemas.

Si está interesado en ver algunas de estas capacidades en acción sin tener que escribir ningún código, la versión gratuita de SQL # (de la que soy el autor) tiene funciones RegEx, agregados personalizados (UDA), tipos personalizados (UDT), etc.