※ Download: Set foreign key check
This prevents all user access to the underlying table for the duration of the operation. The FOREIGN KEY constraint differs from the PRIMARY KEY constraint in that, you can create only one PRIMARY KEY per each table, with the ability to create multiple FOREIGN KEY constraints in each table by referencing multiple parent table. Once done, do not forget to go back to the foreign key constrained world of data.
The COLLATE clause can be used to change the collations only of columns of the char, varchar, nchar, and nvarchar data types. NET Framework user-defined types are created with the CREATE TYPE statement before they can be used in a table definition. Changing column collation The following example shows how to change the collation of a column. Specifies the Windows-compatible FileTable directory name.
- The maximum is 64 processors. This is creating a headache because the tables must all be in the exact order expected by the foreign keys when they are migrated.
Important The syntax for ALTER TABLE is different for disk-based tables and memory-optimized tables. If the table is not in the current database or is not contained by the schema owned by the current user, the database and schema must be explicitly specified. ALTER COLUMN Specifies that the named column is to be changed or altered. First, remove the statistics using the DROP STATISTICS statement. Statistics that are automatically generated by the query optimizer are automatically dropped by ALTER COLUMN. However, changing the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed. However, the length, precision, or scale of a column can be changed if the data type is not changed. For example, changing a nchar or nvarchar column, to char or varchar, may cause the conversion of extended characters. For more information, see. Reducing the precision or scale of a column may cause data truncation. Note The data type of a column of a partitioned table cannot be changed. The data type of columns included in an index cannot be changed unless the column is a varchar, nvarchar, or varbinary data type, and the new size is equal to or larger than the old size. A column included in a primary key constraint, cannot be changed from NOT NULL to NULL. When using Always Encrypted without secure enclaves , if the column being modified is encrypted using 'ENCRYPTED WITH', you can change the datatype to a compatible datatype such as INT to BIGINT but you cannot change any encryption settings. When using Always Encrypted with secure enclaves, you can change any encryption setting, as long as the column encryption key protecting the column and the new column encryption key, if you are changing the key support enclave computations are encrypted with enclave-enabled column master keys. Alias data types are created with the CREATE TYPE statement before they can be used in a table definition. NET Framework user-defined type, and the schema to which it belongs. NET Framework user-defined types are created with the CREATE TYPE statement before they can be used in a table definition. ALTER TABLE operates as if ARITHABORT is set to ON. Note If the COLLATE clause is not specified, changing the data type of a column will cause a collation change to the default collation of the database. For more information about valid precision values, see. For more information about valid scale values, see. Applies only to the xml data type for associating an XML schema with the type. Before typing an xml column to a schema collection, the schema collection must first be created in the database by using. COLLATE Specifies the new collation for the altered column. If not specified, the column is assigned the default collation of the database. Collation name can be either a Windows collation name or a SQL collation name. For a list and more information, see and. The COLLATE clause can be used to change the collations only of columns of the char, varchar, nchar, and nvarchar data types. To change the collation of a user-defined alias data type column, you must execute separate ALTER TABLE statements to change the column to a SQL Server system data type and change its collation, and then change the column back to an alias data type. Statistics created automatically on the column changed are dropped if the column collation is changed. For more information, see. NULL NOT NULL Specifies whether the column can accept null values. Columns that do not allow null values can be added with ALTER TABLE only if they have a default specified or if the table is empty. NOT NULL can be specified for computed columns only if PERSISTED is also specified. If the new column allows null values and no default is specified, the new column contains a null value for each row in the table. If the new column allows null values and a default definition is added with the new column, WITH VALUES can be used to store the default value in the new column for each existing row in the table. If the new column does not allow null values and the table is not empty, a DEFAULT definition must be added with the new column, and the new column automatically loads with the default value in the new columns in each existing row. NULL can be specified in ALTER COLUMN to force a NOT NULL column to allow null values, except for columns in PRIMARY KEY constraints. NOT NULL can be specified in ALTER COLUMN only if the column contains no null values. We recommend that you always explicitly define a column as NULL or NOT NULL for noncomputed columns. If you add a column with a user-defined data type, we recommend that you define the column with the same nullability as the user-defined data type and specify a default value for the column. For more information, see. If the data type, precision, and scale are not changed, specify the current column values. Specifies the ROWGUIDCOL property is added to or dropped from the specified column. ROWGUIDCOL indicates that the column is a row GUID column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column, and the ROWGUIDCOL property can be assigned only to a uniqueidentifier column. ROWGUIDCOL cannot be assigned to a column of a user-defined data type. ROWGUIDCOL does not enforce uniqueness of the values that are stored in the column and does not automatically generate values for new rows that are inserted into the table. To generate unique values for each column, either use the NEWID or NEWSEQUENTIALID function on INSERT statements or specify the NEWID or NEWSEQUENTIALID function as the default for the column. The column must be a computed column that is defined with a deterministic expression. For columns specified as PERSISTED, the Database Engine physically stores the computed values in the table and updates the values when any other columns on which the computed column depends are updated. By marking a computed column as PERSISTED, you can create indexes on computed columns defined on expressions that are deterministic, but not precise. For more information, see. Any computed column that is used as a partitioning column of a partitioned table must be explicitly marked PERSISTED. DROP NOT FOR REPLICATION Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database. Specifies that values are incremented in identity columns when replication agents perform insert operations. SPARSE Indicates that the column is a sparse column. The storage of sparse columns is optimized for null values. Sparse columns cannot be designated as NOT NULL. Converting a column from sparse to nonsparse or from nonsparse to sparse locks the table for the duration of the command execution. You may need to use the REBUILD clause to reclaim any space savings. For additional restrictions and more information about sparse columns, see. Specifies a dynamic data mask. For function parameters, see. Allows many alter column actions to be performed while the table remains available. Alter column can be performed on line for column changes related to data type, column length or precision, nullability, sparseness, and collation. Online alter column allows user created and auto statistics to reference the altered column for the duration of the ALTER COLUMN operation. This allows queries to perform as usual. At the end of the operation, auto-stats that reference the column are dropped and user-created stats are invalidated. The user must manually update user-generated statistics after the operation is completed. If the column is part of a filter expression for any statistics or indexes then you cannot perform an alter column operation. This guarantees that online alter column will not fail because of dependencies introduced while the operation was running. ALTER column is not performed as online regardless of which value was specified for ONLINE option. Online alter column has similar requirements, restrictions, and functionality as online index rebuild. The same limitations apply for online alter column. WITH CHECK WITH NOCHECK Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints. If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. We do not recommend doing this, except in rare cases. The new constraint will be evaluated in all later data updates. Any constraint violations that are suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint. The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL. Important Without using an ALTER TABLE statement, the statements , , , and are not supported for indexes on memory-optimized tables. ADD Specifies that one or more column definitions, computed column definitions, or table constraints are added, or the columns that the system will use for system versioning. For memory-optimized tables, an index can be added. Important Without using an ALTER TABLE statement, the statements , , , and are not supported for indexes on memory-optimized tables. Specifies the names of the columns that the system will use to record the period for which a record is valid. The columns must have the datatype of datetime2 and must be defined as NOT NULL. If a period column is defined as NULL, an error will be thrown. For more information, see and. By default, period columns are not hidden. In order to be used, hidden columns must be explicitly included in all queries that directly reference the temporal table. DROP Specifies that one or more column definitions, computed column definitions, or table constraints are dropped, or to drop the specification for the columns that the system will use for system versioning. Multiple constraints can be listed. The user-defined or system-supplied name of the constraint can be determined by querying the sys. A PRIMARY KEY constraint cannot be dropped if an XML index exists on the table. Important Without using an ALTER TABLE statement, the statements , , , and are not supported for indexes on memory-optimized tables. Multiple columns can be listed. Note Dropping a column does not reclaim the disk space of the column. You may have to reclaim the disk space of a dropped column when the row size of a table is near, or has exceeded, its limit. Reclaim space by creating a clustered index on the table or rebuilding an existing clustered index by using. For information about the impact of dropping LOB data types, see this. Drops the specification for the columns that the system will use for system versioning. WITH Specifies that one or more drop clustered constraint options are set. Overrides the max degree of parallelism configuration option only for the duration of the operation. For more information, see. Use the MAXDOP option to limit the number of processors used in parallel plan execution. The maximum is 64 processors. For more information, see. Note Parallel index operations are not available in every edition of SQL Server. For more information, see. The default is OFF. REBUILD can be performed as an ONLINE operation. ON Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share IS lock is held on the source table. This enables queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared S lock is held on the source object for a very short time. At the end of the operation, for a short time, an S Shared lock is acquired on the source if a nonclustered index is being created; or an SCH-M Schema Modification lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table. Only single-threaded heap rebuild operation is allowed. To execute the DDL for SWITCH or online index rebuild, all active blocking transactions running on a particular table must be completed. When executing, the SWITCH or rebuild operation prevents new transaction from starting and might significantly affect the workload throughput and temporarily delay access to the underlying table. OFF Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification Sch-M lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared S lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements. Multi-threaded heap rebuild operations are allowed. For more information, see. Note Online index operations are not available in every edition of SQL Server. For more information, see. Specifies a location to move the data rows currently in the leaf level of the clustered index. The table is moved to the new location. This option applies only to constraints that create a clustered index. Note In this context, default is not a keyword. This is the default setting. For more information, see. This option can only be used with FOREIGN KEY and CHECK constraints. When NOCHECK is specified, the constraint is disabled and future inserts or updates to the column are not validated against the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled. ALL Specifies that all constraints are either disabled with the NOCHECK option or enabled with the CHECK option. When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled. ALL Specifies that all triggers in the table are enabled or disabled. Specifies whether change tracking is enabled disabled for the table. By default, change tracking is disabled. This option is available only when change tracking is enabled for the database. For more information, see. To enable change tracking, the table must have a primary key. Specifies whether the Database Engine tracks which change tracked columns were updated. The default value is OFF. If reassigning a table's data as a partition to an already-existing partitioned table, or switching a partition from one partitioned table to another, the target partition must exist and it must be empty. If reassigning one partition's data to form a single table, the target table must already be created and it must be empty. Both the source table or partition, and the target table or partition, must reside in the same filegroup. The corresponding indexes, or index partitions, must also reside in the same filegroup. Many additional restrictions apply to switching partitions. These include user-defined type variables and user-defined functions. They cannot reference Transact-SQL expressions. Note that including the partition key to an existing unique index can change the uniqueness. For SWITCH restriction when using replication, see. Nonclustered columnstore indexes built for SQL Server 2016 CTP1, and for SQL Database before version V12 were in a read-only format. Nonclustered columnstore indexes must be rebuilt to the current format which is updatable before any PARTITION operations can be performed. Azure SQL Database does not support FILESTREAM. Specifies where FILESTREAM data is stored. The FILESTREAM columns can be added by using a second ALTER TABLE statement. The table should already be partitioned for row data, and its partition scheme must use the same partition function and columns as the FILESTREAM partition scheme. The filegroup must have one file that is defined for the filegroup by using a or statement, or an error is raised. If there is no FILESTREAM filegroup, an error is raised. All FILESTREAM columns must be dropped first. Either disables system versioning of a table or enables system versioning of a table. To enable system versioning of a table, the system verifies that the datatype, nullability constraint, and primary key constraint requirements for system versioning are met. When creating a link to an existing history table, you can choose to perform a data consistency check. This data consistency check ensures that existing records do not overlap. Performing the data consistency check is the default. For more information, see. Specifies finite or infinte retention for historical data in temporal table. If omitted, infinite retention is assumed. Specifies the allowed methods of lock escalation for a table. AUTO This option allows SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema. After the lock is escalated to the partition level, the lock will not be escalated later to TABLE granularity. TABLE Lock escalation will be done at table-level granularity regardless whether the table is partitioned or not partitioned. TABLE is the default value. DISABLE Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity. REBUILD Use the REBUILD WITH syntax to rebuild an entire table including all the partitions in a partitioned table. If the table has a clustered index, the REBUILD option rebuilds the clustered index. REBUILD can be performed as an ONLINE operation. Use the REBUILD PARTITION syntax to rebuild a single partition in a partitioned table. Rebuilds all partitions when changing the partition compression settings. REBUILD WITH All options apply to a table with a clustered index. If the table does not have a clustered index, the heap structure is only affected by some of the options. When a specific compression setting is not specified with the REBUILD operation, the current compression setting for the partition is used. For complete descriptions of the rebuild options, see. Specifies the data compression option for the specified table, partition number, or range of partitions. The options are as follows: NONE Table or specified partitions are not compressed. This does not apply to columnstore tables. ROW Table or specified partitions are compressed by using row compression. This does not apply to columnstore tables. PAGE Table or specified partitions are compressed by using page compression. This does not apply to columnstore tables. COLUMNSTORE Applies to: SQL Server 2014 12. Applies only to columnstore tables. When the data is restored, it will continue to be compressed with the columnstore compression that is used for all columnstore tables. Applies only to columnstore tables, which are tables stored with a clustered columnstore index. This can be used for archival, or for other situations that require less storage and can afford more time for storage and retrieval To rebuild multiple partitions at the same time, see. If the table does not have a clustered index, changing the data compression rebuilds the heap and the nonclustered indexes. For more information about compression, see. The default is OFF. REBUILD can be performed as an ONLINE operation. ON Long-term table locks are not held for the duration of the index operation. A S-lock on the table is required in the beginning of the index rebuild and a Sch-M lock on the table at the end of the online index rebuild. Although both locks are short metadata locks, especially the Sch-M lock must wait for all blocking transactions to be completed. During the wait time the Sch-M lock blocks all other transactions that wait behind this lock when accessing the same table. OFF Table locks are applied for the duration of the index operation. This prevents all user access to the underlying table for the duration of the operation. Is the name of the column set. A column set is an untyped XML representation that combines all of the sparse columns of a table into a structured output. A column set cannot be added to a table that contains sparse columns. For more information about column sets, see. Enables or disables the system-defined constraints on a FileTable. Can only be used with a FileTable. Azure SQL Database does not support FILETABLE. Specifies the Windows-compatible FileTable directory name. This name should be unique among all the FileTable directory names in the database. Uniqueness comparison is case-insensitive, regardless of SQL collation settings. Can only be used with a FileTable. Enables or disables Stretch Database for a table. For more info, see. For more info about enabling Stretch for a table, see. Before you enable Stretch for a table, you have to enable Stretch on the server and on the database. For more info, see. Enabling Stretch for a table also requires ALTER permissions on the table. Disabling Stretch Database for a table When you disable Stretch for a table, you have two options for the remote data that has already been migrated to Azure. For more info, see. This command can't be canceled. For more info, see. After all the remote data has been copied from Azure back to SQL Server, Stretch is disabled for the table. Disabling Stretch does not remove the remote table. If you want to delete the remote table, you have to drop it by using the Azure management portal. Optionally specifies a filter predicate to select rows to migrate from a table that contains both historical and current data. The predicate must call a deterministic inline table-valued function. For more info, see and. Important If you provide a filter predicate that performs poorly, data migration also performs poorly. Stretch Database applies the filter predicate to the table by using the CROSS APPLY operator. If you don't specify a filter predicate, the entire table is migrated. For more info, see. This operation incurs data transfer costs, and it can't be canceled. For more info, see. An online index rebuild has to wait for blocking operations on this table. The wait time an integer value specified in minutes that the SWITCH or online index rebuild locks will wait with low priority when executing the DDL command. NONE Continue waiting for the lock with normal regular priority. SELF Exit the SWITCH or online index rebuild DDL operation currently being executed without taking any action. BLOCKERS Kill all user transactions that block currently the SWITCH or online index rebuild DDL operation so that the operation can continue. Requires ALTER ANY CONNECTION permission. IF EXISTS Applies to: SQL Server SQL Server 2016 13. Conditionally drops the column or constraint only if it already exists. Remarks To add new rows of data, use. To remove rows of data, use or. To change the values in existing rows, use. If there are any execution plans in the procedure cache that reference the table, ALTER TABLE marks them to be recompiled on their next execution. Changing the Size of a Column You can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. If data exists in the column, the new size cannot be smaller than the maximum size of the data. Also, the column cannot be defined in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index is not the result of a PRIMARY KEY constraint. Locks and ALTER TABLE The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify SCH-M lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows. Adding NOT NULL Columns as an Online Operation Starting with SQL Server 2012 11. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. In contrast, the functions NEWID or NEWSEQUENTIALID are not runtime constants because a unique value is produced for each row in the table. Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive SCH-M lock is acquired for the duration of the operation. While the existing rows reference the value stored in metadata, the default value is stored on the row for any new rows that are inserted and do not specify another value for the column. The default value stored in metadata is moved to an existing row when the row is updated even if the actual column is not specified in the UPDATE statement , or if the table or clustered index is rebuilt. Columns of type varchar max , nvarchar max , varbinary max , xml, text, ntext, image, hierarchyid, geometry, geography, or CLR UDTS, cannot be added in an online operation. A column cannot be added online if doing so causes the maximum possible row size to exceed the 8,060 byte limit. The column is added as an offline operation in this case. Parallel Plan Execution In Microsoft SQL Server 2012 Enterprise and higher, the number of processors employed to run a single ALTER TABLE ADD index based CONSTRAINT or DROP clustered index CONSTRAINT statement is determined by the max degree of parallelism configuration option and the current workload. If the Database Engine detects that the system is busy, the degree of parallelism of the operation is automatically reduced before statement execution starts. You can manually configure the number of processors that are used to run the statement by specifying the MAXDOP option. For more information, see. Partitioned Tables In addition to performing SWITCH operations that involve partitioned tables, ALTER TABLE can be used to change the state of the columns, constraints, and triggers of a partitioned table just like it is used for nonpartitioned tables. However, this statement cannot be used to change the way the table itself is partitioned. To repartition a partitioned table, use and. Additionally, you cannot change the data type of a column of a partitioned table. Restrictions on Tables with Schema-Bound Views The restrictions that apply to ALTER TABLE statements on tables with schema-bound views are the same as the restrictions currently applied when modifying tables with a simple index. Adding a column is allowed. However, removing or changing a column that participates in any schema-bound view is not allowed. If the ALTER TABLE statement requires changing a column used in a schema-bound view, ALTER TABLE fails and the Database Engine raises an error message. For more information about schema binding and indexed views, see. Adding or removing triggers on base tables is not affected by creating a schema-bound view that references the tables. Indexes and ALTER TABLE Indexes created as part of a constraint are dropped when the constraint is dropped. Indexes that were created with CREATE INDEX must be dropped with DROP INDEX. The ALTER INDEX statement can be used to rebuild an index part of a constraint definition; the constraint does not have to be dropped and added again with ALTER TABLE. All indexes and constraints based on a column must be removed before the column can be removed. When a constraint that created a clustered index is deleted, the data rows that were stored in the leaf level of the clustered index are stored in a nonclustered table. You can drop the clustered index and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option. Temporary disk space equal to the size of the existing clustered index is required to drop a clustered index. This additional space is released as soon as the operation is completed. Note The options listed under apply to clustered indexes on tables and cannot be applied to clustered indexes on views or nonclustered indexes. Replicating Schema Changes By default, when you run ALTER TABLE on a published table at a SQL Server Publisher, that change is propagated to all SQL Server Subscribers. This functionality has some restrictions and can be disabled. For more information, see. Data Compression System tables cannot be enabled for compression. If the table is a heap, the rebuild operation for ONLINE mode will be single threaded. Use OFFLINE mode for a multi-threaded heap rebuild operation. For a more information about data compression, see. To evaluate how changing the compression state will affect a table, an index, or a partition, use the stored procedure. Dropping NTEXT Columns When dropping NTEXT columns, the cleanup of the deleted data occurs as a serialized operation on all rows. This can require a substantial time. When dropping an NTEXT column in a table with a large number rows, update the NTEXT column to NULL value first, then drop the column. This can be performed with parallel operations and can be much faster. Online Index Rebuild In order to execute the DDL statement for an online index rebuild, all active blocking transactions running on a particular table must be completed. When the online index rebuild executes, it blocks all new transactions that are ready to start execution on this table. Although the duration of the lock for online index rebuild is very short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, might significantly affect the throughput, causing a workload slow down or timeout, and significantly limit access to the underlying table. Compatibility Support The ALTER TABLE statement allows only two-part schema. In SQL Server 2017, specifying a table name using the following formats fails at compile time with error 117. To resolve the problem, remove the use of a 4-part prefix. Permissions Requires ALTER permission on the table. ALTER TABLE permissions apply to both tables involved in an ALTER TABLE SWITCH statement. Any data that is switched inherits the security of the target table. If any columns in the ALTER TABLE statement are defined to be of a common language runtime CLR user-defined type or alias data type, REFERENCES permission on the type is required. Adding a column that updates the rows of the table requires UPDATE permission on the table. For example, adding a NOT NULL column with a default value or adding an identity column when the table is not empty. Adding a new column The following example adds a column that allows null values and has no values provided through a DEFAULT definition. In the new column, each row will have NULL. Adding a column with a constraint The following example adds a new column with a UNIQUE constraint. Adding an unverified CHECK constraint to an existing column The following example adds a constraint to an existing column in the table. The column has a value that violates the constraint. Therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow for the constraint to be added. Adding a DEFAULT constraint to an existing column The following example creates a table with two columns and inserts a value into the first column, and the other column remains NULL. A DEFAULT constraint is then added to the second column. To verify that the default is applied, another value is inserted into the first column, and the table is queried. Adding several columns with constraints The following example adds several columns with constraints defined with the new column. The first new column has an IDENTITY property. Each row in the table has new incremental values in the identity column. Adding a nullable column with default values The following example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column. The resulting clustered index will have the same name as the constraint. Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database. USE AdventureWorks; GO ALTER TABLE Production. USE AdventureWorks; GO ALTER TABLE Production. Adding a sparse column The following examples show adding and modifying sparse columns in table T1. The code to create table T1 is as follows. CREATE TABLE T1 C1 int PRIMARY KEY, C2 varchar 50 SPARSE NULL, C3 int SPARSE NULL, C4 int ; GO To add an additional sparse column C5, execute the following statement. ALTER TABLE T1 ADD C5 char 100 SPARSE NULL ; GO To convert the C4 non-sparse column to a sparse column, execute the following statement. ALTER TABLE T1 ALTER COLUMN C4 ADD SPARSE ; GO To convert the C4 sparse column to a nonsparse column, execute the following statement. ALTER TABLE T1 ALTER COLUMN C4 DROP SPARSE; GO I. Adding a column set The following examples show adding a column to table T2. A column set cannot be added to a table that already contains sparse columns. The code to create table T2 is as follows. CREATE TABLE T2 C1 int PRIMARY KEY, C2 varchar 50 NULL, C3 int NULL, C4 int ; GO The following three statements add a column set named CS, and then modify columns C2 and C3 to SPARSE. Adding an encrypted column The following statement adds an encrypted column named PromotionCode. Dropping a column or columns The first example modifies a table to remove a column. The second example removes multiple columns. Dropping constraints and columns The first example removes a UNIQUE constraint from a table. The second example removes two constraints and a single column. Remove a single constraint. Remove two constraints and one column -- The keyword CONSTRAINT is optional. The keyword COLUMN is required. Dropping a PRIMARY KEY constraint in the ONLINE mode The following example deletes a PRIMARY KEY constraint with the ONLINE option set to ON. Adding and dropping a FOREIGN KEY constraint The following example creates the table ContactBackup, and then alters the table, first by adding a FOREIGN KEY constraint that references the table Person. Person, then by dropping the FOREIGN KEY constraint. ContactBackup ContactID int ; GO ALTER TABLE Person. Person BusinessEntityID ; GO ALTER TABLE Person. ContactBackup ; Altering a Column Definition A. Changing the data type of a column The following example changes a column of a table from INT to DECIMAL. Changing the size of a column The following example increases the size of a varchar column and the precision and scale of a decimal column. Because the columns contain data, the column size can only be increased. Changing column collation The following example shows how to change the collation of a column. First, a table is created table with the default user collation. Note that the data type is required, even though it is not changed. Encrypting a column The following example shows how to encrypt a column using. First, a table is created without any encrypted columns. CREATE TABLE T3 C1 int PRIMARY KEY, C2 varchar 50 NULL, C3 int NULL, C4 int ; GO Next, column 'C2' is encrypted with a column encryption key, named CEK1, and randomized encryption. Modifying a table to change the compression The following example changes the compression of a nonpartitioned table. The heap or clustered index will be rebuilt. If the table is a heap, all nonclustered indexes will be rebuilt. Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database. Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database. Modifying a columnstore table to change archival compression The following example further compresses a columnstore table partition by applying an additional compression algorithm. This reduces the table to a smaller size, but also increases the time required for storage and retrieval. This is useful for archiving or for situations that require less space and can afford more time for storage and retrieval. Applies to: SQL Server 2014 12. When the data is restored, it will continue to be compressed with the columnstore compression that is used for all columnstore tables. Applies to: SQL Server 2014 12. Switching partitions between tables The following example creates a partitioned table, assuming that partition scheme myRangePS1 is already created in the database. Next, a non-partitioned table is created with the same structure as the partitioned table and on the same filegroup as PARTITION 2 of table PartitionTable. The data of PARTITION 2 of table PartitionTable is then switched into 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. Allowing lock escalation on partitioned tables The following example enables lock escalation to the partition level on a partitioned table. If the table is not partitioned, lock escalation is set at the TABLE level. Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database. Configuring change tracking on a table The following example enables change tracking on the Person. Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database. USE AdventureWorks; ALTER TABLE Person. Applies to: SQL Server 2008 through SQL Server 2017. USE AdventureWorks; GO ALTER TABLE Person. Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database. USE AdventureWorks; Go ALTER TABLE Person. Disabling and re-enabling a constraint The following example disables a constraint that limits the salaries accepted in the data. NOCHECK CONSTRAINT is used with ALTER TABLE to disable the constraint and allow for an insert that would typically violate the constraint. CHECK CONSTRAINT re-enables the constraint. Online index rebuild using low priority wait options The following example shows how to perform an online index rebuild specifying the low priority wait options. Applies to: SQL Server 2014 12. Online Alter Column The following example shows how to perform an alter column operation with the ONLINE option. Applies to: SQL Server 2016 13. For additional assistance, see. Applies to: SQL Server 2016 13. Add System Versioning to Existing Tables The following example shows how to add system versioning to an existing table and create a future history table. This example assumes that there is an existing table called InsurancePolicy with a primary key defined. This example populates the newly created period columns for system versioning using default values for the start and end times because these values cannot be null. This example uses the HIDDEN clause to ensure no impact on existing applications interacting with the current table. Migrate An Existing Solution to Use System Versioning The following example shows how to migrate to system versioning from a solution that uses triggers to mimic temporal support. The example assumes there is an existing solution that uses a ProjectTaskCurrent table and a ProjectTaskHistory table for its existing solution, that is uses the Changed Date and Revised Date columns for its periods, that these period columns do not use the datetime2 datatype and that the ProjectTaskCurrent table has a primary key defined. Disabling and Re-Enabling System Versioning to Change Table Schema This example shows how to disable system versioning on the Department table, add a column, and re-enable system versioning. Disabling system versioning is required in order to modify the table schema. Perform these steps within a transaction to prevent updates to both tables while updating the table schema, which enables the DBA to skip the data consistency check when re-enabling system versioning and gain a performance benefit. Note that tasks such as creating statistics, switching partitions or applying compression to one or both tables does not require disabling system versioning. Removing System Versioning This example shows how to completely remove system versioning from the Department table and drop the DepartmentHistory table. Optionally, you might also want to drop the period columns used by the system to record system versioning information. Note that you cannot drop either the Department or the DepartmentHistory tables while system versioning is enabled. Determining if a table is partitioned The following query returns one or more rows if the table FactResellerSales is partitioned. If the table is not partitioned, no rows are returned. Determining boundary values for a partitioned table The following query returns the boundary values for each partition in the FactResellerSales table. Merging two partitions The following example merges two partitions on a table. Splitting a partition The following example splits a partition on a table. Using SWITCH to move a partition to a history table The following example moves the data in a partition of the Orders table to a partition in the OrdersHistory table. Each partition contains data. Both are hash-distributed on the id column. ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1; As a result, the first partition in Orders is empty and the first partition in OrdersHistory contains data.
A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. So coming back to the objective of pinning down this post, some days ago I came across the requirement of deleting some user records from the user table. This is the whole reason set foreign key check the referential checks on integrity. Once done, do not forget to go back to the foreign key constrained world of data. VendorID, the Database Engine checks for one or more dependent rows in the ProductVendor table. The user-defined or system-supplied name of the constraint can be determined by querying the sys. Restores and backups might be the possible exception but generally the folks doing that work know what there doing.
