Transcript CREATE DATABASE database_name ON filespec

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
C:\
D:\
E:\
Mydb.mdf
Morespac.ndf
Mylog.ldf
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 Field
ON 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_name
ADD 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 follows
sp_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