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

Introduction

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:

-- Example of problematic query with intermixed DDL and DML
CREATE PROCEDURE UpdateAndAlter
AS
BEGIN
    UPDATE Employees SET Salary = Salary * 1.05 WHERE JobTitle = 'Manager';
    ALTER TABLE Employees ADD NewColumn INT;
    SELECT * FROM Employees;
END;

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:

-- Example of corrected query with DDL before DML
CREATE PROCEDURE UpdateAndAlter
AS
BEGIN
    ALTER TABLE Employees ADD NewColumn INT;
    UPDATE Employees SET Salary = Salary * 1.05 WHERE JobTitle = 'Manager';
    SELECT * FROM Employees;
END;

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.

Additional Information

Troubleshooting stored procedure recompilation

Example Test SQL

CREATE PROCEDURE proc_SQLEnlight_Test_SA0134
AS
-- Then DML
create table dbo.t1 (a int)
-- create index idx_t1 on t1(a)
-- create table dbo.t2 (a int)

select * from dbo.t1

select * from dbo.t1 WHERE a = 1 -- 1

select * from dbo.t1 WHERE a = 1 -- 2

create index idx_t1 on t1(a); -- IGNORE:SA0134

select * from dbo.t1 WHERE a = 2 -- 1

select * from dbo.t1 WHERE a = 2 -- 2

create table dbo.t2 (a int)

select * from dbo.t2  -- 1

select * from dbo.t2 -- 2

--DROP INDEX idx_t1 ON dbo.t1

select * from dbo.t1 WHERE a = 2 -- 3

Analysis Results

	Message	Line	Column
1	SA0134 : The DDL statement appears after a DML statement.	20	0

Analysis Rules