SA0134 : Do not interleave DML with DDL statements. Group DDL statements at the beginning of procedures followed by DML statements

Mixing Data Definition Language (DDL) and Data Manipulation Language (DML) within stored procedures, triggers, or functions in SQL Server can lead to inefficiencies.

Description

For example:

SQL
Copy
1-- Example of problematic query with intermixed DDL and DML
2CREATE PROCEDURE UpdateAndAlter
3AS
4BEGIN
5    UPDATE Employees SET Salary = Salary * 1.05 WHERE JobTitle = 'Manager';
6    ALTER TABLE Employees ADD NewColumn INT;
7    SELECT * FROM Employees;
8END;

This approach is problematic because it can cause the procedure to be recompiled when the DML operation follows a DDL change.

  • Recompilation impacts performance, potentially slowing down the entire process.

  • Increases resource usage as SQL Server dedicates more resources for compiling rather than executing queries.

How to fix

Reorganize SQL Server procedures by placing all DDL statements before DML statements to prevent recompilation and enhance performance.

Follow these steps to address the issue:

  1. Identify the DDL and DML statements within your SQL code. DDL statements include CREATE, ALTER, DROP, and others that define or modify database schema. DML statements are used to manipulate data and include SELECT, INSERT, UPDATE, and DELETE.

  2. Rearrange the code so that all DDL statements are grouped at the beginning of the procedure, function, or trigger. This minimizes the potential for unwanted recompilation of the procedure when DML follows DDL.

  3. Verify the updated procedure for correctness and test to ensure there are no logical errors or unexpected behavior changes as a result of the reordering.

For example:

SQL
Copy
1-- Example of corrected query with DDL before DML
2CREATE PROCEDURE UpdateAndAlter
3AS
4BEGIN
5    ALTER TABLE Employees ADD NewColumn INT;
6    UPDATE Employees SET Salary = Salary * 1.05 WHERE JobTitle = 'Manager';
7    SELECT * FROM Employees;
8END;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
3 hours per issue.
Categories

Design Rules, Performance Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1CREATE PROCEDURE proc_SQLEnlight_Test_SA0134
 2AS
 3-- Then DML 
 4create table dbo.t1 (a int)
 5-- create index idx_t1 on t1(a)
 6-- create table dbo.t2 (a int)
 7
 8select * from dbo.t1
 9
10select * from dbo.t1 WHERE a = 1 -- 1
11
12select * from dbo.t1 WHERE a = 1 -- 2
13
14create index idx_t1 on t1(a); -- IGNORE:SA0134
15
16select * from dbo.t1 WHERE a = 2 -- 1
17
18select * from dbo.t1 WHERE a = 2 -- 2
19
20create table dbo.t2 (a int)
21
22select * from dbo.t2  -- 1
23
24select * from dbo.t2 -- 2
25
26--DROP INDEX idx_t1 ON dbo.t1
27
28select * from dbo.t1 WHERE a = 2 -- 3
Analysis Results
  Message Line Column
1 SA0134 : The DDL statement appears after a DML statement. 20 0
See Also

Other Resources

Analysis Rules