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) 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) Script error ... I´ve done one script where i verify the sql server version ... but if i comment the query the script runs without any ... (microsoft.public.sqlserver.server) 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)

• Windows
• Science
• Usenet

• Archive
• About
• Privacy
• Search
• Imprint