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

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

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

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

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

Ответы на вопрос(3)

Решение Вопроса

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

Это можно сделать как изменение только метаданных (т.е. без переноса всех данных в новую таблицу), используя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. Переключатель TO сбрасывает начальное число столбца идентификаторов, и если у вас нет ограничения UNIQUE или PRIMARY KEY для идентификатора (например, при использовании индекса CLUSTERED COLUMNSTORE в SQL 2014), вы сразу не заметите его. Вам нужно использовать DBCC CHECKIDENT («dbo.YourTable», RESEED, [reseed value]), чтобы снова правильно установить начальное значение.

 13 янв. 2016 г., 21:37
Сохранит ли это все индексы и ограничения исходной таблицы?
 05 июл. 2016 г., 22:10
@MartinSmith правильно, я отправил свой комментарий слишком рано. Я хочу сказать, что команда switch сама по себе не позаботится о них. Они должны быть созданы как часть сценария создания таблицы или отдельно. В случае некластеризованных индексов мне лично пришлось создавать их отдельно (и использовать инструмент diff, чтобы облегчить этот процесс). Я думаю, что действительно не помешает сделать быстрый визуальный анализ в SSMS между промежуточной / временной таблицей и конечной таблицей, чтобы убедиться, что ничего не забыто. Кроме того, у меня был готов моментальный снимок базы данных на случай, если я что-то напортачу (test env), но это потому, что я параноик.
 05 июл. 2016 г., 22:12
@MartinSmith, кстати, спасибо за этот ответ .. это решило мою проблему.
 05 июл. 2016 г., 21:34
@xav будет, если вы включите их вcreate table.
 05 июл. 2016 г., 21:32
Нет, он не сохранит все индексы и ограничения.

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

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

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

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

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

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

К сожалению, нет способа сделать это без воссоздания. Вам нужно создать новую таблицу с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 их вручную.

 Mahesh KP09 апр. 2012 г., 06:36
у нас есть представление, связанное с этой таблицей. Итак, что является лучшим методом для переноса данных из этой таблицы во вновь созданную таблицу, не затрагивая представления и связанные таблицы?
 Mahesh KP09 апр. 2012 г., 06:48
да, мы создали представления с опцией привязки схемы. Можем ли мы изменить представление, чтобы удалить привязку схемы, а затем выполнить ваш запрос. Является ли это возможным?
 Mahesh KP09 апр. 2012 г., 07:01
да, мы собираемся сделать полнотекстовый индекс наших просмотров. Точно так же насчет полей идентичности. Как сохранить это без изменений?
 09 апр. 2012 г., 06:51
Индексируются ли ваши взгляды? Какова была причина сделать их связыванием схемы?
 09 апр. 2012 г., 13:08
Вам не нужно копировать все данные в новую таблицу, удалять и заново создавать все индексы. Это можно сделать, просто обновив метаданные таблицы.

Ваш ответ на вопрос