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

The topic describes the SA0134 analysis rule.

Message

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

Description

The rule checks stored procedures, triggers and functions for having a DDL statements placed between DML statements.

If DDL operations are performed within a procedure or batch, the procedure or batch is recompiled when it encounters the first subsequent DML operation affecting the table involved in the DDL.

How to fix

Rearrange the statements and group DDL statements at the beginning of procedures followed by DML statements.

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