Intro to SQL Database

Overview

Introducing SQL Server Databases

Working With SQL Server Programming Tools

Understanding Transact-SQL Elements

Programming Language Elements

Executing Transact-SQL Statements

Lesson: Introducing SQL Server Databases

Types of Databases

User defined database

Database Objects

How Are SQL Server Objects Referenced?

System Tables

How Metadata Is Retrieved

Types of Databases

System Databases

master
model tempdb msdb
User defined database
Overview

Defining Databases
Using Filegroups
Managing Databases
How Are Databases Created?

Creating a Database Defines:

 The name of the database
 The size of the database files
 The location where the database will reside
CREATE DATABASE Sample
ON
PRIMARY ( NAME=SampleData,
FILENAME='c:\Program Files\..\..\Data\Sample.mdf',
SIZE=10MB,
MAXSIZE=15MB,
FILEGROWTH=20%)
LOG ON
( NAME=SampleLog,
FILENAME= 'c:\Program Files\..\..\Data\Sample.ldf',
SIZE=3MB,
MAXSIZE=5MB,
FILEGROWTH=1MB)
How is Database Information Retrieved?

Use System Stored Procedures to Display Information About

Databases and Database Parameters

 sp_helpdb

 sp_helpdb database_name

 sp_spaceused [objname]

 EXEC sp_dboption 'mydb', 'single user', 'FALSE'

 EXEC sp_dboption 'pubs', 'read only', 'FALSE'

 EXEC sp_dboption 'sales', 'offline', 'TRUE'

Lesson: Using Filegroups

What Are Filegroups?

Filegroup Management
What Are Filegroups?

Northwind Database
sys... ...
sys... Orders
sysusers OrdHistYear2
Customers
sysobjects Products OrdHistYear1

C:\ D:\ E:\

OrdHist1.ndf
Northwind.mdf OrdHist2.ndf Northwind.Idf

Default Filegroup OrderHistoryGroup

Guidelines for Managing File Growth

Use Automatic File Growth

Expand Database Files
Add Secondary Database Files
ALTER DATABASE Sample
MODIFY FILE ( NAME = 'SampleLog',
SIZE = 15MB)
GO

ALTER DATABASE Sample

ADD FILE
(NAME = SampleData2,
FILENAME='c:\Program Files\..\..\
Data\Sample2.ndf',
SIZE=15MB,
MAXSIZE=20MB)
GO
How Databases Are Dropped

Methods of Dropping a Database

 Object Explorer
 DROP DATABASE statement
DROP DATABASE Northwind, pubs

Restrictions on Dropping a Database

 When a user is connected to it
 If it is a system database
Lab A: Creating and Managing Databases

Exercise 1: Creating the ClassNorthwind

Database
Exercise 2: Managing the Growth of the
ClassNorthwind Transaction Log File
Exercise 3: Setting the Database
Recovery Model
 Database Objects

Clustered Index
Anderson Stored
PK Anderson FK Procedure
Barr UpdatePhone Trigger
...
EmpNum LastName FirstName CtryCode Extension LastMod
integer varchar(35) varchar(20) char(2) char(6) char(15)

10191 Lang Eric FR x19891 \HR\KarlD

10192 Lang Ingelise FR x19433 \HR\KarlD

10202 Miller Frank SP x21467 \HR\AmyL

EmployeePhoneView
SELECT lastname, firstname, extension Check
FROM employee x#####
How Are SQL Server Objects Referenced?

Fully Qualified Names

 server.database.owner.object
Partially Specified Names
 Server defaults to the current instance on the local server
 Database defaults to current database
 Owner defaults to the logged on user, then to dbo

CREATE TABLE Northwind.dbo.OrderHistory

.
.
.
System Tables

System Tables Store Information (Metadata) About

the System and Database Objects
How Metadata Is Retrieved

System Stored Procedures

EXEC sp_help Employees

System and Metadata Functions

SELECT USER_NAME(10)

Information Schema Views

SELECT * FROM
INFORMATION_SCHEMA.TABLES
Lesson: Working with SQL Server Programming
Tools

Microsoft Sql Server Management Studio

SQLCMD
 SQLCMD Utility

Command-line utility

Use the QUIT or EXIT command to exit

sqlcmd -U login_id [-d db_name]

[-P password] [-S server_name] –Q [“select * from <table name>”]
Lesson: Understanding Transact-SQL Elements

Standards Compliance
Data Control Language Statements
Data Definition Language Statements
Data Manipulation Language Statements
SQL Server Object Names
Guidelines for Naming Database Objects
Data Control Language Statements

Set or Change Permissions

 GRANT
 DENY
 REVOKE
Data Definition Language Statements

Define the Database Objects

 CREATE object_type object_name
 ALTER object_type object_name
 DROP object_type object_name
Data Manipulation Language Statements

Use When Working with Data in the Database

 SELECT
 INSERT
 UPDATE
 DELETE
SQL Server Object Names

Standard Identifiers
 First character must be alphabetic
 Other characters can include letters, numerals,
or symbols
 Identifiers starting with symbols have special uses
Delimited Identifiers
 Use when names contain embedded spaces
 Use when reserved words are portions of names
 Enclose in brackets ([ ]) or quotation marks (" ")
Guidelines for Naming Database Objects

Use Meaningful Names Where Possible

Keep Names Short
Use a Clear and Simple Naming Convention
Chose an Identifier That Distinguishes Types of Objects
 Views
 Stored procedures
Lesson: Programming Language Elements

Local Variables
Operators
Types of Functions
Function Examples
Control-of-Flow Language Elements
Comments
Local Variables

User-defined with DECLARE Statement

Assigned Values with SET or SELECT Statement

DECLARE @vLastName char(20),

@vFirstNamevarchar(11)
SET @vLastName = 'Dodsworth'
SELECT @vFirstName = FirstName
FROM Northwind..Employees
WHERE LastName = @vLastName
PRINT @vFirstName + ' ' + @vLastName
GO
Operators

Types of Operators
 Arithmetic
 Comparison
 String concatenation
 Logical
Types of Functions

Aggregate Functions
SELECT AVG(UnitPrice) FROM Products

Scalar Functions
SELECT DB_NAME() AS 'database'
 Function Examples

SELECT 'ANSI:' AS Region,

CONVERT(varchar(30), GETDATE(), 102) AS Style
UNION
SELECT 'European:', CONVERT(varchar(30), GETDATE(), 113)
UNION
SELECT 'Japanese:', CONVERT(varchar(30), GETDATE(), 111)

Result
Region Style
ANSI: 2000
European: 22 Mar 2000 14:20:00:010
Japanese: 2000/03/22
Control-of-Flow Language Elements

Statement Level
 BEGIN…END blocks IF USER_NAME() <> 'dbo'
BEGIN
 IF…ELSE blocks print('Must be sysadmin
 WHILE constructs to Perform Operation',)
RETURN
Row Level END
ELSE
 CASE expression DBCC CHECKDB(Northwind)
Comments

In-Line Comments
SELECT ProductName,
(UnitsInStock + UnitsOnOrder) AS Max -- Calculates inventory
, SupplierID
FROM Products

Block Comments
/*
This code retrieves all rows of the products table
and displays the unit price, the unit price increased
by 10 percent, and the name of the product.
*/
SELECT UnitPrice, (UnitPrice * 1.1), ProductName
FROM Products
Lesson: Executing Transact-SQL Statements

Dynamically Constructing Statements

Batches
Scripts
Transactions
XML Support
Best Practices
Dynamically Constructing Statements

Use EXECUTE with String Literals and Variables

Use When You Must Assign Value of Variable at
Execution Time
Any Variables and Temporary Tables Last Only
During Execution

DECLARE @dbname varchar(30), @tablename varchar(30)

SET @dbname = 'Northwind'
SET @tablename = 'Products'

EXECUTE
('USE ' + @dbname + ' SELECT * FROM '+ @tablename)
Batches

One or More Transact-SQL Statements

Submitted Together
Define a Batch by Using the GO Statement
How SQL Server Processes Batches
You Cannot Combine Some Statements in a Batch
 CREATE PROCEDURE
 CREATE VIEW
 CREATE TRIGGER
 CREATE RULE
 CREATE DEFAULT
Scripts

Contain Saved Statements

Can Be Written in Any Text Editor
 Save by using .sql file extension
Execute in SQL Query Analyzer or osql Utility
Use to Re-Create Database Objects or to Execute
Statements Repeatedly
Transactions

Processed Like a Batch

Rollback Logic Can Ensure Data Integrity
Changes to the Database Are Either Applied Together
or Rolled Back

BEGIN TRANSACTION
UPDATE savings SET balance = (amount - 100)
WHERE custid = 78910
… <Rollback transaction if error>
UPDATE checking SET balance = (amount + 100)
WHERE custid = 78910
… <Rollback transaction if error>
COMMIT TRANSACTION
XML Support

The FOR XML AUTO Option

 Each table represented as an XML element
 Element includes data and attributes
The FOR XML RAW Option
 Elements have generic identifier row as the element tag