Indexes and NULL values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Indexes and NULL values

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-08 : 08:00:50

I've got an indexed column that always generats a SCAN for

WHERE MyColumn IS NULL

is this because tests for NULL are not able to use an index??

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-08 : 08:07:14

Try this - it should give a seek

create table #a (i int, s char(5000) not null)
insert #a select null, ''
insert #a select 1, ''
insert #a select 2, ''

create index ix on #a (i)

select * from #a where i is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-08 : 08:13:15

Yes, it does.

Maybe I'm not interpeting the plan result correctly. I'm getting a clustered index SCAN . I'm assuming a clustered index SCAN is not as desirable as a clustered index seek?

CREATE TABLE [PageHits] (
[PageHitID] [int] IDENTITY (1000000, 1) NOT NULL ,
[ClientID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[CourseID] [int] NULL ,
[ModuleID] [int] NULL ,
[UrlDate] [datetime] NOT NULL CONSTRAINT [DF__PageHits__UrlDat__73501C2F] DEFAULT (getdate()),
[NextURLDate] [datetime] NULL ,
[Url] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

UPDATE dbo.PageHits
SET NextURLDate = (select min(UrlDate) FROM dbo.PageHits P2 where P2.UrlDate > P1.URLDate and P2.UserID=P1.UserID)
FROM dbo.PageHits P1
WITH (NOLOCK)
WHERE P1.NextURLDate IS NULL

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-08 : 10:43:10

Hah - an update.

Are stats up to date?
How many nulls do you have.
What's the estimated rowcount.
What happens if you change to a select?
Is there an index on NextURLDate
How big is the table - maybe it's not worth using an index.

Clustered index scan usually means it hasn't found a good index and is the same as a table scan on a table without a clustered index.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-08 : 11:09:18

Thanks again Nigel.

Now whaddyamean "Hah - an update"

Had a bad experience with execution plans on update?

Changing it to Select made no difference. Removing the WITH (NOLOCK) made no difference. It stays SCAN until I change the where to

select *
FROM dbo.PageHits P1
WHERE P1.NextURLDate = 'jan 5 2003' -- or anything other than NULL

Now it's a seek. IS NULL and IS NOT NULL both generate SCAN. (Have I answered my own question here ? Tests for NULL don't use the INDEX??)

-- Yes there's an index on NextURLDate

-- The table is about 1,200,000 rows -- The entire stored procedure, of which this is a small part, is full of complex nested groupies

which calculate the average time users spend viewing each URL. It takes 3 minutes to run and locks out other users (even though I've specified WITH (NOLOCK)).

This proc is scheduled to run at 1AM, I've had this query bump users even at 1AM, so now, I'm trying to give the procedure a tune-up and an oil change. The sparks are dirty, but I think they can be cleaned.

To avoid the WHERE X IS NULL generating a SCAN, I guess I can modify the table such that NextURLDate defaults to 'Jan 1, 1900' instead of NULL and give it a go.

Any better ideas?

Sam

---
His thoughts tumbled in his head, making and breaking alliances like underpants in a dryer without Cling Free.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources