ALTER TABLE (Transact-SQL)

 

CETTE RUBRIQUE S’APPLIQUE À : ouiSQL Server (à partir de la version 2008)ouiAzure SQL DatabaseouiAzure SQL Data WarehouseouiParallel Data Warehouse

Modifie la définition d'une table en changeant, en ajoutant ou en supprimant des colonnes et des contraintes, en réaffectant et reconstruisant des partitions, en désactivant ou en activant des contraintes et des déclencheurs.

Topic link icon Conventions de la syntaxe Transact-SQL

-- Syntax for SQL Server and Azure SQL Database  
  
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 | HIDDEN }  
      | { ADD | DROP } MASKED [ WITH ( FUNCTION = ' mask_function ') ]  
    }   
    [ WITH ( ONLINE = ON | OFF ) ]  
    | [ WITH { CHECK | NOCHECK } ]  
  
    | ADD   
    {   
        <column_definition>  
      | <computed_column_definition>  
      | <table_constraint>   
      | <column_set_definition>   
    } [ ,...n ]  
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START   
                   [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ] DEFAULT constant_expression [WITH VALUES] ,  
            system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END   
                   [ HIDDEN ] [ NOT NULL ]  [ CONSTRAINT constraint_name ] DEFAULT constant_expression [WITH VALUES] ,  
         ]  
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )  
    | DROP   
     [ {  
         [ CONSTRAINT ]  [ IF EXISTS ]  
         {   
              constraint_name   
              [ WITH   
               ( <drop_clustered_constraint_option> [ ,...n ] )   
              ]   
          } [ ,...n ]  
          | COLUMN  [ IF EXISTS ]  
          {  
              column_name   
          } [ ,...n ]  
          | PERIOD FOR SYSTEM_TIME  
     } [ ,...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 ]  
        [ WITH ( <low_lock_priority_wait> ) ]  
    | SET   
        (  
            [ FILESTREAM_ON =   
                { partition_scheme_name | filegroup | "default" | "NULL" } ]  
            | SYSTEM_VERSIONING =   
                  {   
                      OFF   
                  | ON   
                      [ ( HISTORY_TABLE = schema_name . history_table_name   
                          [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] 
                          [, HISTORY_RETENTION_PERIOD = 
                          { 
                               INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS } 
                          } 
                          ]  
                        )  
                      ]  
                  }  
          )  
    | REBUILD   
      [ [PARTITION = ALL]  
        [ WITH ( <rebuild_option> [ ,...n ] ) ]   
      | [ PARTITION = partition_number   
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]  
        ]  
      ]  
  
    | <table_option>  
  
    | <filetable_option>  
  
    | <stretch_configuration>  
  
}  
[ ; ]  
  
-- 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 ) ]  
    }  
  
<stretch_configuration> ::=  
    {  
      SET (  
        REMOTE_DATA_ARCHIVE   
        {  
            = ON (  <table_stretch_options>  )  
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )  
          | ( <table_stretch_options> [, ...n] )  
        }  
            )  
    }  
  
<table_stretch_options> ::=  
    {  
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]  
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }  
    }  
  
<single_partition_rebuild__option> ::=  
{  
      SORT_IN_TEMPDB = { ON | OFF }  
    | MAXDOP = max_degree_of_parallelism  
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} }  
    | ONLINE = { ON [( <low_priority_lock_wait> ) ] | OFF }  
}  
  
<low_priority_lock_wait>::=  
{  
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ], ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )   
}  

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
ALTER TABLE [ database_name . [schema_name ] . | schema_name. ] source_table_name   
{  
    ALTER COLUMN column_name  
        {   
            type_name [ ( precision [ , scale ] ) ]   
            [ COLLATE Windows_collation_name ]   
            [ NULL | NOT NULL ]   
        }  
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]  
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]  
    | REBUILD {  
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ] 
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      } 
    | { SPLIT | MERGE } RANGE (boundary_value)  
    | SWITCH [ PARTITION source_partition_number  
        TO target_table_name [ PARTITION target_partition_number ]  
}  
[;]  
  
<column_definition>::=  
{  
    column_name  
    type_name [ ( precision [ , scale ] ) ]   
    [ <column_constraint> ]  
    [ COLLATE Windows_collation_name ]  
    [ NULL | NOT NULL ]  
}  
  
<column_constraint>::=  
    [ CONSTRAINT constraint_name ] DEFAULT constant_expression  

<rebuild_option > ::=   
{  
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]   
}

<single_partition_rebuild_option > ::=   
{  
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }  
}  

database_name
Nom de la base de données dans laquelle la table a été créée.

schema_name
Nom du schéma auquel appartient la table.

table_name
Nom de la table à modifier. Si la table ne se trouve pas dans la base de données active ou si elle n'est pas contenue dans le schéma appartenant à l'utilisateur actif, la base de données et le schéma doivent être explicitement spécifiés.

ALTER COLUMN
Spécifie que la colonne nommée doit être modifiée.

La colonne modifiée ne peut pas être l'une des colonnes suivantes :

  • Une colonne avec un timestamp type de données.

  • la colonne ROWGUIDCOL de la table ;

  • une colonne calculée ou utilisée dans une colonne calculée ;

  • Utilisé dans les statistiques générées par l’instruction CREATE STATISTICS, sauf si la colonne est un varchar, nvarchar, ou varbinary type de données, le type de données n’est pas modifié et la nouvelle taille est égale ou supérieure à l’ancienne, ou si la colonne est passée de not null en null. Vous devez d'abord supprimer les statistiques à l'aide de l'instruction DROP STATISTICS. Les statistiques créées automatiquement par l'optimiseur de requête sont automatiquement supprimées par ALTER COLUMN.

  • une colonne utilisée dans une contrainte PRIMARY KEY ou [FOREIGN KEY] REFERENCES ;

  • une colonne utilisée dans une contrainte CHECK ou UNIQUE. Cependant, la modification de la longueur d'une colonne de longueur variable utilisée dans une contrainte CHECK ou UNIQUE est autorisée.

  • une colonne associée à une définition par défaut. Cependant, il est possible de modifier la longueur, la précision ou l'échelle d'une colonne si le type de données n'est pas modifié.

    Type de données de texte, ntext et image colonnes peuvent être modifiées uniquement des manières suivantes :

    • texte à varchar (max), nvarchar (max), ou xml

    • ntext à varchar (max), nvarchar (max), ou xml

    • image à varbinary (max)

    Certaines modifications de type de données peuvent entraîner une modification des données. Par exemple, remplacer un nchar ou nvarchar colonne char ou varchar peut entraîner la conversion de caractères étendus. Pour plus d’informations, consultez CAST et CONVERT (Transact-SQL). La réduction de la précision ou de l'échelle d'une colonne peut tronquer les données.

    Vous ne pouvez pas modifier le type de données d'une colonne d'une table partitionnée.

Le type de données de colonnes incluses dans un index ne peut pas être modifié, sauf si la colonne est un varchar, nvarchar, ou varbinary type de données, et la nouvelle taille est égale ou supérieure à l’ancienne.

Impossible de modifier les colonnes incluses dans une contrainte de clé primaire de NON NULL à NULL.

Si la colonne modifiée est chiffrée avec CHIFFRÉ, vous pouvez modifier le type de données pour un type de données compatible (par exemple, INT en BIGINT), mais vous ne pouvez pas modifier les paramètres de chiffrement.

nom_colonne
Nom de la colonne à ajouter, modifier ou supprimer. column_name peut comporter un maximum de 128 caractères. Pour les nouvelles colonnes, column_name peut être omis pour les colonnes créées avec une timestamp type de données. Le nom timestamp est utilisé si aucun column_name est spécifié pour un timestamp colonne de type de données.

[ type_schema_name. ] nom_type
Nouveau type de données de la colonne modifiée ou type de données de la colonne ajoutée. TYPE_NAME ne peut pas être spécifié pour les colonnes existantes de tables partitionnées. TYPE_NAME peut être l’une des opérations suivantes :

  • type de données système SQL Server ;

  • type de données alias dérivé d'un type de données système SQL Server. Les types de données alias sont créés à l'aide de l'instruction CREATE TYPE avant de pouvoir être utilisés dans la définition d'une table ;

  • type .NET Framework défini par l'utilisateur et schéma auquel il appartient. Les types de données .NET Framework définis par l'utilisateur sont créés avec l'instruction CREATE TYPE avant de pouvoir être utilisés dans une définition de table.

Voici les critères pour type_name d’une colonne modifiée :

  • Le type de données précédent doit pouvoir être implicitement converti vers le nouveau type de données.

  • TYPE_NAME ne peut pas être timestamp.

  • Les valeurs par défaut ANSI_NULL sont toujours activées pour ALTER COLUMN ; si l'option n'est pas spécifiée, la colonne accepte les valeurs NULL.

  • Le remplissage ANSI_PADDING est toujours activé (ON) pour ALTER COLUMN.

  • Si la colonne modifiée est une colonne d’identité, nouveau_type_de_données doit être un type de données qui prend en charge la propriété identity.

  • La configuration actuelle de SET ARITHABORT est ignorée. ALTER TABLE fonctionne comme si l'option ARITHABORT était activée (ON).

System_CAPS_ICON_note.jpg Remarque


Si la clause COLLATE n'est pas spécifiée, la modification du type de données d'une colonne entraîne une modification du classement, qui est remplacé par le classement par défaut de la base de données.

précision
Précision du type de données spécifié. Pour plus d’informations sur les valeurs de précision valides, consultez la page précision, échelle et longueur &#40 ; Transact-SQL &#41 ;.

mise à l’échelle
Échelle du type de données spécifié. Pour plus d’informations sur les valeurs d’échelle valides, consultez la page précision, échelle et longueur &#40 ; Transact-SQL &#41 ;.

max
S’applique uniquement à la varchar, nvarchar, et varbinary des types de données pour stocker 2 ^ 31-1 octets de données binaires et caractère des données Unicode.

xml_schema_collection

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL (Aperçu dans certaines régions).

S’applique uniquement à la xml type de données pour associer un schéma XML au type. Avant de taper un xml une colonne à une collection de schémas, la collection de schémas doit d’abord être créée dans la base de données à l’aide de CREATE XML SCHEMA COLLECTION.

COLLATE < collation_name >
Spécifie le nouveau classement pour la colonne modifiée. Si l'argument n'est pas spécifié, c'est le classement par défaut de la base de données qui est affecté à la colonne. Le nom du classement peut être un nom de classement Windows ou SQL. Pour une liste et plus d’informations, consultez nom de classement Windows &#40 ; Transact-SQL &#41 ; et SQL Server nom du classement &#40 ; Transact-SQL &#41 ;.

La clause COLLATE peut être utilisée pour modifier seulement les classements des colonnes de la char, varchar, nchar, et nvarchar des types de données. Pour modifier le classement d'une colonne de type de données alias défini par l'utilisateur, vous devez exécuter des instructions ALTER TABLE distinctes pour modifier le type de données de la colonne en type de données système SQL Server et modifier son classement, puis pour modifier à nouveau le type de données de la colonne en type de données alias.

ALTER COLUMN ne peut pas modifier un classement si une ou plusieurs des conditions suivantes sont remplies :

  • Une contrainte CHECK, une contrainte FOREIGN KEY ou des colonnes calculées font référence à la colonne modifiée.

  • Un index, des statistiques ou un index de recherche en texte intégral sont créés sur la colonne. Les statistiques créées automatiquement sur la colonne modifiée sont supprimées si le classement de la colonne est modifié.

  • Une vue ou une fonction liée à un schéma fait référence à la colonne.

Pour plus d’informations, consultez COLLATE (Transact-SQL).

NULL | NOT NULL
Spécifie si la colonne accepte les valeurs NULL. Les colonnes qui n'acceptent pas les valeurs NULL peuvent être ajoutées à l'aide de l'instruction ALTER TABLE seulement si une valeur par défaut a été définie pour celles-ci ou si la table est vide. Il est possible de spécifier NOT NULL pour des colonnes calculées seulement si PERSISTED est également spécifié. Si la nouvelle colonne accepte les valeurs null et qu’aucune valeur par défaut est spécifiée, la nouvelle colonne contient une valeur null pour chaque ligne de la table. Si la nouvelle colonne accepte les valeurs NULL et si une définition de valeur par défaut est ajoutée avec la nouvelle colonne, l'option WITH VALUES peut être utilisée pour stocker la valeur par défaut dans la nouvelle colonne pour chaque ligne existante de la table.

Si la nouvelle colonne n'accepte pas les valeurs NULL et si la table n'est pas vide, une définition de valeur par défaut DEFAULT doit être ajoutée à la nouvelle colonne. La nouvelle colonne charge automatiquement la valeur par défaut dans les nouvelles colonnes de chaque ligne existante.

L'option NULL peut être spécifiée dans l'instruction ALTER COLUMN pour forcer une colonne NOT NULL à accepter les valeurs NULL, excepté pour les colonnes soumises à des contraintes PRIMARY KEY. L'option NOT NULL peut être spécifiée dans ALTER COLUMN seulement si la colonne ne contient pas de valeurs NULL. Les valeurs NULL doivent être mises à jour avec une valeur quelconque avant que ALTER COLUMN NOT NULL soit autorisé. Par exemple :

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

Lorsque vous créez ou modifiez une table à l'aide des instructions CREATE TABLE ou ALTER TABLE, les paramètres de la base de données et de la session influencent et éventuellement modifient la possibilité de valeurs NULL pour le type de données utilisé dans la définition d'une colonne. Nous vous recommandons de définir toujours explicitement une colonne comme NULL ou NOT NULL lorsqu'il s'agit de colonnes non calculées.

Si vous ajoutez une colonne avec un type de données défini par l'utilisateur, définissez la colonne avec la même possibilité de valeur Null que le type de données défini par l'utilisateur et spécifiez une valeur par défaut pour la colonne. Pour plus d’informations, consultez CREATE TABLE (Transact-SQL).

System_CAPS_ICON_note.jpg Remarque


Si NULL ou NOT NULL sont spécifiées avec ALTER COLUMN, nouveau_type_de_données [(précision [, échelle ])] doit également être spécifié. Si vous ne modifiez pas le type de données, la précision ou l'échelle, spécifiez les valeurs actuelles de la colonne.

[ {ADD | DROP} ROWGUIDCOL ]

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Spécifie que la propriété ROWGUIDCOL est supprimée de la colonne spécifiée ou qu'elle lui est ajoutée. ROWGUIDCOL indique que la colonne est une colonne d'identificateur global unique (GUID). Seul uniqueidentifier colonne par table peut être désignée comme colonne ROWGUIDCOL et la propriété ROWGUIDCOL peut être affectée qu’à un uniqueidentifier colonne. La propriété ROWGUIDCOL ne peut pas être affectée à une colonne dont le type de données est défini par l'utilisateur.

ROWGUIDCOL n'impose pas l'unicité des valeurs stockées dans la colonne et ne génère pas automatiquement des valeurs pour les nouvelles lignes insérées dans la table. Pour générer des valeurs uniques pour chaque colonne, utilisez la fonction NEWID sur des instructions INSERT, ou définissez la fonction NEWID par défaut pour la colonne.

[ {ADD | DROP} PERSISTED ]
Spécifie que la propriété PERSISTED est ajoutée à ou supprimée de la colonne spécifiée. La colonne doit être une colonne calculée définie avec une expression déterministe. Pour les colonnes spécifiées avec la propriété PERSISTED, le Moteur de base de données stocke physiquement les valeurs calculées dans la table et met à jour les valeurs lorsque d'autres colonnes dont dépend la colonne calculée sont mises à jour. Si vous marquez une colonne calculée comme PERSISTED, vous pouvez créer des index sur des colonnes calculées définies sur des expressions qui sont déterministes mais pas précises. Pour plus d'informations, consultez Indexes on Computed Columns.

Toute colonne calculée utilisée comme colonne de partitionnement d'une table partitionnée doit être explicitement marquée comme PERSISTED.

DROP NOT FOR REPLICATION

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Spécifie que les valeurs sont incrémentées dans les colonnes d'identité lorsque les agents de réplication effectuent des opérations d'insertion. Cette clause peut être spécifiée que si column_name est une colonne d’identité.

SPARSE
Indique que la nouvelle colonne est une colonne éparse. Le stockage des colonnes éparses est optimisé pour les valeurs Null. Les colonnes éparses ne peuvent pas être désignées comme NOT NULL. Le fait de convertir une colonne éparse en colonne non éparse ou inversement a pour effet de verrouiller la table pendant la durée de l'exécution de la commande. Vous devrez peut-être utiliser la clause REBUILD pour récupérer de l'espace. Pour plus d’informations sur les colonnes éparses et les restrictions supplémentaires, consultez la page utilisation des colonnes éparses.

Ajouter MASQUÉ AVEC (FONCTION = ' mask_function »)

S’applique à: SQL Server 2016 via SQL Server 2016, Base de données SQL.

Spécifie un masque de données dynamiques. mask_function est le nom de la fonction de masquage avec les paramètres appropriés. Trois fonctions sont disponibles :

  • () par défaut

  • email()

  • partial()

  • Random()

Pour supprimer un masque, utilisez DROP MASKED. Paramètres de fonction, consultez masquage dynamique des données.

AVEC (ONLINE = ON | OFF) < s’applique à la modification d’une colonne>

S’applique à: SQL Server 2016 via SQL Server 2016, Base de données SQL.

Permet d'effectuer de nombreuses actions de modification de colonne pendant que la table reste disponible. La valeur par défaut est OFF. La modification de colonne peut être effectuée sur une ligne pour les modifications de colonne liées à un type de données, la longueur de colonne ou la précision, la possibilité de valeur null, le caractère épars et le classement.

La modification de colonne en ligne permet aux statistiques créées par l'utilisateur et automatiques de faire référence à la colonne modifiée pendant la durée de l'opération ALTER COLUMN. Cela permet aux requêtes de s'exécuter comme d'habitude. À la fin de l'opération, les statistiques automatiques qui font référence à la colonne sont supprimées et les statistiques créées par l'utilisateur sont invalidées. L'utilisateur doit mettre à jour manuellement les statistiques générées par l'utilisateur une fois l'opération terminée. Si la colonne fait partie d’une expression de filtre pour les statistiques ou d’index vous ne pouvez pas effectuer une opération de modification de colonne.

  • Pendant l'exécution de l'opération de modification de colonne en ligne, toutes les opérations qui peuvent établir une dépendance sur la colonne (index, vues, etc.) sont bloquées ou échouent avec une erreur appropriée. Cela garantit que l'opération de modification de colonne en ligne n'échouera pas en raison des dépendances introduites pendant son exécution.

  • Le remplacement de la valeur NOT NULL par NULL d'une colonne n'est pas pris en charge en tant qu'opération en ligne quand la colonne modifiée est référencée par les index non cluster.

  • La modification en ligne n'est pas prise en charge quand la colonne est référencée par une contrainte de validation et que l'opération de modification limite la précision de la colonne (numérique ou datetime).

  • Le low_priority_lock_wait option ne peut pas être utilisée avec la modification en ligne de colonne.

  • ALTER COLUMN... ADD/DROP PERSISTED n’est pas pris en charge pour modifier la colonne en ligne.

  • ALTER COLUMN... AJOUTER/DROP ROWGUIDCOL/NOT FOR REPLICATION n’est pas affectée par la ligne de modifier la colonne.

  • La modification de colonne en ligne ne prend pas en charge la modification d'une table où le suivi des modifications est activé ou qui est un serveur de publication de la réplication de fusion.

  • La modification de colonne en ligne ne prend pas en charge la modification depuis ou vers des types de données CLR.

  • La modification de colonne en ligne ne prend pas en charge la modification d'un type de données XML qui possède une collection de schémas différente de la collection de schémas active.

  • La modification de colonne en ligne ne réduit pas les restrictions relatives aux périodes de modification possibles d'une colonne. Les références par index/statistiques, etc. peuvent entraîner l’échec de la modification.

  • La modification de colonne en ligne ne prend pas en charge la modification simultanée de plusieurs colonnes.

  • Modification en ligne colonne n’a aucun effet en cas de la table temporal avec version système. L’opération ALTER n’est pas effectuée en ligne, quelle que soit la valeur spécifiée pour l’option ONLINE.

La modification de colonne en ligne a des exigences, restrictions et fonctionnalités similaires à la reconstruction d'index en ligne. Cela inclut :

  • La reconstruction d'index en ligne n'est pas prise en charge quand la table contient des colonnes LOB ou filestream héritées, ou quand la table possède un index columnstore. Les mêmes limitations s'appliquent à la modification de colonne en ligne.

  • Une colonne existante qui est modifiée nécessite deux fois plus d'allocation d'espace : pour la colonne d'origine et la colonne masquée nouvellement créée.

  • La stratégie de verrouillage lors d'une opération de modification de colonne en ligne suit le même modèle de verrouillage utilisé pour la construction d'index en ligne.

WITH CHECK | WITH NOCHECK
Spécifie si les données de la table doivent être validées par rapport à une contrainte FOREIGN KEY ou CHECK nouvellement ajoutée ou réactivée. Si l'option n'est pas spécifiée, l'option WITH CHECK est utilisée pour les nouvelles contraintes et l'option WITH NOCHECK pour les contraintes réactivées.

Utilisez WITH NOCHECK si vous ne voulez pas vérifier les nouvelles contraintes CHECK ou FOREIGN KEY sur les données existantes. Ceci n'est pas recommandé, sauf dans quelques cas rares. La nouvelle contrainte sera évaluée dans toutes les mises à jour ultérieures. Toute violation de contrainte supprimée par l’option WITH NOCHECK lorsque la contrainte est ajoutée risque d’échouer si ils mettent à jour les lignes avec des données qui ne respecte pas la contrainte des futures mises à jour.

L'optimiseur de requête ne prend pas en compte les contraintes définies avec WITH NOCHECK. De telles contraintes sont ignorées tant qu'elles n'ont pas été réactivées à l'aide d'ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

ADD
Spécifie qu’une ou plusieurs définitions de colonnes, définitions de colonnes calculées ou contraintes de table sont ajoutées, ou les colonnes utilisées par le système pour le contrôle de version système.

Instruction PERIOD FOR SYSTEM_TIME (system_start_time_column_name, system_end_time_column_name)

S’applique à: SQL Server 2016 via SQL Server 2016, Base de données SQL.

Spécifie les noms des colonnes que le système utilise pour enregistrer la période pour laquelle un enregistrement est valid. Vous pouvez spécifier les colonnes existantes ou créer des colonnes dans le cadre de l’argument de l’instruction ADD PERIOD FOR SYSTEM_TIME. Les colonnes doivent avoir le type de données de type datetime2 et doit être définies comme NOT NULL. Si une colonne de période est définie avec la valeur NULL, une erreur sera levée. Vous pouvez définir un column_constraint &#40 ; Transact-SQL &#41 ; et/ou spécifier les valeurs par défaut pour les colonnes pour les colonnes system_start_time et system_end_time. Consultez l’exemple A dans le le système de contrôle de version exemples ci-dessous qui illustrent l’utilisation d’une valeur par défaut pour la colonne system_end_time.

Utilisez cet argument conjointement avec l’argument SYSTEM_VERSIONING DÉFINI pour activer le contrôle de version système sur une table existante. Pour plus d’informations, consultez la page les Tables Temporal et mise en route avec les Tables temporelles dans la base de données SQL Azure.

En tant que de SQL Server 2016, les utilisateurs seront en mesure de marquer un ou plusieurs colonnes de période avec MASQUÉ indicateur implicitement masquer ces colonnes telles que SÉLECTIONNEZ * FROM< table> ne retourne pas de valeur pour ces colonnes. Par défaut, les colonnes de période ne sont pas masqués. Pour pouvoir être utilisée, les colonnes masquées doivent être explicitement inclus dans toutes les requêtes qui référencent directement la table temporelle.

DROP
Spécifie qu’une ou plusieurs définitions de colonnes, définitions de colonnes calculées ou contraintes de table sont supprimées, ou pour supprimer la spécification des colonnes utilisées par le système pour le contrôle de version système.

CONTRAINTE constraint_name
Spécifie que constraint_name est supprimé de la table. Plusieurs contraintes peuvent être répertoriées.

Le nom défini par l’utilisateur ou fourni par le système de la contrainte peut être déterminé en interrogeant le sys.check_constraint, sys.default_constraints, sys.key_constraints, et sys.foreign_keys affichages catalogue.

Il n'est pas possible de supprimer une contrainte PRIMARY KEY s'il existe un index XML sur la table.

COLONNE nom_colonne
Spécifie que constraint_name ou column_name est supprimé de la table. Plusieurs colonnes peuvent être répertoriées.

Une colonne ne peut pas être supprimée lorsqu'elle est :

  • utilisée dans un index ;

  • utilisée dans une contrainte CHECK, FOREIGN KEY, UNIQUE ou PRIMARY KEY ;

  • associée à une valeur par défaut définie à l'aide du mot clé DEFAULT ou liée à un objet par défaut ;

  • liée à une règle.

System_CAPS_ICON_note.jpg Remarque


La suppression d'une colonne ne permet pas de récupérer l'espace disque de la colonne. Vous pouvez être amené à récupérer l'espace disque d'une colonne supprimée lorsque la taille des lignes d'une table est proche de sa limite ou l'a dépassée. Récupérer l’espace en créant un index ordonné en clusters sur la table ou la reconstruction d’un index cluster existant à l’aide de ALTER INDEX. Pour plus d’informations sur l’impact de la suppression de types de données LOB, consultez ce une entrée de blog CSS.

INSTRUCTION PERIOD FOR SYSTEM_TIME

S’applique à: SQL Server 2016 via SQL Server 2016, Base de données SQL.

Supprime la spécification des colonnes utilisées par le système pour le contrôle de version système.

WITH < drop_clustered_constraint_option >
Spécifie qu'une ou plusieurs options de suppression de contrainte cluster sont définies.

MAXDOP = max_degree_of_parallelism

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Remplace le max degré de parallélisme option de configuration uniquement pour la durée de l’opération. Pour plus d’informations, consultez Configurer l’option de configuration du serveur Degré maximal de parallélisme.

Utilisez l'option MAXDOP pour limiter le nombre de processeurs utilisés dans une exécution de plans parallèles. Le nombre maximal de processeurs est égal à 64.

max_degree_of_parallelism peut être une des valeurs suivantes :

1
Supprime la création de plans parallèles.

>1
Limite au nombre spécifié le nombre maximal de processeurs utilisés dans le traitement en parallèle des index.

0 (valeur par défaut)
Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.

Pour plus d’informations, consultez Configurer des opérations d’index parallèles.

System_CAPS_ICON_note.jpg Remarque


Les opérations d'index parallèles ne sont pas disponibles dans toutes les éditions de SQL Server. Pour plus d’informations, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2016.

En LIGNE = {ON | DÉSACTIVER } < s’applique à drop_clustered_constraint_option>
Indique si les tables sous-jacentes et les index associés sont disponibles pour les requêtes et la modification de données pendant l'opération d'index. La valeur par défaut est OFF. REBUILD peut être effectué en tant qu'opération ONLINE.

ON
Les verrous de table à long terme ne sont pas maintenus pendant la durée de l'opération d'index. Lors de la principale phase de l'indexation, seul le verrou de partage intentionnel (IS, Intent Share) est maintenu sur la table source. Cela permet d'effectuer des requêtes ou des mises à jour dans la table sous-jacente et à l'opération sur les index de continuer. Au début de l'opération, un verrou partagé (Shared - S) est maintenu sur l'objet source pendant une période de temps très courte. À la fin de l'opération, pendant une courte période de temps, un verrou partagé (S - Shared) est acquis sur la source si un index non cluster est en cours de création, ou un verrou SCH-M (Modification du schéma) est acquis lorsqu'un index non cluster est créé ou supprimé en ligne et lorsqu'un index cluster ou non-cluster est en cours de reconstruction. ONLINE ne peut pas prendre la valeur ON si un index est en cours de création sur une table locale temporaire. Seule l'opération de reconstruction de segment monothread est autorisée.

Pour exécuter l’instruction DDL pour COMMUTATEUR ou reconstruction d’index en ligne, toutes les transactions en cours d’exécution sur une table particulière bloquantes active doit être terminée. Lors de l’exécution, le COMMUTATEUR ou opération de reconstruction empêche la nouvelle transaction de démarrer et peut affecter de manière significative le débit de la charge de travail et différer temporairement l’accès à la table sous-jacente.

OFF
Des verrous de table sont appliqués pendant l'opération d'indexation. Une opération d'indexation hors ligne qui crée, régénère ou supprime un index cluster, ou régénère ou supprime un index non cluster, acquiert un verrou de modification de schéma (Sch-M) sur la table. Cela empêche tous les utilisateurs d'accéder à la table sous-jacente pendant la durée de l'opération. Une opération d'indexation hors ligne qui crée un index non cluster acquiert un verrou partagé (S, Shared) sur la table. Cela empêche la mise à jour de la table sous-jacente, mais autorise les opérations de lecture, telles que des instructions SELECT. Les opérations de reconstruction de segment multithread sont autorisées.

Pour plus d’informations, consultez Online Index opérations fonctionnement.

System_CAPS_ICON_note.jpg Remarque


Les opérations d'index en ligne ne sont pas disponibles dans toutes les éditions de SQL Server. Pour plus d’informations, consultez Fonctionnalités prises en charge par les éditions de SQL Server 2016.

MOVE TO { partition_scheme_name(column_name [1, ... n] ) | filegroup | "default" }

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Spécifie un emplacement où déplacer les lignes de données actuellement au niveau feuille de l'index cluster. La table est déplacée au nouvel emplacement. Cette option s'applique uniquement aux contraintes qui créent un index cluster.

System_CAPS_ICON_note.jpg Remarque


L'élément « default » n'est pas un mot clé dans ce contexte. Il est un identificateur pour le groupe de fichiers par défaut et doit être délimité, comme dans MOVE TO «par défaut« ou MOVE TO [par défaut]. Si «par défaut« est spécifié, l’option QUOTED_IDENTIFIER doit avoir la valeur ON pour la session active. Il s'agit du paramètre par défaut. Pour plus d’informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

{ CHECK | NOCHECK } CONSTRAINT
Spécifie que constraint_name est activé ou désactivé. Cette option peut être utilisée seulement avec les contraintes FOREIGN KEY et CHECK. Lorsque NOCHECK est spécifié, la contrainte est désactivée ; les insertions et les mises à jour ultérieures de la colonne ne sont pas validées par rapport aux conditions de la contrainte. Il n'est pas possible de désactiver les contraintes DEFAULT, PRIMARY KEY et UNIQUE.

ALL
Spécifie que toutes les contraintes sont désactivées à l'aide de l'option NOCHECK, ou bien activées à l'aide de l'option CHECK.

{ ENABLE | DISABLE } TRIGGER
Spécifie que trigger_name est activé ou désactivé. Lorsqu'un déclencheur est désactivé, il est néanmoins défini pour la table. Toutefois, lorsque des instructions INSERT, UPDATE et DELETE sont exécutées sur la table, les actions du déclencheur ne sont pas effectuées tant que celui-ci n'a pas été réactivé.

ALL
Spécifie que tous les déclencheurs de la table sont activés ou désactivés.

trigger_name
Spécifie le nom du déclencheur à activer ou à désactiver.

{ ENABLE | DISABLE } CHANGE_TRACKING

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Spécifie si le suivi des modifications est activé ou désactivé pour la table. Par défaut, le suivi des modifications est désactivé.

Cette option est disponible uniquement lorsque le suivi des modifications est activé pour la base de données. Pour plus d’informations, consultez Options ALTER DATABASE SET (Transact-SQL).

Pour activer le suivi des modifications, la table doit avoir une clé primaire.

AVEC ( TRACK_COLUMNS_UPDATED = {ON | OFF } )

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Spécifie, si le Moteur de base de données effectue un suivi des modifications, quelles colonnes de suivi des modifications ont été mises à jour. La valeur par défaut est OFF.

COMMUTATEUR [PARTITION source_partition_number_expression ] à [ schema_name. ] table_cible [PARTITION target_ partition_number_expression ]

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Insère un bloc de données de l'une des manières suivantes :

  • Réaffecte toutes les données d'une table en tant que partition d'une table partitionnée déjà existante.

  • Bascule une partition d'une table partitionnée vers une autre.

  • Réaffecte toutes les données d'une partition d'une table partitionnée à une table non partitionnée existante.

Si table est une table partitionnée, source_partition_number_expression doit être spécifié. Si table_cible est partitionnée, target_partition_number_expression doit être spécifié. En cas de réaffectation des données d'une table en tant que partition à une table partitionnée déjà existante ou de basculement d'une partition d'une table partitionnée vers une autre, la partition cible doit exister et être vide.

En cas de réaffectation des données d'une partition pour constituer une seule table, la table cible doit être créée auparavant et être vide. La table ou la partition source ainsi que la table ou la partition cible doivent se trouver dans le même groupe de fichiers. Les index ou les partitions d'index correspondants doivent également se trouver dans le même groupe de fichiers. De nombreuses autres restrictions s'appliquent au basculement des partitions. table et table_cible ne peut pas être le même. table_cible peut être un identificateur multipartie.

source_partition_number_expression et target_partition_number_expression sont des expressions constantes qui peuvent référencer des variables et fonctions. y compris les variables et les fonctions définies par l'utilisateur. Ces arguments ne peuvent pas référencer des expressions Transact-SQL.

Une table partitionnée avec un index ordonné en clusters columstore se comporte comme un segment de mémoire partitionnée :

  • La clé primaire doit inclure la clé de partition.

  • Objet
    index unique doit inclure la clé de partition. Notez que, y compris la clé de partition pour un index unique peut modifier l’unicité.

  • Pour basculer des partitions, tous les index non cluster doivent inclure la clé de partition.

Pour COMMUTATEUR restriction lors de l’utilisation de la réplication, consultez répliquer des Tables partitionnées et des index.

Index columnstore non-clusters générés pour SQL Server 2016 CTP1 et pour la base de données SQL avant la version 12 dans un format en lecture seule. Les index non cluster columnstore doivent être régénérés pour le format actuel (qui est modifiable) avant de pouvoir effectuer des opérations de PARTITION.

DÉFINISSEZ ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | »par défaut» | »NULL» })

S'applique à: SQL Server 2008 et SQL Server 2016.

Spécifie où les données FILESTREAM sont stockées.

ALTER TABLE avec la clause SET FILESTREAM_ON réussit uniquement si la table n'a pas de colonnes FILESTREAM. Les colonnes FILESTREAM peuvent être ajoutées en utilisant une deuxième instruction ALTER TABLE.

Si partition_scheme_name est spécifié, les règles de CREATE TABLE s’appliquent. La table doit déjà être partitionnée pour les données de lignes, et son schéma de partition doit utiliser les mêmes fonction de partition et colonnes de partition que le schéma de partition de FILESTREAM.

filestream_filegroup_name Spécifie le nom d’un groupe de fichiers FILESTREAM. Le groupe de fichiers doit avoir un fichier qui est défini pour le groupe de fichiers en utilisant un CRÉER une base de DONNÉES ou ALTER DATABASE instruction, ou une erreur est générée.

«par défaut« Spécifie le groupe de fichiers FILESTREAM avec le jeu de propriétés par DÉFAUT. S'il n'y a aucun groupe de fichiers FILESTREAM, une erreur est générée.

«NULL« Spécifie que toutes les références à des groupes de fichiers FILESTREAM pour la table seront supprimées. Toutes les colonnes FILESTREAM doivent être supprimées en premier. Vous devez utiliser SET FILESTREAM_ON= «NULL» pour supprimer toutes les données FILESTREAM qui sont associées à une table.

DÉFINISSEZ ( SYSTEM_VERSIONING = {OFF | ON [(HISTORY_TABLE = schema_name. history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] } )

S’applique à: SQL Server 2016 via SQL Server 2016, Base de données SQL.

Désactive le contrôle de version d’une table système ou permet le contrôle de version de système d’une table. Pour activer le contrôle de version de système d’une table, le système vérifie que le type de données, contrainte nullability et exigences de contrainte de clé primaire pour la version du système sont remplies. Si l’argument HISTORY_TABLE n’est pas utilisé, le système génère une nouvelle table d’historique qui correspondent au schéma de la table actuelle, en créant un lien entre les deux tables et permet au système enregistrer l’historique de chaque enregistrement dans la table actuelle dans la table d’historique. Le nom de cette table d’historique sera MSSQL_TemporalHistoryFor<primary_table_object_id>. Si l’argument HISTORY_TABLE est utilisé pour créer un lien vers et à utiliser une table existante de l’historique, le lien est créé entre la table actuelle et la table spécifiée. Lorsque vous créez un lien vers une table de l’historique existante, vous pouvez choisir d’effectuer une vérification de cohérence des données. Cette vérification de cohérence des données garantit que les enregistrements existants ne se chevauchent pas. La vérification de cohérence des données est effectuée par défaut. Pour plus d’informations, voir Temporal Tables.

HISTORY_RETENTION_PERIOD = { INFINIE | nombre {JOURS | JOURS | SEMAINE | SEMAINES | MOIS | MOIS | ANNÉE | ANNÉES}}

S'applique à: Base de données SQL.

Spécifie la conservation finie ou infinte pour les données d’historique dans la table temporelle. Si omis, infinie de rétention est utilisée.

DÉFINISSEZ ( LOCK_ESCALATION = {AUTOMATIQUE | TABLE | DÉSACTIVER} )

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Spécifie les méthodes autorisées d'escalade de verrous pour une table.

AUTO
Cette option permet au Moteur de base de données SQL Server de sélectionner la granularité d'escalade de verrous appropriée pour le schéma de la table.

  • Si la table est partitionnée, l'escalade de verrous est effectuée jusqu'au niveau de la partition. Une fois que l'escalade de verrous a atteint le niveau de la partition, elle n'est pas étendue à la granularité TABLE.

  • Si la table n'est pas partitionnée, l'escalade de verrous continue jusqu'à la granularité TABLE.

TABLE
L'escalade de verrous continue jusqu'à la granularité TABLE, que la table soit ou non partitionnée. TABLE est la valeur par défaut.

DISABLE
Empêche l'escalade de verrous dans la plupart des cas. Les verrous de niveau table ne sont pas totalement interdits. Par exemple, lorsque vous analysez une table ne contenant aucun index cluster sous le niveau d'isolation sérialisable, le Moteur de base de données doit prendre un verrou de table pour protéger l'intégrité des données.

REBUILD
Utilisez la syntaxe REBUILD WITH pour reconstruire une table entière qui inclut toutes les partitions dans une table partitionnée. Si la table a un index cluster, l'option REBUILD reconstruit l'index cluster. REBUILD peut être effectué en tant qu'opération ONLINE.

Utilisez la syntaxe REBUILD PARTITION pour reconstruire une partition unique dans une table partitionnée.

PARTITION = ALL

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Reconstruit toutes les partitions lors de la modification des paramètres de compression de la partition.

REBUILD WITH (<option_reconstruction>)
Toutes les options s'appliquent à une table pourvue d'un index cluster Si la table n'a pas d'index cluster, la structure de segment n'est affectée que par certaines options.

Lorsqu'un paramètre de compression spécifique n'est pas spécifié avec l'opération REBUILD, le paramètre actuel de la partition est utilisé. Pour retourner la valeur actuelle, interrogez la data_compression colonne dans la sys.partitions affichage catalogue.

Pour une description complète des options de reconstruction, consultez index_option &#40 ; Transact-SQL &#41 ;.

DATA_COMPRESSION

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Spécifie l'option de compression de données pour la table, le numéro de partition ou la plage de partitions spécifiés. Les options disponibles sont les suivantes :

NONE
La table ou les partitions spécifiées ne sont pas compressées. Ne s'applique pas aux tables columnstore.

ROW
La table ou les partitions spécifiées sont compressées au moyen de la compression de ligne. Ne s'applique pas aux tables columnstore.

PAGE
La table ou les partitions spécifiées sont compressées au moyen de la compression de page. Ne s'applique pas aux tables columnstore.

COLUMNSTORE

S’applique à: SQL Server 2014 via SQL Server 2016, Base de données SQL.

S'applique uniquement aux tables columnstore. COLUMNSTORE spécifie qu'il faut décompresser une partition compressée à l'aide de l'option COLUMNSTORE_ARCHIVE. Lorsque les données sont restaurées, elles continuent à être compressées à l'aide de la compression columnstore utilisée pour toutes les tables columnstore.

COLUMNSTORE_ARCHIVE

S’applique à: SQL Server 2014 via SQL Server 2016, Base de données SQL.

S'applique uniquement aux tables columnstore, qui sont des tables stockées avec un index cluster columnstore. COLUMNSTORE_ARCHIVE compressera davantage la partition spécifiée en une plus petite taille. Peut être utilisé pour l'archivage, ou d'autres situations qui nécessitent moins de stockage et supportent plus de temps pour le stockage et la récupération.

Pour reconstruire plusieurs partitions en même temps, consultez index_option &#40 ; Transact-SQL &#41 ;. Si la table n'a pas d'index cluster, la modification de la compression de données reconstruit le segment de mémoire et les index non cluster. Pour plus d’informations sur la compression, consultez la Compression de données.

En LIGNE = {ON | DÉSACTIVER } < s’appliqu’à single_partition_rebuild_option>
Spécifie si une seule partition des tables sous-jacentes et les index associés sont disponibles pour modifier des requêtes et des données pendant l'opération d'index. La valeur par défaut est OFF. REBUILD peut être effectué en tant qu'opération ONLINE.

ON
Les verrous de table à long terme ne sont pas maintenus pendant la durée de l'opération d'index. Un verrou S sur la table est requis au début de la reconstruction de l'index, et un verrou Sch-M sur la table à la fin de la reconstruction de l'index en ligne. Bien que les deux verrous soient des verrous de métadonnées courtes, le verrou Sch-M doit notamment attendre que toutes les transactions bloquantes soient terminées. Pendant le temps d'attente, le verrou Sch-M bloque toutes les autres transactions qui attendent derrière ce verrou en cas d'accès à la même table.

System_CAPS_ICON_note.jpg Remarque


Reconstruction d’index en ligne peut définir le low_priority_lock_wait options décrites plus loin dans cette section.

OFF
Des verrous de table sont appliqués pendant l'opération d'indexation. Cela empêche tous les utilisateurs d'accéder à la table sous-jacente pendant la durée de l'opération.

nom_jeu_colonnes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

S’applique à: SQL Server 2008 via SQL Server 2016, Base de données SQL.

Représente le nom du jeu de colonnes. Un jeu de colonnes est une représentation XML non typée qui combine toutes les colonnes éparses d'une table dans une sortie structurée. Un jeu de colonnes ne peut pas être ajouté à une table qui contient des colonnes éparses. Pour plus d’informations sur les jeux de colonnes, consultez Utiliser des jeux de colonnes.

{ ENABLE | DISABLE } FILETABLE_NAMESPACE

S'applique à: SQL Server 2012 et SQL Server 2016.

Active ou désactive les contraintes définies par le système sur un FileTable. Peut être utilisé uniquement avec un FileTable.

DÉFINIR (FILETABLE_DIRECTORY = directory_name )

S'applique à: SQL Server 2012 et SQL Server 2016.

Spécifie le nom de répertoire FileTable compatible Windows. Ce nom doit être unique parmi tous les noms de répertoire FileTable de la base de données. La comparaison d'unicité n'est pas sensible à la casse, indépendamment des paramètres de classement SQL. Peut être utilisé uniquement avec un FileTable.

ENSEMBLE)
REMOTE_DATA_ARCHIVE
{
= ON (< table_stretch_options >)
| = OFF_WITHOUT_DATA_RECOVERY
(MIGRATION_STATE = EN PAUSE) | (< table_stretch_options > [,... n])
} )

S'applique à: SQL Server 2016.

Active ou désactive l’extension de base de données pour une table. Pour plus d'informations, consultez Stretch Database.

L’activation d’étirement de la base de données pour une table

Lorsque vous activez Stretch pour une table en spécifiant ON, vous devez également spécifier MIGRATION_STATE = OUTBOUND pour commencer la migration des données immédiatement, ou MIGRATION_STATE = PAUSED à reporter la migration des données. La valeur par défaut est MIGRATION_STATE = OUTBOUND. Pour plus d’informations sur l’activation d’extension pour une table, consultez Stretch activer la base de données pour une table.

Conditions préalables. Avant d’activer Stretch pour une table, vous devez activer l’extension sur le serveur et sur la base de données. Pour plus d’informations, consultez Activer Stretch Database pour une base de données.

Les autorisations. Activer Stretch pour une base de données ou une table nécessite des autorisations db_owner. L’activation de Stretch pour une table requiert également des autorisations ALTER sur la table.

La désactivation de la base de données Stretch pour une table

Lorsque vous désactivez l’extension pour une table, vous avez deux options pour les données distantes qui a déjà été migrées vers Azure. Pour plus d’informations, consultez Désactiver Stretch Database et récupérer les données distantes.

  • Pour désactiver Stretch pour une table et copier les données distantes pour la table d'Azure vers SQL Server, exécutez la commande suivante. Cette commande ne peut pas être annulée.

    ALTER TABLE <table name>  
       SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;  
    
    

    Cette opération entraîne des coûts de transfert de données et ne peut pas être annulée. Pour plus d'informations, consultez la rubrique Détails de la tarification des transferts de données.

    Une fois que toutes les données distantes ont été copiées d'Azure vers SQL Server, Stretch est désactivée pour la table.

  • Pour désactiver Stretch pour une table et abandonner les données distantes, exécutez la commande suivante.

    ALTER TABLE <table_name>  
       SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;  
    
    

Après avoir désactivé Stretch Database pour une table, la migration des données s’arrête et les résultats de la requête n'incluent plus les résultats de la table distante.

La désactivation de Stretch ne supprime pas la table distante. Si vous souhaitez supprimer la table distante, vous devez la supprimer à l'aide du portail de gestion Azure.

[FILTER_PREDICATE = {null | prédicat }]

S'applique à: SQL Server 2016.

Spécifie éventuellement un prédicat de filtre pour sélectionner les lignes à migrer à partir d’une table qui contient les données historiques et actuelles. Le prédicat doit appeler une fonction table inline déterministe. Pour plus d’informations, consultez Stretch activer la base de données pour une table et Sélectionner les lignes à migrer à l’aide d’une fonction de filtre &#40 ; Étendre la base de données &#41 ;.

System_CAPS_ICON_important.jpg Important


Si vous fournissez un prédicat de filtre qui fonctionne mal, la migration des données fonctionne mal également. Stretch Database applique le prédicat de filtre à la table à l’aide de l’opérateur CROSS APPLY.

Si vous ne spécifiez aucun prédicat de filtre, la table entière est migrée.

Lorsque vous spécifiez un prédicat de filtre, vous devez également spécifier MIGRATION_STATE.

MIGRATION_STATE = {SORTANTS | TRAFIC ENTRANT | SUSPENDU}

S'applique à: SQL Server 2016.

WAIT_AT_LOW_PRIORITY

S'applique à: SQL Server 2014 et SQL Server 2016.

Une reconstruction d'index en ligne doit attendre les opérations de blocage sur cette table. WAIT_AT_LOW_PRIORITY indique que l’opération de reconstruction d’index en ligne doit attendre les verrous de faible priorité, ce qui permet d’autres opérations de continuer pendant que l’opération de création d’index en ligne est en attente. En omettant le ATTENTE BASSE option équivaut à WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = temps [MINUTES ]

S'applique à: SQL Server 2014 et SQL Server 2016.

Temps d’attente (valeur entière spécifiée en minutes) qui le COMMUTATEUR ou les verrous de reconstruction d’index en ligne devront attendre avec une faible priorité lors de l’exécution de la commande DDL. Si l’opération est bloquée pendant la MAX_DURATION du temps, un de le ABORT_AFTER_WAIT actions seront exécutées. MAX_DURATION heure est toujours en minutes et le mot MINUTES peut être omis.

ABORT_AFTER_WAIT = [NONE | SELF | DES BLOCAGES }]

S'applique à: SQL Server 2014 et SQL Server 2016.

NONE
Continuez à attendre le verrou avec la priorité normale.

SELF
Quitter le COMMUTATEUR ou opération de DDL de reconstruction d’index en ligne actuellement exécutée sans effectuer aucune action.

BLOCKERS
Annulez toutes les transactions utilisateur qui bloquent actuellement le COMMUTATEUR ou des index en ligne opération DDL de reconstruction afin que l’opération puisse continuer.

Requiert ALTER ANY CONNECTION autorisation.

S’IL EXISTE

S'applique à: SQL Server (SQL Server 2016 via la version actuelle).

Conditionnelle supprime la colonne ou la contrainte uniquement s’il existe déjà.

Pour ajouter de nouvelles lignes de données, utilisez INSÉRER. Pour supprimer les lignes de données, utilisez SUPPRIMER ou TRUNCATE TABLE. Pour modifier les valeurs des lignes existantes, utilisez mise à JOUR.

Si le cache de procédures contient des plans d'exécution qui référencent la table, ALTER TABLE les marque de façon à les recompiler lors de leur prochaine exécution.

Vous pouvez modifier la longueur, l'échelle ou la précision d'une colonne en spécifiant une nouvelle taille pour le type de données de la colonne dans la clause ALTER COLUMN. Si des données existent dans la colonne, la nouvelle taille ne peut pas être inférieure à la taille maximale des données. En outre, la colonne ne peut pas être définie dans un index, sauf si la colonne est un varchar, nvarchar, ou varbinary type de données et l’index n’est pas le résultat d’une contrainte de CLÉ PRIMAIRE. Voir l'exemple P.

Les modifications spécifiées dans l'instruction ALTER TABLE sont implémentées immédiatement. Si elles nécessitent une modification des lignes de la table, ALTER TABLE met les lignes à jour. ALTER TABLE acquiert un verrou (SCH-M) de modification du schéma sur la table pour garantir qu'aucune autre connexion ne référence même les métadonnées de la table pendant la modification, à l'exception des opérations d'index en ligne qui nécessitent un verrouillage de type SCH-M à la fin. Dans une opération ALTER TABLE…SWITCH, le verrou est acquis à la fois sur la table source et sur la table cible. Les modifications effectuées sur la table sont consignées dans un journal et peuvent être récupérées entièrement. Modifications qui affectent toutes les lignes dans les tables très volumineuses, telles que la suppression d’une colonne ou, dans certaines éditions de SQL Server, l’ajout d’une colonne NOT NULL avec valeur par défaut, peut prendre beaucoup de temps pour terminer et créer plusieurs enregistrements de journal. Ces instructions ALTER TABLE doivent être exécutées avec le même soin que toute instruction INSERT, UPDATE ou DELETE qui affectent un grand nombre de lignes.

Ajout de colonnes NOT NULL en tant qu'opération en ligne

Compter SQL Server 2012 Enterprise Edition, l’ajout d’une colonne NOT NULL avec valeur par défaut est une opération en ligne lorsque la valeur par défaut est un constante d’exécution. Cela signifie que l'opération est terminée presque instantanément indépendamment du nombre de lignes dans la table. Cela est dû au fait que les lignes existantes dans la table ne sont pas mises à jour pendant l'opération ; à la place, la valeur par défaut est stockée uniquement dans les métadonnées de la table et la valeur se trouve autant que nécessaire dans les requêtes qui accèdent à ces lignes. Ce comportement est automatique ; aucune syntaxe supplémentaire n'est nécessaire pour implémenter l'opération en ligne au-delà de la syntaxe COLUMN ADD. Une constante d'exécution est une expression qui produit la même valeur au moment de l'exécution pour chaque ligne dans la table quel que soit son déterminisme. Par exemple, l'expression constante « mes données temporaires », ou la fonction système GETUTCDATETIME () sont des constantes d'exécution. Par opposition, les fonctions NEWID () ou NEWSEQUENTIALID () ne sont pas des constantes d'exécution car une valeur unique est produite pour chaque ligne de la table. L'ajout d'une colonne NOT NULL avec une valeur par défaut qui n'est pas une constante d'exécution est toujours effectuée hors connexion et un verrou (SCH-M) exclusif est acquis pour la durée de l'opération.

Alors que les lignes existantes référencent la valeur stockée dans les métadonnées, la valeur par défaut est stockée dans la ligne pour toutes les nouvelles lignes qui sont insérées et ne spécifient pas une autre valeur pour la colonne. La valeur par défaut stockée dans les métadonnées est déplacée vers une ligne existante lorsque la ligne est mise à jour (même si la colonne réelle n'est pas spécifiée dans l'instruction UPDATE), ou si la table ou l'index cluster est régénéré.

Colonnes de type varchar (max), nvarchar (max), varbinary (max), xml, texte, ntext, image, hierarchyid, geometry, geography, ou CLR UDTS ne peuvent pas être ajoutées dans une opération en ligne. Une colonne ne peut pas être ajoutée en ligne si cela entraîne le dépassement de la limite de 8 060 octets pour la taille de la ligne. Dans ce cas, la colonne est ajoutée en tant que traitement en différé.

Dans Microsoft SQL Server 2012 Enterprise et versions ultérieures, le nombre de processeurs utilisés pour exécuter une instruction ALTER TABLE ADD (basée sur un index) CONSTRAINT ou DROP (index cluster) CONSTRAINT est déterminé par le max degré de parallélisme option de configuration et de la charge de travail en cours. Si le Moteur de base de données détecte que le système est occupé, le degré de parallélisme de l'opération est automatiquement diminué avant le démarrage de l'exécution de l'instruction. Vous pouvez configurer manuellement le nombre de processeurs utilisés pour exécuter l'instruction en spécifiant l'option MAXDOP. Pour plus d’informations, consultez Configurer l’option de configuration du serveur Degré maximal de parallélisme.

Outre les opérations SWITCH qui mettent en œuvre des tables partitionnées, ALTER TABLE peut être utilisée pour modifier l'état des colonnes, des contraintes et des déclencheurs d'une table partitionnée, de la même manière que pour les tables non partitionnées. Cependant, cette instruction n'est pas utilisable pour modifier la façon dont la table elle-même est partitionnée. Pour repartitionner une table partitionnée, utilisez ALTER PARTITION SCHEME et ALTER PARTITION FUNCTION. De plus, vous ne pouvez pas modifier le type de données d'une colonne d'une table partitionnée.

Les restrictions applicables aux instructions ALTER TABLE dans les tables comportant des vues liées au schéma sont identiques à celles qui s'appliquent à la modification de tables comportant un index simple. L'ajout d'une colonne est autorisé. Cependant, la suppression ou la modification d'une colonne intervenant dans une vue associée à un schéma n'est pas autorisée. Si l'instruction ALTER TABLE requiert la modification d'une colonne utilisée dans une vue liée au schéma, ALTER TABLE échoue et le Moteur de base de données génère un message d'erreur. Pour plus d’informations sur la liaison de schéma et les vues indexées, consultez CREATE VIEW &#40 ; Transact-SQL &#41 ;.

L'ajout ou la suppression de déclencheurs sur les tables de base n'est pas affectée par la création d'une vue liée au schéma comportant des références aux tables.

Tout index créé dans le cadre d'une contrainte est supprimé lorsque cette dernière est supprimée. Un index créé au moyen de l'instruction CREATE INDEX doit être supprimé à l'aide de l'instruction DROP INDEX. L'instruction ALTER INDEX peut être utilisée pour reconstruire un index faisant partie de la définition d'une contrainte ; il n'est pas nécessaire de supprimer la contrainte et de l'ajouter à nouveau à l'aide de l'instruction ALTER TABLE.

Tous les index et contraintes basés sur une colonne doivent être supprimés avant que la colonne puisse être supprimée.

Lorsqu'une contrainte qui a créé un index cluster est supprimée, les lignes de données stockées au niveau feuille de l'index cluster sont stockées dans une table non-cluster. Vous pouvez supprimer l'index cluster et déplacer la table résultante vers un autre groupe de fichiers ou schéma de partition dans une transaction unique en spécifiant l'option MOVE TO. Cette option est soumise aux restrictions suivantes :

  • MOVE TO n'est pas valide pour les vues non indexées ou les index non cluster.

  • Le schéma de partition ou le groupe de fichiers doit déjà exister.

  • Si MOVE TO n'est pas spécifié, la table est placée dans le même schéma de partition ou groupe de fichiers qui a été défini pour l'index cluster.

Lorsque vous supprimez un index ordonné en clusters, vous pouvez spécifier en LIGNE = SUR l’option de la transaction DROP INDEX ne bloque pas les requêtes et les modifications aux données sous-jacentes et les index non cluster associés.

En LIGNE = SUR présente les restrictions suivantes :

  • En LIGNE = ON n’est pas valide pour les index ordonnés en clusters qui sont également désactivés. Index désactivés doivent être supprimés à l’aide en LIGNE = désactivée (OFF).

  • Un seul index à la fois peut être supprimé.

  • En LIGNE = ON n’est pas valide pour les vues indexées, les index non ordonnés en clusters ou les index sur des tables temporaires locales.

  • En LIGNE = ON n’est pas valide pour les index columnstore.

Pour supprimer un index cluster, l'espace disque temporaire doit être égal à la taille de l'index cluster existant. Cet espace supplémentaire est libéré dès que l'opération est terminée.

System_CAPS_ICON_note.jpg Remarque


Les options répertoriées sous < drop_clustered_constraint_option > s’appliquent aux index cluster sur les tables et ne peut pas être appliquée index cluster sur les vues ou les index non ordonnés en clusters.

Par défaut, lorsque vous exécutez l'instruction ALTER TABLE sur une table publiée d'un serveur de publication SQL Server, cette modification est propagée à tous les Abonnés SQL Server. Cette fonctionnalité comporte des restrictions et peut être désactivée. Pour plus d’informations, consultez apporter des modifications de schéma sur les bases de données de Publication.

Les tables système ne peuvent pas être activées pour la compression. Si la table est un segment de mémoire, l'opération de reconstruction pour le mode ONLINE sera monothread. Utilisez le mode OFFLINE pour une opération de reconstruction de segment de mémoire multithread. Pour plus d’informations sur la compression de données, consultez la pagela Compression de données.

Pour évaluer la façon dont la modification de l’état de compression affecte une table, un index ou une partition, utilisez la procédure stockée sp_estimate_data_compression_savings .

Les restrictions suivantes s'appliquent aux tables partitionnées :

  • Vous ne pouvez pas modifier le paramètre de compression d'une partition unique si la table possède des index non alignés.

  • L’instruction ALTER TABLE < table> REBUILD PARTITION... reconstruit la partition spécifiée.

  • L’instruction ALTER TABLE < table> REBUILD WITH... reconstruit toutes les partitions.

Lors de la suppression de colonnes NTEXT, le nettoyage des données supprimées se produit en tant qu'opération sérialisée sur toutes les lignes. Cette opération peut prendre du temps. Lorsque vous supprimez une colonne NTEXT dans une table contenant un grand nombre de lignes, mettez à jour la colonne NTEXT avec la valeur NULL au préalable, puis supprimez la colonne. Cela peut être effectué avec des opérations parallèles et peut être plus rapide.

Pour exécuter l'instruction DDL pour une reconstruction d'index en ligne, toutes les transactions bloquantes actives qui s'exécutent sur une table particulière doivent être terminées. Lorsque la reconstruction d'index en ligne s'exécute, elle bloque toutes les nouvelles transactions qui sont prêtes à s'exécuter sur cette table. Bien que la durée du verrou pour la reconstruction de l'index en ligne soit très courte, le fait d'attendre que toutes les transactions ouvertes sur une table spécifique soient exécutées, et le fait de bloquer les nouvelles transactions qui doivent démarrer, peuvent avoir un impact important sur le débit et provoquer un ralentissement ou un délai d'attente des charges de travail, limitant considérablement l'accès à la table sous-jacente. Le WAIT_AT_LOW_PRIORITY option permet aux DBA de gérer les verrous S et Sch-M requis pour les reconstructions d’index en ligne et vous permet de sélectionner une des 3 options. Dans les 3 cas, si pendant le temps d'attente ((MAX_DURATION =n [minutes])) il n'y a aucune activité bloquante, la reconstruction de l'index en ligne est exécutée immédiatement, sans attendre, et l'instruction DDL est terminée.

L'instruction ALTER TABLE permet uniquement les noms de tables (schema.object) en deux parties. Dans SQL Server 2016, la spécification d'un nom de table à l'aide des formats suivants échoue au moment de la compilation, avec l'erreur 117.

  • server.database.schema.table

  • .database.schema.table

  • ..schema.table

Dans les versions antérieures, la spécification du format server.database.schema.table retournait l'erreur 4902. La spécification du format .database.schema.table ou .schema.table aboutissait.

Pour résoudre le problème, supprimez l'utilisation d'un préfixe en quatre parties.

Requiert une autorisation ALTER sur la table.

Les autorisations ALTER TABLE s'appliquent aux tables mises en œuvre dans une instruction ALTER TABLE SWITCH. Toute donnée basculée hérite de la sécurité de la table cible.

Si des colonnes dans l'instruction ALTER TABLE sont définies avec un type CLR défini par l'utilisateur ou un type de données alias, l'autorisation REFERENCES sur le type est requise.

Ajout d’une colonne qui met à jour les lignes de la table requiert mise à JOUR autorisation sur la table. Par exemple, ajoutez un NON NULL colonne avec une valeur par défaut ou l’ajout d’une colonne d’identité lorsque la table n’est pas vide.

CatégorieÉléments syntaxiques proposés
Ajout de colonnes et contraintesADD • PRIMARY KEY avec des options d'index • colonnes éparses et jeux de colonnes •
Suppression de colonnes et contraintesDROP
Modification d’une définition de colonnechangement de type de données • changement de taille de colonne • classement
Modification d’une définition de tableDATA_COMPRESSION • SWITCH PARTITION • ESCALATION • suivi des modifications
Désactivation et activation des contraintes et des déclencheursCHECK • NO CHECK • ENABLE TRIGGER • DISABLE TRIGGER

Ajout de colonnes et contraintes

Les exemples fournis dans cette section expliquent comment ajouter des colonnes et des contraintes à une table.

A. Ajout d'une nouvelle colonne

L'exemple suivant ajoute une colonne qui accepte les valeurs NULL et pour laquelle aucune valeur n'est spécifiée via une définition DEFAULT. Dans la nouvelle colonne, chaque ligne aura la valeur NULL.

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

B. Ajout d'une colonne avec une contrainte

L'exemple suivant ajoute une nouvelle colonne avec une contrainte 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. Ajout d'une contrainte CHECK non vérifiée à une colonne existante

L'exemple suivant ajoute une contrainte à une colonne existante de la table. La colonne comporte une valeur qui ne respecte pas la contrainte. Par conséquent, WITH NOCHECK empêche la validation de la contrainte sur les lignes existantes, et permet l'ajout de la contrainte.

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. Ajout d'une contrainte DEFAULT à une colonne existante

L'exemple suivant crée une table de deux colonnes et insère une valeur dans la première ; l'autre colonne conserve la valeur NULL. Une contrainte DEFAULT est alors ajoutée à la deuxième colonne. Pour vérifier que la valeur par défaut est appliquée, une autre valeur est insérée dans la première colonne et la table fait l'objet d'une requête.

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. Ajout de plusieurs colonnes avec des contraintes

L'exemple suivant ajoute plusieurs colonnes avec des contraintes définies. La première colonne a la propriété IDENTITY. Chaque ligne de la table a de nouvelles valeurs incrémentielles dans la colonne d'identité.

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. Ajout d'une colonne acceptant les valeurs NULL, avec des valeurs par défaut

L'exemple suivant ajoute une colonne qui accepte les valeurs NULL, avec une définition DEFAULT. Il utilise l'option WITH VALUES pour spécifier des valeurs pour chaque ligne existante de la table. Si l'option WITH VALUES n'est pas utilisée, chaque ligne a la valeur NULL dans la nouvelle colonne.

  
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. Création d'une contrainte PRIMARY KEY avec des options d'index

L'exemple suivant crée la contrainte PRIMARY KEY PK_TransactionHistoryArchive_TransactionID et définit les options FILLFACTOR, ONLINE et PAD_INDEX. L'index cluster généré portera le même nom que la contrainte.

S'applique à: SQL Server 2008 et SQL Server 2016.
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. Ajout d'une colonne éparse

Les exemples suivants illustrent l'ajout et la modification des colonnes éparses dans la table T1. Le code pour créer la table T1 est comme suit.

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

Pour ajouter une colonne éparse supplémentaire C5, exécutez l'instruction suivante.

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

Pour convertir la colonne non éparse C4 en colonne éparse, exécutez l'instruction suivante.

ALTER TABLE T1  
ALTER COLUMN C4 ADD SPARSE ;  
GO  

Pour convertir le C4 des colonnes éparses en colonne, exécutez l’instruction suivante.

ALTER TABLE T1  
ALTER COLUMN C4 DROP SPARSE;  
GO  

I. Ajout d'un jeu de colonnes

Les exemples suivants montrent comment ajouter une colonne à la table T2. Un jeu de colonnes ne peut pas être ajouté à une table qui contient déjà des colonnes éparses. Le code pour créer la table T2 est comme suit.

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

Les trois instructions suivantes ajoutent un jeu de colonnes nommé CS, puis changent les colonnes C2 et C3 en 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  

Arrow icon used with Back to Top link Exemples

J. Ajout d’une colonne chiffrée

L’instruction suivante ajoute une colonne chiffrée nommée PromotionCode.

ALTER TABLE Customers ADD  
    PromotionCode nvarchar(100)   
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,  
    ENCRYPTION_TYPE = RANDOMIZED,  
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') ;  

Suppression de colonnes et contraintes

Les exemples fournis dans cette section expliquent comme supprimer des colonnes et des contraintes.

A. Suppression d'une ou plusieurs colonnes

Le premier exemple supprime une colonne dans une table. Le second exemple supprime plusieurs colonnes.

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. Suppression de contraintes et de colonnes

Le premier exemple supprime une contrainte UNIQUE d'une table. Le second exemple supprime deux contraintes et une seule colonne.

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. Suppression d'une contrainte PRIMARY KEY en mode ONLINE

L'exemple suivant supprime une contrainte PRIMARY KEY avec l'option ONLINE ayant pour valeur ON.

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

D. Ajout et suppression d'une contrainte FOREIGN KEY

L'exemple suivant crée la table ContactBackup, puis la modifie en ajoutant d'abord une contrainte FOREIGN KEY qui référence la table Person.Person, puis en supprimant la contrainte FOREIGN KEY.

  
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 ;  

Arrow icon used with Back to Top link Exemples

Modification d’une définition de colonne

A. Modification du type de données d'une colonne

L'exemple suivant modifie le type d'une colonne d'une table de INT en 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. Modification de la taille d’une colonne

L’exemple suivant augmente la taille d’un varchar colonne et la précision et l’échelle d’un décimal colonne. Dans la mesure où les colonnes contiennent des données, la taille de colonne peut uniquement être augmentée. Remarquez aussi que col_a est défini dans un index unique. La taille de col_a peut encore être augmentée car le type de données est un varchar et l’index n’est pas le résultat d’une contrainte de CLÉ PRIMAIRE.

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. Modification du classement des colonnes

L'exemple suivant indique comment modifier le classement d'une colonne. En premier lieu, une table est créée avec le classement de l'utilisateur par défaut.

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

Ensuite, le classement C2 de la colonne est modifié en Latin1_General_BIN. Notez que le type de données est obligatoire, bien qu'il ne soit pas modifié.

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

Arrow icon used with Back to Top link Exemples

Modification d’une définition de Table

Les exemples présentés dans cette section montrent comment modifier la définition d'une table.

A. Modification d'une table pour modifier la compression

L'exemple suivant modifie la compression d'une table non partitionnée. Le segment de mémoire ou l'index cluster sera reconstruit. Si la table est un segment, tous les index non cluster associés à la table sont reconstruits.

ALTER TABLE T1   
REBUILD WITH (DATA_COMPRESSION = PAGE);  

L'exemple suivant modifie la compression d'une table partitionnée. La syntaxe REBUILD PARTITION = 1 provoque uniquement la reconstruction de la partition numéro 1.

S'applique à: SQL Server 2008 et SQL Server 2016.
ALTER TABLE PartitionTable1   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;  
GO  

La même opération utilisant la syntaxe suivante provoque la reconstruction de toutes les partitions dans la table.

S'applique à: SQL Server 2008 et SQL Server 2016.
ALTER TABLE PartitionTable1   
REBUILD PARTITION = ALL   
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;  

Pour plus d’exemples de compression des données supplémentaires, consultez la Compression de données.

B. Modification d'une table columnstore pour modifier la compression d'archivage

L'exemple suivant compresse davantage une partition de table columnstore en appliquant un algorithme de compression supplémentaire. Cela réduit la taille de la table à, mais augmente également le temps nécessaire pour le stockage et la récupération. Cela est utile pour l'archivage, ou d'autres situations qui nécessitent moins d'espace de stockage et supportent plus de temps pour le stockage et la récupération.

S'applique à: SQL Server 2014 et SQL Server 2016.
ALTER TABLE PartitionTable1   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;  
GO  

L'exemple suivant décompresse une partition de table columnstore compressée à l'aide de l'option COLUMNSTORE_ARCHIVE. Lorsque les données sont restaurées, elles continuent à être compressées à l'aide de la compression columnstore utilisée pour toutes les tables columnstore.

S'applique à: SQL Server 2014 et SQL Server 2016.
ALTER TABLE PartitionTable1   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  COLUMNSTORE) ;  
GO  

C. Basculement de partitions entre des tables

L'exemple suivant crée une table partitionnée, en partant du principe que le schéma de partition myRangePS1 est déjà créé dans la base de données. Ensuite, une table non partitionnée est créée avec la même structure que la table partitionnée et sur le même groupe de fichiers que PARTITION 2 de la table PartitionTable. Les données de PARTITION 2 de la table PartitionTable sont ensuite basculées dans la table 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  

D. Autorisation de l'escalade de verrous sur les tables partitionnées

L'exemple suivant autorise l'escalade de verrous au niveau de la partition sur une table partitionnée. Si la table n'est pas partitionnée, l'escalade de verrous est définie au niveau TABLE.

S'applique à: SQL Server 2008 et SQL Server 2016.
ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO);  
GO  

E. Configuration du suivi des modifications sur une table

L'exemple suivant active le suivi des modifications sur la table Person.Person.

S'applique à: SQL Server 2008 et SQL Server 2016.
USE AdventureWorks2012;  
ALTER TABLE Person.Person  
ENABLE CHANGE_TRACKING;  

L'exemple ci-dessous active le suivi des modifications ainsi que le suivi des colonnes qui sont mises à jour lors d'une modification.

S'applique à: SQL Server 2008 et SQL Server 2016.
USE AdventureWorks2012;  
GO  
ALTER TABLE Person.Person  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON)  

L'exemple suivant désactive le suivi des modifications sur la table Person.Person.

S'applique à: SQL Server 2008 et SQL Server 2016.
USE AdventureWorks2012;  
Go  
ALTER TABLE Person.Person  
DISABLE CHANGE_TRACKING;  

Arrow icon used with Back to Top link Exemples

Désactivation et activation des contraintes et des déclencheurs

A. Désactivation et réactivation d'une contrainte

L'exemple suivant désactive la contrainte définissant les salaires pouvant être inclus dans les données. L'option NOCHECK CONSTRAINT est utilisée avec ALTER TABLE pour désactiver la contrainte et permettre une insertion qui entraîne généralement une violation de la contrainte. CHECK CONSTRAINT réactive la contrainte.

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. Désactivation et réactivation d'un déclencheur

L'exemple suivant utilise l'option DISABLE TRIGGER de l'instruction ALTER TABLE pour désactiver le déclencheur et autoriser une insertion qui ne respecte normalement pas le déclencheur. ENABLE TRIGGER est ensuite utilisée pour réactiver le déclencheur.

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  

Arrow icon used with Back to Top link Exemples

Opérations en ligne

A. Reconstruction d'index en ligne à l'aide d'options d'attente de priorité basse

L'exemple suivant montre comment effectuer une reconstruction d'index en ligne qui spécifie les options d'attente de priorité basse.

S'applique à: SQL Server 2014 et SQL Server 2016.
ALTER TABLE T1   
REBUILD WITH   
(  
    PAD_INDEX = ON,  
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) )  
)  
;  

B. Modification de colonne en ligne

L'exemple suivant montre comment effectuer une opération de modification de colonne avec l'option ONLINE.

S’applique à: SQL Server 2016 via SQL Server 2016, Base de données SQL.
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) WITH (ONLINE = ON);  
GO  
sp_help doc_exy;  
DROP TABLE dbo.doc_exy ;  
GO  

Les quatre exemples ci-dessous vous aidera à vous familiariser avec la syntaxe pour le système de contrôle de version. Pour obtenir une assistance supplémentaire, consultez mise en route avec les Tables Temporal avec version système.

S’applique à: SQL Server 2016 via SQL Server 2016, Base de données SQL.

A. Ajouter le contrôle de version système aux Tables existantes

L’exemple suivant montre comment ajouter un contrôle de version système à une table existante et créer une table d’historique futures. Cet exemple suppose qu’il existe une table existante appelée InsurancePolicy avec une clé primaire définie. Cet exemple remplit les colonnes de période qui vient d’être créés pour le contrôle de version de système à l’aide des valeurs par défaut pour les heures de début et de fin, car ces valeurs ne peut pas être null. Cet exemple utilise la clause HIDDEN afin d’aucun impact sur les applications existantes qui interagissent avec la table actuelle. Il utilise également HISTORY_RETENTION_PERIOD qui est disponible sur Base de données SQL uniquement.

--Alter non-temporal table to define periods for system versioning  
ALTER TABLE InsurancePolicy  
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),   
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT GETUTCDATE(),   
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.99999999');  
--Enable system versioning with 1 year retention for historical data
ALTER TABLE InsurancePolicy 
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 1 YEAR));  

B. Migrer une Solution existante pour utiliser le système de contrôle de version

L’exemple suivant montre comment migrer vers des versions de système d’une solution qui utilise des déclencheurs pour imiter temporelle prise en charge. Le suppose qu’il existe une solution existante qui utilise un ProjectTaskCurrent table et un ProjectTaskHistory colonnes de la table pour sa solution existante, il utilise les Date de modification et Date de révision pour ses périodes, que ces colonnes de période n’utilisent pas le type de données datetime2 et que le ProjectTaskCurrent table possède une clé primaire définie.

-- Drop existing trigger  
DROP TRIGGER ProjectTaskCurrent_Trigger;  
-- Adjust the schema for current and history table  
-- Change data types for existing period columns  
ALTER TABLE ProjectTaskCurrent ALTER COLUMN [Changed Date] datetime2 NOT NULL;  
ALTER TABLE ProjectTaskCurrent ALTER COLUMN [Revised Date] datetime2 NOT NULL;  
  
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Changed Date] datetime2 NOT NULL;  
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Revised Date] datetime2 NOT NULL;  
  
-- Add SYSTEM_TIME period and set system versioning with linking two existing tables  
-- (a certain set of data checks happen in the background)  
ALTER TABLE ProjectTaskCurrent  
ADD PERIOD FOR SYSTEM_TIME ([Changed Date], [Revised Date])  
  
ALTER TABLE ProjectTaskCurrent  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProjectTaskHistory, DATA_CONSISTENCY_CHECK = ON))  

C. Désactivation et réactivation des versions de système pour modifier le schéma de Table

Cet exemple montre comment désactiver le contrôle de version système sur le Department table, ajoutez une colonne et réactiver le contrôle de version système. La désactivation du contrôle de version système est requis pour modifier le schéma de table. Effectuez ces étapes dans une transaction pour empêcher les mises à jour pour les deux tables en mettant à jour le schéma de table, qui permet au DBA d’ignorer la cohérence des données vérifier lors de la réactivation de versions de système et les performances un avantage. Notez que les tâches telles que la création des statistiques, de changement de partitions ou de compression d’une des deux tables ne requiert pas la désactivation du contrôle de version système.

BEGIN TRAN  
/* Takes schema lock on both tables */  
ALTER TABLE Department  
    SET (SYSTEM_VERSIONING = OFF);  
/* expand table schema for temporal table */  
ALTER TABLE Department  
     ADD Col5 int NOT NULL DEFAULT 0;  
/* Expand table schema for history table */  
ALTER TABLE DepartmentHistory  
    ADD Col5 int NOT NULL DEFAULT 0;  
/* Re-establish versioning again  */
ALTER TABLE Department  
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentHistory, DATA_CONSISTENCY_CHECK = OFF));  
COMMIT   

D. Suppression du système de contrôle de version

Cet exemple montre comment supprimer complètement les versions de système de la table Department et drop le DepartmentHistory table. Si vous le souhaitez, vous pouvez être amené à supprimer les colonnes des période utilisées par le système pour enregistrer les informations de contrôle de version de système. Notez que vous ne pouvez pas supprimer un le Department ou le DepartmentHistory tables pendant que le système de contrôle de version est activé.

ALTER TABLE Department  
    SET (SYSTEM_VERSIONING = OFF);  
ALTER TABLE Department  
DROP PEROD FOR SYSTEM_TIME;  
DROP TABLE DepartmentHistory;  

Les exemples suivants A à C utilisent la table FactResellerSales dans les AdventureWorksPDW2012 base de données.

E. Déterminer si une table est partitionnée.

La requête suivante renvoie une ou plusieurs lignes si la table FactResellerSales est partitionnée. Si la table n'est pas partitionnée, aucune ligne n'est retournée.

SELECT * FROM sys.partitions AS p  
JOIN sys.tables AS t  
    ON  p.object_id = t.object_id  
WHERE p.partition_id IS NOT NULL  
    AND t.name = 'FactResellerSales';  

F. Détermination des valeurs limites pour une table partitionnée

La requête suivante renvoie les valeurs limites pour chaque partition de la table FactResellerSales .

SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue   
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
    ON i.object_id = p.object_id AND i.index_id = p.index_id   
JOIN  sys.partition_schemes AS s   
    ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f   
    ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r   
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
WHERE t.name = 'FactResellerSales' AND i.type <= 1  
ORDER BY p.partition_number;  

G. Détermination de la colonne de partition pour une table partitionnée

La requête suivante renvoie le nom de la colonne de partitionnement pour une table. FactResellerSales.

SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id as PartitioningColumnID, c.name AS PartitioningColumnName   
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.columns AS c  
    ON t.object_id = c.object_id  
JOIN sys.partition_schemes AS ps  
    ON ps.data_space_id = i.data_space_id  
JOIN sys.index_columns AS ic  
    ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal > 0  
WHERE t.name = 'FactResellerSales'  
AND i.type <= 1  
AND c.column_id = ic.column_id;  

H. Fusionner deux partitions

L’exemple suivant fusionne deux partitions sur une table.

Le Customer table a la définition suivante :

CREATE TABLE Customer (  
    id int NOT NULL,  
    lastName varchar(20),  
    orderCount int,  
    orderDate date)  
WITH   
    ( DISTRIBUTION = HASH(id),  
    PARTITION ( orderCount RANGE LEFT  
    FOR VALUES (1, 5, 10, 25, 50, 100)));  

La commande suivante combine les limites de partition 10 et 25.

ALTER TABLE Customer MERGE RANGE (10);  

Le nouveau DDL pour la table est la suivante :

CREATE TABLE Customer (  
    id int NOT NULL,  
    lastName varchar(20),  
    orderCount int,  
    orderDate date)  
WITH   
    ( DISTRIBUTION = HASH(id),  
    PARTITION ( orderCount RANGE LEFT  
    FOR VALUES (1, 5, 25, 50, 100)));  

I. Fractionnement d’une partition

L’exemple suivant fractionne une partition sur une table.

Le Customer table comporte le DDL suivant :

DROP TABLE Customer;  
  
CREATE TABLE Customer (  
    id int NOT NULL,  
    lastName varchar(20),  
    orderCount int,  
    orderDate date)  
WITH   
    ( DISTRIBUTION = HASH(id),  
    PARTITION ( orderCount RANGE LEFT  
    FOR VALUES (1, 5, 10, 25, 50, 100 )));  

La commande suivante crée une nouvelle partition liée par la valeur de 75, entre 50 et 100.

ALTER TABLE Customer SPLIT RANGE (75);  

Le nouveau DDL pour la table est la suivante :

CREATE TABLE Customer (  
   id int NOT NULL,  
   lastName varchar(20),  
   orderCount int,  
   orderDate date)  
   WITH DISTRIBUTION = HASH(id),  
   PARTITION ( orderCount (RANGE LEFT  
      FOR VALUES (1, 5, 10, 25, 50, 75, 100 )));  

J. Utilisation du COMMUTATEUR pour déplacer une partition dans une table d’historique

L’exemple suivant déplace les données dans une partition de le Orders table à une partition dans la OrdersHistory table.

Le Orders table comporte le DDL suivant :

CREATE TABLE Orders (  
    id INT,  
    city VARCHAR (25),  
    lastUpdateDate DATE,  
    orderDate DATE )  
WITH   
    (DISTRIBUTION = HASH ( id ),  
    PARTITION ( orderDate RANGE RIGHT   
    FOR VALUES ('2004-01-01', '2005-01-01', '2006-01-01', '2007-01-01' )));  

Dans cet exemple, le Orders table possède les partitions suivantes. Chaque partition contient des données.

PartitionContient des données ?Plage de limite
1OuiOrderDate < ' 2004-01-01 »
2Oui« 2004-01-01' < = OrderDate < ' 01 / 01/2005 »
3Oui« 2005-01-01' < = OrderDate < « 2006-01-01 »
4Oui« 2006-01-01'< = OrderDate < ' 2007-01-01 »
5Oui« 2007-01-01' < = OrderDate
  • Partition 1 (contient des données) : OrderDate < ' 2004-01-01 »

  • Partition 2 (contient des données) : ' 2004-01-01' < = OrderDate < ' 01 / 01/2005 »

  • Partition 3 (a des données) : « 2005-01-01' < = OrderDate < ' 2006-01-01 »

  • Partition 4 (contient des données) : « 2006-01-01'< = OrderDate < ' 2007-01-01 »

  • La partition 5 (contient des données) : « 2007-01-01' < = OrderDate

Le OrdersHistory table comporte le DDL suivant, qui a des noms de colonnes et les colonnes comme le Orders table. Les deux sont distribuées de hachage sur le id colonne.

CREATE TABLE OrdersHistory (  
   id INT,  
   city VARCHAR (25),  
   lastUpdateDate DATE,  
   orderDate DATE )  
WITH   
    (DISTRIBUTION = HASH ( id ),  
    PARTITION ( orderDate RANGE RIGHT   
    FOR VALUES ( '2004-01-01' )));  

Bien que les colonnes et les noms de colonnes doivent être identiques, les limites de partition est inutile à l’identique. Dans cet exemple, le OrdersHistory table comporte deux partitions suivantes et les deux partitions sont vides :

  • Partition 1 (aucune donnée) : OrderDate < ' 2004-01-01 »

  • Partition 2 (vide) : « 2004-01-01' < = OrderDate

Pour les deux tables précédentes, la commande suivante déplace toutes les lignes avec OrderDate < '2004-01-01' à partir de la Orders de la table vers le OrdersHistory table.

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;  

Par conséquent, la première dans la partition Orders est vide et la première partition dans OrdersHistory contient des données. Les tables apparaissent désormais comme suit :

Orders table

  • Partition 1 (vide) : OrderDate < ' 2004-01-01 »

  • Partition 2 (contient des données) : ' 2004-01-01' < = OrderDate < ' 01 / 01/2005 »

  • Partition 3 (a des données) : « 2005-01-01' < = OrderDate < ' 2006-01-01 »

  • Partition 4 (contient des données) : « 2006-01-01'< = OrderDate < ' 2007-01-01 »

  • La partition 5 (contient des données) : « 2007-01-01' < = OrderDate

OrdersHistory table

  • Partition 1 (contient des données) : OrderDate < ' 2004-01-01 »

  • Partition 2 (vide) : « 2004-01-01' < = OrderDate

Pour nettoyer le Orders table, vous pouvez supprimer la partition vide à la fusion de partitions 1 et 2 comme suit :

ALTER TABLE Orders MERGE RANGE ('2004-01-01');  

Après la fusion, la Orders table possède les partitions suivantes :

Orders table

  • Partition 1 (contient des données) : OrderDate < ' 01 / 01/2005 »

  • Partition 2 (contient des données) : « 2005-01-01' < = OrderDate < « 2006-01-01 »

  • Partition 3 (a des données) : « 2006-01-01'< = OrderDate < ' 2007-01-01 »

  • Partition 4 (contient des données) : « 2007-01-01' < = OrderDate

Supposons qu’une autre année passe et que vous êtes prêt à archiver l’année 2005. Vous pouvez allouer une partition vide pour l’année 2005 dans le OrdersHistory tableau en fractionnant la partition vide comme suit :

ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');  

Après le fractionnement, la OrdersHistory table possède les partitions suivantes :

OrdersHistory table

  • Partition 1 (contient des données) : OrderDate < ' 2004-01-01 »

  • Partition 2 (vide) : « 2004-01-01' < ' 01 / 01/2005 »

  • Partition 3 (vide) : « 2005-01-01' < = OrderDate

Sys.tables &#40 ; Transact-SQL &#41 ;
sp_rename &#40 ; Transact-SQL &#41 ;
CRÉER une TABLE &#40 ; Transact-SQL &#41 ;
DROP TABLE &#40 ; Transact-SQL &#41 ;
sp_help &#40 ; Transact-SQL &#41 ;
ALTER PARTITION SCHEME &#40 ; Transact-SQL &#41 ;
ALTER PARTITION FUNCTION &#40 ; Transact-SQL &#41 ;
EVENTDATA &#40 ; Transact-SQL &#41 ;

Ajouts de la communauté

AJOUTER
Afficher: