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.
