Обновить параметр ANSI_NULLS в существующей таблице

Question

В нашей базе данных есть таблица, которая создается с ANSI_NULLS OFF, Теперь мы создали представление, используя эту таблицу. И мы хотим добавить кластеризованный индекс для этого представления.

При создании кластеризованного индекса показывается ошибка, например, невозможно создать индекс, поскольку ANSI_NULL отключен для этой конкретной таблицы.

Эта таблица содержит большое количество данных. Поэтому я хочу изменить эту опцию на ВКЛ без потери каких-либо данных.

Есть ли способ изменить таблицу, чтобы изменить эту опцию. Пожалуйста, дайте ваши предложения.

Answer 1

Это было перекрестно размещено на администраторах баз данных, поэтому я мог бы также опубликовать свой ответ оттуда, чтобы помочь будущим поисковикам.

Это можно сделать как изменение только метаданных (то есть без переноса всех данных в новую таблицу), используя ALTER TABLE ... SWITCH,

Пример кода ниже

/*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; 

ВНИМАНИЕ: если ваша таблица содержит столбец IDENTITY, вам необходимо повторно заполнить значение IDENTITY. SWITCH TO сбрасывает начальное число столбца идентификаторов, и если у вас нет ограничения UNIQUE или PRIMARY KEY для идентификатора (например, при использовании индекса CLUSTERED COLUMNSTORE в SQL 2014), вы не заметите это сразу. Вам нужно использовать DBCC CHECKIDENT ('dbo.YourTable', RESEED, [reseed value]), чтобы снова правильно установить начальное значение.

Answer 2

К сожалению, нет способа сделать это без воссоздания. Вам нужно создать новую таблицу с ANSI_NULLS ON и скопируйте туда все данные.

Это должно быть что-то вроде:

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

Если есть какие-либо зависимые объекты, они будут работать с новой таблицей, как только вы переименуете ее. Но если некоторые из них WITH SCHEMABINDING вам нужно DROP а также CREATE их вручную.

Answer 3

Я попробовал вариант ПЕРЕКЛЮЧЕНИЯ, рекомендованный выше, но не смог СБРОСИТЬ личность. Я не мог выяснить почему.

Вместо этого я использовал следующий альтернативный подход:

1. Создать снимок базы данных для базы данных, содержащей таблицу
2. Определение таблицы сценариев таблицы, которую вы собираетесь обновить
3. Удалите таблицу, которую вы собираетесь обновить (убедитесь, что моментальный снимок базы данных успешно создан)
4. Обновите SET ANSI NULL с OFF на ON из сценария, полученного на шаге 2, и запустите обновленный сценарий. Таблица теперь воссоздана.
5. Заполните данные из снимка базы данных в вашу таблицу: 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. Перенос некластеризованного индекса вручную (получить скрипт из моментального снимка базы данных)

Используя вышеупомянутое:

• Мне не нужно было беспокоиться об ограничениях и ключах, поскольку имена таблиц / ограничений всегда остаются неизменными (мне не нужно ничего переименовывать)
• У меня есть резервная копия моих данных (снимок), на которую я могу положиться, чтобы дважды проверить, что ничего не пропало.
• Мне не нужно повторно идентифицировать личность

Я понимаю, что удаление таблицы не всегда может быть простым, если на таблицу ссылаются другие таблицы. В данном случае это было не так для меня.. Мне повезло.