ANSI_NULLS, ANSI_PADDING and CONCAT_NULL_YIELDS_NULL. columns

Question

If the database having these columns ANSI_NULLS, ANSI_PADDING and CONCAT_NULL_YIELDS_NULL. , which set off during the creation.

how to change them back to ON. if there are data/index already inside these column ? need recreate the whole table ?

Answer 1

Your question is not clear. Are you talking about the database options by these names? You can use ALTER DATABASE to that purpose., but there is little reason to. These database settings apply only to really old client API:s, like DB-Library. For a modern client API, they don't matter. They may be OFF, but they will be ON when you connect nevertheless.

If you had something else in mind, you need to clarify.

Answer 2

Hi @sakuraime ,

Example as ANSI_NULLS testing on my own side, please follow the steps test other two on your own side.

how to change them back to ON.

Alter database dbname set ANSI_NULLS on  

or set it on by SSMS

if there are data/index already inside these column ? need recreate the whole table ?

No.You do not need to create the whole table, if you want to create one index or doing caculate or filter, just set it on.
In another word, if set them off, you can not create the filter index.

Quote from this doc.: create-index-transact-sql-Filtered Indexes
If the SET options are incorrect, the following conditions can occur:

The filtered index is not created.
The Database Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.
Query optimizer does not consider the index in the execution plan for any Transact-SQL statements.

Test code:

use master  
go  
  
create database test  
go  
  
use test  
go  
  
--check 1   
select databasepropertyex('test', 'IsAnsiNullsEnabled')  
  
--set it off  
Alter database test set ANSI_NULLS off  
  
--creat table  
CREATE TABLE dbo.t1 (a INT NULL);    
INSERT INTO dbo.t1 values (NULL),(0),(1);    
GO    
    
-- Print message and perform SELECT statements.    
PRINT 'Testing ANSI_NULLS OFF';    
SET ANSI_NULLS OFF;    
GO    
DECLARE @varname int;    
SET @varname = NULL;    
SELECT a     
FROM t1     
WHERE a = @varname;    
    
SELECT a     
FROM t1     
WHERE a <> @varname;    
    
SELECT a     
FROM t1     
WHERE a IS NULL;    
GO    
  
--insert data  
INSERT INTO dbo.t1 values (2);    
GO    
  
--set it on  
Alter database test set ANSI_NULLS on  
  
--insert  data  
INSERT INTO dbo.t1 values (3);    
GO    
  
--create index  
CREATE  CLUSTERED INDEX Idx1 ON t1(a);  
  
--insert data  
INSERT INTO dbo.t1 values (4);    
GO    
  
--set it off  
Alter database test set ANSI_NULLS off  
  
--drop index  
drop INDEX t1.Idx1  
  
--create index  
CREATE  CLUSTERED INDEX Idx1 ON t1(a);  
  
--insert data  
INSERT INTO dbo.t1 values (5);    
GO    
  
--drop index  
drop INDEX t1.Idx1  
  
--create noclustered index  
CREATE NONCLUSTERED INDEX  NonIdx1 ON t1(a)  
  
--insert data  
INSERT INTO dbo.t1 values (6);    
GO    
  
--drop index  
drop INDEX t1.NonIdx1  
  
--ceate filter index  
CREATE NONCLUSTERED INDEX NonIdx1    
    ON t1(a)  
    WHERE a IS NOT NULL ;   

Note:
ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server returns an error that lists all SET options that violate the required values. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server ignores the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views. The options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON, and NUMERIC_ROUNDABORT must be set to OFF.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @sakuraime ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

actually is it you want to show , no matter database option ANSI_NULLS is on and off.session setting option ANSI_NULLS off , will causefilter index failure ?

I want to reply your questions. That is just one of the replies.