一些關鍵的SQL語句及ALTER TABLE

1.查詢MSSQL系統版本

select @@version

2.日志壓縮删除到1MB

use rtool_1;

BACKUP LOG rtool_1 WITH NO_LOG;

DBCC SHRINKDATABASE (rtool_1);

3.備份資料庫

backup database RTool to disk='D:/rtool.bak'

4.恢複資料庫

restore database RTool FROM disk='D:/rtool.bak'

5.日期時間函數應用

SELECT year(getdate()),month(getdate()),day(getdate())

SELECT DATEADD (datepart , number, date )

SELECT DATEDIFF(datepart, startDate, endDate)

DATEPART(标志量):

year,quarter(季度),month,dayofyear,day, week, weekday,

hour,minute,second,millisecond(毫),microsecond(微),nanosecond(納)

SELECT DATEADD(MONTH, -1, GETDATE())

傳回上個月201005

SELECT CONVERT(CHAR(4), YEAR(DATEADD(YEAR, -1, GETDATE()))) + '12'

傳回上年年底201012

http://msdn.microsoft.com/zh-cn/library/ms186724.aspx

6.union all 投影(左右的字段一緻)

7.ALTER文法：

--表中增加一列

ALTER TABLE table_name ADD column_name datatype

--表中一列删除

ALTER TABLE table_name DROP COLUMN column_name

--表中一列增加主鍵限制

ALTER TABLE rtoolLog  ADD CONSTRAINT PK_rtoolLog PRIMARY KEY(rtlIndex)

--表中一列删除主鍵限制

ALTER TABLE rtoolLog DROP CONSTRAINT PK_rtoolLog WITH (ONLINE = ON);

--表中一列修改屬性

ALTER TABLE table_name ALTER COLUMN column_name datatype

--表中非聚集索引建立

CREATE NONCLUSTERED INDEX [PHM_syoukyaku_idx1] ON [dbo].[PHM_syoukyaku]

(

 [HMsyo_ScenarioId] ASC,

 [HMsyo_BranchNo] ASC,

 [HMsyo_CustomerNo] ASC,

 [HMsyo_KijyunNengetu] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

--表中索引删除

USE [RTool_1]

GO

DROP INDEX PHM_syoukyaku_idx1 ON PHM_syoukyaku

--表中一列修改為自動增長字段（先删除限制，再删除字段，再次建立字段）

ALTER TABLE rtoolLog DROP CONSTRAINT PK_rtoolLog WITH (ONLINE = ON);

ALTER TABLE rtoolLog  drop column rtlIndex

ALTER TABLE rtoolLog  ADD rtlIndex bigint not null IDENTITY(1,1) CONSTRAINT PK_rtoolLog PRIMARY KEY(rtlIndex)

ALTER TABLE (Transact-SQL)

更新日期： 2008 年 12 月 4 日

通過更改、添加或删除列和限制，重新配置設定分區，或者啟用或禁用限制和觸發器，進而修改表的定義。

 文法

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 ] 

        [ SPARSE | NULL | NOT NULL ] 

    | {ADD | DROP } 

        { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }

    } 

        | [ WITH { CHECK | NOCHECK } ]



    | ADD 

    { 

        <column_definition>

      | <computed_column_definition>

      | <table_constraint> 

      | <column_set_definition> 

    } [ ,...n ]



    | DROP 

    { 

        [ CONSTRAINT ] constraint_name 

        [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]

        | COLUMN column_name 

    } [ ,...n ] 



    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT 

        { ALL | constraint_name [ ,...n ] } 



    | { ENABLE | DISABLE } TRIGGER 

        { ALL | trigger_name [ ,...n ] }



    | { ENABLE | DISABLE } CHANGE_TRACKING 

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



    | SWITCH [ PARTITION source_partition_number_expression ]

        TO target_table 

        [ PARTITION target_partition_number_expression ]



    | SET ( FILESTREAM_ON = { partition_scheme_name | filegroup | 

                "default" | "NULL" } )



    | REBUILD 

      [ [PARTITION = ALL]

        [ WITH ( <rebuild_option> [ ,...n ] ) ] 

      | [ PARTITION = partition_number 

           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]

        ]

      ]



    | (<table_option>)

}

[ ; ]



<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 } )

    }



<single_partition_rebuild__option> ::=

{

      SORT_IN_TEMPDB = { ON | OFF }

    | MAXDOP = max_degree_of_parallelism

    | DATA_COMPRESSION = { NONE | ROW | PAGE} }

}      

 參數

database_name

要在其中建立表的資料庫的名稱。

schema_name

表所屬架構的名稱。

table_name

要更改的表的名稱。如果表不在目前資料庫中，或者不包含在目前使用者所擁有的架構中，則必須顯式指定資料庫和架構。

ALTER COLUMN

指定要更改命名列。如果相容級别等于或低于 65，則不允許使用 ALTER COLUMN。有關詳細資訊，請參閱sp_dbcmptlevel (Transact-SQL) 。

修改後的列不能為下列任何一種列：

• 資料類型為 timestamp 的列。
• 表的 ROWGUIDCOL 列。
• 計算列或用于計算列的列。
• 用在索引中的列，除非該列資料類型為 varchar 、nvarchar 或 varbinary ，資料類型沒有更改，新的大小等于或者大于舊的大小并且索引不是 PRIMARY KEY 限制的結果。
• 用在 CREATE STATISTICS 語句生成的統計資訊中的列，除非該列的資料類型為 varchar 、nvarchar 或 varbinary ，資料類型沒有更改，新的大小等于或大于舊的大小，或者該列從非 NULL 更改為 NULL。首先，用 DROP STATISTICS 語句删除統計資訊。由查詢優化器自動生成的統計資訊将被 ALTER COLUMN 自動删除。
• 用于 PRIMARY KEY 或 [FOREIGN KEY] REFERENCES 限制中的列。
• 用于 CHECK 或 UNIQUE 限制中的列。但是，允許更改用于 CHECK 或 UNIQUE 限制中的長度可變的列的長度。

• 與預設定義關聯的列。但是，如果不更改資料類型，則可以更改列的長度、精度或小數位數。

  僅能通過下列方式更改 text 、ntext 和 image 列的資料類型：

  • text 改為 varchar(max) 、nvarchar(max) 或 xml
  • ntext 改為 varchar(max) 、nvarchar(max) 或 xml
  • image 改為varbinary(max)

  某些資料類型的更改可能導緻資料的更改。例如，如果将 nchar 或 nvarchar 列改為 char 或 varchar ，則可能導緻轉換擴充字元。有關詳細資訊，請參閱 CAST 和 CONVERT (Transact-SQL) 。降低列的精度或減少小數位數可能導緻資料截斷。

  無法更改已分區表的列的資料類型。

column_name

要更改、添加或删除的列的名稱。column_name 最多可以包含 128 個字元。對于新列，如果建立列時使用的資料類型為 timestamp ，則可以省略 column_name 。如果沒有為 timestamp 資料類型列指定 column_name ，則使用名稱 timestamp 。

[ type_schema_name . ] type_name

更改後的列的新資料類型或添加的列的資料類型。不能為已分區表的現有列指定 type_name 。type_name 可以為下列任意一種類型：

• SQL Server 系統資料類型。
• 基于 SQL Server 系統資料類型的别名資料類型。必須先用 CREATE TYPE 語句建立别名資料類型，然後才能将其用于表定義中。
• .NET Framework 使用者定義類型及其所屬的架構。隻有在使用 CREATE TYPE 語句建立了 .NET Framework 使用者定義類型後，才能将其用于表定義。

更改後的列的 type_name 應符合下列條件：

• 以前的資料類型必須可以隐式轉換為新資料類型。
• type_name 不能為 timestamp 。
• 對于 ALTER COLUMN，ANSI_NULL 預設值始終為 ON；如果沒有指定，列可為空。
• 對于 ALTER COLUMN，ANSI_PADDING 填充始終為 ON。
• 如果修改後的列是辨別列，則 new_data_type 必須是支援辨別屬性的資料類型。
• 目前的 SET ARITHABORT 設定将被忽略。ALTER TABLE 的操作方式與 ARITHABORT 設定為 ON 時相同。

注意：
如果未指定 COLLATE 子句，則更改列的資料類型将導緻更改資料庫的預設排序規則。

precision

指定的資料類型的精度。有關有效精度值的詳細資訊，請參閱精度、小數位數和長度 (Transact-SQL) 。

scale

是指定資料類型的小數位數。有關有效小數位數值的詳細資訊，請參閱精度、小數位數和長度 (Transact-SQL) 。

max

僅應用于 varchar 、nvarchar 和 varbinary 資料類型，以便存儲 2^31-1 個位元組的字元、二進制資料以及 Unicode 資料。

xml_schema_collection

僅應用于 xml 資料類型，以便将 XML 架構與類型相關聯。在架構集合中鍵入 xml 列之前，必須首先使用 CREATE XML SCHEMA COLLECTION 在資料庫中建立架構集合。

COLLATE < collation_name >

指定更改後的列的新排序規則。如果未指定，則為該列配置設定資料庫的預設排序規則。排序規則名稱既可以是 Windows 排序規則名稱，也可以是 SQL 排序規則名稱。有關清單及詳細資訊，請參閱 Windows 排序規則名稱 (Transact-SQL) 和 SQL Server 排序規則名稱 (Transact-SQL) 。

COLLATE 子句隻能用來更改資料類型為 char 、varchar 、nchar 和 nvarchar 的列的排序規則。若要更改使用者定義别名資料類型列的排序規則，必須執行單獨的 ALTER TABLE 語句，将列改為 SQL Server 系統資料類型，并更改其排序規則，然後重新将列改為别名資料類型。

如果出現以下一種或多種情況，則 ALTER COLUMN 不能更改排序規則：

• CHECK 限制、FOREIGN KEY 限制或計算列引用了更改後的列。
• 已為列建立了索引、統計資訊或全文索引。如果更改了列的排序規則，則将删除為更改後的列自動建立的統計資訊。
• 綁定到架構的視圖或函數引用了列。

有關詳細資訊，請參閱 COLLATE (Transact-SQL) 。

SPARSE | NULL | NOT NULL

指定列是否是稀疏列或是否可接受 null 值。如果要更改的列是稀疏列，則您必須顯式指定該屬性，否則該列将恢複為非稀疏列。不能将稀疏列指定為 NOT NULL。将列從稀疏列轉換為非稀疏列或者從非稀疏列轉換為稀疏列會導緻表在指令執行期間被鎖定。

有關稀疏列以及為 Null 性的其他限制和詳細資訊，請參閱使用稀疏列 。

如果列不允許 Null 值，則隻有在為列指定了預設值或整個表為空的情況下，才能用 ALTER TABLE 語句添加該列。隻有同時指定了 PERSISTED 時，才能為計算列指定 NOT NULL。如果新列允許空值，但沒有指定預設值，則新列在表中的每一行都包含一個空值。如果新列允許空值，并且指定了新列的預設值，則可以使用 WITH VALUES 将預設值存儲到表中每個現有行的新列中。

如果新列不允許空值，并且表不為空，那麼 DEFAULT 定義必須與新列一起添加；并且，加載新列時，每個現有行的新列中将自動包含預設值。

在 ALTER COLUMN 語句中指定 NULL，可以強制 NOT NULL 列允許空值，但 PRIMARY KEY 限制中的列除外。隻有列中不包含空值時，才可以在 ALTER COLUMN 中指定 NOT NULL。必須将空值更新為某個值後，才允許執行 ALTER COLUMN NOT NULL 語句，例如：

複制代碼

UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL      

如果用 CREATE TABLE 或 ALTER TABLE 語句建立或更改表，則資料庫或會話設定将影響并且可能覆寫用于列定義的資料類型的為空性。建議您始終針對非計算列将某一列顯式定義為 NULL 或 NOT NULL。

如果添加具有使用者定義的資料類型的列，則建議您将該列的為 Null 性定義為與使用者定義的資料類型的 null 屬性相同，并為該列指定一個預設值。有關詳細資訊，請參閱 CREATE TABLE (Transact-SQL) 。

注意：
如果 ALTER COLUMN 與 NULL 或 NOT NULL 一起指定，則必須同時指定 new_data_type [(precision [, scale ])]。如果未更改資料類型、精度和小數位數，則指定目前的列值。

[ {ADD | DROP} ROWGUIDCOL ]

指定在指定列中添加或删除 ROWGUIDCOL 屬性。ROWGUIDCOL 訓示列為行 GUID 列。每個表中隻有一個 uniqueidentifier 列能指定為 ROWGUIDCOL 列，并且隻能為 uniqueidentifier 列配置設定 ROWGUIDCOL 屬性。不能将 ROWGUIDCOL 配置設定給使用者定義資料類型的列。

ROWGUIDCOL 不強制要求列中存儲的值的唯一性，也不為插入到表中的新行自動生成值。若要為每列生成唯一值，則可以在 INSERT 語句中使用 NEWID 函數，也可以将 NEWID 函數指定為列的預設值。

[ {ADD | DROP} PERSISTED ]

指定在指定列中添加或删除 PERSISTED 屬性。該列必須是由确定性表達式定義的計算列。對于指定為 PERSISTED 的列，資料庫引擎将以實體方式在表中存儲計算值；并且，當更新了計算列依賴的任何其他列時，這些值也将被更新。通過将計算列标記為 PERSISTED，可以對确定（但不精确）的表達式中定義的計算列建立索引。有關詳細資訊，請參閱為計算列建立索引 。

用作已分區表的分區依據列的任何計算列必須顯式标記為 PERSISTED。

DROP NOT FOR REPLICATION

指定當複制代理執行插入操作時，辨別列中的值将增加。隻有當 column_name 是辨別列時，才可以指定此子句。有關詳細資訊，請參閱使用 NOT FOR REPLICATION 來控制限制、辨別和觸發器 。

SPARSE

指定要添加或删除的列是稀疏列。稀疏列已針對 NULL 值進行了存儲優化。不能将稀疏列指定為 NOT NULL。将列從稀疏列轉換為非稀疏列或者從非稀疏列轉換為稀疏列會導緻表在指令執行期間被鎖定。

注意：
必須在每次更改該列時都指定 SPARSE 屬性，否則該列将恢複為非稀疏列。

有關稀疏列的其他限制和詳細資訊，請參閱使用稀疏列 。

WITH CHECK | WITH NOCHECK

指定表中的資料是否用新添加的或重新啟用的 FOREIGN KEY 或 CHECK 限制進行驗證。如果未指定，對于新限制，假定為 WITH CHECK，對于重新啟用的限制，假定為 WITH NOCHECK。

如果不想根據現有資料驗證新的 CHECK 或 FOREIGN KEY 限制，請使用 WITH NOCHECK。除極個别的情況外，建議不要進行這樣的操作。在以後所有資料更新中，都将計算該新限制。如果添加限制時用 WITH NOCHECK 禁止了限制沖突，則将來使用不符合該限制的資料來更新行時，可能導緻更新失敗。

查詢優化器不考慮使用 WITH NOCHECK 定義的限制。在使用 ALTER TABLE table CHECK CONSTRAINT ALL 語句重新啟用這些限制之前，将忽略這些限制。

ADD

指定添加一個或多個列定義、計算列定義或者表限制。

DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }

指定從表中删除 constraint_name 或 column_name 。可以列出多個列或限制。

可通過查詢 sys.check_constraint 、sys.default_constraints 、sys.key_constraints 和 sys.foreign_keys 目錄視圖來确定限制的使用者定義名稱或系統提供的名稱。

如果表中存在 XML 索引，則不能删除 PRIMARY KEY 限制。

無法删除以下列：

• 用于索引的列。
• 用于 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 限制的列。
• 與預設值（由 DEFAULT 關鍵字定義）相關聯的列，或綁定到預設對象的列。
• 綁定到規則的列。

注意：
删除列并不回收列所占的磁盤空間。當表的行大小接近或超過其限額時，必須回收已删除的列占用的磁盤空間。通過建立表的聚集索引或使用 ALTER INDEX 重新生成現有的聚集索引，可以回收空間。

WITH <drop_clustered_constraint_option>

指定設定一個或多個删除聚集限制選項。

MAXDOP = max_degree_of_parallelism

隻在操作期間覆寫 max degree of parallelism 配置選項。有關詳細資訊，請參閱max degree of parallelism 選項 。

使用 MAXDOP 選項來限制執行并行計劃時所用的處理器數量。最大數量為 64 個處理器。

max_degree_of_parallelism 可以是下列值之一：

1

取消生成并行計劃。

>1

将并行索引操作中使用的最大處理器數量限制為指定數量。

0（預設值）

根據目前系統工作負荷使用實際的處理器數量或更少數量的處理器。

有關詳細資訊，請參閱配置并行索引操作 。

注意：
并行索引操作僅在 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用。

ONLINE = { ON | OFF }

指定在索引操作期間基礎表和關聯的索引是否可用于查詢和資料修改操作。預設為 OFF。REBUILD 可作為 ONLINE 操作執行。

ON

在索引操作期間不持有長期表鎖。在索引操作的主要階段，源表上隻使用意向共享 (IS) 鎖。這使得能夠繼續對基礎表和索引進行查詢或更新。操作開始時，将對源對象保持極短時間的共享 (S) 鎖。操作結束時，如果建立非聚集索引，将在短期内對源擷取 S（共享）鎖；當聯機建立或删除聚集索引時，以及重新生成聚集或非聚集索引時，将在短期内擷取 SCH-M（架構修改）鎖。對本地臨時表建立索引時，ONLINE 不能設定為 ON。

OFF

在索引操作期間應用表鎖。建立、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脫機索引操作将對表擷取架構修改 (Sch-M) 鎖。這樣可以防止所有使用者在操作期間通路基礎表。建立非聚集索引的脫機索引操作将對表擷取共享 (S) 鎖。這樣可以防止更新基礎表，但允許讀操作（如 SELECT 語句）。

有關詳細資訊，請參閱聯機索引操作的工作方式 。有關鎖的詳細資訊，請參閱鎖模式 。

注意：
聯機索引操作僅在 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用。

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

指定一個位置以移動聚集索引的葉級别中的目前資料行。表被移至新位置。

注意：
在此上下文中，default 不是關鍵字。它是預設檔案組的辨別符，必須對其進行分隔，就像在 MOVE TO " default" 或 MOVE TO [ default] 中一樣。如果指定了 " default" ，則目前會話的 QUOTED_IDENTIFIER 選項必須為 ON。這是預設設定。有關詳細資訊，請參閱 SET QUOTED_IDENTIFIER (Transact-SQL) 。

{ CHECK | NOCHECK} CONSTRAINT

指定啟用或禁用 constraint_name 。此選項隻能與 FOREIGN KEY 和 CHECK 限制一起使用。如果指定了 NOCHECK，則将禁用限制，進而在将來插入或更新列時，不根據限制條件進行驗證。無法禁用 DEFAULT、PRIMARY KEY 和 UNIQUE 限制。

ALL

指定使用 NOCHECK 選項禁用所有限制，或者使用 CHECK 選項啟用所有限制。

{ ENABLE | DISABLE } TRIGGER

指定啟用或禁用 trigger_name 。禁用觸發器時，仍會為表定義該觸發器；但是，當對表執行 INSERT、UPDATE 或 DELETE 語句時，除非重新啟用觸發器，否則不會執行觸發器中的操作。

ALL

指定啟用或禁用表中的所有觸發器。

trigger_name

指定要啟用或禁用的觸發器的名稱。

{ ENABLE | DISABLE } CHANGE_TRACKING

指定是啟用還是禁用表的更改跟蹤。預設情況下會禁用更改跟蹤。

隻有對資料庫啟用了更改跟蹤，此選項才可用。有關詳細資訊，請參閱 ALTER DATABASE SET 選項 (Transact-SQL) 。

若要啟用更改跟蹤，表必須具有一個主鍵。

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

指定資料庫引擎是否跟蹤哪些更改跟蹤列已更新。預設值為 OFF。

SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name . ] target_table [ PARTITION target_ partition_number_expression ]

用下列方式之一切換資料塊：

• 将表的所有資料作為分區重新配置設定給現有的已分區表。
• 将分區從一個已分區表切換到另一個已分區表。
• 将已分區表的一個分區中的所有資料重新配置設定給現有的未分區的表。

如果 table 為已分區表，則必須指定 source_partition_number_expression 。如果 target_table 已進行分區，則必須指定 target_partition_number_expression 。如果要将表的資料作為分區重新配置設定給現有的已分區表，或者将分區由一個已分區表切換到另一個已分區表，則目标分區必須存在，并且必須為空。

如果重新配置設定一個分區的資料以組成單個表，則必須已經建立了目标表，并且該表必須為空。源表或分區以及目标表或分區必須在同一個檔案組中。對應的索引或索引分區也必須在同一個檔案組中。切換分區還有許多其他限制。有關詳細資訊，請參閱 使用分區切換高效傳輸資料 。table 和 target_table 不能相同。target_table 可以是由多個部分構成的辨別符。

source_partition_number_expression 和 target_partition_number_expression 為常量表達式，可以引用變量和函數，其中包括使用者定義類型變量和使用者定義函數。它們不能引用 Transact-SQL 表達式。

注意：
不能對複制表使用 SWITCH 語句。

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

指定 FILESTREAM 資料的存儲位置。

帶有 SET FILESTREAM_ON 子句的 ALTER TABLE 隻有在表不包含任何 FILESTREAM 列時才會成功。可以通過使用第二個 ALTER TABLE 語句添加 FILESTREAM 列。

如果指定 partition_scheme_name ，則會應用 CREATE TABLE 的規則。表應該已經對行資料進行了分區，并且其分區方案必須使用與 FILESTREAM 分區方案相同的分區函數和分區列。

filestream_filegroup_name 指定 FILESTREAM 檔案組的名稱。該檔案組必須包含一個使用 CREATE DATABASE 或 ALTER DATABASE 語句為該檔案組定義的檔案；否則，将引發錯誤。

" default" 指定 FILESTREAM 檔案組具有 DEFAULT 屬性集。如果沒有 FILESTREAM 檔案組，将引發錯誤。

" NULL" 指定對表的 FILESTREAM 檔案組的所有引用都将被删除。首先必須删除所有 FILESTREAM 列。必須使用 SET FILESTREAM_ON=" NULL" 删除與表關聯的所有 FILESTREAM 資料。

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

指定允許的對表的鎖進行更新的方法。

AUTO

此選項允許 SQL Server 資料庫引擎選擇适合于表架構的鎖更新粒度。

• 如果該表已分區，則允許将鎖更新到分區。鎖更新到分區級别之後，該鎖以後将不會更新到 TABLE 粒度。
• 如果該表未分區，則會将鎖更新到 TABLE 粒度。

TABLE

無論表是否已分區，都會在表級粒度完成鎖更新。此行為與 SQL Server 2005 中相同。預設值為 TABLE。

DISABLE

在大多數情況下禁止鎖更新。表級别的鎖未完全禁止。例如，當掃描在可序列化隔離級别下沒有聚集索引的表時，資料庫引擎必須使用表鎖來保證資料的完整性。

REBUILD

使用 REBUILD WITH 文法可重新生成包含分區表中的所有分區的整個表。如果表具有聚集索引，則 REBUILD 選項将重新生成該聚集索引。REBUILD 可作為 ONLINE 操作執行。

使用 REBUILD PARTITION 文法可重新生成分區表中的單個分區。

PARTITION = ALL

更改分區壓縮設定時重新生成所有分區。

REBUILD WITH ( <rebuild_option> )

為具有聚集索引的表應用所有選項。如果表沒有聚集索引，則隻有部分選項會影響堆結構。

有關重新生成選項的完整說明，請參閱 index_option (Transact-SQL) 。

DATA_COMPRESSION

為指定的表、分區号或分區範圍指定資料壓縮選項。選項如下所示：

NONE

不壓縮表或指定的分區。

ROW

使用行壓縮來壓縮表或指定的分區。

PAGE

使用頁壓縮來壓縮表或指定的分區。

若要同時重新生成多個分區，請參閱 index_option (Transact-SQL) 。如果表沒有聚集索引，則更改資料壓縮會重新生成堆和非聚集索引。有關壓縮的詳細資訊，請參閱建立壓縮表和索引 。

column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

列集的名稱。列集是一種非類型化的 XML 表示形式，它将表的所有稀疏列合并為一種結構化的輸出。如果某個表包含稀疏列，則不能向該表添加列集。有關列集的詳細資訊，請參閱使用列集 。

 注釋

若要添加新資料行，請使用 INSERT 。若要删除資料行，請使用 DELETE 或 TRUNCATE TABLE 。若要更改現有行中的值，請使用 UPDATE 。

如果過程緩存中存在引用表的執行計劃，ALTER TABLE 會将這些執行計劃标記為下次執行時重新編譯。

更改列的大小

可以通過在 ALTER COLUMN 子句中指定列資料類型的新大小來更改列的長度、精度或小數位數。如果列中存在資料，則新大小不能小于資料的最大大小。此外，不能在某個索引中定義該列，除非該列的資料類型為 varchar 、nvarchar 或 varbinary 并且該索引不是 PRIMARY KEY 限制的結果。請參見示例 P。

鎖和 ALTER TABLE

ALTER TABLE 語句指定的更改将立即實作。如果這些更改需要修改表中的行，ALTER TABLE 将更新這些行。ALTER TABLE 将擷取表上的架構來修改鎖，以確定在更改期間沒有其他連接配接引用（甚至是該表上的中繼資料，也不引用），但可在結束時執行需要一個極短的 SCH-M 鎖的聯機索引操作。在 ALTER TABLE…SWITCH 操作中，源表和目标表都需要鎖。對表進行的更改将記錄于日志中，并且可以完整恢複。影響超大型表中所有行的更改，比如删除列或者用預設值添加 NOT NULL 列，可能需要較長時間才能完成，并将生成大量日志記錄。如同影響許多行的 INSERT、UPDATE 或者 DELETE 語句一樣，應謹慎執行這些 ALTER TABLE 語句。

并行計劃執行

在 SQL Server 2008 Enterprise 中，根據 max degree of parallelism 配置選項和目前工作負荷，确定運作單個 ALTER TABLE ADD（基于索引）CONSTRAINT 或 DROP（聚集索引）CONSTRAINT 語句采用的處理器數。如果資料庫引擎檢測到系統正忙，則在語句執行開始之前将自動降低操作并行度。可以通過指定 MAXDOP 選項，手動配置用于運作此語句的處理器數。

已分區表

除了執行涉及到已分區表的 SWITCH 操作外，ALTER TABLE 還可用于更改已分區表的列、限制和觸發器的狀态，就像它用于非分區表一樣。但是，該語句不能用于更改表本身進行分區的方式。若要對已分區表進行重新分區，請使用 ALTER PARTITION SCHEME 和 ALTER PARTITION FUNCTION 。此外，不能更改已分區表中列的資料類型。

對包含綁定到架構視圖的表的限制

應用于包含架構綁定視圖的表的 ALTER TABLE 語句的限制，與目前修改包含簡單索引的表時應用的限制相同。允許添加列。但是，不允許删除或更改參與任何綁定到架構視圖的列。如果 ALTER TABLE 語句要求更改用于架構綁定視圖中的列，ALTER TABLE 将失敗，并且資料庫引擎将引發錯誤消息。有關綁定到架構的視圖和索引視圖的詳細資訊，請參閱 CREATE VIEW (Transact-SQL) 。

建立引用表的架構綁定視圖不會影響為基表添加或删除觸發器。

索引和 ALTER TABLE

删除限制時，作為限制的一部分而建立的索引也将被删除。由 CREATE INDEX 建立的索引必須使用 DROP INDEX 删除。ALTER INDEX 語句可用于重新生成限制定義的索引部分；而不必再使用 ALTER TABLE 來删除和添加限制。

必須删除所有基于列的索引和限制後，才能删除列。

如果删除了建立聚集索引的限制，則存儲在聚集索引葉級别的資料行将存儲在非聚集表中。通過指定 MOVE TO 選項，可以在單個事務中删除聚集索引并将生成的表移動到另一個檔案組或分區方案。MOVE TO 選項有以下限制：

• MOVE TO 對索引視圖或非聚集索引無效。
• 分區方案或檔案組必須已經存在。
• 如果沒有指定 MOVE TO，則表将位于為聚集索引定義的同一分區方案或檔案組中。

删除聚集索引時，可以指定 ONLINE = ON 選項，這樣 DROP INDEX 事務就不會阻塞對基礎資料和相關的非聚集索引的查詢和修改。

ONLINE = ON 具有下列限制：

• ONLINE = ON 對于也被禁用的聚集索引無效。必須使用 ONLINE = OFF 删除禁用的索引。
• 一次隻能删除一個索引。
• ONLINE = ON 對于索引視圖、非聚集索引或本地臨時表的索引無效。

删除聚集索引時，需要大小等于現有聚集索引的大小的臨時磁盤空間。操作完成後，即可釋放此額外空間。

注意：
<drop_clustered_constraint_option> 中列出的選項可适用于表的聚集索引，但不能用于視圖的聚集索引或非聚集索引。

複制架構更改

預設情況下，當在 SQL Server 釋出伺服器中對釋出的表運作 ALTER TABLE 時，此更改将傳播到所有 SQL Server 訂閱伺服器。此功能存在一些限制并可禁用。有關詳細資訊，請參閱對釋出資料庫進行架構更改 。

資料壓縮

不能為系統表啟用壓縮功能。如果表是堆，重新生成操作将在單個線程内完成。有關資料壓縮的詳細資訊，請參閱建立壓縮表和索引 。

若要評估更改壓縮狀态将對表、索引或分區有何影響，請使用 sp_estimate_data_compression_savings 存儲過程。

以下限制适用于已分區表：

• 如果表具有非對齊索引，則無法更改單個分區的壓縮設定。
• ALTER TABLE <table> REBUILD PARTITION ... 文法可重新生成指定分區。
• ALTER TABLE <table> REBUILD WITH ... 文法可重新生成所有分區。

 權限

需要對表的 ALTER 權限。

ALTER TABLE 權限适用于 ALTER TABLE SWITCH 語句涉及的兩個表。任何已切換的資料都将繼承目标表的安全性。

如果将 ALTER TABLE 語句中的任何列定義為公共語言運作時 (CLR) 使用者定義類型或别名資料類型，都需要對該類型有 REFERENCES 權限。

 示例

A. 添加新列

下面的示例将添加一個允許空值的列，而且沒有通過 DEFAULT 定義提供的值。在該新列中，每一行都将有

NULL

值。

CREATE TABLE dbo.doc_exa (column_a INT) ;

GO

ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;

GO

EXEC sp_help doc_exa ;

GO

DROP TABLE dbo.doc_exa ;

GO

      

B. 删除列

下面的示例将修改一個表以删除列。

CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;

GO

ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;

GO

EXEC sp_help doc_exb ;

GO

DROP TABLE dbo.doc_exb ;

GO

      

C. 更改列的資料類型

下面的示例将表中列的資料類型由

INT

改為

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

      

D. 添加包含限制的列

以下示例将添加一個包含

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

      

E. 在現有列中添加一個未經驗證的 CHECK 限制

下面的示例将在表中的現有列中添加一個限制。該列包含一個違反限制的值。是以，将使用

WITH NOCHECK

以避免根據現有行驗證該限制，進而允許添加該限制。

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

      

F. 在現有列中添加一個 DEFAULT 限制

下面的示例将建立一個包含兩列的表，在第一列插入一個值，另一列保持為 NULL。然後在第二列中添加一個

DEFAULT

限制。驗證是否已應用了預設值，另一個值是否已插入第一列以及是否已查詢表。

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

      

G. 添加多個包含限制的列

下面的示例将添加多個包含随新列定義的限制的列。第一個新列具有

IDENTITY

屬性。表中的每一行在辨別列中都有新的增量值。

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

      

H. 添加包含預設值的可為空的列

下面的示例将添加一個包含

DEFAULT

定義的可為空的列，并使用

WITH VALUES

為表中的各個現有行提供值。如果沒有使用 WITH VALUES，那麼每一行的新列中都将具包含 NULL 值。

USE AdventureWorks ; 

GO

CREATE TABLE dbo.doc_exf ( column_a INT) ;

GO

INSERT INTO dbo.doc_exf VALUES (1) ;

GO

ALTER TABLE dbo.doc_exf 

ADD AddDate smalldatetime NULL

CONSTRAINT AddDateDflt

DEFAULT GETDATE() WITH VALUES ;

GO

DROP TABLE dbo.doc_exf ;

GO

      

I. 禁用和重新啟用限制

下面的示例禁用對資料中接受的薪金進行限制的限制。

NOCHECK CONSTRAINT

将與

ALTER TABLE

配合使用來禁用該限制，進而允許執行通常會違反該限制的插入操作。

CHECK CONSTRAINT

将重新啟用該限制。

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) ;

      

J. 删除限制

下面的示例将從表中删除

UNIQUE

限制。

CREATE TABLE dbo.doc_exc ( column_a INT

CONSTRAINT my_constraint UNIQUE) ;

GO

ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;

GO

DROP TABLE dbo.doc_exc ;

GO

      

K. 在表之間切換分區

以下示例建立一個已分區表，并假定在資料庫中已經建立了分區方案

myRangePS1

。然後，在

PartitionTable

表的

PARTITION 2

所在的同一檔案組中，建立與已分區表結構相同的未分區的表。最後，将

PartitionTable

表的

PARTITION 2

中的資料切換到

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      

L. 禁用和重新啟用觸發器

下面的示例将使用

ALTER TABLE

的

DISABLE TRIGGER

選項來禁用觸發器，以允許執行通常會違反此觸發器的插入操作。然後，使用

ENABLE TRIGGER

重新啟用觸發器。

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

      

M. 建立包含索引選項的 PRIMARY KEY 限制

下面的示例将建立 PRIMARY KEY 限制

PK_TransactionHistoryArchive_TransactionID

，并設定

FILLFACTOR

、

ONLINE

和

PAD_INDEX

選項。生成的聚集索引将與限制同名。

USE AdventureWorks;

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

      

N. 在 ONLINE 模式下删除 PRIMARY KEY 限制

下面的示例在

ONLINE

選項設定為

ON

的情況下删除 PRIMARY KEY 限制。

USE AdventureWorks;

GO

ALTER TABLE Production.TransactionHistoryArchive

DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID

WITH (ONLINE = ON);

GO

      

O. 添加和删除 FOREIGN KEY 限制

下面的示例将建立

ContactBackup

表，然後更改此表。首先添加引用

Contact

表的

FOREIGN KEY

限制，然後再删除

FOREIGN KEY

限制。

USE AdventureWorks ;

GO

CREATE TABLE Person.ContactBackup

(ContactID int) ;

GO

ALTER TABLE Person.ContactBackup

ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)

    REFERENCES Person.Contact (ContactID) ;

ALTER TABLE Person.ContactBackup

DROP CONSTRAINT FK_ContactBacup_Contact ;

GO

DROP TABLE Person.ContactBackup ;

      

P.更改列的大小

下面的示例增加 varchar 列的大小和 decimal 列的精度和小數位數。因為列包含資料，是以隻能增加列的大小。此外，請注意：

col_a

是在一個唯一索引中定義的。仍然可以增加

col_a

的大小，因為其資料類型為 varchar 并且該索引不是 PRIMARY KEY 限制的結果。

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');      

Q. 允許已分區表中的鎖更新

下面的示例在已分區表的分區級别啟用鎖更新。如果該表未分區，則會将鎖更新到 TABLE 級别。

ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO)

GO      

R. 配置表的更改跟蹤

下面的示例啟用

AdventureWorks

資料庫中

Person.Contact

表的更改跟蹤。

USE AdventureWorks;

ALTER TABLE Person.Contact

ENABLE CHANGE_TRACKING;

      

下面的示例啟用更改跟蹤，并啟用在進行某項更改期間會進行更新的列的跟蹤。

USE AdventureWorks;

ALTER TABLE Person.Contact

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON)

      

下面的示例禁用

AdventureWorks

資料庫中

Person.Contact

表的更改跟蹤。

USE AdventureWorks;

ALTER TABLE Person.Contact

DISABLE CHANGE_TRACKING;

      

S. 修改表以更改壓縮

下面的示例更改未分區表的壓縮。将會重新生成堆或聚集索引。如果表是一個堆，将重新生成所有非聚集索引。

ALTER TABLE T1 

REBUILD WITH (DATA_COMPRESSION = PAGE);      

下面的示例更改已分區表的壓縮。

REBUILD PARTITION = 1

文法僅僅導緻重新生成編号為

1

的分區。

ALTER TABLE PartitionTable1 

REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;

GO      

使用以下替代文法的相同操作則會導緻重新生成表中的所有分區。

ALTER TABLE PartitionTable1 

REBUILD PARTITION ALL 

WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;      

有關其他資料壓縮示例，請參閱建立壓縮表和索引 。

T. 添加稀疏列

下面的示例示範如何在表 T1 中添加和修改稀疏列。建立表

T1

的代碼如下所示。

CREATE TABLE T1

(C1 int PRIMARY KEY,

C2 varchar(50) SPARSE NULL,

C3 int SPARSE NULL,

C4 int ) ;

GO      

若要添加另一個稀疏列

C5

，請執行以下語句。

ALTER TABLE T1

ADD C5 char(100) SPARSE NULL ;

GO      

若要将

C4

非稀疏列轉換為稀疏列，請執行以下語句。

ALTER TABLE T1

ALTER COLUMN C4 ADD SPARSE ;

GO      

若要将

C4

稀疏列轉換為非稀疏列，請執行以下語句。

ALTER TABLE T1

ALTER COLUMN C4 DROP SPARSE;

GO      

U. 添加列集

下面的示例示範如何向表

T2

中添加一列。如果表已包含稀疏列，則不能向該表添加列集。建立表

T2

的代碼如下所示。

CREATE TABLE T2

(C1 int PRIMARY KEY,

C2 varchar(50) NULL,

C3 int NULL,

C4 int ) ;

GO      

下面的三個語句添加名為

CS

的列集，然後将列

C2

和

C3

修改為

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      

<continue>