2779 Module Overview
Module 1 Creating a DB
Module 2 Creating Data Types and tables
Module 3 Using XML
Module 4 Creating and Tuning indexes
Module 5 Data Integrity and using constraints
Module 7 Implementing Views
Module 8 Sprocs (can use .NET Managed Code code to write sprocs)
Module 10 Managed Code
Module 12 Service Broker
Skips 9-Implementing Functions, 11-Transactions and Logs, 13-Notification Services
Module 1 – Creating Databases
Considerations:
-Purpose of the data:
-to support online transaction processing (OLTP)
· i.e. on going business customers place orders, the orders are placed into the database
· throughput is important
· relational tables reduce redundant information and increase speed of updates.
· SQL server allows large numbers of users to perform transactions and change real-time data in OLTP databases.
-to support online analytical processing (OLAP)
· Pull data from a OLTP database to be inserted into a OLAP database to be analyzed later by SQL Server Analysis Services or other Reporting software.
· throughput is not as important
-Transaction throughput
- A measure of the amount of data transferred in a specific amount of time, usually expressed as bits per second (bps).
-important to an OLTP database
-Potential growth
-Calculation online (above url)
-File Location
-
Transaction Logging:
- Data modification occurs
- Data pages to be modified are placed in a buffer cache in RAM.
- Modifications are made to the pages.
- Modifications are then written to the transaction log.
- Once a check point is set (once a minute ish) in the log the log is written to the database.
DB Options:
Option Purposes
- Automatic: automation ability (statistics, database shrinking,etc.)
- Availability: online, who can connect, is it read-only
- Cursor: Cursor behavior and scope
- Recovery: Simple, Full, Bulk logged
- SQL: ANSI compliance options
-use SSMS or ALTER DATABASE command to set these options (page 1-8)
Finding Information about Databases:
-Use SSMS
-Use catalog views
· –return rows of info on database objects (replaces infoschema views)
· -i.e select * from sys.databases returns one row for each db on the server
-Use Metadata functions – returns one single value
· i.e. select DB_ID ('adventureWorks') returns 8 (the ID of the DB)
-Use System sprocs – new ones available in SQL2005
· i.e. sp_databases returns one row for every DB.
Creating a database
-blah blah blah I already know how
to do this…
Here is syntax for reference even though SSMS will generate the syntax for me
if I want.
CREATE DATABASE
NAME
= N'TestDB',
FILENAME
= N'C:\ DATA\TestDB.mdf' ,
SIZE
= 2048KB ,
FILEGROWTH
= 1024KB
)
--gotta put the log somewhere
LOG
ON
(
NAME
= N'TestDB_log',
FILENAME
= N'C:\DATA\TestDB_log.ldf' ,
SIZE
= 1024KB ,
FILEGROWTH
= 10%
)
Filegroups (page 1-19)
-a way to manage files on a server
-example:
A new database by default has all files go into the primary filegroup (mdf)
Create a secondary filegroup
Leave system tables in primary
Move all new objects in the secondary filegroup.
Syntax:
-here I have created a filegroup called “Extra”, made it the default filegroup, and put two files in it.
ALTER DATABASE TransactTestDB
ADD FILEGROUP Extra
GO
ALTER DATABASE TransactTestDB
ADD FILE ( NAME = N'TransactSQLTestDB2',
FILENAME=
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TransactSQLDB2.ndf')
TO FILEGROUP EXTRA
ALTER DATABASE TransactTestDB
ADD FILE ( NAME = N'TransactSQLTestDB3',
FILENAME=
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TransactSQLDB3.ndf')
TO FILEGROUP EXTRA
GO
ALTER DATABASE TransactTestDB
MODIFY FILEGROUP Extra DEFAULT
Schema (page 1-27)
Namespace for database objects
servername.dbname.schemaname.objectname
Object name resolution
Users
Bill = Tech
Mary = Ops
Three schemas
Tech (contains table project)
Ops
dbo
-If bill does a select * from project he gets results.
-If mary does a select * from tech.project she gets results because she prequalified it with “tech.”.
-If mary wants to access dbo.Errorlog select * from errorlog she will get results because sql server looks in her schema first, Ops, then if it doesn’t find the object it looks in dbo by default.
-If mary wants to select * from contact which doesnt live in the Ops or dbo schemas she will get an error.
Define schemas under DBàSecurityà Schemas
Syntax for manually adding a schema is:
CREATE schema SchemaName
Database Snapshots
-a snapshot copies the structure of a database at a certain point in time and is read only.
-uses a NTFS sparse file.
-the only way data is put into the snapshot database is by changing the source database.
-a snapshot database is dependant on the source database for it’s information.
-storage is on a page level
-can not be created with GUI (SSMS)
-syntax:
CREATE
DATABASE Adventureworks_snapshot
ON (
NAME = N'AdventureWorks_Data',
FILENAME
= N'C:\DATA\TestDB.ss'
)
AS
SNAPSHOT OF AdventureWorks
GO
-examples of how the snap shot works:
update Adventureworks.person.Address
set addressline1 = '100
Where addressID
=1
Select addressid,addressline1,modifieddate
From AdventureWorks.Person.Address
Where AddressID=1
addressid addressline1
modifieddate
-----------
------------------------------------------------------------
-----------------------
1 100
Select addressid,addressline1,modifieddate
From AdventureWorks_snapshot.Person.Address
Where AddressID=1
addressid addressline1
modifieddate
-----------
------------------------------------------------------------
-----------------------
Module finished at 12:44pm 3/4/08.