.net - resueltos - procedimientos almacenados sql ejemplos

¿Se prefieren los procedimientos almacenados de CLR sobre los procedimientos almacenados de TSQL en SQL 2005+? (12)

Mi vista actual es no, prefiero los procedimientos almacenados de Transact SQL porque son una opción más liviana y (posiblemente) de mayor rendimiento, mientras que los procedimientos CLR permiten a los desarrolladores hacer todo tipo de travesuras.

Sin embargo, recientemente tuve que depurar algunos prox almacenados de TSQL mal escritos. Como de costumbre, encontré muchos de los problemas debido a que el desarrollador original del desarrollador no tenía experiencia real en TSQL, estaban enfocados en ASP.NET / C #.

Por lo tanto, el uso de procedimientos CLR proporcionaría primero un conjunto de herramientas mucho más familiar para este tipo de desarrollador y, en segundo lugar, las funciones de depuración y prueba son más potentes (es decir, Visual Studio en lugar de SQL Management Studio).

Estaría muy interesado en escuchar su experiencia, ya que parece que no es una simple elección.

Agregaría un par de razones para usar CLR que pueden no haber sido mencionadas.

Reemplace y amplíe las funciones básicas de consulta, no consulta y escalar sql.
A) Los informes de errores y las alertas se pueden integrar en función de los requisitos definidos. B) Defina fácilmente los niveles de depuración. C) Permitir una forma más fácil de interactuar con servidores SQL extranjeros
Mueva el código heredado a un entorno administrado.

Aparte del acceso al sistema de archivos (donde los procedimientos CLR tienen una ventaja muy pronunciada) usaría procs T-SQL. Si tiene cálculos especialmente complejos, podría poner esa pieza en una función CLR y llamarla desde su proceso (los udf son donde he encontrado que la integración de CLR realmente brilla). Luego obtendrá los beneficios de la integración de CLR para esa parte particular de su tarea, pero conserve la mayor cantidad posible de su lógica de proceso almacenada en la base de datos.

Creo que esos dos no son equivalentes ... que encajan entre sí.
Se supone que la integración CLR eliminará gradualmente los "procedimientos almacenados extendidos" de antaño. Tenemos algunos de estos en nuestro lugar de trabajo ... esencialmente bloques de procesamiento / lógica sobre datos SQL que eran demasiado difíciles / imposibles de hacer a través de procedimientos almacenados DB convencionales / T SQL. Así que lo escribieron como procedimientos almacenados extendidos en C ++ DLL que se pueden invocar de manera similar. Ahora se han eliminado y la integración de CLR es el reemplazo

Procedimientos almacenados de DB: si se puede hacer en T SQL, procédalos almacenados, hágalo.
Procedimientos almacenados de CLR: si la lógica es demasiado compleja o tediosa de hacer a través de T SQL ... si es algo que tomará menos líneas de código CLR para abordarlo (manipulación de cadenas, clasificación / filtrado complejo / personalizado, etc.) use esto enfoque.

El alojamiento de CLR dentro de SQL Server está destinado a brindarles a los desarrolladores de bases de datos opciones más flexibles sobre cómo buscaron lograr las tareas. Como han mencionado otros, SQL es excelente para operaciones y modificaciones en conjuntos de datos . Cualquiera que haya hecho un extenso y extenso desarrollo de aplicaciones con reglas complejas de negocios / dominios probablemente lo diga: intentar imponer algunas de estas reglas usando SQL puro (algunas veces en una macro consulta) puede convertirse en una auténtica pesadilla.

Simplemente hay ciertas tareas que se manejan mejor en forma de procedimiento u OO. Al tener la opción de utilizar el código .NET para analizar la secuencia de la lógica, las operaciones de consulta pueden ser más fáciles de leer y depurar. Después de haber utilizado los procesos almacenados CLR, puedo decir que avanzar con el depurador realmente facilita el seguimiento de lo que está sucediendo en el nivel de la base de datos.

Solo un ejemplo, con frecuencia usamos procesos almacenados CLR aquí como una "puerta de enlace" para consultas dinámicas de búsqueda. Diga una solicitud de búsqueda que puede tener hasta 30 parámetros de búsqueda diferentes. Los usuarios, obviamente, no usan los 30 de ellos, por lo que la estructura de datos aprobada tendrá 30 parámetros, pero en su mayoría DBNULL. El lado del cliente no tiene la opción de generar una declaración dinámica, por razones obvias de seguridad. La declaración dinámica resultante se genera internamente sin temor a "extras" externos.

En general, utiliza el CLR si tiene algo que no necesita interconectarse mucho con la base de datos. Entonces digamos que está analizando o decodificando un valor. Esto es más fácil de hacer en el CLR y luego devuelve el valor.

Intentar hacer una consulta compelx en el CLR simplemente no es el camino a seguir.

Por cierto, esto no cambió en 2008 tampoco.

Hay lugares para T-SQL y CLR bien escritos y bien pensados. Si alguna función no se llama con frecuencia y si requirió procedimientos extendidos en SQL Server 2000, CLR puede ser una opción. También ejecutar cosas como el cálculo justo al lado de los datos puede ser atractivo. Pero resolver malos programadores lanzando nuevas tecnologías suena como una mala idea.

La página de Libros en Libros de SQL Server sobre el tema enumera estos beneficios:

Un mejor modelo de programación. Los lenguajes de .NET Framework son, en muchos aspectos, más ricos que Transact-SQL, ya que ofrecen construcciones y capacidades que anteriormente no estaban disponibles para los desarrolladores de SQL Server. Los desarrolladores también pueden aprovechar el poder de .NET Framework Library, que proporciona un amplio conjunto de clases que se pueden usar para resolver problemas de programación de manera rápida y eficiente.
Mejora de la seguridad y protección. El código administrado se ejecuta en un entorno de tiempo de ejecución de lenguaje común, alojado por el Motor de base de datos. SQL Server aprovecha esto para proporcionar una alternativa más segura y segura a los procedimientos almacenados extendidos disponibles en versiones anteriores de SQL Server.
Posibilidad de definir tipos de datos y funciones agregadas. Los tipos definidos por el usuario y los agregados definidos por el usuario son dos nuevos objetos de base de datos administrados que amplían las capacidades de almacenamiento y consulta de SQL Server.
Desarrollo simplificado a través de un entorno estandarizado. El desarrollo de la base de datos está integrado en versiones futuras del entorno de desarrollo Microsoft Visual Studio .NET. Los desarrolladores usan las mismas herramientas para desarrollar y depurar objetos de base de datos y scripts que utilizan para escribir componentes y servicios de nivel medio o de capa del cliente .NET Framework.
Potencial para un mejor rendimiento y escalabilidad. En muchas situaciones, los modelos de compilación y ejecución de lenguaje de .NET Framework ofrecen un mejor rendimiento en Transact-SQL.

Los procedimientos almacenados de CLR no están destinados a reemplazar las consultas basadas en conjuntos. Si necesita consultar la base de datos, aún necesitará colocar SQL en su código CLR, como si estuviera incrustado en un código normal. Esto sería un desperdicio de esfuerzo.

Los procedimientos almacenados de CLR son para dos cosas principales: 1) interacción con el sistema operativo, como leer un archivo o colocar un mensaje en MSMQ, y 2) realizar cálculos complejos, especialmente cuando ya tiene el código escrito en un lenguaje .NET para hacer el cálculo

Nos encontramos con una situación con una función CLR que se llamaba miles de veces en un proceso SQL regular. Este fue un proceso para importar datos desde otro sistema. La función validó los datos y manejó nulos muy bien.

Si hicimos la operación en TSQL, el proceso terminó en aproximadamente 15 segundos. Si usamos la función CLR, el proceso terminó en 20 - 40 minutos. La función CLR se veía más elegante, pero por lo que pudimos ver, hubo un golpe de inicio para cada uso de la función CLR. Por lo tanto, si realiza una operación grande utilizando una función CLR, está bien, ya que el tiempo de inicio es pequeño en comparación con el tiempo de la operación. O si llama a la función CLR un número modesto de veces, el tiempo de inicio total para todas las invocaciones de la función sería pequeño. Pero ten cuidado con los bucles.

Además, para la mantenibilidad, es mejor no tener más idiomas de los que realmente necesita.

Publiqué la siguiente respuesta a una pregunta similar: Ventaja de SQL SERVER CLR . Añadiré aquí, sin embargo, que C # / VB.net / etc es un lenguaje con el que alguien está más cómodo que T-SQL no debería ser una razón para usar SQLCLR sobre T-SQL. Si alguien no sabe cómo lograr algo en T-SQL, primero solicite ayuda para encontrar una solución de T-SQL. Si no existe uno, entonces vaya a la ruta CLR.

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.

Siempre se trata de la herramienta adecuada para el trabajo, por lo que realmente depende de lo que está tratando de lograr.

Sin embargo, como regla general, tiene razón en que los procesos de CLR tienen una sobrecarga mayor y nunca funcionarán en operaciones de conjunto como T-SQL. Mi guía es hacerlo todo en T-SQL a menos que lo que necesita se vuelva demasiado complicado en T-SQL. Luego, intente más para que el enfoque T-SQL funcione. :-)

Los procesos CLR son geniales y tienen su lugar, pero su uso debe ser la excepción, no la regla.

Teniendo en cuenta lo que ha dicho, preferiría que los desarrolladores estuvieran debidamente capacitados en t-SQl y en las bases de datos en general, que permitirles crear posiblemente mucho más daño al rendimiento al permitirles realizar tareas de t-sql en CLR. Los desarrolladores que no entienden las bases de datos lo utilizan como una excusa para evitar hacer las cosas de la mejor manera para el rendimiento de la base de datos porque quieren tomar lo que ven como la ruta más fácil.