ALTER TABLE (Transact-SQL)

Modifica una definición de tabla al alterar, agregar o quitar columnas y restricciones, reasignar particiones, o deshabilitar o habilitar restricciones y desencadenadores.

Convenciones de sintaxis de Transact-SQL

Sintaxis

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name [ ( { precision [ , scale ] 
            | max | xml_schema_collection } ) ] 
        [ COLLATE collation_name ] 
        [ NULL | NOT NULL ] [ SPARSE ]

    | {ADD | DROP } 
        { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
    } 
        | [ WITH { CHECK | NOCHECK } ]

    | ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...n ]

    | DROP 
     {
         [ CONSTRAINT ] 
         { 
              constraint_name 
              [ WITH 
               ( <drop_clustered_constraint_option> [ ,...n ] ) 
              ] 
          } [ ,...n ]
          | COLUMN 
          {
              column_name 
          } [ ,...n ]
     } [ ,...n ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT 
        { ALL | constraint_name [ ,...n ] } 

    | { ENABLE | DISABLE } TRIGGER 
        { ALL | trigger_name [ ,...n ] }

    | { ENABLE | DISABLE } CHANGE_TRACKING 
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table 
        [ PARTITION target_partition_number_expression ]

    | SET ( FILESTREAM_ON = { partition_scheme_name | filegroup | 
                "default" | "NULL" } )

    | REBUILD 
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ] 
      | [ PARTITION = partition_number 
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>

    | <filetable_option>

}
[ ; ]

-- ALTER TABLE options


<column_set_definition> ::= 
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism 
      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name ( column_name ) | filegroup
          | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

<filetable_option> ::=
    {
       [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ]
       [ SET ( FILETABLE_DIRECTORY = directory_name ) ]
    }
<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE} }
}

Argumentos

• database_name
  Es el nombre de la base de datos en la que se creó la tabla.

• schema_name
  Es el nombre del esquema al que pertenece la tabla.

• table_name
  Es el nombre de la tabla que se va a modificar. Si la tabla no se encuentra en la base de datos actual o no está contenida en el esquema propiedad del usuario actual, la base de datos y el esquema deben especificarse explícitamente.

• ALTER COLUMN
  Especifica que la columna con nombre va a cambiarse o modificarse.

  La columna modificada no puede ser ninguna de las siguientes:

  • Una columna con un tipo de datos timestamp.

  • La columna ROWGUIDCOL de la tabla.

  • Una columna calculada o usada en una columna calculada.

  • Se usa en las estadísticas generadas por la instrucción CREATE STATISTICS a menos que la columna sea del tipo de datos varchar, nvarchar o varbinary, que el tipo de datos no se cambie y que el nuevo tamaño sea igual o mayor que el tamaño anterior, o que la columna se cambie de NOT NULL a NULL. Quite primero las estadísticas con la instrucción DROP STATISTICS. Las estadísticas generadas automáticamente por el optimizador de consultas se quitan automáticamente con ALTER COLUMN.

  • Una columna utilizada en una restricción PRIMARY KEY o [FOREIGN KEY] REFERENCES.

  • Una columna utilizada en una restricción CHECK o UNIQUE. Sin embargo, se permite el cambio de longitud de una columna de longitud variable en una restricción CHECK o UNIQUE.

  • Una columna asociada a la definición predeterminada. No obstante, la longitud, precisión o escala de una columna puede cambiarse si el tipo de datos no se cambia.

    El tipo de datos de las columnas text, ntext e image puede cambiarse de las formas siguientes:

    • text por varchar(max), nvarchar(max) o xml

    • ntext por varchar(max), nvarchar(max) o xml

    • image por varbinary(max)

    Algunos cambios del tipo de datos podrían suponer un cambio en los datos. Por ejemplo, cambiar una columna nchar o nvarchar por una columna char o varchar puede suponer la conversión de caracteres extendidos. Para obtener más información, vea CAST y CONVERT (Transact-SQL). Reducir la precisión o escala de una columna puede dar como resultado que se trunquen los datos.

    El tipo de datos de una columna de una tabla con particiones no puede cambiarse.

  El tipo de datos de las columnas incluidas en un índice no se cambiará a menos que la columna sea del tipo de datos varchar nvarchar o varbinary, y que el nuevo tamaño sea igual o mayor que el tamaño anterior.

  Una columna incluida en una restricción de clave principal no puede cambiarse de NOT NULL a NULL.

• column_name
  Es el nombre de la columna que se va a modificar, agregar o quitar. column_name puede tener un máximo de 128 caracteres. Si se trata de columnas nuevas, column_name se puede omitir en las columnas creadas con un tipo de datos timestamp. El nombre timestamp se usa si no se especifica un column_name para una columna del tipo de datos timestamp.

• [ type_schema_name**.** ] type_name
  Es el nuevo tipo de datos de la columna modificada o el tipo de datos de la columna agregada. type_name no se puede especificar para columnas existentes de tablas con particiones. type_name puede ser cualquiera de las siguientes:

  • Un tipo de datos del sistema de SQL Server.

  • Un tipo de datos del alias basado en el tipo de datos del sistema de SQL Server. Los tipos de datos de alias se crean con la instrucción CREATE TYPE antes de que puedan utilizarse en una definición de tabla.

  • Un tipo definido por el usuario de .NET Framework y el esquema al que pertenece. Los tipos definidos por el usuario de .NET Framework se crean con la instrucción CREATE TYPE antes de poder usarlos en una definición de tabla.

  A continuación se indican los criterios de type_name en una columna alterada:

  • El tipo de datos anterior debe poderse convertir implícitamente al nuevo tipo de datos.

  • type_name no puede ser de tipo timestamp.

  • Los valores predeterminados de ANSI_NULL están siempre activados para ALTER COLUMN; si no se especifican, la columna admite valores NULL.

  • El relleno ANSI_PADDING está siempre activado para ALTER COLUMN.

  • Si la columna alterada es una columna de identidad, new_data_type debe ser de un tipo de datos compatible con la propiedad de la identidad.

  • La configuración actual de SET ARITHABORT se ignora. ALTER TABLE opera como si la opción ARITHABORT estuviera activada.

  [!NOTA]

  Si no se especifica la cláusula COLLATE, la modificación de un tipo de datos de columna tendrá como resultado un cambio de intercalación a la intercalación predeterminada de la base de datos.

• precision
  Es la precisión del tipo de datos especificado. Para obtener más información acerca de los valores de precisión válidos, vea Precisión, escala y longitud (Transact-SQL).

• scale
  Es la escala del tipo de datos especificado. Para obtener más información acerca de los valores de escala válidos, vea Precisión, escala y longitud (Transact-SQL).

• max
  Solo se aplica a los tipos de datos varchar, nvarchar y varbinary para el almacenamiento de 2^31-1 bytes de caracteres, datos binarios y datos Unicode.

• xml_schema_collection
  Solo se aplica al tipo de datos xml para asociar un esquema XML con el tipo. Antes de escribir una columna xml en una colección de esquemas, la colección de esquemas debe crearse en la base de datos mediante el uso de CREATE XML SCHEMA COLLECTION.

• COLLATE < collation_name >
  Especifica la nueva intercalación para la columna alterada. Si no se especifica, se asigna a la columna la intercalación predeterminada de la base de datos. El nombre de intercalación puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Para obtener más información y una lista de nombres de intercalación, vea Nombre de intercalación de Windows (Transact-SQL) y Nombre de intercalación de SQL Server (Transact-SQL).

  Es posible utilizar la cláusula COLLATE solo para alterar las intercalaciones de las columnas cuyo tipo de datos sea char, varchar, nchar y nvarchar. Para modificar la intercalación de una columna de tipo de datos de alias definido por el usuario, debe ejecutar distintas instrucciones ALTER TABLE para cambiar la columna por un tipo de datos del sistema de SQL Server y modificar su intercalación, y a continuación volver a modificar la columna para que sea un tipo de datos de alias.

  ALTER COLUMN no puede tener un cambio de intercalación si existe alguna de las siguientes condiciones:

  • Si una restricción CHECK, una restricción FOREIGN KEY o las columnas calculadas hacen referencia a la columna cambiada.

  • Si se ha creado algún índice, estadística o índice de texto completo en la columna. Las estadísticas creadas automáticamente en la columna cambiada se quitarán si se altera la intercalación de columna.

  • Si una función o vista enlazada a esquema hace referencia a la columna.

  Para obtener más información, vea COLLATE (Transact-SQL).

• NULL | NOT NULL
  Especifica si la columna puede aceptar valores NULL. Las columnas que no permiten valores NULL solo se pueden agregar con ALTER TABLE si tienen especificado un valor predeterminado o si la tabla está vacía. NOT NULL solo puede especificarse para las columnas si también se especifica PERSISTED. Si la nueva columna permite valores NULL y no se especifica un valor predeterminado, la nueva columna contendrá un valor NULL en cada fila de la tabla. Si la nueva columna permite valores NULL y se agrega una definición predeterminada con la nueva columna, se puede utilizar la opción WITH VALUES para almacenar el valor predeterminado en la nueva columna para cada fila existente en la tabla.

  Si la nueva columna no permite valores NULL y la tabla no está vacía, debe agregarse una definición DEFAULT con la nueva columna y ésta se carga automáticamente con el valor predeterminado en las nuevas columnas de cada fila existente.

  NULL puede especificarse en ALTER COLUMN para forzar que una columna NOT NULL permita valores NULL, excepto en el caso de las columnas de las restricciones PRIMARY KEY. NOT NULL solo se puede especificar en ALTER COLUMN si la columna no contiene valores NULL. Los valores NULL deben actualizarse a algún valor para poder permitir ALTER COLUMN NOT NULL, como:

  UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL;
  ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL;
  

  Cuando cree o altere una tabla con las instrucciones CREATE TABLE o ALTER TABLE, los valores de sesión y de la base de datos influirán en la nulabilidad, y posiblemente la invalidarán, para el tipo de datos utilizado en la definición de una columna. Recomendamos definir siempre explícitamente una columna como NULL o como NOT NULL en el caso de las columnas no calculadas.

  Si agrega una columna con un tipo de datos definidos por el usuario, recomendamos definir la columna con la misma nulabilidad del tipo de datos definidos por el usuario y especificar un valor predeterminado para la columna. Para obtener más información, vea CREATE TABLE (Transact-SQL).

  [!NOTA]

  Si se especifica NULL o NOT NULL con ALTER COLUMN, también se debe especificar new_data_type [(precision [, scale ])]. Si el tipo de datos, la precisión y la escala no se cambian, especifique los valores actuales de la columna.

• [ {ADD | DROP} ROWGUIDCOL ]
  Especifica que la propiedad ROWGUIDCOL se agrega a la columna indicada o se quita de ella. ROWGUIDCOL indica que la columna es una columna GUID de fila. Solo puede designarse una columna uniqueidentifier por tabla como la columna ROWGUIDCOL, y la propiedad ROWGUIDCOL solo puede asignarse a una columna uniqueidentifier. ROWGUIDCOL no puede asignarse a una columna de un tipo de datos definido por el usuario.

  ROWGUIDCOL no exige la exclusividad de los valores almacenados en la columna y no genera automáticamente valores para las nuevas filas que se insertan en la tabla. Para generar valores únicos para cada columna, utilice la función NEWID en instrucciones INSERT o especifique la función NEWID como la predeterminada para la columna.

• [ {ADD | DROP} PERSISTED ]
  Especifica que la propiedad PERSISTED se agrega a la columna indicada o se quita de ella. La columna debe ser una columna calculada definida mediante una expresión determinista. Para las columnas especificadas como PERSISTED, Motor de base de datos almacena físicamente los valores calculados en la tabla y actualiza los valores cuando se actualiza cualquiera de las otras columnas de las que depende la columna calculada. Al marcar una columna calculada como PERSISTED, puede crear índices sobre columnas calculadas definidas sobre expresiones que son deterministas, pero no precisas. Para obtener más información, vea Índices en columnas calculadas.

  Las columnas calculadas que se utilizan como columna de partición de una tabla con particiones deben marcarse explícitamente como PERSISTED.

• DROP NOT FOR REPLICATION
  Especifica que los valores de columnas de identidad se incrementan cuando los agentes de replicación realizan operaciones de inserción. Esta cláusula solo se puede especificar si column_name es una columna de identidad.

• SPARSE
  Indica que la columna es una columna dispersa. El almacenamiento de columnas dispersas está optimizado para los valores NULL. Las columnas dispersas no se pueden designar como NOT NULL. Al convertir una columna de dispersa a no dispersa o viceversa, se bloquea la tabla durante la ejecución del comando. Es posible que tenga que usar la cláusula REBUILD para recuperar espacio. Para obtener restricciones adicionales y más información sobre columnas dispersas, vea Usar columnas dispersas.

• WITH CHECK | WITH NOCHECK
  Especifica si los datos de la tabla se han validado o no frente a una restricción FOREIGN KEY o CHECK recién agregada o habilitada de nuevo. Si no se especifica, se supone WITH CHECK para las restricciones nuevas y WITH NOCHECK para las restricciones que se han habilitado otra vez.

  Si no desea volver a comprobar las restricciones CHECK o FOREIGN KEY nuevas con los datos existentes, utilice WITH NOCHECK. No se recomienda que haga esto, excepto en casos muy contados. La nueva restricción se evaluará en todas las actualizaciones futuras. Las infracciones de restricción que se supriman mediante WITH NOCHECK cuando se agrega la restricción pueden hacer que las actualizaciones futuras no se puedan llevar a cabo si actualizan filas con datos que no cumplan la restricción.

  El optimizador de consultas no considera las restricciones definidas como WITH NOCHECK. Estas restricciones se pasan por alto hasta que se vuelven a habilitar mediante ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

• ADD
  Especifica que se agregan una o más definiciones de columna, definiciones de columnas calculadas o restricciones de tabla.

• DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
  Especifica que constraint_name o column_name se ha quitado de la tabla. Se pueden enumerar múltiples columnas y restricciones.

  El nombre de la restricción, ya sea proporcionado por el sistema o definido por el usuario, puede determinarse mediante la consulta de las vistas de catálogo sys.check_constraint, sys.default_constraints, sys.key_constraints y sys.foreign_keys.

  Una restricción PRIMARY KEY no puede quitarse si existe un índice XML en la tabla.

  Una columna no puede quitarse cuando:

  • Se utiliza en un índice.

  • Se utiliza en una restricción CHECK, FOREIGN KEY, UNIQUE o PRIMARY KEY.

  • Está asociada con un valor predeterminado definido con la palabra clave DEFAULT o enlazada a un objeto predeterminado.

  • Está enlazada a una regla.

  [!NOTA]

  Quitar una columna no recupera el espacio en disco de la columna. Tendrá que recuperar el espacio en disco de una columna quitada cuando el tamaño de fila de una tabla esté a punto de superar su límite o lo haya hecho ya. Recupere el espacio mediante la creación de un índice clúster de la tabla o regenerando un índice clúster existente mediante la utilización de ALTER INDEX. Para obtener más información acerca del impacto de quitar los tipos de datos LOB, vea esta entrada del blog de CSS.

• WITH <drop_clustered_constraint_option>
  Especifica que se han establecido una o más opciones para quitar restricciones en clúster.

• MAXDOP = max_degree_of_parallelism
  Invalida la opción de configuración grado máximo de paralelismo solo durante la operación. Para obtener más información, vea Establecer la opción de configuración del servidor Grado máximo de paralelismo.

  Utilice la opción MAXDOP para limitar el número de procesadores utilizados en la ejecución de planes paralelos. El máximo es 64 procesadores.

  max_degree_of_parallelism puede tener uno de los siguientes valores:

  • 1
    Suprime la generación de planes paralelos.

  • >1
    Restringe el número máximo de procesadores utilizados en una operación de índice paralelo para el número especificado.

  • 0 (valor predeterminado)
    Usa el número real de procesadores o menos, según la carga de trabajo actual del sistema.

  Para obtener más información, vea Configurar operaciones de índice en paralelo.

  [!NOTA]

  Las operaciones de índices en paralelo no están disponibles en todas las ediciones de SQL Server. Para obtener más información, vea Características compatibles con las ediciones de SQL Server 2012.

• ONLINE = { ON | OFF }
  Especifica si las tablas subyacentes y los índices asociados están disponibles para realizar consultas y modificar datos durante la operación de indización. El valor predeterminado es OFF. REBUILD se puede realizar como una operación ONLINE.

  • ON
    Los bloqueos de tabla de larga duración no se mantienen durante la operación de indización. Durante la fase principal de la operación de índice, solo se mantiene un bloqueo preventivo en la tabla de origen. De esta forma, las consultas o actualizaciones realizadas sobre la tabla y los índices subyacentes pueden continuar. Al principio de la operación, se mantiene un bloqueo compartido (S) sobre el objeto de origen durante un breve espacio de tiempo. Al final de la operación, durante un breve espacio de tiempo, se adquiere un bloqueo compartido (S) sobre el origen si se está creando un índice no clúster; o se adquiere un bloqueo SCH-M (modificación del esquema) cuando se crea o se quita un índice clúster en línea y cuando se regenera un índice clúster o no clúster. ONLINE no se puede establecer en ON cuando se crea un índice en una tabla temporal local. Solo se permite la operación de regeneración de montón de un único subproceso.

  • OFF
    Los bloqueos de tabla se aplican durante la operación de índice. Una operación de índice sin conexión para crear, volver a crear o quitar un índice clúster, o para volver a crear o quitar un índice no clúster, adquiere un bloqueo de modificación del esquema (Sch-M) de la tabla. Esto evita que todos los usuarios tengan acceso a la tabla subyacente durante la operación. Una operación de índice sin conexión que crea un índice no clúster adquiere un bloqueo compartido (S) en la tabla. Esto evita que se realicen actualizaciones en la tabla subyacente, pero permite la realización de operaciones de lectura, como instrucciones SELECT. Se permiten operaciones multiproceso de regeneración del montón.

  Para obtener más información, vea Cómo funcionan las operaciones de índice en línea.

  [!NOTA]

  Las operaciones de índices en línea no están disponibles en todas las ediciones de SQL Server. Para obtener más información, vea Características compatibles con las ediciones de SQL Server 2012.

• MOVE TO { partition_scheme_name**(column_name [ 1,** ... n] ) | filegroup | "default" }
  Especifica una ubicación a la que mover las filas de datos que se encuentran en el nivel hoja del índice en clúster. La tabla se mueve a la nueva ubicación. Esta opción solo se aplica a las restricciones que crean un índice clúster.

  [!NOTA]

  En este contexto, el valor predeterminado no es una palabra clave. Es un identificador para el grupo de archivos predeterminado y debe delimitarse, como en MOVE TO "default" o en MOVE TO [default]. Si se especifica "default", la opción QUOTED_IDENTIFIER debe estar ON para la sesión actual. Esta es la configuración predeterminada. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).

• { CHECK | NOCHECK } CONSTRAINT
  Especifica si constraint_name está habilitado o deshabilitado. Esta opción solo se puede utilizar con las restricciones FOREIGN KEY y CHECK. Cuando se especifica NOCHECK, la restricción se deshabilita y las posteriores inserciones o actualizaciones de la columna no se validan con las condiciones de la restricción. Las restricciones DEFAULT, PRIMARY KEY y UNIQUE no se pueden deshabilitar.

• ALL
  Especifica que todas las restricciones están deshabilitadas con la opción NOCHECK o habilitadas con la opción CHECK.

• { ENABLE | DISABLE } TRIGGER
  Especifica si trigger_name está habilitado o deshabilitado. Aunque un desencadenador esté deshabilitado, sigue estando definido para la tabla; sin embargo, si se ejecutan las instrucciones INSERT, UPDATE o DELETE en la tabla, las acciones del desencadenador no se ejecutan hasta que este se vuelva a habilitar.

• ALL
  Especifica si todos los desencadenadores de la tabla están habilitados o deshabilitados.

• trigger_name
  Especifica el nombre del desencadenador que se va a habilitar o deshabilitar.

• { ENABLE | DISABLE } CHANGE_TRACKING
  Especifica si el seguimiento de cambios está habilitado o deshabilitado para la tabla. El seguimiento de cambios está deshabilitado de manera predeterminada.

  Esta opción solo está disponible cuando el seguimiento de cambios está habilitado para la base de datos. Para obtener más información, vea Opciones de ALTER DATABASE SET (Transact-SQL).

  Para habilitar el seguimiento de cambios, la tabla debe tener una clave principal.

• WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )
  Especifica si Motor de base de datos realiza el seguimiento de las columnas sometidas a seguimiento de cambios que se actualizaron. El valor predeterminado es OFF.

• SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name**.** ] target_table [ PARTITION target_ partition_number_expression ]
  Modifica un bloqueo de datos de una de las formas siguientes:

  • Vuelve a asignar todos los datos de una tabla como una partición en una tabla con particiones ya existente.

  • Modifica una partición de una tabla con particiones a otra.

  • Vuelve a asignar todos los datos de una partición de una tabla con particiones a una tabla sin particiones ya existente.

  Si table es una tabla con particiones, se debe especificar source_partition_number_expression. Si target_table es una tabla con particiones, se debe especificar target_partition_number_expression. Si se vuelven a asignar los datos de una tabla como partición a una tabla con particiones ya existente, o se modifica una partición de una tabla con particiones a otra, la partición de destino debe existir y debe estar vacía.

  Si se vuelven a asignar los datos de una partición para formar una sola tabla, la tabla de destino debe ya estar creada y vacía. Tanto la tabla o partición de origen como la tabla o partición de destino deben residir en el mismo grupo de archivos. Los índices correspondientes, o particiones de índice, también deben residir en el mismo grupo de archivos. Son muchas las restricciones adicionales que se aplican a las particiones que se modifican. table y target_table no pueden ser iguales. target_table puede ser un identificador de varias partes.

  source_partition_number_expression y target_partition_number_expression son expresiones constantes que pueden hacer referencia a variables y funciones. Incluyen las variables de tipos definidos por el usuario y las funciones definidas por el usuario. No pueden hacer referencia a expresiones de Transact-SQL.

  Para obtener información sobre la restricción SWITCH cuando se use la replicación, vea Replicar tablas e índices con particiones.

• SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })
  Especifica dónde se almacenan los datos FILESTREAM.

  La ejecución de ALTER TABLE con la cláusula SET FILESTREAM_ON será correcta únicamente si la tabla no tiene columnas FILESTREAM. Las columnas FILESTREAM se pueden agregar utilizando una segunda instrucción ALTER TABLE.

  Si se especifica partition_scheme_name, se aplican las reglas para CREATE TABLE. La tabla ya debería tener particiones para los datos de la fila y su esquema de partición debe utilizar la misma función de partición y columnas que el esquema de partición de FILESTREAM.

  filestream_filegroup_name especifica el nombre de un grupo de archivos FILESTREAM. El grupo de archivos debe tener un archivo que esté definido para el grupo de archivos, utilizando para ello las instrucciones CREATE DATABASE o ALTER DATABASE; de lo contrario, se producirá un error.

  "default" especifica el grupo de archivos FILESTREAM con la propiedad DEFAULT establecida. Si no hay ningún grupo de archivos FILESTREAM, se produce un error.

  "NULL" especifica que se quitarán todas las referencias a los grupos de archivos FILESTREAM para la tabla. Se deben quitar primero todas las columnas FILESTREAM. Debe usar SET FILESTREAM_ON**="NULL"** para eliminar todos los datos FILESTREAM que estén asociados a una tabla.

• SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
  Especifica los métodos permitidos de extensión de bloqueo para una tabla.

  • AUTO
    Esta opción permite a Motor de base de datos de SQL Server seleccionar la granularidad de la extensión de bloqueo que sea adecuada para el esquema de tabla.

    • Si la tabla tiene particiones, la extensión del bloqueo se permitirá en la partición. Una vez realizada la extensión del bloqueo hasta el nivel de la partición, el bloqueo no se extenderá a la granularidad de TABLE más adelante.

    • Si la tabla no tiene particiones, la extensión del bloqueo se aplicará a la granularidad de TABLE.

  • TABLE
    La extensión de bloqueo se aplicará a la granularidad en el nivel de tabla, independientemente de que la tabla tenga o no particiones. Este comportamiento es el mismo que en SQL Server 2005. TABLE es el valor predeterminado.

  • DISABLE
    Evita la extensión de bloqueo en la mayoría de los casos. No siempre se evitan los bloqueos de nivel de la tabla. Por ejemplo, si está examinando una tabla que no tiene ningún índice clúster en el nivel de aislamiento serializable, Motor de base de datos debe realizar un bloqueo de la tabla para proteger la integridad de los datos.

• REBUILD
  Utilice la sintaxis de REBUILD WITH para volver a generar una tabla completa que incluya todas las particiones en una tabla con particiones. Si la tabla tiene un índice clúster, la opción REBUILD vuelve a generarlo. REBUILD se puede realizar como una operación ONLINE.

  Utilice la sintaxis de REBUILD PARTITION para volver a generar una partición única en una tabla con particiones.

• PARTITION = ALL
  Vuelve a generar todas las particiones al cambiar los valores de compresión de la partición.

• REBUILD WITH ( <rebuild_option> )
  Todas las opciones se aplican a una tabla con un índice clúster. Si la tabla no tiene un índice clúster, solo algunas de las opciones afectan a la estructura del montón.

  Cuando no se especifica un valor de compresión específico con la operación REBUILD, se usa el valor de compresión actual de la partición. Para devolver el valor actual, realice una consulta en la columna data_compression de la vista del catálogo sys.partitions.

  Para obtener una descripción completa de las opciones de recompilación, vea index_option (Transact-SQL).

• DATA_COMPRESSION
  Especifica la opción de compresión de datos para la tabla, el número de partición o el intervalo de particiones especificados. Las opciones son las siguientes:

  • NONE
    No se comprimen la tabla ni las particiones especificadas.

  • ROW
    La tabla o las particiones especificadas se comprimen utilizando la compresión de fila.

  • PAGE
    La tabla o las particiones especificadas se comprimen utilizando la compresión de página.

  Para volver a generar al mismo tiempo varias particiones, vea index_option (Transact-SQL). Si la tabla no tiene un índice clúster, al cambiar la compresión de datos se vuelven a generar el montón y los índices no clúster. Para obtener más información acerca de la compresión, vea Compresión de datos.

• column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
  Es el nombre del conjunto de columnas. Un conjunto de columnas es una representación XML sin tipo que combina todas las columnas dispersas de una tabla en una salida estructurada. No se puede agregar un conjunto de columnas a una tabla que contenga columnas dispersas. Para obtener más información sobre conjuntos de columnas, vea Usar conjuntos de columnas.

• { ENABLE | DISABLE } FILETABLE_NAMESPACE
  Habilita o deshabilita las restricciones definidas por el sistema en un objeto FileTable. Solo se puede utilizar con un objeto FileTable.

• SET ( FILETABLE_DIRECTORY = directory_name )
  Especifica el nombre de directorio de FileTable compatible con Windows. Este nombre debe ser único entre todos los nombres de directorio de FileTable en la base de datos. La comparación de unicidad no distingue mayúsculas de minúsculas, independientemente de la configuración de intercalación de SQL. Solo se puede utilizar con un objeto FileTable.

Comentarios

Para agregar nuevas filas de datos, utilice INSERT. Para quitar filas de datos, utilice DELETE o TRUNCATE TABLE. Para cambiar los valores de las filas existentes, utilice UPDATE.

Si hubiera algún plan de ejecución en la memoria caché del procedimiento que hace referencia a la tabla, ALTER TABLE los marca para que se vuelvan a compilar en la siguiente ejecución.

Cambiar el tamaño de una columna

Puede cambiar la longitud, precisión o escala de una columna especificando un nuevo tamaño para el tipo de datos de columna en la cláusula ALTER COLUMN. Si hay datos en la columna, el nuevo tamaño no puede ser menor que el tamaño máximo de los datos. Asimismo, la columna se puede definir en un índice, a menos que sea de un tipo de datos varchar, nvarchar o varbinary, y el índice no sea el resultado de una restricción KEY PRIMARY. Vea el ejemplo P.

Bloqueos y ALTER TABLE

Los cambios especificados en ALTER TABLE se ejecutan inmediatamente. Si los cambios requieren modificaciones de las filas de la tabla, ALTER TABLE actualiza las filas. ALTER TABLE adquiere un bloqueo de modificación del esquema (SCH-M) sobre la tabla para asegurar que ninguna otra conexión haga referencia ni a los metadatos de la tabla durante el cambio, excepto las operaciones de indización en línea que precisen un breve bloqueo SCH-M al final. En una operación ALTER TABLE…SWITCH, el bloqueo se adquiere tanto en las tablas de origen como en las de destino. Las modificaciones realizadas en la tabla se registran y son completamente recuperables. Los cambios que afectan a todas las filas de tablas muy grandes, como quitar una columna o, en algunas ediciones de SQL Server, agregar una columna NOT NULL con un valor predeterminado, pueden tardar mucho tiempo en completarse y generan muchos registros. Estas instrucciones ALTER TABLE deben ejecutarse con el mismo cuidado que cualquier instrucción INSERT, UPDATE o DELETE que afecte a un gran número de filas.

Agregar columnas NOT NULL como una operación en línea

En SQL Server 2012 Enterprise Edition, agregar una columna NOT NULL con un valor predeterminado es una operación en línea cuando el valor predeterminado es una constante de tiempo de ejecución. Esto significa que la operación se ha completado de forma casi instantánea, independientemente del número de filas de la tabla. Esto se debe a que las filas existentes en la tabla no se actualizan durante la operación; en su lugar, el valor predeterminado se almacena únicamente en los metadatos de la tabla y el valor se busca según sea necesario en las consultas que tienen acceso a estas filas. Este comportamiento es automático; no se requiere sintaxis adicional alguna para implementar la operación en línea aparte de la sintaxis de COLUMN ADD. Una constante de tiempo de ejecución es una expresión que genera el mismo valor en tiempo de ejecución para cada fila de la tabla independientemente de su determinismo. Por ejemplo, la expresión constante “mis datos temporales” o la función del sistema GETUTCDATETIME() son constantes de tiempo de ejecución. En cambio, las funciones NEWID() o NEWSEQUENTIALID() no son constantes de tiempo de ejecución porque se genera un valor único para cada fila de la tabla. Cuando se agrega una columna NOT NULL con un valor predeterminado que no es una constante de tiempo de ejecución, se realiza siempre sin conexión y se adquiere un bloqueo exclusivo (SCH-M) mientras dura la operación.

Mientras que las filas existentes hacen referencia al valor almacenado en los metadatos, el valor predeterminado se almacena en la fila para aquellas filas nuevas que se inserten y que no especifiquen otro valor para la columna. El valor predeterminado que se almacena en los metadatos se traslada a una fila existente cuando se actualiza la fila (aunque la columna real no se especifique en la instrucción UPDATE) o bien, si la tabla o el índice clúster se vuelve a generar.

Las columnas de tipo varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography o los UDT de CLR no se pueden agregar en una operación en línea. Una columna no se puede agregar en línea si al hacerlo provoca que el tamaño máximo posible de fila supere el límite de 8.060 bytes. En este caso, la columna se agrega como una operación sin conexión.

Ejecutar planes paralelos

En Microsoft SQL Server 2012 Enterprise, el número de procesadores utilizados para ejecutar una sola instrucción ALTER TABLE ADD (basada en índices) CONSTRAINT o DROP (índice clúster) CONSTRAINT viene determinado por la opción de configuración max degree of parallelism y la carga de trabajo actual. Si el Motor de base de datos detecta que el sistema está ocupado, el grado de paralelismo de la operación se reduce automáticamente antes de comenzar la ejecución de la instrucción. Puede configurar manualmente el número de procesadores que se utilizan para ejecutar la instrucción si especifica la opción MAXDOP. Para obtener más información, vea Establecer la opción de configuración del servidor Grado máximo de paralelismo.

Tablas con particiones

Además de realizar operaciones SWITCH que implican a tablas con particiones, ALTER TABLE puede utilizarse para cambiar el estado de las columnas, restricciones y desencadenadores de una tabla con particiones, de la misma forma que se utiliza para las tablas sin particiones. Sin embargo, esta sentencia no puede utilizarse para cambiar la forma en que se realizan las particiones de la tabla misma. Para volver a realizar las particiones de una tabla con particiones, utilice ALTER PARTITION SCHEME y ALTER PARTITION FUNCTION. Además, no puede cambiar el tipo de datos de una columna de una tabla con particiones.

Realizar restricciones en tablas con vistas enlazadas a esquema

Las restricciones que se aplican a instrucciones ALTER TABLE en tablas con vistas enlazadas a esquema son las mismas que las restricciones que se aplican actualmente cuando se alteran tablas con un solo índice. Se permite agregar una columna. No obstante, no se permite quitar ni cambiar una columna que participa en una vista enlazada a esquema. Si la instrucción ALTER TABLE requiere la alteración de una columna que se utiliza en una vista enlazada a esquema, se produce un error en ALTER TABLE y el Motor de base de datos genera un mensaje de error. Para obtener más información acerca de los enlaces a esquemas y vistas indizadas, vea CREATE VIEW (Transact-SQL).

La creación de una vista enlazada a esquema que hace referencia a las tablas no afecta a la adición ni a la eliminación de desencadenadores en las tablas base.

Índices y ALTER TABLE

Los índices creados como parte de una restricción se quitan cuando se quita la restricción. Los índices que se crearon con CREATE INDEX deben quitarse con DROP INDEX. La instrucción ALTER INDEX se puede emplear para volver a crear un índice que es parte de una definición de restricción; no es necesario quitar o agregar de nuevo la restricción con ALTER TABLE.

Todos los índices y restricciones basados en una columna deben eliminarse para que se pueda quitar la columna.

Cuando se elimina una restricción que ha creado un índice clúster, las filas de datos que se han almacenado en el nivel hoja del índice clúster se almacenan en una tabla no clúster. Puede quitar el índice clúster y mover la tabla resultante a otro grupo de archivos o esquema de partición en una sola transacción especificando la opción MOVE TO. La opción MOVE TO tiene las siguientes restricciones:

• MOVE TO no es válido para vistas indizadas o índices no clúster.

• El esquema de partición o el grupo de archivos debe existir previamente.

• Si no se especifica MOVE TO, la tabla se ubicará en el mismo esquema de partición o grupo de archivos que se definió para el índice clúster.

Cuando quita un índice clúster, puede especificar la opción ONLINE = ON para que la transacción DROP INDEX no bloquee las consultas y modificaciones en los datos subyacentes y en los índices no clúster asociados.

ONLINE = ON tiene las siguientes restricciones:

• ONLINE = ON no es válida para índices clúster que también estén deshabilitados. Los índices deshabilitados deben quitarse con ONLINE = OFF.

• Solo un índice puede quitarse cada vez.

• ONLINE = ON no es válida para las vistas indizadas, índices no clúster ni índices de tablas temporales locales.

Para quitar un índice clúster, se necesita un espacio temporal en disco del mismo tamaño que el del índice clúster existente. Este espacio adicional se libera en cuanto se completa la operación.

[!NOTA]

Las opciones enumeradas en <drop_clustered_constraint_option> se aplican a índices clúster de tablas y no pueden aplicarse a índices clúster de vistas o de índices no clúster.

Replicar cambios de esquema

De forma predeterminada, cuando se ejecuta ALTER TABLE en una tabla publicada en un publicador de SQL Server, el cambio se propaga a todos los suscriptores de SQL Server. Esta funcionalidad tiene algunas restricciones y se puede deshabilitar. Para obtener más información, vea Realizar cambios de esquema en bases de datos de publicaciones.

Compresión de datos

En las tablas del sistema no se puede habilitar la compresión. . Si la tabla es un montón, la operación de regeneración en modo ONLINE será de un solo subproceso. Utilice el modo OFFLINE para una operación de regeneración de montón de varios subprocesos. Para obtener más información acerca de la compresión de datos, vea Compresión de datos.

Para evaluar cómo afecta el cambio del estado de compresión a una tabla, índice o partición, utilice el procedimiento almacenado sp_estimate_data_compression_savings.

Las restricciones siguientes se aplican a las tablas con particiones:

• No se puede cambiar la configuración de compresión de una partición única si la tabla tiene índices no alineados.

• La sintaxis ALTER TABLE <table> REBUILD PARTITION ... vuelve a generar la partición especificada.

• La sintaxis ALTER TABLE <tabla> REBUILD WITH… vuelve a generar todas las particiones.

Quitar columnas NTEXT

Cuando se quitan columnas NTEXT, la limpieza de los datos eliminados se produce como una operación serializada en todas las filas. Esto puede requerir bastante tiempo. Cuando se quita una columna NTEXT de una tabla con muchas filas, actualice primero la columna NTEXT a NULL y, a continuación, quite la columna. Esto puede realizarse con operaciones en paralelo y puede ser mucho más rápido.

Soporte de compatibilidad

La instrucción ALTER TABLE solo permite nombres de tabla de dos partes (esquema.objeto). En SQL Server 2012, la especificación de un nombre de tabla con uno de los siguientes formatos produce el error 117 en tiempo de compilación.

• servidor.baseDeDatos.esquema.tabla

• .baseDeDatos.esquema.tabla

• ..esquema.tabla

En versiones anteriores, al especificar el formato servidor.baseDeDatos.esquema.tabla se devolvía el error 4902. La especificación del formato .baseDeDatos.esquema.tabla o ..esquema.tabla se realizaba correctamente.

Para resolver el problema, quite el uso de un prefijo de 4 partes.

Permisos

Requiere el permiso ALTER en la tabla.

Los permisos ALTER TABLE se aplican a las tablas relacionadas con una instrucción ALTER TABLE SWITCH. Los datos que se modifican heredan la seguridad de la tabla de destino.

Si se define alguna columna de la instrucción ALTER TABLE para que sea un tipo definido por el usuario de CLR (Common Language Runtime) o un tipo de datos del alias, se necesita el permiso REFERENCES sobre el tipo.

La adición de una columna que actualiza las filas de la tabla requiere el permiso UPDATE en ella. Por ejemplo, agregar una columna NOT NULL con un valor predeterminado o agregar una columna de identidad si la tabla no está vacía.

Ejemplos

Categoría	Elementos de sintaxis ofrecidos
Agregar columnas y restricciones	ADD • PRIMARY KEY con opciones de índice • columnas dispersas y conjuntos de columnas •
Quitar columnas y restricciones	DROP
Modificar una definición de columna	cambiar tipo de datos • cambiar tamaño de columna • intercalación
Modificar una definición de tabla	DATA_COMPRESSION • SWITCH PARTITION • LOCK ESCALATION • seguimiento de cambios
Deshabilitar y habilitar restricciones y desencadenadores	CHECK • NO CHECK • ENABLE TRIGGER • DISABLE TRIGGER

Agregar columnas y restricciones

En los ejemplos de esta sección se muestra cómo agregar columnas y restricciones a una tabla.

A.Agregar una columna nueva

En el ejemplo siguiente se agrega una columna que permite valores NULL y a la que no se han proporcionado valores mediante una definición DEFAULT. En la nueva columna, cada fila tendrá valores NULL.

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO

B.Agregar una columna con una restricción

En el ejemplo siguiente se agrega una nueva columna con una restricción UNIQUE.

CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL 
    CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

C.Agregar una restricción CHECK no comprobada a una columna existente

En el ejemplo siguiente se agrega una restricción a una columna existente de la tabla. La columna tiene un valor que infringe la restricción. Por tanto, WITH NOCHECK se usa para evitar que la restricción se valide en las filas existentes y para poder agregar la restricción.

CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

D.Agregar una restricción DEFAULT a una columna existente

En el ejemplo siguiente se crea una tabla con dos columnas y se inserta un valor en la primera columna, y la otra columna sigue siendo NULL. A continuación se agrega una restricción DEFAULT a la segunda columna. Para comprobar que se aplica el valor predeterminado, se inserta otro valor en la primera columna y se consulta la tabla.

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

E.Agregar varias columnas con restricciones

En el ejemplo siguiente se agregan varias columnas con restricciones que se definen con la nueva columna. La primera columna nueva tiene una propiedad IDENTITY. Cada fila de la tabla tiene nuevos valores incrementales en la columna de identidad.

CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD 

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, 

-- Add a column that references another column in the same table.
column_c INT NULL  
CONSTRAINT column_c_fk 
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that 
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL 
CONSTRAINT column_d_chk
CHECK 
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

F.Agregar una columna que admite valores NULL con valores predeterminados

En el ejemplo siguiente se agrega una columna que acepta valores NULL con una definición DEFAULT y se usa WITH VALUES para proporcionar valores para cada fila existente en la tabla. Si no se utiliza WITH VALUES, cada fila tiene el valor NULL en la nueva columna.

USE AdventureWorks2012 ; 
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

G.Crear una restricción PRIMARY KEY con opciones de índice

En el ejemplo siguiente se crea la restricción PRIMARY KEY PK_TransactionHistoryArchive_TransactionID y se establecen las opciones FILLFACTOR, ONLINE y PAD_INDEX. El índice clúster resultante tendrá el mismo nombre que la restricción.

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);
GO

H.Agregar una columna dispersa

En los ejemplos siguientes se muestra cómo agregar y modificar columnas dispersas en la tabla T1. El código para crear la tabla T1 es el siguiente.

CREATE TABLE T1
(C1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
C4 int ) ;
GO

Para agregar una columna dispersa adicional C5, ejecute la instrucción siguiente.

ALTER TABLE T1
ADD C5 char(100) SPARSE NULL ;
GO

Para convertir la columna no dispersa C4 en una columna dispersa, ejecute la instrucción siguiente.

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

Para convertir la columna dispersa C4 en una columna no dispersa, ejecute la instrucción siguiente.

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

I.Agregar un conjunto de columnas

En los ejemplos siguientes se muestra cómo agregar una columna a la tabla T2. No se puede agregar un conjunto de columnas a una tabla si esta ya contiene columnas dispersas. El código para crear la tabla T2 es el siguiente.

CREATE TABLE T2
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

Las tres instrucciones siguientes agregan un conjunto de columnas denominado CS y, a continuación, modifican las columnas C2 y C3 a SPARSE.

ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO

ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ; 
GO

ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO

[Arriba]

Quitar columnas y restricciones

En los ejemplos de esta sección se muestra cómo quitar columnas y restricciones.

A.Quitar una o varias columnas

En el primer ejemplo se modifica una tabla para quitar una columna. En el segundo ejemplo se quitan varias columnas.

CREATE TABLE dbo.doc_exb 
    (column_a INT
     ,column_b VARCHAR(20) NULL
     ,column_c datetime
     ,column_d int) ;
GO
-- Remove a single column.
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
-- Remove multiple columns.
ALTER TABLE dbo.doc_exb DROP COLUMN column_c, column_d;

B.Quitar restricciones y columnas

En el primer ejemplo se quita una restricción UNIQUE de una tabla. En el segundo ejemplo se quitan dos restricciones y una sola columna.

CREATE TABLE dbo.doc_exc ( column_a int NOT NULL CONSTRAINT my_constraint UNIQUE) ;
GO

-- Example 1. Remove a single constraint.
ALTER TABLE dbo.doc_exc DROP my_constraint ;
GO

DROP TABLE dbo.doc_exc;
GO

CREATE TABLE dbo.doc_exc ( column_a int  
                          NOT NULL CONSTRAINT my_constraint UNIQUE
                          ,column_b int 
                          NOT NULL CONSTRAINT my_pk_constraint PRIMARY KEY) ;
GO

-- Example 2. Remove two constraints and one column
-- The keyword CONSTRAINT is optional. The keyword COLUMN is required.
ALTER TABLE dbo.doc_exc 

    DROP CONSTRAINT CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b ;
GO

C.Quitar una restricción PRIMARY KEY en modo ONLINE

En el ejemplo siguiente se elimina una restricción KEY PRIMARY con la opción ONLINE establecida en ON.

USE AdventureWorks2012;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

D.Agregar y quitar una restricción FOREIGN KEY

En el ejemplo siguiente se crea la tabla ContactBackup y, a continuación, se modifica la tabla; primero se agrega una restricción FOREIGN KEY que hace referencia a la tabla Person.Person y, a continuación, se quita la restricción FOREIGN KEY.

USE AdventureWorks2012 ;
GO
CREATE TABLE Person.ContactBackup
    (ContactID int) ;
GO

ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Person (BusinessEntityID) ;
GO

ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO

DROP TABLE Person.ContactBackup ;

[Arriba]

Modificar una definición de columna

A.Cambiar el tipo de datos de una columna

En el ejemplo siguiente se modifica una columna de una tabla de INT a DECIMAL.

CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE dbo.doc_exy ;
GO

B.Cambiar el tamaño de una columna

En el ejemplo siguiente se aumenta el tamaño de una columna varchar y la precisión y la escala de una columna decimal. Dado que las columnas contienen datos, solo se puede aumentar el tamaño de columna. Observe también que col_a se define en un índice único. Aún se puede aumentar el tamaño de col_a porque el tipo de datos es varchar y el índice no es el resultado de una restricción KEY PRIMARY.

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2));
GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25);
GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4);
GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

C.Cambiar la intercalación de columnas

En el siguiente ejemplo se muestra cómo cambiar la intercalación de una columna. Primero se crea una tabla con la intercalación predeterminada del usuario.

CREATE TABLE T3
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

Después, la intercalación de la columna C2 se cambia a Latin1_General_BIN. Observe que el tipo de datos se requiere, incluso aunque no se cambie.

ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN;
GO

[Arriba]

Modificar una definición de tabla

En los ejemplos de esta sección se muestra cómo modificar la definición de una tabla.

A.Modificar una tabla para cambiar la compresión

En el ejemplo siguiente se cambia la compresión de una tabla sin particiones. Se volverá a generar el montón o el índice clúster. Si la tabla es un montón, se volverán a generar todos los índices no clúster.

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

En el ejemplo siguiente se cambia la compresión de una tabla con particiones. La sintaxis REBUILD PARTITION = 1 hace que solo se vuelva a generar la partición número 1.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

La misma operación que utiliza la sintaxis alternativa siguiente hace que se vuelvan a generar todas las particiones de la tabla.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

Para obtener más ejemplos de compresión de datos, vea Compresión de datos.

B.Cambiar las particiones entre tablas

En el ejemplo siguiente se crea una tabla con particiones, suponiendo que el esquema de partición myRangePS1 ya esté creado en la base de datos. A continuación, se crea una tabla sin particiones con la misma estructura que la tabla con particiones y en el mismo grupo de archivos que PARTITION 2 de la tabla PartitionTable. Los datos de PARTITION 2 de la tabla PartitionTable se cambian entonces a la tabla NonPartitionTable.

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

C.Permitir la extensión de bloqueo en tablas con particiones

En el ejemplo siguiente se habilita la extensión de bloqueo al nivel de partición en una tabla con particiones. Si la tabla no tiene particiones, la extensión de bloqueo se establece en el nivel TABLE.

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO);
GO

D.Configurar el seguimiento de cambios en una tabla

En el ejemplo siguiente se habilita el seguimiento de cambios en la tabla Person.Person.

USE AdventureWorks2012;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING;

El ejemplo siguiente habilita el seguimiento de cambios y el seguimiento de las columnas que se actualizan durante un cambio.

USE AdventureWorks2012;
GO
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

En el ejemplo siguiente se deshabilita el seguimiento de cambios en la tabla Person.Person.

USE AdventureWorks2012;
Go
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;

[Arriba]

Deshabilitar y habilitar restricciones y desencadenadores

A.Deshabilitar y volver a habilitar una restricción

En el ejemplo siguiente se deshabilita una restricción que limita los salarios aceptados en los datos. NOCHECK CONSTRAINT se usa con ALTER TABLE para deshabilitar la restricción y permitir una inserción que normalmente infringiría la restricción. CHECK CONSTRAINT vuelve a habilitar la restricción.

CREATE TABLE dbo.cnst_example 
(id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

B.Deshabilitar y volver a habilitar un desencadenador

En el ejemplo siguiente se usa la opción DISABLE TRIGGER de ALTER TABLE para deshabilitar el desencadenador y permitir una inserción que normalmente infringiría el desencadenador. Después se usa ENABLE TRIGGER para volver a habilitar el desencadenador.

CREATE TABLE dbo.trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
    print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO

[Arriba]

Vea también

Referencia

sys.tables (Transact-SQL)

sp_rename (Transact-SQL)

CREATE TABLE (Transact-SQL)

DROP TABLE (Transact-SQL)

sp_help (Transact-SQL)

ALTER PARTITION SCHEME (Transact-SQL)

ALTER PARTITION FUNCTION (Transact-SQL)

EVENTDATA (Transact-SQL)