Click or drag to resize

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.

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0134

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, Performance Rules

Additional Information
Example Test Script
SQL
 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

 MessageLineColumn
1SA0134 : The DDL statement appears after a DML statement.200
See Also

Other Resources