Designproblem med T-SQL

Gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Förhandsversion av Microsoft Fabric

När du analyserar T-SQL-koden i databasprojektet kan en eller flera varningar kategoriseras som designproblem. Du bör åtgärda designproblem för att undvika följande situationer:

• Efterföljande ändringar i databasen kan bryta program som är beroende av den.
• Koden kanske inte ger det förväntade resultatet.
• Koden kan brytas om du kör den med framtida versioner av SQL Server.

I allmänhet bör du inte förhindra ett designproblem eftersom det kan bryta ditt program, antingen nu eller i framtiden.

De angivna reglerna identifierar följande designproblem:

• SR0001: Undvik SELECT * i lagrade procedurer, vyer och tabellvärdesfunktioner
• SR0008: Överväg att använda SCOPE_IDENTITY i stället för @@IDENTITY
• SR0009: Undvik att använda typer av variabellängd som är storlek 1 eller 2
• SR0010: Undvik att använda inaktuell syntax när du kopplar tabeller eller vyer
• SR0013: Utdataparametern (parametern) fylls inte i i alla kodsökvägar
• SR0014: Dataförlust kan uppstå vid gjutning från {Type1} till {Type2}

SR0001: Undvik SELECT * i lagrade procedurer, vyer och tabellvärdesfunktioner

Om du använder ett jokertecken i en lagrad procedur, vy eller tabellvärdesfunktion för att markera alla kolumner i en tabell eller vy kan antalet eller formen på returnerade kolumner ändras om den underliggande tabellen eller vyn ändras. Formen på en kolumn är en kombination av dess typ och storlek. Den här variansen kan orsaka problem i program som använder den lagrade proceduren, vyn eller tabellvärdesfunktionen eftersom dessa konsumenter förväntar sig ett annat antal kolumner.

Så här åtgärdar du överträdelser

Du kan skydda användare av den lagrade proceduren, vyn eller tabellvärdesfunktionen från schemaändringar genom att ersätta jokertecknet med en fullständigt kvalificerad lista med kolumnnamn.

Example

I följande exempel definieras först en tabell med namnet [Table2] och sedan två lagrade procedurer. Den första proceduren innehåller en SELECT *, som bryter mot regeln SR0001. Den andra proceduren undviker SELECT * och listar uttryckligen kolumnerna i SELECT-instruktionen.

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: Överväg att använda SCOPE_IDENTITY i stället för @@IDENTITY

Eftersom @@IDENTITY är ett globalt identitetsvärde kan det ha uppdaterats utanför det aktuella omfånget och fått ett oväntat värde. Utlösare, inklusive kapslade utlösare som används av replikering, kan uppdatera @@IDENTITY utanför ditt aktuella omfång.

Så här åtgärdar du överträdelser

För att lösa det här problemet måste du ersätta referenser till @@IDENTITY med SCOPE_IDENTITY, som returnerar det senaste identitetsvärdet i användarutdragets omfång.

Example

I det första exemplet används @@IDENTITY i en lagrad procedur som infogar data i en tabell. Tabellen publiceras sedan för sammanslagningsreplikering, vilket lägger till utlösare i tabeller som publiceras. Därför kan @@IDENTITY returnera värdet från infogningsåtgärden till en replikeringssystemtabell i stället för infogningsåtgärden i en användartabell.

Tabellen Sales.Customer har ett maximalt identitetsvärde på 29483. Om du infogar en rad i tabellen returnerar @@IDENTITY och SCOPE_IDENTITY() olika värden. SCOPE_IDENTITY() returnerar värdet från infogningsåtgärden i användartabellen, men @@IDENTITY returnerar värdet från infogningsåtgärden till replikeringssystemets tabell.

Det andra exemplet visar hur du kan använda SCOPE_IDENTITY() för att komma åt det infogade identitetsvärdet och lösa varningen.

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: Undvik att använda typer av variabellängd som är storlek 1 eller 2

När du använder datatyper med variabel längd, till exempel VARCHAR, NVARCHAR och VARBINARY, medför du en extra lagringskostnad för att spåra längden på värdet som lagras i datatypen. Dessutom lagras kolumner med variabel längd efter alla kolumner med fast längd, vilket kan få prestandakonsekvenser. Du får också en varning om du deklarerar en typ av variabellängd, till exempel VARCHAR, men du anger ingen längd. Den här varningen beror på att standardlängden är 1 om den är ospecificerad.

Så här åtgärdar du överträdelser

Om längden på typen är mycket liten (storlek 1 eller 2) och konsekvent deklarerar du dem som en typ av fast längd, till exempel CHAR, NCHAR och BINARY.

Example

Det här exemplet visar definitioner för två tabeller. Den första tabellen deklarerar en sträng med variabel längd för att ha längd 2. Den andra tabellen deklarerar en sträng med fast längd i stället, vilket undviker varningen.

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]

Data för typer av variabel längd lagras fysiskt efter data för typer av fast längd. Därför orsakar du dataförflyttning om du ändrar en kolumn från variabel till fast längd i en tabell som inte är tom.

SR0010: Undvik att använda inaktuell syntax när du ansluter tabeller eller vyer

Kopplingar som använder den inaktuella syntaxen finns i två kategorier:

• Inre koppling: För en inre koppling jämförs värdena i de kolumner som kopplas med hjälp av en jämförelseoperator som =, <, >=och så vidare. Inre kopplingar returnerar endast rader om minst en rad från varje tabell matchar kopplingsvillkoret.
• Yttre koppling: Yttre kopplingar returnerar alla rader från minst en av tabellerna eller vyerna som anges i FROM-satsen, så länge raderna uppfyller ett WHERE- eller HAVING-sökvillkor. Om du använder = eller = för att ange en yttre koppling använder du inaktuell syntax.

Så här åtgärdar du överträdelser

Om du vill åtgärda en överträdelse i en inre koppling använder du syntaxen INNER JOIN .

Om du vill åtgärda en överträdelse i en yttre koppling använder du lämplig OUTER JOIN syntax. Du kan välja mellan följande alternativ:

• VÄNSTER YTTRE KOPPLING ELLER VÄNSTER KOPPLING
• HÖGER YTTRE KOPPLING ELLER HÖGERKOPPLING

Exempel på den inaktuella syntaxen och den uppdaterade syntaxen finns i följande exempel. Mer information om kopplingar finns i Kopplingar.

Examples

De sex exemplen visar följande alternativ:

1. Exempel 1 visar den inaktuella syntaxen för en inre koppling.
2. Exempel 2 visar hur du kan uppdatera exempel 1 för att använda aktuell syntax.
3. Exempel 3 visar den inaktuella syntaxen för en vänster yttre koppling.
4. Exempel 4 visar hur du kan uppdatera exempel 2 för att använda aktuell syntax.
5. Exempel 5 visar den inaktuella syntaxen för en höger yttre koppling.
6. Exempel 6 visar hur du kan uppdatera exempel 5 för att använda aktuell syntax.

-- 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: Utdataparametern (parametern) fylls inte i i alla kodsökvägar

Den här regeln identifierar kod där utdataparametern inte är inställd på ett värde i en eller flera kodsökvägar via den lagrade proceduren eller funktionen. Den här regeln identifierar inte i vilka sökvägar utdataparametern ska anges. Om flera utdataparametrar har det här problemet visas en varning för varje parameter.

Så här åtgärdar du överträdelser

Du kan åtgärda det här problemet på ett av två sätt. Du kan åtgärda det här problemet enklast om du initierar utdataparametrarna till ett standardvärde i början av procedurens brödtext. Alternativt kan du också ange utdataparametern till ett värde i de specifika kodsökvägar där parametern inte har angetts. Du kan dock förbise en ovanlig kodsökväg i en komplex procedur.

Example

I följande exempel visas två enkla procedurer. Den första proceduren anger inte värdet för utdataparametern , @Sum. Den andra proceduren initierar parametern @Sum i början av proceduren, vilket säkerställer att värdet anges i alla kodsökvägar.

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: Dataförlust kan uppstå vid gjutning från {Type1} till {Type2}

Om datatyper är inkonsekvent tilldelade till kolumner, variabler eller parametrar konverteras de implicit när den Transact-SQL kod som innehåller dessa objekt körs. Den här typen av konvertering minskar inte bara prestandan utan orsakar även i vissa fall subtil dataförlust. En tabellgenomsökning kan till exempel köras om varje kolumn i en WHERE-sats måste konverteras. Värre är att data kan gå förlorade om en Unicode-sträng konverteras till en ASCII-sträng som använder en annan kodsida.

Den här regeln gör INTE:

• Kontrollera typen av en beräknad kolumn eftersom typen inte är känd innan körtid.
• Analysera allt i ett CASE-uttryck. Den analyserar inte heller returvärdet av ett CASE-uttryck.
• Analysera indataparametrarna eller returvärdet för ett anrop till ISNULL

Den här tabellen sammanfattar de kontroller som omfattas av regeln SR0014:

CREATE PROCEDURE p1(@p1 INT = 1)
AS
BEGIN
END

CREATE INDEX index1 ON table1 (column1)
WHERE column1 > 10

SET @v = LEFT('abc', 2)

SET @v = CAST('abc' AS CHAR(10))

SET @v1 = 'xyz'
SELECT @v1 = c1 FROM t1

IF (@v > 10)

WHILE (@v > 10)

INSERT INTO t1(c1, c2) VALUES (99, 'xyz')
INSERT INTO t1 SELECT c1 FROM t2.

SELECT * FROM t1 WHERE c1 > 10

SELECT TOP 4 * FROM t1
SELECT TOP 1.5 PERCENT * FROM t1

UPDATE t1 SET c1 = 100

UPDATE t1 SET c1 = 100
WHERE c1 > 100

UPDATE TOP 4 table1

DELETE t1 WHERE c1 > 10

DELETE TOP 2 FROM t1

DECLARE @v INT = 10

CREATE PROCEDURE p1 (@p1 INT) AS
GO
EXECUTE p1 100
EXECUTE @v1 = p1 100

CREATE FUNCTION f1() RETURNS INT
AS
BEGIN
  RETURN 100
END

MERGE t1 USING t2
ON t1.c1 = t2.c2
WHEN t1.c1 > 10 THEN DELETE

Så här åtgärdar du överträdelser

Du kan undvika och lösa dessa problem genom att tilldela datatyper konsekvent och genom att explicit konvertera typer där de behövs. Mer information om hur du explicit konverterar datatyper finns på den här sidan på Microsofts webbplats: CAST och CONVERT (Transact-SQL).

Example

Det här exemplet visar två lagrade procedurer som infogar data i en tabell. Den första proceduren, procWithWarning, orsakar en implicit konvertering av en datatyp. Den andra proceduren, procFixed, visar hur du kan lägga till en explicit konvertering för att maximera prestanda och behålla alla data.

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

Related content

• SQL-kodanalys för att förbättra kodkvaliteten
• problem med T-SQL-namngivning
• T-SQL-prestandaproblem
• Analysera T-SQL-kod för att hitta fel