In our database there is a table which is created with ANSI_NULLS OFF
. Now we have created a view using this table. And we want to add a clustered index for this view.
While creating the clustered index it is showing an error like can't create an index since the ANSI_NULL is off for this particular table.
This table contains a large amount of data. So I want to change this option to ON without losing any data.
Is there any way to alter the table to modify this option . Please give your suggestions.
ANSI_NULLS should be set to ON for executing distributed queries. 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.
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 non-null values in column_name.
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure. When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed. When SET ANSI_DEFAULTS is ON, QUOTED_IDENTIFIER is also ON.
ANSI_NULLS define the comparison rule for NULL values in SQL Server. 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. Example.
This was cross posted on Database Administrators so I might as well post my answer from there here too to help future searchers.
It can be done as a metadata only change (i.e. without migrating all the data to a new table) using ALTER TABLE ... SWITCH
.
Example code below
/*Create table with option off*/
SET ANSI_NULLS OFF;
CREATE TABLE dbo.YourTable (X INT)
/*Add some data*/
INSERT INTO dbo.YourTable VALUES (1),(2),(3)
/*Confirm the bit is set to 0*/
SELECT uses_ansi_nulls, *
FROM sys.tables
WHERE object_id = object_id('dbo.YourTable')
GO
BEGIN TRY
BEGIN TRANSACTION;
/*Create new table with identical structure but option on*/
SET ANSI_NULLS ON;
CREATE TABLE dbo.YourTableNew (X INT)
/*Metadata only switch*/
ALTER TABLE dbo.YourTable SWITCH TO dbo.YourTableNew;
DROP TABLE dbo.YourTable;
EXECUTE sp_rename N'dbo.YourTableNew', N'YourTable','OBJECT';
/*Confirm the bit is set to 1*/
SELECT uses_ansi_nulls, *
FROM sys.tables
WHERE object_id = object_id('dbo.YourTable')
/*Data still there!*/
SELECT *
FROM dbo.YourTable
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH;
WARNING: when your table contains an IDENTITY column you need to reseed the IDENTITY value. The SWITCH TO will reset the seed of the identity column and if you do not have a UNIQUE or PRIMARY KEY constraint on the identity (e.g. when using CLUSTERED COLUMNSTORE index in SQL 2014) you won't notice it right away. You need to use DBCC CHECKIDENT ('dbo.YourTable', RESEED, [reseed value]) to correctly set the seed value again.
Unfortunately, there is no way how to do it without recreating. You need to create new table with ANSI_NULLS ON
and copy there all data.
It should be something like:
SET ANSI_NULLS ON;
CREATE TABLE new_MyTBL (
....
)
-- stop all processes changing your data at this point
SET IDENTITY_INSERT new_MyTBL ON
INSERT new_MyTBL (...) -- including IDENTITY field
SELECT ... -- including IDENTITY field
FROM MyTBL
SET IDENTITY_INSERT new_MyTBL OFF
-- alter/drop WITH SCHEMABINDING objects at this point
EXEC sp_rename @objname = 'MyTBL', @newname = 'old_MyTBL'
EXEC sp_rename @objname = 'new_MyTBL', @newname = 'MyTBL'
-- alter/create WITH SCHEMABINDING objects at this point
-- re-enable your processes
DROP TABLE old_MyTBL -- do that when you are sure that system works OK
If there are any depending objects, they will work with new table as soon as you rename it. But if some of them are WITH SCHEMABINDING
you need to DROP
and CREATE
them manualy.
I tried the SWITCH option recommended above but was unable to RESEED the identity. I could not find out why.
I used the following alternative approach instead:
SET IDENTITY_INSERT TABLE_NAME ON
INSERT INTO TABLE_NAME (PK, col1, etc.)
SELECT PK, col1, etc.
FROM [Database_Snapshot].dbo.TABLE_NAME
SET IDENTITY_INSERT TABLE_NAME OFF
Using the above:
I realize deleting table may not always be straightforward if table is referenced in other tables. That was not the case for me in this instance.. I was lucky.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With