Click or drag to resize

SA0076 : Check UPDATE and DELETE statements for not filtering using all columns of the table's PRIMARY KEY or UNIQE KEY

The topic describes the SA0076 analysis rule.

Message

Check UPDATE and DELETE statements for not filtering using all columns of the table's PRIMARY KEY or UNIQE KEY

Description

The rule tries to prevent deletion or updating of more rows than desired.

It checks UPDATE and DELETE statements for not using all the columns of the target table's PRIMARY KEY or UNIQE KEY in the statement's filtering clauses.

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:SA0076

ConsiderPrimaryKeysOnly

The parameter specifies whether the unique keys are also considered or not.

no

RequireKeyWithClusteredIndex

The parameter makes the rule to require the unique or primary key to have clustered index.

no

SkipStatementsNotHavingWhereClause

The parameter can be used to force the rule to ignore UPDATE and DELETE statements which do not have WHERE clause. This way you can avoid duplicating the the results from rules SA0035 and SA0036.

yes

ConsiderJoinedTableUniqueKey

The parameter makes the rule to check if any of the joined table for having all unique columns referenced and ignore the statement if is such table source is matched.

yes

CheckWhereClauseConditions

The parameter makes the rule to check filtering conditions in the WHERE clause.

yes

CheckJoinOnClauseConditions

The parameter makes the rule to check filtering conditions in the JOIN's ON clauses. Outer joined tables are always ignored.

no

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 1UPDATE edh SET  ModifiedDate = getdate(  )
 2FROM  HumanResources.EmployeeDepartmentHistory AS edh
 3WHERE  ShiftID = 5 AND
 4       StartDate IN( '2010-05-22', '2010-05-22' ) AND
 5       DepartmentID LIKE '43 '
 6
 7UPDATE t  SET  ModifiedDate = getdate(  )
 8FROM  #TempTable t
 9
10UPDATE HumanResources.EmployeeDepartmentHistory  SET  ModifiedDate = getdate(  )
11FROM  HumanResources.EmployeeDepartmentHistory 
12
13UPDATE Person.Address SET AddressLine1 = 'Address Line' WHERE AddressLine1 = 'Address 1'
14
15UPDATE Person.Address SET AddressLine1 = 'Address Line' WHERE AddressLine1 = 'Address 1' AND AddressLine2 = 'Address 2'
16
17UPDATE Person.Address SET AddressLine1 = 'Address Line' 
18
19UPDATE Person.Address SET  AddressLine1 = 'Address Line'  WHERE AddressID = 5
20
21UPDATE HumanResources.Shift SET EndTime = dateadd(day,EndTime ,1) WHERE StartTime > getdate()
22
23DELETE FROM HumanResources.Shift WHERE StartTime > getdate()
24
25DELETE FROM HumanResources.NonExistingTable WHERE StartTime > getdate()
26
27UPDATE  HumanResources.EmployeeDepartmentHistory 
28SET  ModifiedDate = getdate(  )
29FROM  HumanResources.EmployeeDepartmentHistory ed
30LEFT OUTER JOIN HumanResources.Department AS d
31ON  d.DepartmentID =  ed.DepartmentID
32LEFT OUTER JOIN HumanResources.Employee AS e
33ON  e.BusinessEntityID =  ed.BusinessEntityID
34WHERE  ShiftID = 5 AND  
35       ed.StartDate IN( '2010-05-22', '2010-05-22' ) AND 
36       d.DepartmentID LIKE '43 '

Analysis Results

 MessageLineColumn
1SA0076 : The UPDATE statement may affect more than the expected rows in table [HumanResources].[EmployeeDepartmentHistory].The Primary Key [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID] columns ([BusinessEntityID]) are not referenced in the statement’s filter.10
2SA0076 : The UPDATE statement may affect more than the expected rows in table [Person].[Address].The Unique Key [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] columns ([AddressLine2],[City],[StateProvinceID],[PostalCode]) are not referenced in the statement’s filter.130
3SA0076 : The UPDATE statement may affect more than the expected rows in table [Person].[Address].The Unique Key [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] columns ([City],[StateProvinceID],[PostalCode]) are not referenced in the statement’s filter.150
4SA0076 : The UPDATE statement may affect more than the expected rows in table [HumanResources].[Shift].The Unique Key [AK_Shift_StartTime_EndTime] columns ([EndTime]) are not referenced in the statement’s filter.210
5SA0076 : The DELETE statement may affect more than the expected rows in table [HumanResources].[Shift].The Unique Key [AK_Shift_StartTime_EndTime] columns ([EndTime]) are not referenced in the statement’s filter.230
6SA0076 : The UPDATE statement may affect more than the expected rows in table [HumanResources].[EmployeeDepartmentHistory].The Primary Key [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID] columns ([BusinessEntityID],[DepartmentID]) are not referenced in the statement’s filter.270
See Also

Other Resources