15

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.

shA.t
  • 16,580
  • 5
  • 54
  • 111
Mahesh KP
  • 6,248
  • 13
  • 50
  • 71

3 Answers3

21

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.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Will this retain all indexes and constraints of the original table? – Dan Ling Jan 13 '16 at 20:37
  • 1
    No it won't retain all indexes and constraints. – xav Jul 05 '16 at 19:32
  • 1
    @xav it will if you include them in the `create table`. – Martin Smith Jul 05 '16 at 19:34
  • @MartinSmith correct I fired my comment too soon. My point is that the switch command won't take care of them by itself. They have to be created as part of the create table script or separately. In the case of non clustered indexes I personally had to create them separately (and use a diff tool to make that process easier). I think it really does not hurt to do a quick visual diff in SSMS between the staging/temporary table and the end table to make sure that nothing is forgotten. Also, I had a database snapshot ready in case I screwed up something (test env), but that's because I am paranoid. – xav Jul 05 '16 at 20:10
  • @MartinSmith, by the way thanks for this answer.. it solved my problem. – xav Jul 05 '16 at 20:12
  • @Martin Smith Following your script, it doesn`t work for me... I get the following error: ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables... when doing the "ALTER TABLE dbo.YourTable SWITCH TO dbo.YourTableNew;"... what am I doing wrong? I created the create table script from the old table. the only thing I changed was the name of the primaryKey... – JohnG Jun 26 '17 at 19:48
  • @JohnG probably the `ANSI_PADDING` setting is different between the two tables. – Martin Smith Jun 26 '17 at 19:57
  • If there is an Identity column on I would have thought it easier to just run SELECT * INTO new_table from oldtable ( after setting ANSI_NULLS appropriately). – Alex Ayscough Feb 11 '21 at 16:55
  • @AlexAyscough - Depends how many rows you are dealing with. If the table is large it is much easier to do it as a metadata only change that doesn't copy rows around – Martin Smith Feb 11 '21 at 18:16
2

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.

Andrey Gurinov
  • 2,825
  • 1
  • 20
  • 23
  • we have got a view associated with this table. So what is the best method to migrate data from this table to newly created table without affecting the views and associated tables?? – Mahesh KP Apr 09 '12 at 04:36
  • yes, we created the views with schemabinding option. Can we alter the view to remove schemabinding and then run your query . Is it possible? – Mahesh KP Apr 09 '12 at 04:48
  • Are your views indexed? What was the reason to make them schemabinding? – Andrey Gurinov Apr 09 '12 at 04:51
  • yes we are going to do full text index on our views. Similarly what about identity fields. How to keep it unaffected? – Mahesh KP Apr 09 '12 at 05:01
  • Check the update, please. Use SET IDENTITY_INSERT to keep identity fields unchanged. As for indexes, they have to be dropped and created again. – Andrey Gurinov Apr 09 '12 at 05:10
  • 1
    You don't need to copy all the data across to a new table and drop and recreate all indexes. It is possible to do it just by updating the table metadata. – Martin Smith Apr 09 '12 at 11:08
  • @Andrey Gurinov : i think the other method is a better way to do these things.. That is why changed the answer.. – Mahesh KP Apr 10 '12 at 06:35
1

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:

  1. Create database snapshot for the database that contains the table
  2. Script table definition of the table you intend to update
  3. Delete the table that you intend to update (Make sure the database snapshot is successfully created)
  4. Update SET ANSI NULLs from OFF to ON from the script obtained from step 2 and run updated script. Table is now recreated.
  5. Populate data from database snapshot to your table: 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
  6. Migrate non clustered index manually (get script from database snapshot)

Using the above:

  • I did not have to worry about constraints and keys since table/constraint names always remain the same (I do not need to rename anything)
  • I have a backup of my data (the snapshot) which I can rely on to double check that nothing is missing.
  • I do not need to reseed the identity

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.

xav
  • 812
  • 8
  • 9