SQL Server 2008读书笔记（3）:表

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition>
| <column_set_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ FILESTREAM_ON { partition_scheme_name | filegroup
| "default" } ]
[ WITH ( <table_option> [ ,...n ] ) ][ ; ]
<column_definition> ::=
column_name <data_type>
[ FILESTREAM ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[ [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] |
| [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] ]
[ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] [ SPARSE ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
<column_set_definition> ::=
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
74 CHAPTER 3 Tables
<table_option> ::=
{ DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) ]}

Lesson 1: Creating Tables

The simplest syntax to create a schema is:
CREATE SCHEMA <schema name> AUTHORIZATION <owner name>

Quick Check
1. How do you design a database?
2. What are three new options that you can configure for columns, rows, or pages within a table?
Quick Check Answers
1. The ruling principle for designing a database is “Put things where they belong.”
If the need is to store multiple rows of information that link back to a single
entity, you need a separate table for those rows. Otherwise, each table defi nes a
major object for which you want to store data and the columns within the table
defi ne the specifi c data that you want to store.
2. You can designate columns as SPARSE to optimize the storage of NULLs. You can
apply the FILESTREAM property to a VARBINARY(MAX) column to enable the
storage of documents in a directory on the operating system that exceed 2 GB.
Rows can be compressed to fit more rows on a page. Pages can be compressed to
reduce the amount of storage space required for the table, index, or indexed view.

USE AdventureWorks2008R2
GO

CREATE SCHEMA test AUTHORIZATION dbo
GO

CREATE TABLE test.Customer
(CustomerId        INT          IDENTITY(1,1),
LastName           VARCHAR(50)  NOT NULL,
FirstName          VARCHAR(50)  NOT NULL,
CreditLine         MONEY        SPARSE NULL,        
CreationDate       DATE         NOT NULL)
GO

CREATE TABLE test.OrderHeader
(OrderID        INT     IDENTITY(1,1),
CustomerID      INT     NOT NULL,
OrderDate       DATE    NOT NULL,
OrderTime       TIME    NOT NULL,
SubTotal        MONEY   NOT NULL,
ShippingAmt     MONEY   NOT NULL,
OrderTotal      AS (SubTotal + ShippingAmt))
WITH (DATA_COMPRESSION = ROW)
GO

Lesson Summary
Schemas allow you to group related objects together as well as provide a security
container for objects.
The most important decision you can make when designing a table is the data type of
a column.
You can use a column set defi nition along with sparse columns to create tables with up
to 30,000 columns.
Tables, indexes, and indexed views can be compressed using either row or page
compression; however, compression is not compatible with sparse columns.

Lesson 2: Implementing Constraints

Quick Check
1. What is the difference between a primary key and a unique constraint?
2. What restrictions does the parent table have when creating a foreign key?
Quick Check Answers
1. A primary key does not allow NULLs.
2. The parent table must have a primary key that is used to defi ne the relationship
between the parent and child tables. In addition, if the parent’s primary key is
defined on multiple columns, all the columns must exist in the child table for the
foreign key to be created.

Clustered Index
•Only one per table
•Faster to read than non clustered as data is physically stored in index order

Non Clustered Index
•Can be used many times per table
•Quicker for insert and update operations than a clustered index

ALTER TABLE test.Customer
ADD CONSTRAINT pk_customer PRIMARY KEY CLUSTERED (CustomerID)
GO

ALTER TABLE test.OrderHeader
ADD CONSTRAINT pk_orderheader PRIMARY KEY CLUSTERED (OrderID)
GO

ALTER TABLE test.OrderHeader
ADD CONSTRAINT fk_orderheadertocustomer FOREIGN KEY(CustomerID)
REFERENCES test.Customer (CustomerID)
GO

ALTER TABLE test.Customer
ADD CONSTRAINT df_creationdate DEFAULT (GETDATE()) FOR CreationDate
GO

ALTER TABLE test.OrderHeader
ADD CONSTRAINT df_orderdate DEFAULT (GETDATE()) FOR OrderDate
GO

ALTER TABLE test.OrderHeader
ADD CONSTRAINT ck_subtotal CHECK (SubTotal > 0)
GO

Lesson Summary
A primary key defi nes the column(s) that uniquely identify each row in a table.
Foreign keys are used to enforce referential integrity between tables.
Default constraints provide a value when the application does not specify a value for a
column.
Check constraints limit the acceptable values for a column.