Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get Your ANSI_NULLs Settings Consistent

By William Talada,

SET IGNORE_FOR_AS_LONG_AS_POSSIBLE_EVERYTHING_TO_DO_WITH_ANSI_SETTINGS ON

Most likely your databases are working sufficiently well that you don't want to even think about digging into ANSI standards and aligning your settings with the rest of the world. But, if you look in the BOL index for "null values [SQL Server], comparison operators" you'll see an important message stating that ANSI_NULLS will be forced ON in a future release. And believe me, if you haven't looked into this, you probably have lurking bugs in your code.

If you read the remarks underneath the message, you'll see that things get confusing and buggy when comparing null columns and variables. It took me a while to decipher exactly which settings makes everyone happy. I'll show you the five simple steps I've taken to get through this quagmire to get back to producing bug free code on the ANSI bandwagon.

1) First go into SSMS and go into Tools > Options > Query Execution > SQL Server > ANSI and press the button labeled "Reset to Default" so we are working with the same connection settings.

2) Make sure your databases have the correct settings since they affect object creation settings. At the very least, try to get model updated so new databases will be created correctly.

-- list databases and their ANSI settings
select 
        name,
        case when
         is_ansi_null_default_on = 1
        and is_ansi_nulls_on=1
        and is_ansi_padding_on=1
        and is_ansi_warnings_on=1
        and is_arithabort_on=1
        and is_concat_null_yields_null_on=1
        and is_numeric_roundabort_on=0
        and is_quoted_identifier_on=1 then 'ANSI'
        else 'not' 
        end
from
        sys.databases
order by
        name

3) Make sure your objects were created with the correct settings by running the next six selects. The tables are the hardest to fix, since they will need scripted and recreated. You can trick the scripter into making a script by making a minor column change such as nullability, then manually changing it back in the generated code. If you don't have the willpower to fix these objects right now, please continue on to steps 4 and 5.

select 
        name as 'Table Not ANSI',
        uses_ansi_nulls
from
        sys.tables 
where
        uses_ansi_nulls <> 1
order by
        name

select 
        name as 'Proc Not ANSI',
        objectproperty(object_id, 'ExecIsAnsiNullsOn') as AnsiNullsOn,
        objectproperty(object_id, 'ExecIsQuotedIdentOn') as QuotedIdentOn
from 
        sys.procedures 
where 
        (isnull(objectproperty(object_id, 'ExecIsAnsiNullsOn'),0)=0 or isnull(objectproperty(object_id, 'ExecIsQuotedIdentOn'),0)=0)
and
        name not in (select cast(cast(assembly_method as varbinary(100)) as varchar(100)) from sys.assembly_modules)
order by
        name

select 
        name as 'Trigger Not ANSI',
        objectproperty(object_id, 'ExecIsAnsiNullsOn') as AnsiNullsOn,
        objectproperty(object_id, 'ExecIsQuotedIdentOn') as QuotedIdentOn
from 
        sys.triggers
where 
        (isnull(objectproperty(object_id, 'ExecIsAnsiNullsOn'),0)=0 or isnull(objectproperty(object_id, 'ExecIsQuotedIdentOn'),0)=0)
order by
        name

select 
        name as 'View Not ANSI',
        objectproperty(object_id, 'ExecIsAnsiNullsOn') as AnsiNullsOn,
        objectproperty(object_id, 'ExecIsQuotedIdentOn') as QuotedIdentOn
from 
        sys.views
where 
        (isnull(objectproperty(object_id, 'ExecIsAnsiNullsOn'),0)=0 or isnull(objectproperty(object_id, 'ExecIsQuotedIdentOn'),0)=0)
order by
        name

select 
        name as 'Function Not ANSI',
        objectproperty(object_id, 'ExecIsAnsiNullsOn') as AnsiNullsOn,
        objectproperty(object_id, 'ExecIsQuotedIdentOn') as QuotedIdentOn
from 
        sys.objects
where 
        (isnull(objectproperty(object_id, 'ExecIsAnsiNullsOn'),0)=0 or isnull(objectproperty(object_id, 'ExecIsQuotedIdentOn'),0)=0)
and
        type = 'FN'
order by
        name

select
        object_name(object_id) as 'Column Not ANSI',
        c.*,
        t.name
        --select distinct t.name
from
        sys.columns c
join
        sys.types t on c.system_type_id = t.system_type_id
where
        is_ansi_padded =0 
and
        t.name in ('nchar')
and
        object_name(object_id) <> 'sysfiles1'

4) Fix lurking bugs by replacing all "= null" with "is null" since this will work regardless of ANSI settings. Also, replace all "<> null" with "is not null".

--Run the following query to return a list of objects that might need changed.
-- Some false positives are returned since initializing with "set @v = null" is a valid ANSI statement.
select distinct 'exec sp_helptext '+object_name(id) 
from sys.syscomments 
where text like '%= null%'
order by 1

5) You can use the following function anytime you need to do a nullable ints comparision in your t-sql. This is only necessary when comparing a variable to a column. This happens quite often with stored procedure parameters in "where" clauses. It would have been nice for this to have been built into t-sql. You may need to make another function called NullableVarcharsMatch.

-- compare nullable int variables
-- return true if the value matches or if both parameters are null
create function [dbo].[NullableIntsMatch] (@a int, @b int)
returns Bit
as
-- written by Bill Talada
begin
        -- no need to call this function if @a and @b are both columns
        -- function calls are slow...so if you want to inline the tests, use one of the following lines...
        --same: where ((@a = @b) or (@a is null and @b is null))
        --diff: where ((@a <> @b) or (@a is null and @b is not null) or (@a is not null and @b is null))

        declare @r bit
        if @a = @b or (@a is null and @b is null) set @r=1 else set @r=0
        return (@r)
end
GO

Good work! Rest assured your queries will no longer be "missing" rows.

Total article views: 6365 | Views in the last 30 days: 11
 
Related Articles
BLOG

Remember OBJECTPROPERTY(..) when investigating objects.

The object property function appears to be often overlooked. This function will tell us about any s...

FORUM

Select premission was denied on Object

Select premission Denied on Object

FORUM

Question on OBJECTPROPERTY

This calls return NULL even if "TableName" exists in the Development DB and definitely has an identi...

FORUM

ObjectProperty returning Null

Trying to find out if a Trigger is enabled

BLOG

T-SQL Tuesday #004: IO -- Where Are My TempDB Objects?

T-SQL Tuesday #004: IO -- Where Are My TempDB Objects? This blog entry is participating in T-SQL Tu...

Tags
ansi_nulls    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones