Estándares Transact-SQL
Buenas Prácticas de Programación

1. Convenciones y Estándares de Nombres
Nota: Los términos “notación Pascal” y “notación de Camell” son usados a través de este documento.

Notación Pascal – El primer carácter de todas las palabras se escribe en Mayúsculas y los otros caracteres en minúsculas.

Ejemplo: ColorDeFondo

Notación de Camell – El primer carácter de todas las palabras, excepto de la primera palabra se escribe en Mayúsculas y los otros caracteres en minúsculas.

Ejemplo: colorDeFondo

1. Usa notación Pascal para el nombre de las Tablas
CREATE TABLE dbo.Employee 2. Usa notación Pascal para el nombre de los campos de tabla
CREATE TABLE dbo.Employee
(
IdEmployee INT, FirstName VARCHAR(20), LastName VARCHAR(20)
)

3. NO usar nunca “sp_”, La razón es que: SQL Server reconoce el prefijo “sp_” como “System Stored Procedure”, es decir, un procedimiento almacenado de Sistema y lo buscaría en la Base de Datos.

Usa la siguiente sintaxis para los nombres de los Stored procedures: Usp_<Nombre Esquema>_<Nombre Tabla> _<Accion>
Ejemplo:
usp_GEN_Employee_Insert usp_GEN_Employee_GetAll

4. Usa esquemas para agrupar los objetos como tablas, los nombres deben de ser Abreviados.
Incorrecto:
GEN_Employee
Correcto:
GEN.Employee

2. Consideraciones en el diseño de base de datos 1. El nombre de la base de datos debe de asemejarse al nombre de la aplicación, no deberá de contener el prefijo DB, ya que se estaría redundando indicando que es una base de datos.
Utilizar notación Pascal para el nombre.

Incorrecto:
DB_Ventas

Correcto: Ventas

2. Eliminación de registro. Es recomendable, NO eliminar físicamente un registro de primeras. Pasarlo a un estado de “baja”.

3. Crear campos de auditoria que guardes los últimos valores ejecutadas por el usuario: * FechaRegistro * Responsable * Maquina * IP

4. Evite crear campos que acepten NULLs, solo aplíquelo para los campos que realmente lo requiera.

5. Implemente “SET NOCOUNT ON” en sus consultas.
Al ejecutar sentencias tipo DML en un procedimiento almacenado, el servidor SQL regresa un mensaje indicando el número de filas afectadas por el proceso. Aunque esta información puede ser útil para depurar el código, resulta obsoleta si no se está depurando. Al establecer el uso de SET NOCOUNT ON, se desactiva la función de retorno de esta información adicional. En procedimientos almacenados formados por varias instrucciones o declaraciones de sentencias DML, habilitar SET NOCOUNT ON puede ayudarnos a conseguir un aumento de rendimiento considerable, además, si tomamos en cuenta una base de datos que contenga decenas de Procedimientos Almacenados para uso en una aplicación orientada a múltiples clientes al mismo tiempo, un detalle tan sencillo como este puede aportar una diferencia notable. Se podría usar a modo de ejemplo el siguiente esqueleto:

CREATE PROC dbo.StoreProcedureExample
(
@variable_1 INT,
@variable_2 VARCHAR(10),
@variable_n BIT
)
AS
BEGIN
SET NOCOUNT ON; --INSTRUCCIÓN 1 --INSTRUCCIÓN 2 --INSTRUCCIÓN N SET NOCOUNT OFF;
END

6. Evite usar “SELECT *”
Aunque resulte fácil y cómodo usar el comodín (*) para traer todos los campos, este debe omitirse y en su lugar especificarse los campos que sean necesario traerse. El uso del comodín impide además un uso efectivo de forma eficiente de los índices. En caso de que sean todos como el uso del “*” establece, especifique cada uno de ellos, un simple ALT+F1 sobre el nombre de la tabla seleccionada traerá su estructura, por lo que copiar, pegar los campos y añadir una coma supone un esfuerzo mínimo.
Me he topado en varias ocasiones que al hacer un chequeo de existencia se recurre por lo regular a la siguiente sintaxis:

IF EXISTS (SELECT * FROM Tabla WHERE Campo = Condicionante) BEGIN --INSTRUCCIONES 1,2…N END

Evítelo, no existe ningún motivo para cargar más trabajo a la base de datos trayendo todos los campos si lo único que se desea saber es si existe. Puede sustituirse sin ningún problema el * por ‘’ o 1 cumpliendo con su función de chequeo de existencia sin necesidad de traer datos:

IF EXISTS (SELECT '' FROM Tabla WHERE Campo = Condicionante) BEGIN --INSTRUCCIONES 1,2…N END

O bien:

IF EXISTS (SELECT 1 FROM Tabla WHERE Campo = Condicionante) BEGIN --INSTRUCCIONES 1,2…N END

Ambos le darán la misma funcionalidad sin necesidad de cargar de trabajo adicional e innecesario a la base de datos.

7. Use NOLOCK… sabiamente.
El uso de NOLOCK puede mejorar considerablemente la velocidad de algunas consultas. Al usar NOLOCK en las consultas se establece que la lectura no “atrapa” la tabla y esta puede ser leída al mismo tiempo por todos los usuarios. Sin embargo, hay que tomar en cuenta que el conjunto de datos mostrados es considerado como una “lectura sucia”. Esto significa que los datos mostrados podrían no ser del todo precisos, pudiendo estos encontrarse en medio de alguna transacción del tipo DELETE, UPDATE o INSERT. Aun así, para el caso de tablas estáticas como en algunos casos las dedicadas a catálogos fijos y para escenarios de reportes cuya información es más que nada histórica y que por lo tanto no se verá afectada en el momento, podría suponer una mejora aceptable sobre todo si son consultas frecuentes en un ambiente multiusuario.
No se puede usar NOLOCK de forma indiscriminada en todas los Procedimientos Almacenados, es más una cuestión de evaluación sobre escenarios particulares.
NOLOCK se usa al momento de efectuar una consulta y solo aplica a las tablas. Su estructura seria la siguiente:

SET NOCOUNT ON SELECT Campo1, Campo2, . . CampoN FROM Tabla1 T1(NOLOCK) INNER JOIN Tabla2 T2(NOLOCK) ON T2.CampoX=T1.CampoX INNER JOIN Tabla3 T3(NOLOCK) ON T3.CampoY=T2.CampoY WHERE <Condicionantes>
SET NOCOUNT OFF

8. Utilize SP_EXECUTESQL en lugar EXECUTE.
Se recomienda el uso de sp_executesql, en lugar de la instrucción EXECUTE o EXEC al ejecutar código dinámico. Esto debido a que sp_executesql admite la sustitución de parámetros, es mucho más versátil que EXECUTE y además genera planes de ejecución; Lo que aumenta las probabilidades de que al volverlo a utilizar resulte más eficaz.

9. Evada el uso de CURSORES
Los cursores son una herramienta usada para acceder y modificar el resultado de una cláusula SELECT fila por fila. El problema con su uso es que consumen una enorme cantidad de recursos, especialmente de memoria. Siempre que sea posible, se debe omitir el uso de cursores o minimizar su implementación. Algunas alternativas y sustitutos al uso de cursores pueden ser:

* Usar ciclos WHILE. * Uso de tablas derivadas. * Uso de subqueries correlacionados. * Uso de CASE. * Uso de múltiples consultas. * La combinación de todas las anteriores.

10. Utilice adecuadamente las variables TIPO TABLA & TABLAS TEMPORALES
¿Siempre es mejor usar variables tipo tabla en lugar de tablas temporales?, revisemos las características y diferencias entre una y otra.
Variables tipo tabla: * Su uso en procedimientos almacenados provoca menos re compilaciones. * Apuntan a estructuras de memoria por lo que producen menos consumo de recursos que las tablas temporales. * Su contenido no siempre está en memoria. En caso de que se inserte una cantidad grande de registros esta se almacena en TEMPDB. * El contenido de la variable no es afectado por el comando ROLLBACK. * No se pueden generar al vuelo. * No usan paralelismo (multiple threads) en su plan de ejecución. Su uso para tablas de concurrencia alta o con una gran cantidad de datos puede afectar su desempeño, siendo este menor en comparación con una tabla temporal. * No se les puede agregar índices. * No se les pueden modificar ni truncar una vez creadas.

Tablas temporales:

* Se almacenan automáticamente en la base de datos TEMPDB. * Su creación puede ocasionar bloqueos en las tablas sysobjects, sysindexes y afectar a todo el servidor. * Permite el uso de índices. * Su uso en procedimientos almacenados puede provocar una re compilación continua. * A grandes rasgos, pueden tratarse como una tabla normal.

En general, se puede sugerir el uso de variables tipo tabla siempre que sea posible en lugar de tablas temporales. Use estas últimas solo en caso de que se maneje una cantidad muy grande de información y siempre procurando crear su estructura previamente nunca creándolas “al vuelo”.

11. Implementación de sql dinamico (o codigo rojo).
Aunque en general el uso de SQL Dinámico esta algo condenado debido a que una mala implementación puede resultar en un grieta de seguridad que de entrada a un severo caso de SQL Injection. La implementación que se sugiere esta encapsulada dentro de un procedimiento almacenado, no es del todo dinámica para la aplicación cliente, es decir, no le permite estructurar sentencias libres y está orientada más que nada a procedimientos almacenados cuya función principal es una consulta parametrizada con opciones variables. Usare la base de datos AdventureWorksDW2008R2 para ilustrar un ejemplo. Probaremos con el siguiente procedimiento almacenado:

CREATE PROCEDURE BeforeRedCodeSELECT
(
@ProductKey INT=NULL,
@SalesOrderLineNumber TINYINT=NULL,
@OrderQuantity SMALLINT=NULL,
@CurrencyKey INT=NULL
)
AS
BEGIN
SET NOCOUNT ON SELECT FIS.ProductKey, FIS.OrderQuantity, FIS.UnitPrice, FIS.SalesOrderNumber, FIS.CurrencyKey, FIS.SalesOrderLineNumber FROM FactInternetSales FIS(NOLOCK) WHERE FIS.ProductKey=ISNULL(@ProductKey, FIS.ProductKey) AND FIS.SalesOrderLineNumber= ISNULL (@SalesOrderLineNumber, FIS.SalesOrderLineNumber) AND FIS.OrderQuantity= ISNULL (@OrderQuantity, FIS.OrderQuantity) AND FIS.CurrencyKey= ISNULL (@CurrencyKey, FIS.CurrencyKey) SET NOCOUNT OFF
END

En este caso la consulta acepta valores nulos y se usa ISNULL (se puede usar COALESCE en su lugar, pero para razones prácticas en el ejemplo no tiene mucho caso y ISNULL es un poco más rápido que COALESCE) para dar la flexibilidad al procedimiento almacenado y poder consultar ya sea usando uno, los cuatro o cualquier combinación de parámetros. Ahora, convirtiendo la consulta en SQL Dinámico, quedaría de la siguiente forma:

CREATE PROCEDURE AfterRedCodeSELECT
(
@ProductKey INT=NULL,
@SalesOrderLineNumber TINYINT=NULL,
@OrderQuantity SMALLINT=NULL,
@CurrencyKey INT=NULL
)
AS
BEGIN
SET NOCOUNT ON DECLARE @Query AS NVARCHAR(MAX) SET @Query=N' SELECT FIS.ProductKey, FIS.OrderQuantity, FIS.UnitPrice, FIS.SalesOrderNumber, FIS.CurrencyKey, FIS.SalesOrderLineNumber FROM FactInternetSales FIS(NOLOCK) WHERE 1=1 ' IF @ProductKey IS NOT NULL BEGIN SET @Query = @Query + ' AND FIS.ProductKey = @ProductKey ' END IF @SalesOrderLineNumber IS NOT NULL BEGIN SET @Query = @Query + ' AND FIS.SalesOrderNumber = @SalesOrderLineNumber ' END IF @OrderQuantity IS NOT NULL BEGIN SET @Query = @Query + ' AND FIS.OrderQuantity = @OrderQuantity ' END IF @CurrencyKey IS NOT NULL BEGIN SET @Query = @Query + ' AND FIS.CurrencyKey =@CurrencyKey ' END --PRINT @Query EXECUTE sp_executesql @Query , N'@ProductKey INT, @SalesOrderLineNumber TINYINT, @OrderQuantity SMALLINT, @CurrencyKey INT ' , @ProductKey = @ProductKey , @SalesOrderLineNumber = @SalesOrderLineNumber , @OrderQuantity = @OrderQuantity , @CurrencyKey = @CurrencyKey SET NOCOUNT OFF
END

Aquí el procedimiento almacenado solo incluirá las condiciones que sean necesarias y que no tengan un valor nulo. Ambos procedimientos devuelven los mismos resultados y son equivalentes el uno al otro. Usando SQL Profiler, podemos apreciar la diferencia en la optimización de recursos:

Las lecturas se reducen para este escenario particular, en poco más de un 80%, la duración en un 60% y en el uso del CPU, un cambio total.

Por lo que se aprecia el SQL Dinámico podría no será tan malo como parece y puede ofrecernos alternativas creativas para optimizar nuestras consultas. Evalué sus consultas complejas y pesadas para sopesar si una refactorización usando SQL Dinámico puede ayudarle, cuidando siempre que esta no se preste para casos de SQL Injection.

12. Aplicar ÍNDICES de forma optimizada.
Es ya bien conocido el uso de índices para acelerar consultas en SQL Server y otros motores de bases de datos. Estos le indican al motor de la base de datos en turno, que esa columna o conjunto de columnas se van a usar con mayor frecuencia, lo cual hace que los datos se almacenen en memoria para contar con un acceso más eficiente a los datos.
Es aconsejable que los índices se formen sobre los campos que encuentren frecuentemente en alguno de los siguientes escenarios:

* Son PRIMARY KEY o FOREIGN KEY. * Se usan frecuentemente para enlazar tablas con JOIN. * Se usan de forma habitual dentro de los procedimientos almacenados o en consultas emparejados con alguno de los siguientes comandos: BETWEEN, ORDER BY, GROUP BY, TOP, DISTINCT (Aunque ya mencionamos que estos deben de evitarse en lo posible, si no hay otra alternativa, hay que considerar sus campos para la aplicación de índices). * Son de uso corriente para filtrados en la clausura WHERE.

Pese a que los índices generen una mejora en tiempos de respuesta, tampoco se puede abusar indiscriminadamente de ellos. En contraposición a su mejora de tiempo de respuesta cada índice ocupa un espacio equivalente al número de registros en la tabla, penalizando el rendimiento en la base de datos al ejecutar INSERT, UPDATES y DELETE.
Omita crear índices con campos que contienen pocos valores, como aquellos que son de tipo BIT o CHAR (de uno a cuatro) y asegúrese de no agregar el mismo índice con distintos nombres.
El estudio profundo de la creación, diseño y mantenimiento de índices escapa del alcance de este articulo (espero escribir algo más elaborado sobre el tema de índices en un futuro), sin embargo es importante que se tome el tiempo para comprenderlos. Su correcta aplicación puede favorecer por mucho el tiempo de respuesta de sus consultas.

13. Recomendaciones adicionales:

* Si el procedimiento almacenado contiene demasiadas sentencias IF-ELSE para ejecutar distintos procesos acorde a ciertos parámetros, resulta más eficiente separar cada bloque y encapsularlos en procedimientos almacenados diferentes; Esto para evitar que el plan de ejecución cambie acorde al valor del resultado de la expresión IF, desperdiciando con esto la ventaja de el plan de ejecución en memoria y la pre compilación. * Evite el uso de la instrucción SELECT-INTO. Al ejecutarse se bloquean las tablas involucradas. Aplique en su lugar la sentencia INSERT INTO-SELECT. * Trate de usar siempre la instrucción JOIN antes que cualquier sub consulta. * Use la instrucción BETWEEN en lugar de IN siempre que sea posible. * En el caso de que se use la instrucción LIKE, evite el uso del comodín “%” al inicio de la cadena a buscar. Esto debido a que si se aplica, la búsqueda tendría que leer todos los datos de la tabla o tablas involucradas para responder a la consulta. Se recomienda que existan al menos tres caracteres antes del comodín. * Evite en la medida de lo posible el uso de DISTINCT * En caso de usar la instrucción UNION y existiera la seguridad de que en los SELECT involucrados no se obtendrán registros duplicados, entonces lo recomendable en este escenario es sustituir UNION por UNION ALL para evitar que se haga uso implícito de la instrucción DISTINCT, ya que esta aumenta el consumo de recursos. * Evada siempre que sea posible el uso de ORDER BY. Al igual que DISTINCT consume una elevada cantidad de recursos. Considere si es realmente necesario usarlo o, si por otro lado se puede dejar el ordenamiento de los resultados a la aplicación que recibirá los datos. * Use SQL Profiler y levante una traza para estudiar el rendimiento de los procedimientos almacenados de mayor concurrencia y vea opciones para refactorizar el proceso. Otorgue especial atención a las columnas CPU, Duraction,reads y writes para optimizar el uso de memoria y el I/O generado. * Mantenga las transacciones lo más cortas posibles dentro de un procedimiento almacenado. Esto favorece a la reducción del número de bloqueos, promoviendo a acelerar el rendimiento general de la base de datos. * Utilice el Plan de Ejecución para revisar sus consultas, entienda como SQL Server está ejecutando sus consultas y evalué sus sugerencias. * Si requiere almacenar volúmenes de texto muy grandes, pero son menores a 8000 caracteres, use el tipo de datoVARCHAR en lugar de TEXT. * Evalué cuidadosamente el uso de CHAR y VARCHAR dependiendo si el campo en el que se va a usar varía mucho o no de tamaño. Esto para sopesar rendimiento de velocidad sobre rendimiento de almacenamiento. SQL Server procesa más rápido las columnas de longitud fija. Use CHAR para columnas de poca variación en longitud y VARCHAR para aquellas que no tienen una longitud estable o promedio. * No use columnas con tipos de datos FLOAT, REAL o DATETIME como FOREIGN KEY.