表是用来存储数据和操作数据的逻辑结构，用来组织和存储数据，关系数据库中的所有数据都表现为表的形式，数据表由行和列组成。SQL Server中的数据表分为临时表和永久表，临时表存储在tempdb系统数据库中，当不再使用或退出SQL Server时，临时表会自动删除；永久表一旦创建之后，除非用户删除，否则将一直存在数据库文件中。
创建数据表的两种方法：(1) 通过对象资源管理器创建；(2) 通过Transact-SQL语句进行创建。
1 Transact-SQL创建表
1.1 语法
CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name ( { | | } [ ] [ ,...n ] ) [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] [ { TEXTIMAGE_ON { filegroup | "default" } ] [ FILESTREAM_ON { partition_scheme_name | filegroup | "default" } ] [ WITH ( [ ,...n ] ) ][ ; ] ::=column_name [ FILESTREAM ] [ COLLATE collation_name ] [ NULL | NOT NULL ] [ [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] ] [ ROWGUIDCOL ] [ [ ...n ] ] [ SPARSE ] ::= [ type_schema_name . ] type_name [ ( precision [ , scale ] | max | [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ] ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( < index_option > [ , ...n ] ) ] [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] | [ FOREIGN KEY ] REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } ::=column_name AS computed_column_expression [ PERSISTED [ NOT NULL ] ][ [ CONSTRAINT constraint_name ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( [ , ...n ] ) ] | [ FOREIGN KEY ] REFERENCES referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE } ] [ ON UPDATE { NO ACTION } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ]] ::=column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS< table_constraint > ::=[ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,...n ] ) [ WITH FILLFACTOR = fillfactor |WITH ( [ , ...n ] ) ] [ ON { partition_scheme_name (partition_column_name) | filegroup | "default" } ] | FOREIGN KEY ( column [ ,...n ] ) REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } ::={ DATA_COMPRESSION = { NONE | ROW | PAGE } [ ON PARTITIONS ( { | } [ , ...n ] ) ]} ::={ PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor | IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF } | ALLOW_ROW_LOCKS = { ON | OFF} | ALLOW_PAGE_LOCKS ={ ON | OFF} | DATA_COMPRESSION = { NONE | ROW | PAGE } [ ON PARTITIONS ( { | } [ , ...n ] ) ]} ::= TO 2 Transact-SQL修改表
2.1 语法
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name { ALTER COLUMN column_name { [ type_schema_name. ] type_name [ ( { precision [ , scale ] | max | xml_schema_collection } ) ] [ COLLATE collation_name ] [ NULL | NOT NULL ] [ SPARSE ] | { ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE } } | [ WITH { CHECK | NOCHECK } ] | ADD { | | | } [ ,...n ] | DROP { [ CONSTRAINT ] { constraint_name [ WITH ( [ ,...n ] ) ] } [ ,...n ] | COLUMN { column_name } [ ,...n ] } [ ,...n ] | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] } | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] } | { ENABLE | DISABLE } CHANGE_TRACKING [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ] | SWITCH [ PARTITION source_partition_number_expression ] TO target_table [ PARTITION target_partition_number_expression ] [ WITH ( ) ] | SET ( FILESTREAM_ON = { partition_scheme_name | filegroup | "default" | "NULL" } ) | REBUILD [ [PARTITION = ALL] [ WITH ( [ ,...n ] ) ] | [ PARTITION = partition_number [ WITH ( [ ,...n ] ) ] ] ] | | }[ ; ] 2.2 示例
修改表添加新列
ALTER TABLE [dbo].[Product] ADD [UnitPrice] DECIMAL(18,2) NULL
ALTER TABLE [dbo].[Product] ADD [UnitsInStock] INT NULL, [UnitsOnSale] INT NULL
修改列
ALTER TABLE [dbo].[Product] ALTER COLUMN [ProductName] VARCHAR(100) NOT NULL
删除列
ALTER TABLE [dbo].[Product] DROP COLUMN [UnitPrice]
3 Transact-SQL删除表
3.1 语法
DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]table_name [ ,...n ] [ ; ]
4 表操作示例
查看数据库中全部的表
USE [Portal]GOSELECT * FROM INFORMATION_SCHEMA.TABLESGO
INFORMATION_SCHEMA是一个特定的访问路径，用于显示系统数据库及其内容的相关元数据。如INFORMATION_SCHEMA.VIEWS、INFORMATION_SCHEMA.SCHEMATA。
查看表的属性
存储过程sp_help的功能是查看任意数据库对象、用户自定义数据类型或SQL Server数据类型的信息。
执行存储过程sp_help的语法结构：
EXEC sp_help
查看Product表的属性：
EXEC sp_help Product