Problemen met T-SQL-ontwerp

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Wanneer u de T-SQL-code in uw databaseproject analyseert, kunnen een of meer waarschuwingen worden gecategoriseerd als ontwerpproblemen. Los ontwerpproblemen op om de volgende situaties te voorkomen:

• Volgende wijzigingen in uw database kunnen toepassingen verbreken die hiervan afhankelijk zijn.
• De code produceert mogelijk niet het verwachte resultaat.
• De code kan worden verbroken als u deze uitvoert met toekomstige releases van SQL Server.

Over het algemeen moet u een ontwerpprobleem niet onderdrukken, omdat dit uw toepassing inmiddels of in de toekomst mogelijk onderbreekt.

De opgegeven regels identificeren de volgende ontwerpproblemen:

• SR0001: Vermijd SELECT * in opgeslagen procedures, weergaven en tabelwaardefuncties
• SR0008: Overweeg SCOPE_IDENTITY te gebruiken in plaats van @@IDENTITY
• SR0009: Vermijd het gebruik van typen variabele lengten met een grootte van 1 of 2
• SR0010: Vermijd het gebruik van afgeschafte syntaxis wanneer u tabellen of weergaven samenvoegt
• SR0013: Uitvoerparameter (parameter) wordt niet ingevuld in alle codepaden
• SR0014: Gegevensverlies kan optreden bij het casten van {Type1} naar {Type2}

SR0001: Vermijd SELECT * in opgeslagen procedures, weergaven en tabelwaardefuncties

Als u een jokerteken gebruikt in een opgeslagen procedure, weergave of tabelwaardefunctie om alle kolommen in een tabel of weergave te selecteren, kan het aantal of de vorm van geretourneerde kolommen veranderen als de onderliggende tabel of weergave verandert. De vorm van een kolom is een combinatie van het type en de grootte. Deze variantie kan problemen veroorzaken in toepassingen die de opgeslagen procedure, weergave of tabelwaardefunctie gebruiken, omdat deze gebruikers een ander aantal kolommen verwachten.

Hoe schendingen op te lossen

U kunt consumenten van de opgeslagen procedure, weergave of tabelwaardefunctie beschermen tegen schemawijzigingen door het jokerteken te vervangen door een volledig gekwalificeerde lijst met kolomnamen.

Example

In het volgende voorbeeld wordt eerst een tabel met de naam [Table2] gedefinieerd en worden vervolgens twee opgeslagen procedures gedefinieerd. De eerste procedure bevat een SELECT *, die de regel SR0001 schendt. De tweede procedure vermijdt SELECT * en vermeldt expliciet de kolommen in de SELECT-instructie.

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: Overweeg SCOPE_IDENTITY te gebruiken in plaats van @@IDENTITY

Omdat @@IDENTITY een globale identiteitswaarde is, is deze mogelijk bijgewerkt buiten het huidige bereik en is er een onverwachte waarde verkregen. Triggers, waaronder geneste triggers die worden gebruikt voor replicatie, kunnen @@IDENTITY buiten uw huidige bereik bijwerken.

Hoe schendingen op te lossen

Als u dit probleem wilt oplossen, moet u verwijzingen naar @@IDENTITY vervangen door SCOPE_IDENTITY, die de meest recente identiteitswaarde in het bereik van de gebruikersinstructie retourneert.

Example

In het eerste voorbeeld wordt @@IDENTITY gebruikt in een opgeslagen procedure waarmee gegevens in een tabel worden ingevoegd. De tabel wordt vervolgens gepubliceerd voor samenvoegreplicatie, waarmee triggers worden toegevoegd aan tabellen die worden gepubliceerd. Daarom kan @@IDENTITY de waarde van de invoegbewerking in een replicatiesysteemtabel retourneren in plaats van de invoegbewerking in een gebruikerstabel.

De Sales.Customer tabel heeft een maximale identiteitswaarde van 29483. Als u een rij in de tabel invoegt, retourneert @@IDENTITY en SCOPE_IDENTITY() verschillende waarden. SCOPE_IDENTITY() retourneert de waarde van de invoegbewerking in de gebruikerstabel, maar @@IDENTITY retourneert de waarde van de invoegbewerking in de tabel van het replicatiesysteem.

In het tweede voorbeeld ziet u hoe u SCOPE_IDENTITY() kunt gebruiken om toegang te krijgen tot de ingevoegde identiteitswaarde en de waarschuwing op te lossen.

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: Vermijd het gebruik van typen variabele lengten die grootte 1 of 2 hebben

Wanneer u gegevenstypen van variabele lengte zoals VARCHAR, NVARCHAR en VARBINARY gebruikt, worden extra opslagkosten in rekening gebracht om de lengte van de waarde bij te houden die is opgeslagen in het gegevenstype. Daarnaast worden kolommen met variabele lengte opgeslagen na alle kolommen met een vaste lengte, wat gevolgen kan hebben voor de prestaties. U ontvangt ook een waarschuwing als u een type variabelelengte declareert, zoals VARCHAR, maar u geeft geen lengte op. Deze waarschuwing treedt op omdat, indien niet opgegeven, de standaardlengte 1 is.

Hoe schendingen op te lossen

Als de lengte van het type zeer klein is (grootte 1 of 2) en consistent is, declareert u deze als een type vaste lengte, zoals CHAR, NCHAR en BINARY.

Example

In dit voorbeeld ziet u definities voor twee tabellen. De eerste tabel declareert een tekenreeks met variabele lengte 2. De tweede tabel declareert in plaats daarvan een tekenreeks met vaste lengte, waardoor de waarschuwing wordt vermeden.

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]

Gegevens voor typen variabele lengte worden fysiek opgeslagen na gegevens voor typen vaste lengte. Daarom veroorzaakt u gegevensverplaatsing als u een kolom wijzigt van variabele in vaste lengte in een tabel die niet leeg is.

SR0010: Vermijd het gebruik van afgeschafte syntaxis wanneer u tabellen of weergaven samenvoegt

Joins die gebruikmaken van de afgeschafte syntaxis, vallen in twee categorieën:

• Inner Join: Voor een inner join worden de waarden in de kolommen die worden samengevoegd vergeleken met behulp van een vergelijkingsoperator zoals =, <, >=, enzovoort. Inner joins retourneren alleen rijen als ten minste één rij uit elke tabel overeenkomt met de joinvoorwaarde.
• Outer Join: Outer joins retourneren alle rijen uit ten minste één van de tabellen of weergaven die zijn opgegeven in de FROM-component, zolang die rijen voldoen aan een WHERE- of HAVING-zoekvoorwaarde. Als u = of = gebruikt om een outer join op te geven, gebruikt u afgeschafte syntaxis.

Hoe schendingen op te lossen

Gebruik de INNER JOIN syntaxis om een schending in een inner join op te lossen.

Gebruik de juiste OUTER JOIN syntaxis om een schending in een outer join op te lossen. U hebt de volgende opties:

• LEFT OUTER JOIN of LEFT JOIN
• RIGHT OUTER JOIN of RIGHT JOIN

Voorbeelden van de afgeschafte syntaxis en de bijgewerkte syntaxis worden gegeven in de volgende voorbeelden. Meer informatie over joins vindt u in Joins.

Examples

In de zes voorbeelden ziet u de volgende opties:

1. Voorbeeld 1 toont de afgeschafte syntaxis voor een inner join.
2. Voorbeeld 2 laat zien hoe u voorbeeld 1 kunt bijwerken om de huidige syntaxis te gebruiken.
3. Voorbeeld 3 toont de verouderde syntaxis voor een left outer join.
4. Voorbeeld 4 laat zien hoe u voorbeeld 2 kunt bijwerken om de huidige syntaxis te gebruiken.
5. In voorbeeld 5 wordt de verouderde syntaxis voor een right outer join uitgelegd.
6. Voorbeeld 6 laat zien hoe u voorbeeld 5 kunt bijwerken om de huidige syntaxis te gebruiken.

-- 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: Uitvoerparameter (parameter) wordt niet ingevuld in alle codepaden

Deze regel identificeert code waarin de uitvoerparameter niet is ingesteld op een waarde in een of meer codepaden via de opgeslagen procedure of functie. Deze regel identificeert niet in welke paden de uitvoerparameter moet worden ingesteld. Als dit probleem optreedt bij meerdere uitvoerparameters, wordt er één waarschuwing weergegeven voor elke parameter.

Hoe schendingen op te lossen

U kunt dit probleem op twee manieren oplossen. U kunt dit probleem het eenvoudigst oplossen als u de uitvoerparameters initialiseert naar een standaardwaarde aan het begin van de hoofdtekst van de procedure. Als alternatief kunt u de uitvoerparameter ook instellen op een waarde in de specifieke codepaden waarin de parameter niet is ingesteld. U kunt echter een ongebruikelijk codepad in een complexe procedure over het hoofd zien.

Important

Het opgeven van een waarde in de proceduredeclaratie, zoals CREATE PROC MyProcedure (@param1 INT = 10 OUTPUT) het probleem wordt niet opgelost. U moet een waarde toewijzen aan de uitvoerparameter in de hoofdtekst van de procedure.

Example

In het volgende voorbeeld ziet u twee eenvoudige procedures. Met de eerste procedure wordt de waarde van de uitvoerparameter niet ingesteld. @Sum Met de tweede procedure wordt de @Sum parameter aan het begin van de procedure geïnitialiseerd, waardoor de waarde in alle codepaden wordt ingesteld.

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: Gegevensverlies kan optreden bij het casten van {Type1} naar {Type2}

Als gegevenstypen inconsistent zijn toegewezen aan kolommen, variabelen of parameters, worden ze impliciet geconverteerd wanneer de Transact-SQL code die deze objecten bevat, wordt uitgevoerd. Dit type conversie vermindert niet alleen de prestaties, maar veroorzaakt in sommige gevallen ook subtiel verlies van gegevens. Een tabelscan kan bijvoorbeeld worden uitgevoerd als elke kolom in een WHERE-component moet worden geconverteerd. Nog erger, gegevens gaan verloren als een Unicode-tekenreeks wordt geconverteerd naar een ASCII-tekenreeks die gebruikmaakt van een andere codepagina.

Deze regel doet het volgende NIET:

• Controleer het type van een berekende kolom omdat het type pas in runtime bekend is.
• Analyseer alles binnen een CASE-instructie. Ook wordt de retourwaarde van een CASE-instructie niet geanalyseerd.
• Analyseer de invoerparameters of retourwaarde van een aanroep naar ISNULL

Deze tabel bevat een overzicht van de controles die worden gedekt door de regel SR0014:

Taalconstructie	Wat is aangevinkt	Example
Standaardwaarde van parameters	Parametergegevenstype	CREATE PROCEDURE p1(@p1 INT = 1) AS BEGIN END
CREATE INDEX-predicaat	Predicaat is Booleaanse waarde	CREATE INDEX index1 ON table1 (column1) WHERE column1 > 10
Argumenten van functies LEFT of RIGHT	Tekenreeksargumenttype en lengte	SET @v = LEFT('abc', 2)
Argumenten van CAST- en CONVERT-functies	Expressie en typen zijn geldig	SET @v = CAST('abc' AS CHAR(10))
SET-instructie	Linkerzijde en rechterzijde hebben compatibele typen	SET @v1 = 'xyz' SELECT @v1 = c1 FROM t1
ALS-voorwaarde	Predicaat is Booleaanse waarde	IF (@v > 10)
WHILE-instructievoorwaarde	Predicaat is Booleaanse waarde	WHILE (@v > 10)
INSERT-instructie	Waarden en kolommen zijn juist	INSERT INTO t1(c1, c2) VALUES (99, 'xyz') INSERT INTO t1 SELECT c1 FROM t2.
SELECT WHERE predicaat	Predicaat is Booleaanse waarde	SELECT * FROM t1 WHERE c1 > 10
SELECT TOP-expressie	Expressie is een geheel getal of floattype	SELECT TOP 4 * FROM t1 SELECT TOP 1.5 PERCENT * FROM t1
UPDATE-instructie	Expressie en kolom hebben compatibele typen	UPDATE t1 SET c1 = 100
Update-predicaat	Predicaat is Booleaanse waarde	UPDATE t1 SET c1 = 100 WHERE c1 > 100
TOP-expressie BIJWERKEN	Expressie is een geheel getal of floattype	UPDATE TOP 4 table1
PREDICAAT VERWIJDEREN	Predicaat is Booleaanse waarde	DELETE t1 WHERE c1 > 10
VERWIJDER TOP-expressie	Expressie is een geheel getal of floattype	DELETE TOP 2 FROM t1
DECLARE variabele declaratie	Initiële waarde en gegevenstype zijn compatibel	DECLARE @v INT = 10
Argumenten en retourtype van de EXECUTE-instructie	Parameters en argumenten	CREATE PROCEDURE p1 (@p1 INT) AS GO EXECUTE p1 100 EXECUTE @v1 = p1 100
RETURN-instructie	RETURN-expressie heeft een compatibel gegevenstype	CREATE FUNCTION f1() RETURNS INT AS BEGIN RETURN 100 END
VOORWAARDEN VOOR MERGE-instructie	Conditie is een Booleaanse waarde	MERGE t1 USING t2 ON t1.c1 = t2.c2 WHEN t1.c1 > 10 THEN DELETE

Hoe schendingen op te lossen

U kunt deze problemen voorkomen en oplossen door gegevenstypen consistent toe te wijzen en door expliciet typen te converteren waar ze nodig zijn. Zie deze pagina op de Website van Microsoft: CAST en CONVERT (Transact-SQL) voor meer informatie over het expliciet converteren van gegevenstypen.

Example

In dit voorbeeld ziet u twee opgeslagen procedures waarmee gegevens in een tabel worden ingevoegd. De eerste procedure, procWithWarning, veroorzaakt een impliciete conversie van een gegevenstype. De tweede procedure, procFixed, laat zien hoe u een expliciete conversie kunt toevoegen om de prestaties te maximaliseren en alle gegevens te behouden.

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

Verwante inhoud

• SQL-codeanalyse om de kwaliteit van code te verbeteren
• T-SQL-naamgevingsproblemen
• prestatieproblemen met T-SQL
• T-SQL-code analyseren om defecten te vinden