T-SQL笔记4：表

本章摘要

1:CREATE

2:SQL Server 2005 Data Types

3:Adding aColumn to an Existing Table

4:Changing a Column Definition

5:Creating aComputed Column

6:Dropping aTable Column

1:CREATE

The simplified syntax is as follows:

CREATE TABLE 
[ database_name . [ schema_name ] . | schema_name . ] table_name 
( column_name <data_type> [ NULL | NOT NULL ] [ ,...n ]  )

2:SQL Server 2005 Data Types

bigint: Whole number from –2^63 (-9,223,372,036,854,775,808) through 2^63-1(9,223,372,036,854,775,807).
binary: Fixed-length binary data with amaximum of 8000 bytes.

bit: Whole number either 0 or 1.
char: Fixed-length character data with maximum length of 8000 characters.
datetime: Date and time from January 1, 1753, through December 31, 9999. (1753 was the year following the adoption of the Gregorian calendar, which producedadifference in days to the previous calendar of 12 days. Beginning with the year 1753 sidesteps all sorts of calculation problems.)

Decimal or numeric: (no difference between the two) range from –10^38 +1 through 10^38-1.Decimal uses precision and scale. Precision determines maximum totalnumber of decimal digits both left and right of the decimal point. Scaledetermines maximum decimal digits to the right of the decimal point.
float: Floating precision number from - 1.79E + 38 to -2.23E - 38, 0 and 2.23E –38 to 1.79E + 38.
image: Variable-length binary data from 0 through 2^31 –1. This data type will be removed in afuture version of SQL Server. Instead of using this data type, use varbinary(max)instead.
int: Whole number from –2^31 (-2,147,483,648) through 2^31-1 (2,147,483,647).
money: Monetary value between –2^63 ( -922,377,203,685,477.5808) through 2^63-1 (+922,337,203,685,477.5807).
nchar: Fixed-length Unicode character data with amaximum length of 4000 characters.
ntext: Variable-length Unicode character data with amaximum length of 1,073,741,823 characters. This data type will be removed in afuture version of SQL Server. Instead of using this data type, use nvarchar(max)instead.
nvarchar: Variable-length Unicode character data with maximum length of 4000 characters. SQL Server 2005 has also added a“max” option which allows you to store up to 2^31-1bytes. This new option allows you to use the regular data types instead of SQL Server 2000’s text, ntext, and image.
real: Floating precision number from -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.
smalldatetime: Date and time from January 1, 1900, through June 6, 2079.
smallint: Whole number from –32,768 through 32,767.
smallmoney: Monetary value between –214,748.3648 through +214,748.3647.
sql_variant: A data type which can store all data types except text, ntext, timestamp, varchar(max), nvarchar(max), varbinary(max), xml, image, user-defined types, and another sql_variant.

table: The tabledata type can’t be used in CREATE TABLEas acolumn type. Instead it is used for table variables or for storage of rows for atable-valued function.
text: Variable-length data with maximum length of 2,147,483,647 characters. This data type will be removed in afuture version of SQL Server. Instead of using this data type, use varchar(max)instead.
timestamp: Database-wide unique number that is updated when arow is modified.

tinyint: Whole number from 0 through 255.
uniqueidentifier: Stores a16-byte GUID (globally-unique identifier).
varbinary: Variable-length data with amaximum of 8000 bytes. SQL Server 2005 has also added amax value, which allows you to store up to 2^31-1bytes. This new option allows you to use the regular data types instead of SQL Server 2000’s text, ntext, and image.
varchar: Variable-length character data with amaximum length of 8,000 characters.SQL Server 2005 has also added amax value, which allows you to store up to 2^31-1bytes. This new option allows you to use the regular data types
instead of SQL Server 2000’s text, ntext, and image.
xml: New to SQL Server 2005, this data type stores native xml data.

tip: If your character data type columns use the same or a similar number of characters consis-
tently, use fixed length data types (char, nchar).

3:Adding aColumn to an Existing Table

Sugest we alread have a table, such as :

CREATE TABLE Person.EducationType
(EducationTypeID int NOT NULL,
EducationTypeNM varchar(40) NOT NULL)
GO

And, After atable is created, you can modify it using the ALTER TABLEcommand.

ALTER TABLE table_name
ADD { column_name data_type } NULL

For example:

ALTER TABLE HumanResources.Employee 
ADD Latest_EducationTypeID int NULL

When adding columns to atable that already has data in it, you will be required to add the column with NULLvalues allowed. You can’t specify that the column be NOT NULL, because you must first add the column to the table before you can put avalue in that column for existing rows.

4:Changing a Column Definition

The syntax for doing this is as follows:

ALTER TABLE table_name 
ALTER COLUMN column_name 
[type_name] [NULL | NOT NULL] [COLLATE collation_name] 

example:

This example demonstrates how to change an existing table column’s nullability and data type.The Gender column in the HumanResources.Employeetable is originally NOT NULL and the original data type of the LoginIDcolumn is nvarchar(256):
-- Make it Nullable
ALTER TABLE HumanResources.Employee
ALTER COLUMN Gender nchar(1) NULL
-- Expanded nvarchar(256) to nvarchar(300)  
ALTER TABLE HumanResources.Employee
ALTER COLUMN LoginID nvarchar(300) NOT NULL

5:Creating aComputed Column

The syntax for adding acomputed column either by CREATEor ALTER TABLEis as follows:

column_name AS computed_column_expression [ PERSISTED ]

The column_name is the name of the new column. The computed_column_expression is the calculation you wish to be performed in order to derive the column’s value. Adding the PERSISTED keyword actually causes the results of the calculation to be physically stored.

In this example, anew, calculated column is added to an existing table:

ALTER TABLE Production.TransactionHistory
ADD CostPerUnit AS (ActualCost/Quantity) 

The next example creates aPERSISTEDcalculated column, which means the calculated data will actually be physically stored in the database (but still automatically calculated by SQL Server):

CREATE TABLE HumanResources.CompanyStatistic
(CompanyID int NOT NULL,
StockTicker char(4) NOT NULL,
SharesOutstanding int NOT NULL,
Shareholders int NOT NULL,
AvgSharesPerShareholder AS (SharesOutStanding/Shareholders) PERSISTED)

6:Dropping aTable Column

You can use ALTER TABLEto drop acolumn from an existing table.
The syntax for doing so is as follows:

ALTER TABLE table_name
DROP COLUMN column_name

本文基于Creative Commons Attribution 2.5 China Mainland License发布，欢迎转载，演绎或用于商业目的，但是必须保留本文的署名http://www.cnblogs.com/luminji（包含链接）。如您有任何疑问或者授权方面的协商，请给我留言。