March 25, 2003 at 5:37 am
Hi, we've encounterd a odd problem.
SQL server isnt able to evaluate NULL's properly.
It has been working fine, but today it's properly gone down the pan.
If I run these querys
select count(*) from message
select count(*) from message where id is null
select count(*) from message where id is not null
select count(*) from message where isnull(id,0x0)=0x0
I get
20939148
20914216
16956682
3982466
the third and forth querys equal the first but the second isnt the same as the forth..
Does any one have any ideas?
dan
March 25, 2003 at 6:29 am
Hmm, really strange. Not that I think it is the cause of the problem, but what is ANSI_NULLS set to?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 25, 2003 at 6:31 am
HI, the box is unticked.
dan
March 25, 2003 at 6:34 am
quote:
HI, the box is unticked.
Ok, so that means that the following statement returns 3982466, right?
select count(*) from message where id = null
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 25, 2003 at 6:39 am
No, it returns 0.
The results we get back seam to be completly random.
Sometimes there similar to what has previously been requested but seam to fluxuate(sp).
dan
Edited by - dreaddan on 03/25/2003 06:45:32 AM
March 25, 2003 at 7:27 am
OK, now I'm really getting confused. Are you sure ANSI_NULLS are set off? What does these statements return? Change DATABASENAME to your database's name.
SELECT DATABASEPROPERTYEX('DATABASENAME','IsAnsiNullsEnabled')
SELECT DATABASEPROPERTYEX('DATABASENAME','IsAnsiNullDefault')
If the values are really random then something is very strange. How have you determined them to be random?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 25, 2003 at 7:37 am
Hi, both querys return 0.
We know it's random because we've run the aboth quries several times..
Unluckerly this is on a production server 🙁
dan
March 25, 2003 at 8:03 am
Very unlucky indeed. And weird...
How are you running the queries? Is it in Query Analyzer, or through some other tool? Have you tried running it with ANSI_NULLS explicitly set on and off, to see whether the results change? Just trying to pinpoint the problem, still no solution though. 🙁
SET ANSI_NULLS ON
select count(*) from message
select count(*) from message where id is null
select count(*) from message where id is not null
select count(*) from message where isnull(id,0x0)=0x0
SET ANSI_NULLS OFF
select count(*) from message
select count(*) from message where id is null
select count(*) from message where id is not null
select count(*) from message where isnull(id,0x0)=0x0
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 25, 2003 at 8:18 am
Hi, this is starting to really do our heads in , we've droped the table and recorated.
Recreated the indexes, just about everything we can think of.
Doing the qureies doent make any diffrence 🙁
I think we may need to call ms about this.
dan
March 25, 2003 at 10:24 am
Well we've found out that it's the index's that's causing the problem.
Dono why or how.. I think were going to rebuild the whole database over the weekend to try to fix the problem.
dan
March 25, 2003 at 10:36 am
Have you run DBCC CHECKTABLE? Any errors? What kind of index(es) do you have for the table?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 26, 2003 at 3:08 am
Hi, yes have run dbcc checktable, no errors reported.
We have one clustered index on a date field, and then one normal index on the id.
One of our guys created another table in the same database, imported data knone to be good via BCC (not native format). And that still not looking good.
Were currently trying another database with the same table & indexes.
dan
March 26, 2003 at 7:06 am
I thought I would just add some input from a funny problem I ran into. I accidently updated several columns as:
UPDATE tblItem
SET ItemStatus = 'NULL'
instead of
UPDATE tblItem
SET ItemStatus = NULL
and the quotes make a huge difference.
It would return 0 if I used a query like yours too.
I guess its just something to think about.
Good luck!
Andrew J. Hahn
Andrew J. Hahn
March 26, 2003 at 7:09 am
lol, luckerly it's unqueid so that wont go in..
dan
March 28, 2003 at 11:24 am
Are you running SQL 2000, service pack 3, on a multi-processor machine? If so, there is a known bug that affects count(*) queries with IS NULL in the WHERE clause. It should be fixed in SP4. I believe there is also a hotfix. Meanwhile the workaround is to specify OPTION (MAXDOP 1) in your query. Using MAXDOP 1 not only made my results accurate again, it also improved the query response time about 5-fold. Good luck.
-Jean
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply