Intro To Tsql - Unit 7

Introduction To SQL Unit Introduction 7

To TSQL Unit 7

Modern Business Technology 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 (CompID Address City State

address int not null, varchar(50) not null, varchar(30) default 'Chicago', 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, LastName varchar(50) not null, …)

or create 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, LastName varchar(50) not null, …)

Table Constraints • Used for more than one column create table discounts (Type varchar(40) not null, StoreID char(4) not null, LowQty int not null, HighQty int not null, Discount float 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 BTree to support quick location of information

Indexes

Index Pages

Data Pages 177-32-1176... 213-46-8915... 238-95-7766... 177-32-1176 267-41-2394

267-41-2394...

409-56-7008

341-53-8472... 402-31-7808...

177-32-1176 756-30-7391

409-56-7008... 756-30-7391

532-86-9471...

899-46-2035

655-27-5281...

756-30-7391... 775-93-6481... 835-21-6639...

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

Data Pages Key ptr Page 1007 Bennet 1132 Greane 1133 Hunter 1127 Key ptr Page 1001 Bennet 1007 Karsen 1009 Smith 1062

Page 1133 Greane Green Greene Key ptr Page 1009 Karsen 1315

Root Page

Page 1132 Bennet Chan Dull Edwards

Intermediate

Page 11127 Hunter Jenkins

Leaf Level

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

Nonclustered Indexes

Row ptr Page 1132 Bennet 1421,1 Chan 1129,3 Dull 1409,1 Edwards 1018,5 Key

Row Page ptr ptr Page 1007 Bennet 1421,1 1132 Greane 1242,4 1133 Hunter 1242,1 1127 Key

Row Page ptr ptr Page 1001 Bennet 1421,1 1007 Karsen 1876,1 1305 Smith 1242,1 1062 Key

Page 1133 Greane 1242,4 Green 1409,2 Greene 1421,2 Page 1305 Karsen 1876,1 1311 Page 1127 Hunter 1242,1 Jenkins 1241,4

Page 1241 10 O'Leary 11 Ringer 12 White 13 Jenkins

Page 1242 14 Hunter 15 Smith 16 Ringer 17 Greane

Page 1421 18 Bennet 19 Greene 20 Ringer

Page 1409 21 Dull 22 Green 23 White

Root Page

Intermediate

Leaf Pages

Data Pages

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_name varchar(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_name varchar(30)) create table sales (stor_id char(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 • Column create table (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 Level create 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_id char(4) not null, ord_num varchar(20) not null, title_id tid not null, qty int not null, discount float 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

Unit 7 Review

• •

• • • • • • • • •

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 Exercises

• Time allotted for exercises is 30 minutes

---

Related Documents

---

More Documents from ""