Actualizar la opción ANSI_NULLS en una tabla existente

En nuestra database hay una tabla que se crea con ANSI_NULLS OFF . Ahora hemos creado una vista usando esta tabla. Y queremos agregar un índice agrupado para esta vista.

Al crear el índice agrupado, muestra un error, como no se puede crear un índice, ya que ANSI_NULL está desactivado para esta tabla en particular.

Esta tabla contiene una gran cantidad de datos. Así que quiero cambiar esta opción a ON sin perder ningún dato.

¿Hay alguna manera de alterar la tabla para modificar esta opción? Por favor, da tus sugerencias.

Esto se publicó de forma cruzada en Administradores de bases de datos, así que también podría publicar mi respuesta desde allí para ayudar a los buscadores futuros.

Se puede hacer como un cambio de metadatos solamente (es decir, sin migrar todos los datos a una nueva tabla) usando ALTER TABLE ... SWITCH .

Ejemplo de código a continuación

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

ADVERTENCIA: cuando su tabla contiene una columna de IDENTIDAD, debe resembrar el valor de IDENTIDAD. El SWITCH TO reiniciará el inicio de la columna de identidad y, si no tiene una restricción UNIQUE o PRIMARY KEY en la identidad (por ejemplo, al usar el índice CLUSTERED COLUMNSTORE en SQL 2014), no lo notará de inmediato. Debe usar DBCC CHECKIDENT ('dbo.YourTable', RESEED, [reseed value]) para establecer correctamente el valor inicial.

Desafortunadamente, no hay forma de cómo hacerlo sin volver a crear. Necesita crear una nueva tabla con ANSI_NULLS ON y copyr allí todos los datos.

Debería ser algo así como:

 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 

Si hay objects dependientes, trabajarán con la nueva tabla tan pronto como la renombre. Pero si algunos de ellos están WITH SCHEMABINDING usted debe WITH SCHEMABINDING y CREARlos manualmente.

Probé la opción de INTERRUPCIÓN recomendada más arriba, pero no pude RESISTIR la identidad. No pude averiguar por qué.

Usé el siguiente enfoque alternativo en su lugar:

  1. Crear una instantánea de database para la database que contiene la tabla
  2. Definición de la tabla de scripts de la tabla que desea actualizar
  3. Elimine la tabla que desea actualizar (asegúrese de que la instantánea de la database se haya creado correctamente)
  4. Actualice SET ANSI NULLs de OFF a ON desde la secuencia de commands obtenida en el paso 2 y ejecute la secuencia de commands actualizada. La tabla ahora está recreada.
  5. Rellene los datos de la instantánea de la database con su tabla: 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. Migre manualmente el índice no agrupado (obtenga el script de la instantánea de la database)

Usando lo anterior:

  • No tuve que preocuparme por las restricciones y las keys, ya que los nombres de tabla / restricción siempre permanecen iguales (no necesito cambiar el nombre de nada)
  • Tengo una copy de security de mis datos (la instantánea) en la que puedo confiar para verificar que no falte nada.
  • No necesito resembrar la identidad

Me doy count de que eliminar la tabla no siempre es sencillo si se hace reference a la tabla en otras tablas. Ese no fue el caso para mí en este caso … Tuve suerte.

Intereting Posts