November 15, 2011 at 2:53 am
Before Few days i was working on some stored procedure and it did not give result as expected,i tried to simulate situation here
here is the following code which creates necessary table and data.
[Code]
USE tempdb
IF OBJECT_ID('EmployeeAllData') IS NOT NULL
DROP TABLE EmployeeAllData
IF OBJECT_ID('EmployeeSectionData') IS NOT NULL
DROP TABLE EmployeeSectionData
GO
--Table Creation
CREATE TABLE EmployeeAllData
( EmployeeID INT )
CREATE TABLE EmployeeSectionData
(
SectionID INT ,
EmployeeID INT
)
GO
--Insert Simulated EmployeeID into EmployeeAllData
SET NOCOUNT ON
DECLARE @i INT= 1
DECLARE @cnt INT = 1000
WHILE @i <= @cnt
BEGIN
INSERT INTO dbo.EmployeeAllData
( EmployeeID )
VALUES ( @i )
SET @i = @i + 1
END
GO
--Insert data into EmployeeSectionData having some employeeid is null for some section
DECLARE @i INT= 1
DECLARE @cnt INT = 1000
WHILE @i <= @cnt
BEGIN
IF @i % 5 = 0
BEGIN
INSERT INTO EmployeeSectionData
VALUES ( 1, NULL )
END
ELSE
BEGIN
INSERT INTO EmployeeSectionData
VALUES ( 1, @i )
END
SET @i = @i + 2
END
[/code]
Now let us see look at the Query that Creates Mysterious Predicates
Query 1
SELECT COUNT(DISTINCT EmployeeID)
FROM dbo.EmployeeAllData
--1000
Query2
We get 0 in result set
--We Supposed to get some count here
--(at least even number company cause ,even number was not inserted as employeeID into EmployeeSectionData but odd was inserted into EmployeeAllData)
SELECT COUNT(DISTINCT EmployeeID)
FROM dbo.EmployeeAllData
WHERE EmployeeID NOT IN ( SELECT EmployeeID
FROM dbo.EmployeeSectionData
WHERE SectionID = 1 )
Query3
SELECT COUNT(EmployeeID)
FROM dbo.EmployeeSectionData
WHERE SectionID = 1
[/Code]
Execution plan for Query 2 is attached
As we can see in marked area
Value of Predicate is "[tempdb].[dbo].[EmployeeSectionData].[EmployeeID] IS NULL AND [tempdb].[dbo].[EmployeeSectionData].[SectionID]=(1)"
now i don't understand how [tempdb].[dbo].[EmployeeSectionData].[EmployeeID] IS NULL is added in Predicate?
Configuration is also attached of tempdb.
November 15, 2011 at 3:35 am
Because you're doing a Count(<column name>). That means COUNT(*) where <column name> IS NOT NULL. Hence that has to be added to the predicates.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply