Re: IS NULL not working in WHERE clause.

From: Earl (Earl_at_discussions.microsoft.com)
Date: 12/17/04

Next message: Earl: "RE: IS NULL not working in WHERE clause."
Previous message: Graham Charles: "Re: stored procedure to copy a stored procedure to a different DB"
In reply to: David Portas: "Re: IS NULL not working in WHERE clause."
Next in thread: David Portas: "Re: IS NULL not working in WHERE clause."
Reply: David Portas: "Re: IS NULL not working in WHERE clause."
Messages sorted by: [ date ] [ thread ]

Date: Fri, 17 Dec 2004 14:25:02 -0800

The table contains 277,587,011 rows so it will be difficult to replicate. It
is a DW fact table to be used in building Analysis Services cubes.

I took a single 28 day fiscal period's worth of data from 2004-10-01 to
2004-10-28 and copied it to fill out the full fiscal year (13 periods for us).

I have created a new test table with just the bus_date of '2003-11-01' with
654,899
rows but the results were correct for both queries. So far the only
difference that I I can point a finger at is the number of rows in the tables.

The full-blown fact table create script is:
if exists (select * from dbo.sysobjects where id =
object_id(N'[VICORP\GilliC].[pmix_fact1]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [VICORP\GilliC].[pmix_fact1]
GO

CREATE TABLE [VICORP\GilliC].[pmix_fact1] (
        [STORE_NO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [BUS_DATE] [smalldatetime] NOT NULL ,
        [CHECK_NO] [decimal](18, 0) NULL ,
        [CHECK_SUB_NO] [decimal](18, 0) NULL ,
        [TRANS_TYPE] [decimal](3, 0) NULL ,
        [CREDIT_CARD_TYPE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [FULL_PLU] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [SHORT_PLU] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [TIME_ORDERED] [datetime] NOT NULL ,
        [ITEM_ID] [decimal](6, 0) NULL ,
        [MENU_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [MENU_VARIANT_NAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [QTY_ORDERED] [decimal](6, 0) NULL ,
        [EXTENDED_AMOUNT] [decimal](12, 2) NULL ,
        [THEO_FOOD_COST] [decimal](12, 2) NULL ,
        [DISC_NAME] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [DISC_TYPE] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [DISC_AMOUNT] [decimal](12, 2) NULL ,
        [time_id] [decimal](18, 0) NULL ,
        [CONCEPT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Parent_item_id] [decimal](6, 0) NULL ,
        [EMP_ID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [EMP_LNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [EMP_FNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [ADJ1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [ADJ2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [ADJ3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [CUST_LNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [CUST_FNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Head_Count] [decimal](18, 0) NULL ,
        [Entree] [decimal](18, 0) NULL ,
        [PARENT_PLU] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [EntreeCnt] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

CREATE INDEX [IX_pmix_fact1] ON [VICORP\GilliC].[pmix_fact1]([STORE_NO],
[EMP_LNAME]) ON [PRIMARY]
GO

CREATE INDEX [IX_pmix_fact1_1] ON
[VICORP\GilliC].[pmix_fact1]([SHORT_PLU], [CONCEPT]) ON [PRIMARY]
GO

CREATE INDEX [IX_pmix_fact1_2] ON
[VICORP\GilliC].[pmix_fact1]([MENU_VARIANT_NAME]) ON [PRIMARY]
GO

CREATE INDEX [IX_pmix_fact1_3] ON [VICORP\GilliC].[pmix_fact1]([time_id])
ON [PRIMARY]
GO

CREATE INDEX [IX_pmix_fact1_4] ON [VICORP\GilliC].[pmix_fact1]([Entree])
ON [PRIMARY]
GO

CREATE INDEX [IX_pmix_fact1_5] ON [VICORP\GilliC].[pmix_fact1]([STORE_NO],
[BUS_DATE], [CHECK_NO], [CHECK_SUB_NO], [ITEM_ID], [SHORT_PLU]) ON [PRIMARY]
GO

CREATE INDEX [IX_pmix_fact1_6] ON [VICORP\GilliC].[pmix_fact1]([BUS_DATE],
[TIME_ORDERED]) ON [PRIMARY]
GO

The table create script for my smaller one day table is:
if exists (select * from dbo.sysobjects where id =
object_id(N'dbo.[pmix_temp1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.[pmix_temp1]
GO

CREATE TABLE dbo.[pmix_temp1] (
        [BUS_DATE] [smalldatetime] NOT NULL ,
        [TIME_ORDERED] [datetime] NOT NULL ,
        [time_id] [decimal](18, 0) NULL ,
) ON [PRIMARY]
GO

CREATE INDEX [IX_pmix_temp1_3] ON dbo.[pmix_fact1]([time_id]) ON [PRIMARY]
GO

CREATE INDEX [IX_pmix_temp1_6] ON dbo.[pmix_fact1]([BUS_DATE],
[TIME_ORDERED]) ON [PRIMARY]
GO

insert into dbo.pmix_temp1
select bus_date, time_ordered, time_id
from [vicorp\gillic].pmix_fact1
where bus_date = '2003-11-01'
go

I am running SQL Server 2000 EE. SP3 has been applied.
The OS is Windows 2003 SE.

The queries with a subset of results follow.
QUERY 1: (works just fine; all rows have a null time_id.)
select bus_date, time_ordered, time_id
  from [vicorp\gillic].pmix_fact1
where bus_date = '2003-11-01'
   and time_ordered between ('2003-10-31 00:05:54') and ('2003-11-05
13:38:11')
   and time_id is null
order by time_ordered
RESULTS 1:
Bus_date Time_ordered Time_id
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL

QUERY 2: ( Not working; this query results rows where the time_id is not null)
select bus_date, time_ordered, time_id
from [vicorp\gillic].pmix_fact1
where bus_date = '2003-11-01'
and time_id is null
order by time_ordered

RESULTS 2:
Bus_date Time_ordered Time_id
2003-11-01 00:00:00 2003-11-04 04:21:18.000 7368
2003-11-01 00:00:00 2003-11-04 04:21:18.000 7368
2003-11-01 00:00:00 2003-11-04 04:21:18.000 7368
2003-11-01 00:00:00 2003-11-04 04:21:18.000 7368
2003-11-01 00:00:00 2003-11-04 04:21:18.000 7368
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:13:05.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-04 05:59:41.000 NULL
2003-11-01 00:00:00 2003-11-05 05:37:57.000 NULL
2003-11-01 00:00:00 2003-11-05 05:37:57.000 NULL
2003-11-01 00:00:00 2003-11-05 05:37:57.000 NULL
2003-11-01 00:00:00 2003-11-05 05:37:57.000 NULL
2003-11-01 00:00:00 2003-11-05 05:38:21.000 NULL
2003-11-01 00:00:00 2003-11-05 05:47:01.000 NULL
2003-11-01 00:00:00 2003-11-05 05:47:01.000 NULL
2003-11-01 00:00:00 2003-11-05 05:48:10.000 NULL
2003-11-01 00:00:00 2003-11-05 05:48:10.000 NULL
2003-11-01 00:00:00 2003-11-05 05:48:10.000 NULL
2003-11-01 00:00:00 2003-11-05 05:48:10.000 NULL
2003-11-01 00:00:00 2003-11-05 05:48:10.000 NULL
2003-11-01 00:00:00 2003-11-05 05:48:10.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:50:46.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 05:58:48.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:12:23.000 NULL
2003-11-01 00:00:00 2003-11-05 06:17:56.000 NULL
2003-11-01 00:00:00 2003-11-05 06:17:56.000 NULL
2003-11-01 00:00:00 2003-11-05 06:17:56.000 NULL
2003-11-01 00:00:00 2003-11-05 06:17:56.000 NULL
2003-11-01 00:00:00 2003-11-05 06:17:56.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:19:54.000 NULL
2003-11-01 00:00:00 2003-11-05 06:26:11.000 NULL
2003-11-01 00:00:00 2003-11-05 06:26:11.000 NULL
2003-11-01 00:00:00 2003-11-05 06:26:11.000 NULL
2003-11-01 00:00:00 2003-11-05 06:26:11.000 NULL
2003-11-01 00:00:00 2003-11-05 06:26:11.000 NULL
2003-11-01 00:00:00 2003-11-05 06:36:32.000 NULL
2003-11-01 00:00:00 2003-11-05 06:36:32.000 NULL
2003-11-01 00:00:00 2003-11-05 06:36:32.000 NULL
2003-11-01 00:00:00 2003-11-05 06:36:32.000 NULL
2003-11-01 00:00:00 2003-11-05 06:36:32.000 NULL
2003-11-01 00:00:00 2003-11-05 06:36:32.000 NULL
2003-11-01 00:00:00 2003-11-05 06:36:32.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:38:06.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:39:39.000 NULL
2003-11-01 00:00:00 2003-11-05 06:43:31.000 NULL
2003-11-01 00:00:00 2003-11-05 06:43:31.000 NULL
2003-11-01 00:00:00 2003-11-05 06:43:31.000 NULL
2003-11-01 00:00:00 2003-11-05 06:43:31.000 NULL
2003-11-01 00:00:00 2003-11-05 06:43:31.000 NULL
2003-11-01 00:00:00 2003-11-05 06:55:33.000 NULL
2003-11-01 00:00:00 2003-11-05 06:55:33.000 NULL

QUERY 3: (works against a smaller table).
select *
from pmix_temp1
where bus_date = '2003-11-01'
and time_id is null

RESULTS 3:
Bus_date Time_ordered Time_id
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL
2003-11-01 00:00:00 2003-11-04 05:07:46.000 NULL

"David Portas" wrote:

> Please post some code that will actually reproduce the problem for us:
> CREATE TABLE, INSERT INTO as well as your SELECT statements. Also tell us
> your version, edition and service pack.
>
> --
> David Portas
> SQL Server MVP
> --
>
>
>

Next message: Earl: "RE: IS NULL not working in WHERE clause."
Previous message: Graham Charles: "Re: stored procedure to copy a stored procedure to a different DB"
In reply to: David Portas: "Re: IS NULL not working in WHERE clause."
Next in thread: David Portas: "Re: IS NULL not working in WHERE clause."
Reply: David Portas: "Re: IS NULL not working in WHERE clause."
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: Technical questions
... >technical difficulties. ... >of lines allowed per query. ... What I have to do is configure the script to be ... >compatible with sql server. ...
(microsoft.public.sqlserver.mseq)
Re: Connecting to SQL Server Express 2005
... databases from SQL server 2000 to SQL server 2005, which is what I'm doing), ... and pasted it into the "Query" field in the web-based SQL management tool my ... the script against it. ... Microsoft KB (which was referenced in the very useful DataBase Journal ...
(microsoft.public.sqlserver.connect)
Technical questions
... technical difficulties. ... of lines allowed per query. ... What I have to do is configure the script to be ... compatible with sql server. ...
(microsoft.public.sqlserver.mseq)
Re: Just starting to learn it . . .
... > I can't get a script to run that's supposed to generate a Query ... the above is gibberish as far as SQL Server is concerned. ...
(comp.databases.ms-sqlserver)
Re: CONTAINS performance
... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
(microsoft.public.sqlserver.fulltext)