intro to tsql unit 7

Introduction To SQLUnit 7

Modern Business Technology

Introduction To TSQLUnit 7

Developed by

Michael Hotek

Integrity

• Integrity is the process by which data is validated and consistency is enforced

• Databases were designed with integrity as a primary factor

• Integrity can be enforced by a variety of means– Rules– Defaults– Constraints– Primary keys– Foreign keys– Unique indexes– Triggers

• Integrity can also be programmatic or declarative

Declarative Integrity

• Defaults and constraints can be used directly in a create table statement, hence declarative integrity

• Constraints include– Check– Unique– Primary Key– Reference

• Constraints can be column or table level

• Defaults are only column level

Defaults

• A default clause is used to supply a value for a column when one is not explicitly specified in an insert statement

• For a DEFAULT constraint:[CONSTRAINT constraint_name]

DEFAULT {constant_expression | niladic-function | NULL}

[FOR col_name]

create table address

(CompID int not null,

Address varchar(50) not null,

City varchar(30)

default 'Chicago',

State char(2) default 'IL')

Defaults

• Functions can also be used in place of constants as long as they return a single value

• The value of the default must match the datatype of the column

• Character and date values must be enclosed in quotes

• A column can have only one default• sp_helpconstraint can be used to

return constraint information about a table.

Check Constraints

• Check constraints are used to enforce domain integrity

• Can be applied at a table and a column level

• Constraints are used to specify:– List or set of values– range of values– Format for data– Conditions on a value

• Enforced during inserts and updates• Must evaluate to a true or false

Column Constraints

create table people

(SSN char(11) not null

constraint chk_ssn

check (SSN like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]',

FirstName varchar(30) not null,

LastNamevarchar(50) not null,

…)

orcreate table people

(SSN char(11) not null

check (SSN like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]',

FirstName varchar(30) not null,

LastNamevarchar(50) not null,

…)

Table Constraints

• Used for more than one columncreate table discounts

(Type varchar(40) not null,

StoreID char(4) not null,

LowQty int not null,

HighQty int not null,

Discountfloat null,

constraint chk_low_high

check (LowQty <= HighQty))

Indexes

• Separate structure attached to a table

• Contain pointers to the physical data• Used to increase performance when:

– Finding rows– Correlating data across tables– Ordering result sets– Inserting data in some cases

• Can enforce unique values in a column or table

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name

ON [[database.]owner.]table_name (column_name [, column_name]...)[WITH[FILLFACTOR = x][[,] IGNORE_DUP_KEY]

[[,] {SORTED_DATA |SORTED_DATA_REORG}]

[[,] {IGNORE_DUP_ROW |ALLOW_DUP_ROW}]][ON segment_name]

Pages

• Data is stored in SQL Server is a set of structures called pages

• Each page is 2K in size (8K in 7.0)• Many rows can be on a single page• A single row must be contained

entirely on a page• Each page contains a header area

that identifies the contents of each page

• Pages are stored in a doubly linked list

Indexes

• As data gets added, a large number of pages are created

• Indexes were devised to quickly navigate these pages

• Indexes are also stored in pages • The index pages are stored in a B-

Tree to support quick location of information

177-32-1176

756-30-7391

756-30-7391

899-46-2035

177-32-1176

267-41-2394

409-56-7008

177-32-1176...

213-46-8915...

238-95-7766...

267-41-2394...

341-53-8472...

402-31-7808...

409-56-7008...

532-86-9471...

655-27-5281...

756-30-7391...

775-93-6481...

835-21-6639...

Data PagesIndex Pages

Indexes

Indexes

• There are two types of indexes– clustered– nonclusterd

• Only be 1 clustered index per table• Up to 249 nonclustered indexes• Order of data in the table is

determined by the type of index– clustered index

• Data in the same order as the index

– nonclustered index• Data in the order it was inserted

Keys and Indexes

• Keys:• Logical

– Primary, foreign, and common

• Physical– Single column or composite– This is an index

• Indexes are not necessarily logical keys

• Indexes can be applied to columns that are not keys

• Can contain up to 16 columns• Can have a maximum size of 256

bytes

Clustered Index

• Only one per table• This is your most powerful index• Physically orders the data in a table• Can be equated to the old card

catalog• Good for range searches• Slow for inserts

– page splitting

Clustered Indexes

Bennet 1007Page 1001

Karsen 1009Smith 1062

Key ptr

Karsen 1315Page 1009Key ptr

Bennet 1132Page 1007

Greane 1133Hunter 1127

Key ptr

HunterPage 11127

Jenkins

GreanePage 1133

GreenGreene

BennetPage 1132

Chan

EdwardsDull

Data Pages

Leaf LevelIntermediateRoot Page

Clustered Indexes

• The leaf level of the index is the data page of the table

• Only one entry can point to a page in the next level

• Require an additional 120% of space during creation

Nonclustered Indexes

• Data is stored in a random order at the data page level

• Up to 249 nonclustered indexes can be defined per table

• Good for searches of explicit values• Are much larger than a clustered

index

Page 1001Bennet 10071421,1Karsen 13051876,1Smith 10621242,1

Page 1007Bennet 11321421,1Greane 11331242,4Hunter 11271242,1

Page 1305Karsen 13111876,1

Page 1133Greane 1242,4Green 1409,2Greene 1421,2

Page 1127Hunter 1242,1Jenkins 1241,4

Page 142118 Bennet19 Greene20 Ringer

Page 140921 Dull22 Green23 White

Page 124110 O'Leary11 Ringer12 White13 Jenkins

Page 124214 Hunter15 Smith16 Ringer17 Greane

Page 1132Bennet 1421,1Chan 1129,3Dull 1409,1

Edwards 1018,5

KeyRowptr

Pageptr

KeyRowptr

Pageptr

KeyRowptr

Root Page Intermediate Leaf Pages Data Pages

Nonclustered Indexes

Nonclustered Indexes

• The root and intermediate levels work similarly for both clustered and nonclustered indexes

• The leaf level of a nonclustered index contains a pointer to the row on each data page

• The pointers at the leaf level are in index order

Unique Constraint

• Ensures no two rows have the same value

• Allows one null value in a column• Creates a unique, nonclustered index

by default

create table publishers

(pub_id char(4) null,

constraint u_pub_id unique,

pub_namevarchar(30) not null)

Primary Key

• Ensures no two rows have the same value

• Nulls are not allowed• Creates a unique, clustered index by

default

create table publishers

(pub_id char(4)

constraint publishers_PK primary key,

pub_namevarchar(30))

create table sales

(stor_idchar(4) not null,

ord_num varchar(20) not null,

date datetime nit null,

constraint sales_PK primary key

nonclustered (stor_id, ord_num))

Referential Integrity

• Used to maintain foreign keys when data is inserted or updated

• Columncreate table <table name>

(column datatype

[constraint constraint_name]

references ref_table [ref_column]

• Table[constraint constraint_name]

foreign key (column [{,column}…])

references ref_table

[(column [{, column}…])]

Referential Integrity

• Use a column level constraint when only one column needs to be compared

• Use a table level constraint when more than one column needs to be compared

• The table in the references clause must already have a primary/unique constraint or a unique index defined on the columns

• A roll back is issued if referential integrity is violated and a message is sent back

Referential Integrity

• Column Levelcreate table titles

(title_id tid not null,

title varchar(80) null,

pub_id char(4) null

constraint publishers_pub_id

references publishers(pub_id),

notes varchar(200) null)

• Restrictions

Referential Integrity

create table salesdetail

(stor_idchar(4) not null,

ord_num varchar(20) not null,

title_idtid not null,

qty int not null,

discountfloat not null,

constraint salesdetail_FK1

foreign key (stor_id, ord_num)

references sales(stor_id, ord_num),

constraint salesdetail_FK2

foreign key (title_id)

references titles(title_id))

Referential Integrity

• When primary keys are deleted and updated, three different option could be performed:– Restrict– Cascade– Set null

• Declarative RI enforces a restrict• Cascade and set null can only be

accomplished with triggers• In a perfect world, updates to a

primary key are not allowed• In an imperfect world, these should

be kept to a minimum

Error Messages

• Custom messages can be added with sp_addmessage

• Drop a message with sp_dropmessage

• Get a message with sp_getmessage• In Sybase, these messages can be

bound to a constraint so that on a failure, a nice message to returned to the user

• Bind messages using sp_bindmsg• Unbind messages using

sp_unbindmsg

Alter Table

• Once a table is created, certain modifications to its structure can be performed

• Allowed:– Add columns– Add, drop, or change constraints

• Not allowed– Dropping columns– Changing datatypes– Changing width of columns– Changing nullability options

• Constraints can only be modified with an alter table statement

• Modifications to constraints do not affect existing data

Alter Table

ALTER TABLE [database.[owner].]table_name [WITH NOCHECK][ADD

{col_name column_properties [column_constraints]

| [[,] table_constraint]}[, {next_col_name |

next_table_constraint}]...]| [DROP [CONSTRAINT]

constraint_name [, constraint_name2]...]

Getting Help

• To obtain information about constraints and defaults defined for a table use

sp_helpconstraint table

Unit 7 Review

• Constraints are used to enforce integrity

• A default will supply a value during an insert

• Check constraints enforce valid data during inserts and updates

• Data is stored in data pages that are 2K in size

• A table can have 1 clustered index

– Physically orders the data

– Leaf level of index is the data pages

– Used for range searches

• A table can have up to 249 nonclusterd indexes

– Does not order the data

– The leaf level of the index contains pointers to rows

– Used for explicit searches

• Indexes can have up to 16 columns

• Can be a maximum of 256 bytes

• Unique constraint creates a unique, nonclustered index by default and allows one null

• Primary key constraint creates a unique, clustered index by default and doe not allow nulls

• Foreign keys are enforced via a references constraint

• Referenced column(s) must have a primary/unique constraint or a unique index defined

• Roll back is performed if RI is violated

• The only type of RI that can be applied when modifying a primary key with constraints is restrict

• You can add custom messages

• Alter table can add columns

• Alter can add, drop, or modify constraints

• You can not drop a column

• You can not change a datatype

• You can not change the length of a column

• You can not change the nullability

Unit 7 Review

• Time allotted for exercises is 30 minutes

Unit 7 Exercises