What's the purpose of "0=1" clause in the Update Command?

  • Thread starter Thread starter pedestrian
  • Start date Start date
P

pedestrian

I use SqlCommandBuilder to auto-generate update commands (INSERT, UPDATE,
DELETE) before calling data adapter's Update method.

I view the Sql statement using SQL Server Profiler tool.
The following is the update statement:

exec sp_executesql N'UPDATE [tblEmployee]
SET [FirstName] = @p1
WHERE (([ID] = @p2)
AND ([FirstName] = @p3)
AND ([LastName] = @p4)
AND ((@p5 = 1 AND [DepartmentID] IS NULL) OR ([DepartmentID] = @p6))
AND ((@p7 = 1 AND [Salary] IS NULL) OR ([Salary] = @p8)))',
N'@p1 varchar(5),@p2 int,@p3 varchar(5),@p4 varchar(7),@p5 int,@p6 int,@p7
int,@p8 money', @p1='Karen',@p2=19,@p3='Katie',@p4='Chideya',@p5=0,@p6=4,
@p7=0,@p8=$76000.0000

Refer to the above statement, it seems that every column that allow NULL (in
SQL Server table) always come with @p# = 1 (where @p# always equals 0),
what's the purpose of it? So what's the different between:

1. ((@p5 = 1 AND [DepartmentID] IS NULL) OR ([DepartmentID] = @p6))
2. (([DepartmentID] IS NULL) OR ([DepartmentID] = @p6))

Isn't the statement be the same without that (@p# = 1)?

I appreciate your assistance...
 
pedestrian,

It is strictly a performance enhancement feature.

Parameter values can be replaced with 0 or 1 for null checking and therefore
less data needs to be sent over the wire to the database server.

See SourceColumnNullMapping in MSDN.

Kerry Moorman
 
Back
Top