Problemas de diseño de T-SQL

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Base de datos de Azure SQL de Microsoft Fabric

Al analizar el código T-SQL en el proyecto de base de datos, una o varias advertencias podrían clasificarse como problemas de diseño. Debe solucionar los problemas de diseño para evitar las situaciones siguientes:

• Los cambios posteriores en la base de datos pueden interrumpir las aplicaciones que dependen de ella.
• Es posible que el código no genere el resultado esperado.
• El código podría interrumpirse si lo ejecuta con versiones futuras de SQL Server.

En general, no debe suprimir un problema de diseño porque podría interrumpir la aplicación, ya sea ahora o en el futuro.

Las reglas proporcionadas identifican los siguientes problemas de diseño:

• SR0001: Evite SELECT * en procedimientos almacenados, vistas y funciones con valores de tabla
• SR0008: Considere la posibilidad de usar SCOPE_IDENTITY en lugar de @@IDENTITY
• SR0009: Evite usar tipos de longitud variable con un tamaño de 1 o 2
• SR0010: Evite usar sintaxis en desuso al combinar tablas o vistas
• SR0013: El parámetro de salida (parámetro) no se rellena en todas las rutas de acceso al código
• SR0014: La pérdida de datos puede producirse al convertir de {Type1} a {Type2}

SR0001: Evite SELECT * en procedimientos almacenados, vistas y funciones con valores de tabla

Si usa un carácter comodín en un procedimiento almacenado, una vista o una función con valores de tabla para seleccionar todas las columnas de una tabla o vista, el número o la forma de las columnas devueltas pueden cambiar si cambia la tabla o vista subyacente. La forma de una columna es una combinación de su tipo y tamaño. Esta varianza puede provocar problemas en las aplicaciones que consumen el procedimiento almacenado, la vista o la función con valores de tabla, ya que esos consumidores esperarán un número diferente de columnas.

Cómo corregir infracciones

Puede proteger a los consumidores del procedimiento almacenado, la vista o la función con valores de tabla de los cambios de esquema reemplazando el carácter comodín por una lista completa de nombres de columna.

Ejemplo

En el ejemplo siguiente se define primero una tabla denominada [Table2] y, a continuación, se definen dos procedimientos almacenados. El primer procedimiento contiene un SELECT *, que infringe la regla SR0001. El segundo procedimiento evita SELECT * y enumera explícitamente las columnas de la instrucción SELECT.

CREATE TABLE [dbo].[Table2]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL,
[Comment] NVARCHAR (50)
)
ON [PRIMARY]

CREATE PROCEDURE [dbo].[procWithWarning]
AS
BEGIN
-- Contains code that breaks rule SR0001
SELECT *
FROM [dbo].[Table2]
END

CREATE PROCEDURE [dbo].[procFixed]
AS
BEGIN
-- Explicitly lists the column names in a SELECT statement
SELECT [dbo].[Table2].[ID], [dbo].[Table2].[c1], [dbo].[Table2].[Comment]
FROM [dbo].[Table2]
END

SR0008: considere la posibilidad de usar SCOPE_IDENTITY en lugar de @@IDENTITY

Dado que @@IDENTITY es un valor de identidad global, es posible que se haya actualizado fuera del ámbito actual y haya obtenido un valor inesperado. Los desencadenadores, incluidos los desencadenadores anidados usados por replicación, pueden actualizar @@IDENTITY fuera del ámbito actual.

Cómo corregir infracciones

Para resolver este problema, debe reemplazar las referencias a @@IDENTITY por SCOPE_IDENTITY, que devuelve el valor de identidad más reciente en el ámbito de la instrucción user.

Ejemplo

En el primer ejemplo, @@IDENTITY se usa en un procedimiento almacenado que inserta datos en una tabla. A continuación, la tabla se publica para la replicación de mezcla, que agrega desencadenadores a las tablas que se publican. Por lo tanto, @@IDENTITY puede devolver el valor de la operación de inserción en una tabla de sistema de replicación en lugar de la operación de inserción en una tabla de usuario.

La tabla Sales.Customer tiene un valor de identidad máximo de 29483. Si inserta una fila en la tabla, @@IDENTITY y SCOPE_IDENTITY() devolverán valores diferentes. SCOPE_IDENTITY() devuelve el valor de la operación de inserción en la tabla de usuario, mientras que @@IDENTITY devuelve el valor de la operación de inserción en la tabla del sistema de replicación.

En el segundo ejemplo se muestra cómo puede usar SCOPE_IDENTITY() para acceder al valor de identidad insertado y resolver la advertencia.

CREATE PROCEDURE [dbo].[ProcedureWithWarning]
@param1 INT,
@param2 NCHAR(1),
@Param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = @@IDENTITY
END

CREATE PROCEDURE [dbo].[ProcedureFixed]
@param1 INT,
@param2 NCHAR(1),
@param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = SCOPE_IDENTITY()
END

SR0009: evite usar tipos de longitud variable con un tamaño de 1 o 2

Cuando se usan tipos de datos de longitud variable como VARCHAR, NVARCHAR y VARBINARY, se incurre en un coste de almacenamiento adicional para realizar un seguimiento de la longitud del valor almacenado en el tipo de datos. Además, las columnas de longitud variable se almacenan después de todas las columnas de longitud fija, lo que puede afectar al rendimiento. También recibirá una advertencia si declara un tipo de longitud variable, como VARCHAR, pero no especifica ninguna longitud. Esta advertencia se produce porque, si no se especifica, la longitud predeterminada es 1.

Cómo corregir infracciones

Si la longitud del tipo va a ser muy pequeña (tamaño 1 o 2) y coherente, decárelas como un tipo de longitud fija, como CHAR, NCHAR y BINARY.

Ejemplo

En este ejemplo se muestran las definiciones de dos tablas. La primera tabla declara una cadena de longitud variable con longitud 2. En su lugar, la segunda tabla declara una cadena de longitud fija, que evita que se produzca la advertencia.

CREATE TABLE [dbo].[TableWithWarning]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL PRIMARY KEY,
[c2] INT,
[c3] INT,
[SmallString] VARCHAR(2)
)
ON [PRIMARY]

CREATE TABLE [dbo].[FixedTable]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL PRIMARY KEY,
[c2] INT,
[c3] INT,
[SmallString] CHAR(2)
)
ON [PRIMARY]

Los datos de los tipos de longitud variable se almacenan físicamente después de los datos para los tipos de longitud fija. Por lo tanto, si cambia una columna de variable a longitud fija en una tabla que no está vacía, provocará un movimiento de datos.

SR0010: Evite usar sintaxis en desuso al combinar tablas o vistas

Las combinaciones que usan la sintaxis en desuso se dividen en dos categorías:

• Combinación interna: para una combinación interna, los valores de las columnas que se van a combinar se comparan mediante un operador de comparación como =, <, >=, etc. Las combinaciones internas devuelven filas solo si al menos una fila de cada tabla coincide con la condición de combinación.
• Combinación externa: las combinaciones externas devuelven todas las filas de una de las tablas o vistas especificadas en la cláusula FROM, como mínimo, siempre que tales filas cumplan alguna de las condiciones de búsqueda de WHERE o HAVING. Si usa = o = para especificar una combinación externa, estará usando sintaxis en desuso.

Cómo corregir infracciones

Para corregir una infracción en una combinación interna, use la sintaxis INNER JOIN.

Para corregir una infracción en una combinación externa, use la sintaxis OUTER JOIN adecuada. Dispone de las siguientes opciones:

• COMBINACIÓN EXTERNA IZQUIERDA o COMBINACIÓN IZQUIERDA
• COMBINACIÓN EXTERNA DERECHA o COMBINACIÓN DERECHA

En los ejemplos siguientes se proporcionan ejemplos de sintaxis en desuso y sintaxis actualizada. Puede encontrar más información sobre combinaciones en Combinaciones.

Ejemplos

Los seis ejemplos siguientes muestran las siguientes opciones:

1. En el ejemplo 1 se muestra la sintaxis en desuso en una combinación interna.
2. En el ejemplo 2 se muestra cómo se puede actualizar el ejemplo 1 para usar la sintaxis actual.
3. En el ejemplo 3 se muestra la sintaxis en desuso en una combinación externa izquierda.
4. En el ejemplo 4 se muestra cómo puede actualizar el ejemplo 2 para usar la sintaxis actual.
5. En el ejemplo 5 se muestra la sintaxis en desuso en una combinación externa derecha.
6. En el ejemplo 6 se muestra cómo puede actualizar el ejemplo 5 para usar la sintaxis actual.

-- Example 1: Deprecated syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]

-- Example 2: Current syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
INNER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

-- Example 3: Deprecated syntax for a left outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] *= [T2].[ID]

-- Example 4: Fixed syntax for a left outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
LEFT OUTER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

-- Example 5: Deprecated syntax for a right outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] =* [T2].[ID]

-- Example 6: Fixed syntax for a right outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
RIGHT OUTER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

SR0013: El parámetro de salida (parámetro) no se rellena en todas las rutas de acceso al código

Esta regla identifica el código en el que el parámetro de salida no tiene ningún valor configurado en una o varias rutas de acceso de código a través del procedimiento almacenado o la función. Esta regla no identifica en qué rutas de acceso se debe configurar el parámetro de salida. Si hay varios parámetros de salida con este problema, aparece una advertencia para cada parámetro.

Cómo corregir infracciones

Hay dos maneras de corregir este problema. La manera más fácil es inicializar los parámetros de salida con un valor predeterminado al principio del cuerpo del procedimiento. Como alternativa, también puede configurar un valor para el parámetro de salida en las rutas de acceso a código específicas en las que no está configurado el parámetro. Sin embargo, puede pasar por alto una ruta de acceso al código poco común en un procedimiento complejo.

Importante

Especificando un valor dentro de la declaración de procedimiento, como CREATE PROC MyProcedure (@param1 INT = 10 OUTPUT), no se resolverá el problema. Debe asignar un valor al parámetro de salida dentro del cuerpo del procedimiento.

Ejemplo

En el ejemplo siguiente se muestran dos procedimientos sencillos. El primer procedimiento no establece el valor del parámetro de salida, @Sum. El segundo procedimiento inicializa el parámetro @Sum al inicio del procedimiento, lo que garantiza que el valor se determinará en todas las rutas de acceso al código.

CREATE PROCEDURE [dbo].[procedureHasWarning]
(
@Value1 BIGINT,
@Value2 INT,
@Value3 INT,
@Sum INT OUTPUT
)
AS
BEGIN
-- No initialization of the output parameter
--
-- Additional statements here.
--
RETURN 0;
END
--
CREATE PROCEDURE [dbo].[procedureFixed]
(
@Value1 BIGINT,
@Value2 INT,
@Value3 INT,
@Sum INT OUTPUT
)
AS
BEGIN
-- Initialize the out parameter
SET @Sum = 0;
--
-- Additional statements here
--
RETURN 0;
END

SR0014: Pueden perderse datos al convertir de {Type1} a {Type2}

Si los tipos de datos se asignan a columnas, variables o parámetros de manera incoherente, se convierten implícitamente cuando se ejecuta el código de Transact-SQL que contiene esos objetos. Este tipo de conversión no solo reduce el rendimiento, sino que, en algunos casos, puede provocar una ligera pérdida de datos. Por ejemplo, podría ejecutarse un recorrido de tabla si se deben convertir todas las columnas de una cláusula WHERE. O peor, pueden perderse datos si una cadena Unicode se convierte en una cadena ASCII que usa una página de códigos diferente.

Esta regla NO:

• Comprueba el tipo de una columna calculada porque el tipo no se conoce hasta el tiempo de ejecución.
• Analiza ningún elemento contenido en una instrucción CASE. Tampoco analiza el valor devuelto de una instrucción CASE.
• Analiza los parámetros de entrada ni el valor devuelto de una llamada a ISNULL

En esta tabla se resumen las comprobaciones cubiertas por la regla SR0014:

Construcción de lenguaje	Qué se comprueba	Ejemplo
Valor predeterminado de los parámetros	Tipo de datos de parámetro	CREATE PROCEDURE p1(@p1 INT = 1) AS BEGIN END
Predicado CREATE INDEX	El predicado es booleano	CREATE INDEX index1 ON table1 (column1) WHERE column1 > 10
Argumentos de las funciones LEFT o RIGHT	Longitud y tipo de argumento de la cadena	SET @v = LEFT('abc', 2)
Argumentos de las funciones CAST y CONVERT	La expresión y los tipos son válidos	SET @v = CAST('abc' AS CHAR(10))
Instrucción SET	El lado izquierdo y el lado derecho tienen tipos compatibles	SET @v1 = 'xyz' SELECT @v1 = c1 FROM t1
Predicado de la instrucción IF	El predicado es booleano	IF (@v > 10)
Predicado de la instrucción WHILE	El predicado es booleano	WHILE (@v > 10)
Instrucción INSERT	Los valores y columnas son correctos	INSERT INTO t1(c1, c2) VALUES (99, 'xyz') INSERT INTO t1 SELECT c1 FROM t2.
Predicado SELECT WHERE	El predicado es booleano	SELECT * FROM t1 WHERE c1 > 10
Expresión SELECT TOP	La expresión es de tipo entero o float.	SELECT TOP 4 * FROM t1 SELECT TOP 1.5 PERCENT * FROM t1
Instrucción UPDATE	La expresión y la columna tienen tipos compatibles	UPDATE t1 SET c1 = 100
Predicado UPDATE	El predicado es booleano	UPDATE t1 SET c1 = 100 WHERE c1 > 100
Expresión UPDATE TOP	La expresión es de tipo entero o float.	UPDATE TOP 4 table1
Predicado DELETE	El predicado es booleano	DELETE t1 WHERE c1 > 10
Expresión DELETE TOP	La expresión es de tipo entero o float.	DELETE TOP 2 FROM t1
Declaración de variable DECLARE	El valor inicial y el tipo de datos son compatibles	DECLARE @v INT = 10
Argumentos y tipo de valor devuelto de la instrucción EXECUTE	Parámetros y argumentos	CREATE PROCEDURE p1 (@p1 INT) AS GO EXECUTE p1 100 EXECUTE @v1 = p1 100
Instrucción RETURN	La expresión RETURN tiene un tipo de datos compatible	CREATE FUNCTION f1() RETURNS INT AS BEGIN RETURN 100 END
Condiciones de la instrucción MERGE	La condición es booleana	MERGE t1 USING t2 ON t1.c1 = t2.c2 WHEN t1.c1 > 10 THEN DELETE

Cómo corregir infracciones

Puede evitar y resolver estos problemas asignando tipos de datos de forma coherente y convirtiendo explícitamente los tipos ahí donde sea necesario. Para obtener más información sobre cómo convertir explícitamente tipos de datos, consulte esta página en el sitio web de Microsoft: CAST y CONVERT (Transact-SQL).

Ejemplo

En este ejemplo se muestran dos procedimientos almacenados que insertan datos en una tabla. El primer procedimiento, procWithWarning, provocará una conversión implícita de un tipo de datos. El segundo procedimiento, procFixed, muestra cómo agregar una conversión explícita para maximizar el rendimiento y conservar todos los datos.

CREATE TABLE [dbo].[Table2]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL,
[c2] INT NOT NULL,
[c3] BIGINT NOT NULL,
[Comment] VARCHAR (25)
)
ON [PRIMARY]

CREATE PROCEDURE [dbo].[procWithWarning]
(
@Value1 INT,
@Value2 INT,
@Value3 BIGINT,
@Comment CHAR(30)
)
AS
BEGIN
INSERT INTO [Table2] ([c1], [c2], [c3], Comment)
VALUES (@Value1, @Value2, @Value3, @Comment)

END

CREATE PROCEDURE [dbo].[procFixed]
(
@Value1 INT,
@Value2 INT,
@Value3 BIGINT,
@Comment CHAR(10)
)
AS
BEGIN
INSERT INTO [Table2] ([c1], [c2], [c3], Comment)
VALUES (@Value1, @Value2, @Value3, CAST(@Comment AS VARCHAR(25)))

END

Contenido relacionado

• Análisis de código SQL para mejorar la calidad del código
• Problemas de nomenclatura de T-SQL
• Problemas de rendimiento de T-SQL
• Análisis del código T-SQL para buscar defectos