Problemi di progettazione T-SQL

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure database SQL in Microsoft Fabric

Quando si analizza il codice T-SQL nel progetto di database, uno o più avvisi potrebbero essere classificati come problemi di progettazione. È consigliabile risolvere i problemi di progettazione per evitare le situazioni seguenti:

• Le modifiche successive al database potrebbero interrompere le applicazioni che dipendono da esso.
• Il codice potrebbe non produrre il risultato previsto.
• Il codice potrebbe interrompersi se viene eseguito con le versioni future di SQL Server.

In generale, non è consigliabile eliminare un problema di progettazione perché potrebbe interrompere l'applicazione, ora o in futuro.

Le regole fornite identificano i problemi di progettazione seguenti:

• SR0001: evitare SELECT * in stored procedure, viste e funzioni con valori di tabella
• SR0008: prendere in considerazione l'uso di SCOPE_IDENTITY anziché di @@IDENTITY
• SR0009: evitare di usare tipi di lunghezza variabile con dimensioni 1 o 2
• SR0010: evitare di usare la sintassi deprecata quando si unisce tabelle o viste
• SR0013: il parametro di output (parametro) non viene popolato in tutti i percorsi di codice
• SR0014: la perdita di dati può verificarsi durante il cast da {Type1} a {Type2}

SR0001: evitare SELECT * in stored procedure, viste e funzioni con valori di tabella

Se si utilizza un carattere jolly in una stored procedure, una vista o una funzione con valori di tabella per selezionare tutte le colonne in una tabella o vista, il numero o la forma delle colonne restituite può cambiare se la tabella o la vista sottostante cambia. La forma di una colonna è una combinazione di tipo e dimensione. Questa varianza può causare problemi nelle applicazioni che utilizzano la stored procedure, la vista o la funzione con valori di tabella perché tali consumer prevedono un numero diverso di colonne.

Come correggere le violazioni

È possibile proteggere i consumer della stored procedure, della vista o della funzione con valori di tabella dalle modifiche dello schema sostituendo il carattere jolly con un elenco completo di nomi di colonna.

Esempio

Nell'esempio seguente viene prima definita una tabella denominata [Table2] e quindi vengono definite due stored procedure. La prima procedura contiene un SELECT *, che viola la regola SR0001. La seconda procedura evita SELECT * ed elenca in modo esplicito le colonne nell'istruzione 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: prendere in considerazione l'uso di SCOPE_IDENTITY anziché di @@IDENTITY

Poiché @@IDENTITY è un valore di identità globale, potrebbe essere stato aggiornato all'esterno dell'ambito corrente e ottenuto un valore imprevisto. I trigger, inclusi i trigger annidati usati dalla replica, possono aggiornare @@IDENTITY all'esterno dell'ambito corrente.

Come correggere le violazioni

Per risolvere questo problema, è necessario sostituire i riferimenti a @@IDENTITY con SCOPE_IDENTITY, che restituisce il valore identity più recente nell'ambito dell'istruzione utente.

Esempio

Nel primo esempio, @@IDENTITY viene usato in una stored procedure che inserisce i dati in una tabella. La tabella viene quindi pubblicata per la replica di tipo merge, che aggiunge trigger alle tabelle pubblicate. @@IDENTITY può pertanto restituire il valore dell'inserimento dell’operazione in una tabella del sistema di replica anziché dell'inserimento dell’operazione in una tabella utente.

Il valore Identity massimo consentito per la tabella Sales.Customer è 29483. Se si inserisce una riga nella tabella, vengono restituiti valori diversi da @@IDENTITY e SCOPE_IDENTITY(). SCOPE_IDENTITY() restituisce il valore dell'inserimento dell’operazione nella tabella utente, mentre @@IDENTITY restituisce il valore dell'inserimento dell’operazione nella tabella del sistema di replica.

Il secondo esempio mostra come usare SCOPE_IDENTITY() per accedere al valore identity inserito e risolvere l'avviso.

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: evitare di usare tipi di lunghezza variabile con dimensioni 1 o 2

Quando si usano tipi di dati di lunghezza variabile, ad esempio VARCHAR, NVARCHAR e VARBINARY, si deve sostenere un costo di archiviazione aggiuntivo per tenere traccia della lunghezza del valore archiviato nel tipo di dati. Inoltre, le colonne di lunghezza variabile vengono archiviate dopo tutte le colonne di lunghezza fissa, che possono avere implicazioni sulle prestazioni. Si riceverà anche un avviso se si dichiara un tipo di lunghezza variabile, ad esempio VARCHAR, ma non si specifica alcuna lunghezza. Questo avviso si verifica perché, se non specificato, la lunghezza predefinita è 1.

Come correggere le violazioni

Se la lunghezza del tipo sarà molto piccola (dimensione 1 o 2) e coerente, dichiararli come tipi di lunghezza fissa, ad esempio CHAR, NCHAR e BINARY.

Esempio

Questo esempio mostra le definizioni per due tabelle. La prima tabella dichiara una stringa di lunghezza variabile per avere lunghezza 2. La seconda tabella dichiara invece una stringa di lunghezza fissa, che evita l'avviso.

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]

I dati per i tipi di lunghezza variabile vengono archiviati fisicamente dopo i dati per i tipi di lunghezza fissa. Pertanto, si causerà lo spostamento dei dati se si modifica una colonna da variabile a lunghezza fissa in una tabella che non è vuota.

SR0010: evitare di usare la sintassi deprecata quando si unisce tabelle o viste

I join che usano la sintassi deprecata rientrano in due categorie:

• Inner Join: per un inner join, i valori nelle colonne di cui viene eseguito il join vengono confrontati usando un operatore di confronto, ad esempio =, <, >= e così via. Gli inner join restituiscono righe solo se almeno una riga di ogni tabella corrisponde alla condizione di join.
• Outer Join: gli outer join restituiscono tutte le righe di almeno una delle tabelle o viste specificate nella clausola FROM, a condizione che tali righe soddisfino una delle condizioni di ricerca della clausola WHERE o HAVING. Se si usa = o = per specificare un outer join, si usa la sintassi deprecata.

Come correggere le violazioni

Per correggere una violazione in un inner join, usare la sintassi INNER JOIN.

Per correggere una violazione in un outer join, usare la sintassi appropriata OUTER JOIN. Sono disponibili le seguenti opzioni:

• LEFT OUTER JOIN o LEFT JOIN
• RIGHT OUTER JOIN o RIGHT JOIN

Esempi della sintassi deprecata e della sintassi aggiornata sono disponibili negli esempi seguenti. Ulteriori informazioni sui join sono disponibili su Join.

Esempi

I sei esempi illustrano le opzioni seguenti:

1. Nell'esempio 1 viene illustrata la sintassi deprecata per un inner join.
2. Nell'esempio 2 viene illustrato come aggiornare l'esempio 1 per usare la sintassi corrente.
3. Nell'esempio 3 viene illustrata la sintassi deprecata per un left outer join.
4. Nell'esempio 4 viene illustrato come aggiornare l'esempio 2 per usare la sintassi corrente.
5. Nell'esempio 5 viene illustrata la sintassi deprecata per un right outer join.
6. Nell'esempio 6 viene illustrato come aggiornare l'esempio 5 per usare la sintassi corrente.

-- 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: il parametro di output (parametro) non viene popolato in tutti i percorsi di codice

Questa regola identifica il codice in cui il parametro di output non è impostato su un valore in uno o più percorsi di codice tramite la stored procedure o la funzione. Questa regola non identifica i percorsi in cui deve essere impostato il parametro di output. Se più parametri di output presentano questo problema, viene visualizzato un avviso per ogni parametro.

Come correggere le violazioni

È possibile risolvere questo problema in due modi. È possibile risolvere questo problema più facilmente se si inizializzano i parametri di output su un valore predefinito all'inizio del corpo della procedura. In alternativa, è anche possibile impostare il parametro di output su un valore nei percorsi di codice specifici in cui il parametro non è impostato. Tuttavia, è possibile ignorare un percorso di codice non comune in una procedura complessa.

Importante

Se si specifica un valore all'interno della dichiarazione di procedura, ad esempio CREATE PROC MyProcedure (@param1 INT = 10 OUTPUT) non verrà risolto il problema. È necessario assegnare un valore al parametro di output all'interno del corpo della routine.

Esempio

Nell'esempio seguente vengono illustrate due semplici procedure. La prima procedura non imposta il valore del parametro di output, @Sum. La seconda procedura inizializza il parametro @Sum all'inizio della routine, assicurando che il valore venga impostato in tutti i percorsi di codice.

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: la perdita di dati può verificarsi durante il cast da {Type1} a {Type2}

Se i tipi di dati vengono assegnati in modo incoerente a colonne, variabili o parametri, vengono convertiti in modo implicito quando viene eseguito il codice Transact-SQL che contiene tali oggetti. Questo tipo di conversione non solo riduce le prestazioni, ma anche, in alcuni casi, causa una perdita sottile di dati. Ad esempio, un'analisi di tabella potrebbe essere eseguita se ogni colonna in una clausola WHERE deve essere convertita. Peggio, i dati potrebbero andare persi se una stringa Unicode viene convertita in una stringa ASCII che usa una tabella codici diversa.

Questa regola NON:

• Controlla il tipo di una colonna calcolata perché il tipo non è noto fino al runtime.
• Analizza qualsiasi elemento all'interno di un'istruzione CASE. Inoltre, non analizza il valore di ritorno di un'istruzione CASE.
• Analizza i parametri di input o il valore restituito di una chiamata a ISNULL

Questa tabella riepiloga i controlli coperti dalla regola SR0014:

Costrutto di linguaggio	Che cos'è Checked	Esempio
Valore predefinito dei parametri	Tipo di dati del parametro	CREATE PROCEDURE p1(@p1 INT = 1) AS BEGIN END
Predicato CREATE INDEX	Il predicato è booleano	CREATE INDEX index1 ON table1 (column1) WHERE column1 > 10
Argomenti delle funzioni LEFT o RIGHT	Tipo di argomento stringa e lunghezza	SET @v = LEFT('abc', 2)
Argomenti delle funzioni CAST e CONVERT	Espressione e tipi validi	SET @v = CAST('abc' AS CHAR(10))
Istruzione SET	Lato sinistro e lato destro hanno tipi compatibili	SET @v1 = 'xyz' SELECT @v1 = c1 FROM t1
Predicato dell'istruzione IF	Il predicato è booleano	IF (@v > 10)
Predicato dell'istruzione WHILE	Il predicato è booleano	WHILE (@v > 10)
Istruzione INSERT	I valori e le colonne sono corretti	INSERT INTO t1(c1, c2) VALUES (99, 'xyz') INSERT INTO t1 SELECT c1 FROM t2.
Predicato SELECT WHERE	Il predicato è booleano	SELECT * FROM t1 WHERE c1 > 10
Espressione SELECT TOP	L’espressione è di tipo intero o varabile	SELECT TOP 4 * FROM t1 SELECT TOP 1.5 PERCENT * FROM t1
Istruzione UPDATE	Espressione e colonna hanno tipi compatibili	UPDATE t1 SET c1 = 100
Predicato UPDATE	Il predicato è booleano	UPDATE t1 SET c1 = 100 WHERE c1 > 100
Espressione UPDATE TOP	L’espressione è di tipo intero o varabile	UPDATE TOP 4 table1
DELETE PREDICATE	Il predicato è booleano	DELETE t1 WHERE c1 > 10
Espressione DELETE TOP	L’espressione è di tipo intero o varabile	DELETE TOP 2 FROM t1
Dichiarazione di variabile DECLARE	Il valore iniziale e il tipo di dati sono compatibili	DECLARE @v INT = 10
Argomenti dell'istruzione EXECUTE e tipo restituito	Parametri e argomenti	CREATE PROCEDURE p1 (@p1 INT) AS GO EXECUTE p1 100 EXECUTE @v1 = p1 100
Istruzione RETURN	L'espressione RETURN ha un tipo di dati compatibile	CREATE FUNCTION f1() RETURNS INT AS BEGIN RETURN 100 END
Condizioni dell'istruzione MERGE	La condizione è booleana	MERGE t1 USING t2 ON t1.c1 = t2.c2 WHEN t1.c1 > 10 THEN DELETE

Come correggere le violazioni

È possibile evitare e risolvere questi problemi assegnando tipi di dati in modo coerente e convertendo in modo esplicito i tipi in cui sono necessari. Per altre informazioni su come convertire in modo esplicito i tipi di dati, vedere questa pagina nel sito web Microsoft: CAST e CONVERT (Transact-SQL).

Esempio

In questo esempio vengono illustrate due stored procedure che inseriscono dati in una tabella. La prima procedura, procWithWarning, causerà una conversione implicita di un tipo di dati. La seconda procedura, procFixed, mostra come aggiungere una conversione esplicita per ottimizzare le prestazioni e conservare tutti i dati.

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

Contenuto correlato

• Analisi del codice SQL per migliorare la qualità del codice
• Problemi di denominazione T-SQL
• Problemi relativi alle prestazioni di T-SQL
• Analizzare il codice T-SQL per individuare i difetti