All objects within a database are created within a collection known as a Schema. Schemas act as containers for related objects and provide a security context for the collection.

Schemas are created with the 'CREATE SCHEMA' statement.

CREATE SCHEMA Robots AUTHORIZATION dbo
GO
CREATE SCHEMA Customers AUTHORIZATION dbo
GO
CREATE SCHEMA Suppliers AUTHORIZATION dbo
GO
CREATE SCHEMA Orders AUTHORIZATION dbo
GO

The fully-qualified name for each database object is specified in the format: instance.database.schema.object. Object names can also be specified relative to the current context.

Table with Autoincrement ID

T-SQL combined with SQL Server datatypes and constraints are used to create tables in a SQL Server 2008 database.

CREATE TABLE Robots.Robot (
RobotID		INT		IDENTITY(1,1),
RobotName	VARCHAR(50)	NOT NULL,
CreatedDate	DATE		NOT NULL DEFAULT GETDATE(),
DesignerID	INT		NOT NULL,
RobotManual	XML		NULL)
GO

Table with Computed Column

Columns can be specified as a computation. SQL Server 2008 stores the computation in the table, and returns the result of the computation in response to queries

CREATE TABLE Orders.OrderData (
OrderID		INT	IDENTITY(1,1),
OrderDate	DATE	NOT NULL,
SubTotal	MONEY	NOT NULL,
VAT		MONEY	NOT NULL,
Delivery	MONEY	NOT NULL,
GrandTotal	AS (SubTotal + VAT + Delivery),
ShippingDate	DATE	NULL,)
GO

Table with Sparse Column

Fields with NULL values will still use a certain amount of storage. Add the 'SPARSE' attribute to the column definition to minimise the space used by NULL fields:

CREATE TABLE Orders.DeliveryAddress (
AddressID	INT	IDENTITY(1,1),
AddressLine1	VARCHAR(50)	NOT NULL,
AddressLine2	VARCHAR(50)	SPARSE NULL,
AddressLine3	VARCHAR(50)	SPARSE NULL,
Country		VARCHAR(50)	NULL)
GO

Table with Primary Key

Primary Keys uniquely identify each row in a table and are essential for modifying or deleting single rows of data.

DROP TABLE Orders.OrderData
GO

CREATE TABLE Orders.OrderData (
OrderID		INT	IDENTITY(1,1),
OrderDate	DATE	NOT NULL,
SubTotal	MONEY	NOT NULL,
VAT		MONEY	NOT NULL,
Delivery	MONEY	NOT NULL,
GrandTotal	AS (SubTotal + VAT + Delivery),
ShippingDate	DATE	NULL,
CONSTRAINT pk_orderdata PRIMARY KEY (OrderID))
GO

Table with Unique Constraint

Unique constraints define columns that must contain unique values. More than one unique constraint can be specified for a table. FILESTREAM data requires a unique constraint on a column.

CREATE TABLE Robots.RobotManual (
DocID		UNIQUEIDENTIFIER 	ROWGUIDCOL UNIQUE,
DocumentType	VARCHAR(30)		NOT NULL,
Document	VARBINARY(MAX)		FILESTREAM NULL,
CONSTRAINT pk_robotmanual PRIMARY KEY(DocID))
GO

Table with Foreign Key

Foreign key constraints limit the contents of a column to the range of values specified in the corresponding column of the foreign table

DROP TABLE Robots.Robot
GO
CREATE TABLE Robots.Robot (
RobotID		INT		IDENTITY(1,1),
RobotName	VARCHAR(50)	NOT NULL,
CreatedDate	DATE		NOT NULL DEFAULT GETDATE(),
DesignerID	INT		NOT NULL,
RobotManual	XML		NULL
CONSTRAINT pk_robots PRIMARY KEY(RobotID))
GO
CREATE TABLE Robots.Designers (
DesignerID	INT		IDENTITY(1,1),
DesignerName	VARCHAR(50)	NOT NULL,
CONSTRAINT pk_designers PRIMARY KEY(DesignerID))
GO
ALTER TABLE Robots.Robot
ADD CONSTRAINT fk_robotdesigner FOREIGN KEY(DesignerID)
REFERENCES Robots.Designers(DesignerID)
GO

Table with Check Constraint

A check constraint express a logical condition that values for a field must fulfill

DROP TABLE Orders.OrderData
GO
CREATE TABLE Orders.OrderData (
OrderID		INT	IDENTITY(1,1),
OrderDate	DATE	NOT NULL,
SubTotal	MONEY	NOT NULL CHECK (SubTotal > 0),
VAT		MONEY	NOT NULL CHECK (VAT >= 0),
Delivery	MONEY	NOT NULL,
GrandTotal	AS (SubTotal + VAT + Delivery),
ShippingDate	DATE	NULL,
CONSTRAINT pk_orderdata PRIMARY KEY(OrderID))
GO
ALTER TABLE Orders.OrderData WITH CHECK
ADD CONSTRAINT ck_shippingdate CHECK (ShippingDate > OrderDate)
GO