SQLServerCh11

Chapter 11

How to create and
maintain databases,
tables, and sequences
with SQL statements

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 1

Objectives
Applied
 Given a complete database design, write the SQL DDL statements
to create the database, including all tables, relationships,
constraints, indexes, and sequences.
Knowledge
 Describe how each of these types of constraints restricts the
values that can be stored in a table: NOT NULL, PRIMARY
KEY, UNIQUE, CHECK, and FOREIGN KEY (or
REFERENCES).
 Describe the difference between a column-level constraint and a
table-level constraint.
 Explain how the CASCADE and NO ACTION options differ in
enforcing referential integrity on deletes and updates.

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 2

Objectives (cont.)
 Describe the use of a sequence.
 Describe the use of a script that contains one or more batches for
creating a database.

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 3

DDL statements to create, modify,
and delete objects
CREATE DATABASE
CREATE TABLE
CREATE INDEX
CREATE SEQUENCE
CREATE FUNCTION
CREATE PROCEDURE
CREATE TRIGGER
CREATE VIEW
ALTER TABLE
ALTER SEQUENCE
ALTER FUNCTION

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 4

DDL statements to create, modify,
and delete objects (continued)
ALTER PROCEDURE
ALTER TRIGGER
ALTER VIEW
DROP DATABASE
DROP TABLE
DROP SEQUENCE
DROP INDEX
DROP FUNCTION
DROP PROCEDURE
DROP TRIGGER
DROP VIEW

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 5

Formatting rules for identifiers
 The first character of an identifier must be a letter as defined by
the Unicode Standard 2.0, an underscore (_), an at sign (@), or a
number sign (#).
 All characters after the first must be a letter as defined by the
Unicode Standard 2.0, a number, an at sign, a dollar sign ($), a
number sign, or an underscore.
 An identifier can’t be a Transact-SQL reserved keyword.
 An identifier can’t contain spaces or special characters other than
those already mentioned.

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 6

Valid regular identifiers
Employees
#PaidInvoices
ABC$123
Invoice_Line_Items
@TotalDue

Valid delimited identifiers
[%Increase]
"Invoice Line Items"
[@TotalDue]

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 7

Basic syntax of the CREATE DATABASE statement
CREATE DATABASE database_name
[ON [PRIMARY] (FILENAME = 'file_name')]
[FOR ATTACH]

Create a new database
CREATE DATABASE New_AP;

The response from the system
Command(s) completed successfully.

Attach an existing database file
CREATE DATABASE Test_AP
ON PRIMARY (FILENAME =
'C:\Murach\SQL Server 2012\Databases\Test_AP.mdf')
FOR ATTACH;

The response from the system
Command(s) completed successfully.

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 8

Basic syntax of the CREATE TABLE statement
CREATE TABLE table_name
(column_name_1 data_type [column_attributes]
[, column_name_2 data_type [column_attributes]]...
[, table_attributes])

Common column attributes


NULL|NOT NULL



PRIMARY KEY|UNIQUE



IDENTITY



DEFAULT default_value



SPARSE

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 9

Create a table without column attributes
CREATE TABLE Vendors
(VendorID
INT,
VendorName
VARCHAR(50));

Create a table with column attributes
CREATE TABLE Invoices
(InvoiceID
INT
VendorID
INT
InvoiceDate
SMALLDATETIME
InvoiceTotal
MONEY

PRIMARY KEY IDENTITY,
NOT NULL,
NULL,
NULL DEFAULT 0);

A column definition that uses
the SPARSE attribute
VendorAddress2

Murach's SQL Server 2012, C11

VARCHAR(50) SPARSE NULL

© 2012, Mike Murach & Associates, Inc.

Slide 10

Basic syntax of the CREATE INDEX statement
CREATE [CLUSTERED|NONCLUSTERED] INDEX index_name
ON table_name (col_name_1 [ASC|DESC]
[, col_name_2 [ASC|DESC]]...)
[WHERE filter-condition]

Create a nonclustered index on a single column
CREATE INDEX IX_VendorID
ON Invoices (VendorID);

Create a nonclustered index on two columns
CREATE INDEX IX_Invoices
ON Invoices (InvoiceDate DESC, InvoiceTotal);

Note
 SQL Server automatically creates a clustered index for a table’s
primary key.

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 11

Create a filtered index for a subset of data
in a column
CREATE INDEX IX_InvoicesPaymentFilter
ON Invoices (InvoiceDate DESC, InvoiceTotal)
WHERE PaymentDate IS NULL;

Create a filtered index for categories in a column
CREATE INDEX IX_InvoicesDateFilter
ON Invoices (InvoiceDate DESC, InvoiceTotal)
WHERE InvoiceDate > '2012-02-01';

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 12

The snippet picker with a list of object folders

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 13

The snippet picker with the list of snippets
for a table

The CREATE TABLE snippet
after it has been inserted

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 14

Terms
 Transaction log file
 Attach a database file
 Full-table index
 Filtered index

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 15

Column-level constraints
Constraint
NOT NULL
PRIMARY KEY

UNIQUE
CHECK
[FOREIGN KEY]
REFERENCES

Murach's SQL Server 2012, C11

Description
Prevents null values from being stored in the
column.
Requires that each row in the table have a
unique value in the column. Null values are not
allowed.
Requires that each row in the table have a
unique value in the column.
Limits the values for a column.
Enforces referential integrity between a column
in the new table and a column in a related table.

© 2012, Mike Murach & Associates, Inc.

Slide 16

Table-level constraints
Constraint
PRIMARY KEY

UNIQUE
CHECK
[FOREIGN KEY]
REFERENCES

Murach's SQL Server 2012, C11

Description
Requires that each row in the table have a unique
set of values over one or more columns. Null
values are not allowed.
Requires that each row in the table have a unique
set of values over one or more columns.
Limits the values for one or more columns.
Enforces referential integrity between one or more
columns in the new table and one or more columns
in the related table.

© 2012, Mike Murach & Associates, Inc.

Slide 17

Create a table with a two-column
primary key constraint
CREATE TABLE InvoiceLineItems1
(InvoiceID
INT
NOT NULL,
InvoiceSequence
SMALLINT
NOT NULL,
InvoiceLineItemAmount
MONEY
NOT NULL,
PRIMARY KEY (InvoiceID, InvoiceSequence));

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 18

Create a table with two column-level
check constraints
CREATE TABLE Invoices1
(InvoiceID
INT
NOT NULL IDENTITY PRIMARY KEY,
InvoiceTotal
MONEY NOT NULL
CHECK (InvoiceTotal >= 0),
PaymentTotal
MONEY NOT NULL DEFAULT 0
CHECK (PaymentTotal >= 0));

The same check constraints
coded at the table level
CREATE TABLE Invoices2
(InvoiceID
INT
NOT
InvoiceTotal
MONEY NOT
PaymentTotal
MONEY NOT
CHECK ((InvoiceTotal >= 0)

Murach's SQL Server 2012, C11

NULL IDENTITY PRIMARY KEY,
NULL,
NULL DEFAULT 0,
AND (PaymentTotal >= 0)));

© 2012, Mike Murach & Associates, Inc.

Slide 19

The syntax of a check constraint
CHECK (condition)

A column-level check constraint
CREATE TABLE Invoices3
(InvoiceID
INT
NOT NULL IDENTITY PRIMARY KEY,
InvoiceTotal
MONEY NOT NULL CHECK (InvoiceTotal > 0));

An INSERT statement that fails
due to the check constraint
INSERT Invoices3
VALUES (-100);

The response from the system
The INSERT statement conflicted with the CHECK constraint
"CK__Invoices3__Invoi__0BC6C43E". The conflict occurred in
database "New_AP", table "dbo.Invoices3", column
'InvoiceTotal'.
The statement has been terminated.

Murach's SQL Server 2012, C11

© 2012, Mike Murach & Associates, Inc.

Slide 20