Agenda for Today

Chapter 5– Skip Lesson 2

Review questions Midterm Chapter 6 Review questions

Database files

Each SQL Server database has at least one primary data file and one transaction log file

Primary data file (.mdf) Transaction log file (.ldf) Secondary data files (.ndf)

Files and filegroups

Possible layout

Mydb.mdf Mylog.ldfMorespac.ndf

D:\C:\ E:\

Default Filegroup

Myxtra Filegroup

Units of Storage

The fundamental unit of data storage is Page. A page is 8 KB in size

Extents are the basic unit in which space is allocated to tables and indexes

An extent is made of 8 pages (64 KB) Two types of Extents

– Mixed - can stored upto 8 objects

– Uniform - data from one object

Indexes

Indexes are used to lookup information

In a database, an index allows the database program to find data in a table without scanning the entire table

Automatically created on a PK field Two Types

– Clustered - data is sorted in order

– Nonclustered - data is not sorted

– Pages 140 and 141

Stored Procedures (sp’s)

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan

Stored procedures can use input and output parameters

Executes faster than writing T-SQL as it is compiled and part of the database

Typically used to enforce Business Rules

Also another security mechanism

Views

A view can be thought of as either a virtual table or a stored query

A view can represent data from more than one table

Views can be used as security mechanisms by granting permission on a view and not on the table

Can be created using– CREATE VIEW name AS

System and Database Catalogs

System Catalog - system tables with info on the server

Database Catalog - system tables with info on each database

System sp’s are used to report information on a database object

System Functions Some of these objects are listed in

Tables 5-1 through 5-5

Class Assignments

Try the following exercises Page 137 Page 147 Page 151 Page 152 Page 155 Page 157 Take a break when done

Midterm

On 2/12, no exceptions Ch 1 - 7 + lecture material 100 questions, 200 points Multiple choice 2 hours - 5:30 - 7:30 Open Book (straight curve) Closed Book (distribution curve) Your Vote - Majority Wins Sample midterm on the website

Object identifiers for DB

First character:– Alphabetic or letter character– _, @, or #

Remaining characters:– Any Unicode standard 2.0 letters– Decimal numbers– @, $, _, and #

Not allowed for standard identifiers– Embedded spaces– Reserved words

Naming conventions

Keep identifiers as short as possible. Keep object names and user names

unique. When possible, use names that

describe the object in a meaningful fashion.

Use names that identify the object types.

Database parameters

What do you need? Database name Database size and location Transaction log name Transaction log size and location Growth parameters

CREATING DATABASES

Creating new databases Three options Using Create Database Wizard in

Enterprise Manager using Tools - Wizards menu option

Using Enterprise Manager CREATE DATABASE T-SQL

Command

1. Create Database Wizard

Database name and file locations

continued

Database File

continued

Transaction Log File

2. Enterprise Manager

continued

Database File

continued

Transaction Log File

General properties

CREATE DATABASE

Using Query Analyzer CREATE DATABASE syntax:CREATE DATABASE database_name

ON filespec

[LOG ON filespec]

FOR ATTACH

CREATE DATABASE example:

CREATE DATABASE FieldON PRIMARY

FILENAME='c:\SQL Admin\C03E0301\Field.mdf')

FOR ATTACH

Class Assignments

Do the Exercises on Pages 168, 171 and 173 Review the results with me Take a break when done

DATABASE options

User database follows the options set for model database

You have different ways of reviewing and changing database options

Enterprise Manager DATABASEPROPERTYEX system function ALTER DATABASE command sp_dboption system stored procedure

Enterprise Manager

ALTER DATABASE

ALTER DATABASE syntax:ALTER DATABASE database_nameADD FILE filespec [TO FILEGROUP filegroup_name]| ADD LOG FILE filespec| REMOVE FILE logical_filename| ADD FILEGROUP filegroup_name| REMOVE FILEGROUP filegroup_name| MODIFY FILE filespec| MODIFY FILEGROUP filegroup_name filegroup_property|SET optionspec [WITH termination]|COLLATE collation_name

Sp_dboption syntax

Supported for backward compatibility Displays or changes database options. sp_dboption should not be used on

either the master or tempdb databases.

Syntax is as followssp_dboption [[@dbname=]'database_name']

[,[@optname=]'option_name']

[,[@optvalue=]'value']

Sp_dboption choices ANSI null default ANSI warnings arithabort auto update

statistics autoclose autoshrink concat null yields

null cursor close on

commit dbo use only local cursor

merge publish numeric roundabort offline published read only recursive triggers select into/bulkcopy single user torn page detection trun. Log on chkpt.

Managing Database Size

Automatic Growth (data and log) Auto Shrink (under options) Manually - preferred Enterprise Manager ALTER DATABASE T-SQL command

Reducing file sizes

In order to shrink the data and log files, you have two options

Enterprise Manager DBCC SHRINKFILE T-SQL provides DBCC statements

that act as the “database consistency checker” for SQL Server

Shrinks the size of the specified data file or log file for the related database.

Reducing database size

In order to shrink the database, you have two options

Enterprise Manager DBCC SHRINKDATABASE Shrinks the size of the data files in the

specified database.

Enterprise Manager

T-SQL

DBCC SHRINKDATABASE syntax:

DBCC SHRINKDATABASE (database_name[, target_percentage])

[, NOTRUNCATE | TRUNCATEONLY]

Multiple Disk for Databases

Many benefits– Increase Performance– Fault Tolerance– Recoverability– Easy Maintenance

Two options RAID Filegroups

RAID

RAID stands for Redundant Array of Inexpensive Disks

RAID is a way of combining multiple disk drives into a single entity to improve performance and/or reliability.

Many types of RAID, some of which are listed below

RAID

Disk Striping (RAID 0) Disk Mirroring (RAID 1) Disk Striping with parity (RAID

5) Disk Striping with parity - RAID

10 (RAID 1 + RAID 0)

RAID 0 - Disk Striping - fast

RAID 1 - Disk Mirror - fault tolerance

RAID 5 - Striping with Parity - speed and fault tolerance

RAID 10 - Striping with Mirror - the works

Class Assignments

Try the following exercises Page 182 Page 184 Review questions on Page 196 Next week Chapter 7 and Midterm

Review