Esta es una breve lista de recomendaciones básicas que ayudan mucho al momento de trabajar con stored procedures en SQL Server.
Como últimamente he utilizado mucho SQL Server 2008 R2 puede haber alguna sugerencia que solo aplique a dicha versión.
1) Para facilitar la lectura utilice una identación adecuada.
2) Agregue comentarios en la lógica compleja y utilice el wizard de creación de objetos (stored procedures en este caso) para completar los campos sugeridos (autor, fecha de creación, breve descripción). Recuerde utilizar Ctrl+Shift+M para especificar los valores del template.
3) Es buena práctica escribir las palabras reservadas en mayúsculas (por ej. SELECT, RTRIM, FROM, DELETE, etc..) porque facilitan mucho la lectura del código.
4) Escriba el nombre del stored procedure de forma correctamente calificada ( por ej. [dbo]. EmployeeSalaryCalc )
5) En lo posible siempre declarar e inicializar las variables al comienzo del código del stored procedure, tratar de usar la cantidad de variables mínimas necesarias.
6) No escriba el nombre del stored procedure comenzando con el prefijo “sp_”, esto es reservado para los sotred procedures de sistema de SQL Server. Cuando se hace la solicitud de un stored procedure que comienza con ese prefijo el motor de base de datos intenta buscar primero en la base de datos “master” y luego en las demas.
7) Agregue la opción de SET NOCOUNT ON al inicio del procedimiento para evitar el mensaje innecesario de número de filas afectadas.
8) Utilice tablas temporales solamente cuando es necesario. Los stored procedure suelen utilizar un plan de ejecución, almacenado en caché, para aumentar el rendimiento, cuando se utilizan tablas temporales se requiere hacer la compilación en cada ejecución y no se puede aprovechar el plan de ejecución.
9) Trate de devolver siempre las columnas necesarias, es muy recomendable evitar sentencias del tipo “SELECT * FROM”.
10) Utilice cursores solo cuando es necesario. Los cursores requieren más memoria y por tanto se degrada el rendimiento del stored procedure. En algunos casos se puede utilizar la variable de tabla y el bucle WHILE para iterar sobre un el conjunto de resultados.
11) Es buena práctica asignar el valor por defecto para cada parámetro y siempre establecer el tamaño correcto de las variables (tanto de retorno como internas). Por ejemplo si en la tabla un campo determinado tiene largo 100 (Username (varchar(100)) y en el procedimiento se utiliza como varchar(200) se puede producir un error en tiempo de ejecución del tipo “error de cadena truncada” si se está frente a un INSERT o UPDATE de dicho campo.
12) Utilice la sentencia Try para capturar adecuadamente los errores en tiempo de ejecución dentro del stored procedure y hacer un manejo acorde en dicha situación.
13) Ante consultas complejas y que se utilizan en varias ocasiones, tenga en cuenta crear vistas para un manejo más adecuado.
14) Evite utilizar sub-consultas (SELECT A.column1 FROM (SELECT data1 FROM) as A …), utilice la sentencia INNER JOIN siempre que sea posible.
15) Trate de evitar la condición de filtrado en la cláusula WHERE si puede ser escrito al momento de utilizar la sentencia de JOIN.
16) Utilice la sentencia SELECT TOP 1 para la comprobación/obtención de un dato en base a una condición.
Ej. en lugar de : SELECT @ name = nombre FROM Empleados WHERE nombre LIKE ‘%Rob%’
Si se devuelven múltiples registros ocasiona un error en momento de ejecución.
SELECT TOP 1 @ name = nombre FROM Empleados WHERE nombre LIKE ‘%Rob%’
17) En los caso donde se puede aplicar una instrucción CASE es mejor que el anidamiento de sentencias IF-ELSE.
18) Consultas Dinámicas: trate de reducir al mínimo el uso de las consultas dinámicas.
Si está utilizando una consulta dinámica similar a “SELECT * FROM mydb.dbo.emp WHERE empid = @ EmpId”, entonces no hay problema. En este caso se proporciona un valor para el parámetro @EmpId y el motor no tiene que generar o recargar un nuevo plan de ejecución. En cambio si se utiliza una consulta del tipo @query = “SELECT * FROM emp donde empid =” + @ EmpId esto por defecto no admite un plan de ejecución predeterminado y cargado en el motor de base de datos.
19) Utilice las instrucciones ORDER BY y DISTINCT solo cuando se requieran. Caso contrario el motor de SQL Server obtendrá el primer resultado y lo hará de nuevo si requiere utilizar algunas de estas instrucciones.
20) Utilice los índices correctos en las columnas de cada tabla. No es buena práctica crear índices en las columnas que no se utilizan en las cláusulas WHERE. Cuando se crean índices extras se puede requerir un ida y vuelta extra del motor de base de datos para consultar el resultado.