NULL Changed in 2008 versus 2005? 2

[ousoonerjoe]

Database Upgraded from SQL Server 2005 Ent. to SQL Server 2008 Ent.

Is there a difference in the way NULL is evaluated in in 2008 versus 2005? In a stored procedure...

WHERE @x <> 'Y'

... NULL values would be returned in 2005. Now, after the upgrade last weekend, they are no longer being returned in the query. After setting all NULLs to '', the procedure reverted to working correctly again.

My consern is that NULL's have changed in the way they are being treated/evaluated and may cause an immediate clean up/change of all tables if true.

I realize this is poor coding to begin with, but it wasn't mine to write, just mine to clean up. If NULL has changed, as stated above, it will impact many tables and stored procedures... possibly a view or two.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[gmmastros]

Uh oh. You are relying on a feature that will be removed from a future version of SQL server. Lucky for you, it appears to still exist in sql 2008.

http://technet.microsoft.com/en-us/library/ms188048.aspx

If you open SQL Server Management Studio:

Right click your database
Click Properties
On 'select a page', click Options
In the 'Miscellaneous' section, you should see an option for "ANSI NULLS Enabled".

I suspect this value is False. I also suspect that changing this value to true may "fix" your problem. But, I strongly encourage you to re-work your code so that you do NOT rely on this behavior because it appears as though Microsoft is going to remove this in a future version.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom