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 ) ]
              
                ::=column_name AS computed_column_expression[ PERSISTED [ NOT NULL ] ]
               
                 ::=column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS74 CHAPTER 3 Tables
                
                  ::={ DATA_COMPRESSION = { NONE | ROW | PAGE }[ ON PARTITIONS ( { 
                 
                   | 
                  
                    } [ , ...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 AdventureWorks2008R2GOCREATE SCHEMA test AUTHORIZATION dboGO

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.CustomerADD CONSTRAINT pk_customer PRIMARY KEY CLUSTERED (CustomerID)GOALTER TABLE test.OrderHeaderADD CONSTRAINT pk_orderheader PRIMARY KEY CLUSTERED (OrderID)GO

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

ALTER TABLE test.CustomerADD CONSTRAINT df_creationdate DEFAULT (GETDATE()) FOR CreationDateGOALTER TABLE test.OrderHeaderADD CONSTRAINT df_orderdate DEFAULT (GETDATE()) FOR OrderDateGO

ALTER TABLE test.OrderHeaderADD 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.