July 29, 2010 at 7:17 am
I was comparing a view from my production and development databases to see if they were in synch. I noticed that the definition of the view in production had a line of code that was not in my development server. That line was:
SET ANSI_NULLS OFF
I am confused about three things:
1. I use the same script to create this view in both development and production. That script doesn't contain any line of code that deals with ANSI_NULLS. Therefore I am not sure where this line of code came from?
2. I am not 100% on what SET ANSI_NULLS OFF means?
3. What is the default value for ANSI NULL (on or off)? My hope is that it is off so that even though the line of code is not in development that the behavior is the same between my production and developement environments.
Thanks
July 29, 2010 at 8:05 am
July 29, 2010 at 8:38 am
bitbucket-25253 (7/29/2010)
OOPs posted incorrectly -- hence removed...
Sorry if I am being dense. I read the article on Forum Etiquette and I don't see why my post was incorrect? Any guidence for this post and future posts would be appreciated.
Thanks
July 29, 2010 at 11:20 am
The default should be ON, but that can be changed at the database level.
From BOL:
Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.
When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.
Also from BOL:
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
July 30, 2010 at 6:21 am
Lamprey13 (7/29/2010)
The default should be ON, but that can be changed at the database level.From BOL:
Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.
When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.
Also from BOL:
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
First I want to thank you for the information. What confuses me is where this line of code comes from. What I mean is that the script I wrote doesn't set ANSI_NULLS on or off, but when I look at the view in Management Studio, the line SET ANSI_NULLS OFF is there
USE [DGIS_PRODUCTION]
GO
/****** Object: View [dbo].[RequestForQuote_View] Script Date: 07/30/2010 08:18:12 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[RequestForQuote_View]
I am not sure where the lines before CREATE VIEW came from (my script starts with the line CREATE VIEW).
Thanks again
July 30, 2010 at 6:38 am
meichner (7/30/2010)
USE [DGIS_PRODUCTION]
GO
/****** Object: View [dbo].[RequestForQuote_View] Script Date: 07/30/2010 08:18:12 ******/
meichner, this looks like you used the Right Click --> Script View As --> CREATE To to generate the statement, dint you ? If yes, then SSMS itself created that SET ANSI_NULL OFF setting statement in your code, because when you initially created teh view in your PROD DB, u had set the setting to OFF..
July 30, 2010 at 6:42 am
To add to it, the default setting of your PROD DB should have the Right Click on DB - Properties - Options - Miscellanous - ANSI NULL default - FALSE .. Thats what causing the statement to be generated..
July 30, 2010 at 7:11 am
ColdCoffee (7/30/2010)
To add to it, the default setting of your PROD DB should have the Right Click on DB - Properties - Options - Miscellanous - ANSI NULL default - FALSE .. Thats what causing the statement to be generated..
I did as you say and you were correct that ANSI NULL Default is false. What confuses me is that in my development environment the value of ANSI NULL default is also false, but SSMS shows SET ANSI_NULLS ON for the view.
View in Development
GO
/****** Object: View [dbo].[RequestForQuote_View] Script Date: 07/30/2010 09:10:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[RequestForQuote_View]
GO
ALTER DATABASE [DGIS_DEVELOPMENT] SET ANSI_NULL_DEFAULT OFF
GO
/****** Object: View [dbo].[RequestForQuote_View] Script Date: 07/30/2010 09:08:58 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[RequestForQuote_View]
GO
ALTER DATABASE [DGIS_PRODUCTION] SET ANSI_NULL_DEFAULT OFF
Based on what you stated I understand why ANSI NULLS are off in Production. I cannot understand why my development server shows them as being on?
Thanks so much for the help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply