sql server unable to evaluate null

Question

Post reply

sql server unable to evaluate null

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

sqljean SSC Veteran Points: 208 More actions · Answer 1

sqljean

SSC Veteran

Points: 208

March 28, 2003 at 11:28 am

#452477

Forgot to mention, when we first noticed this problem, we spent hours rebuilding indexes on the affected tables. It did not help. If this scenario matches your problem, try using MAXDOP 1 in your query before you go to the trouble of rebuilding indexes.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

Chris Hedgate

One Orange Chip

Points: 25041

March 29, 2003 at 1:34 am

#452507

Interesting. Do you have a URL sqljean?

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

sqljean SSC Veteran Points: 208 More actions · Answer 3

Sorry, I don't have a URL for this. One of our DBAs called MS about 6 wks. ago after we had were unable to troubleshoot this problem. She got the workaround solution (using MAXDOP 1) from MS. MS told her it was a recently reported bug. I just did a quick search against the KB but didn't find anything. Perhaps I didn't look hard enough. The same DBA called MS again last week. That's how we learned this will be fixed in SP4. The good news is, this only affects counting queries. The underlying data is fine. If I do SELECT * instead of count(*) with an IS NULL in the WHERE clause, I get the correct records returned without having to specify MAXDOP 1. BTW, this problem presented itself the day after we applied SP3 to a data warehouse server. After that night's load, my validation counts against a very large table were way off. I lost some sleep fearing we had a big corruption problem despite DBCC CHECKDB and DBCC CHECKTABLE indicating no problem. Also lost time rebuiling indexes unnecessarily. Then I had to review the entire ETL to make sure no processes had caused downstream errors based on incorrect counts. It was not a happy week at work!

-Jean

andrewg68 Grasshopper Points: 17 More actions · Answer 4

andrewg68

Grasshopper

Points: 17

March 30, 2003 at 4:15 pm

#452529

Hi Everyone,

You may recall in the SQL help file, SQL NULL is nothing, which means you cannot measure it or compare it. This means that SQL is working correctly. The way around this is to use a function in the query where clause ex. Select * from MyTable where ISNULL(MyField, '1' ) = 1 .

HTH

Andrew