Chapter 2: Using TransactSQL on a SQL Server Database

1
Chapter 2 Using Transact-SQL on a SQL Server
Database
2
Overview

• SQL Server Programming Tools
• The Transact-SQL Programming Language
• Elements of Transact-SQL
• Additional Language Elements
• Ways to Execute Transact-SQL Statements

3
SQL Server Programming Tools
P46-60

• SQL Query Analyzer
• Color-codes syntax elements automatically
• Multiple query windows
• Customizable views of result sets
• Graphical execution plans
• Execute portions of scripts
• osql Utility
• Command-line utility

4
SQL Query Analyzer Windows
P46-57

• Query Window Results Pane
• Grids tab
• Results tab
• Execution Plan tab
• Trace tab
• Statistics tab
• Message tab
• Estimated Execution Plan tab
• Object Browser Window

5
Command Line Prompts
P59-61

• isqlw Command-Prompt utility
• Enter SQL statements, procedures, scripts
• isql Command-Prompt utility
• Enter SQL statements, procedures, scripts
• Uses DB-Library to communicate with the server
• osql Command-Prompt utility
• Enter SQL statements, procedures, scripts
• Uses ODBC to communicate with the server

6
The Transact-SQL Programming Language
P66

• SQL Server Implementation of Entry-Level ANSI ISO
  Standard
• Can Be Run on Any Entry-Level Compliant Product
• Contains Additional Unique Functionality

7
Elements of Transact-SQL
P66

• Data Definition Language Statements (DDL)
• Data Control Language Statements (DCL)
• Data Manipulation Language Statements (DML)
• SQL Server Object Names
• Naming Guidelines

8
Data Definition Language Statements (DDL)
P67

• Define the Database Objects
• CREATE object_type object_name
• ALTER object_type object_name
• DROP object_type object_name

9
Data Control Language Statements (DCL)
P68

• Set or Change Permissions
• GRANT
• DENY
• REVOKE
• By Default, Only sysadmin, dbcreator, db_owner,
  and db_securityadmin Roles Can Execute

10
Data Manipulation Language Statements (DML)
P69

• Use When Working with Data in the Database
• SELECT
• INSERT
• UPDATE
• DELETE

11
SQL Server Object Names
P77-78

• Standard Identifiers (Regular)
• 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 (" ")

12
Naming Guidelines

• Use Meaningful Names Where Possible
• Keep Names Short
• Use a Clear and Simple Naming Convention
• Chose an Identifier That Distinguishes Types of
  Objects
• Views (v_Name)
• Stored procedures (p_Name)
• Keep Object Names and User Names Unique

13
Additional Language Elements
P78

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

14
Local Variables
P78

• User-defined with DECLARE Statement
• Assigned Values with SET or Select Statement

DECLARE _at_vLastName char(20), _at_vFirstName varchar(
11) SET _at_vLastName 'Dodsworth' SELECT
_at_vFirstName FirstName FROM Northwind..Employee
s WHERE LastName _at_vLastName PRINT _at_vFirstName
' ' _at_vLastName GO
15
Functions (Built-in)
P79-80

• Aggregate Functions
• Scalar Functions
• Rowset Functions

SELECT AVG (UnitPrice) FROM Products
SELECT DB_NAME() AS 'database'
SELECT FROM OPENQUERY (OracleSvr, 'SELECT
ENAME, EMPNO FROM SCOTT.EMP')
16
Function Examples
P81
SELECT 'ANSI' AS Region,
CONVERT(varchar(30), GETDATE(), 102) AS
StyleUNIONSELECT 'European',
CONVERT(varchar(30), GETDATE(), 113) UNIONSELECT
'Japanese', CONVERT(varchar(30), GETDATE(), 111)
Result
17
Operators
P82-83

• Types of Operators
• Arithmetic
• Comparison
• String concatenation
• Logical
• Operator Precedence Levels

18
Control of Flow Language Elements
P83

• Statement Level
• BEGINEND blocks
• IFELSE blocks
• WHILE constructs
• Row Level
• CASE expression

IF USER_NAME() ltgt 'dbo' BEGIN
RAISERROR('Must be sysadmin to Perform
Operation', 10, 1) RETURN END ELSE
DBCC CHECKDB(Northwind)
19
Comments
P84-85

• In-Line Comments
• Block Comments

SELECT ProductName, (UnitsInStock UnitsOnOrder)
AS Max -- Calculates inventory , SupplierID FROM
Products
/ 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
20
Ways to Execute Transact-SQL Statements
P89

• Dynamically Constructing Statements
• Using Batches
• Using Scripts
• Using Transactions
• Using XML

21
Dynamically Constructing Statements
P89

• 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 _at_dbname varchar(30), _at_tblname
varchar(30)SET _at_dbname 'Northwind'SET
_at_tblname 'Products' EXECUTE('USE ' _at_dbname
' SELECT FROM ' _at_tblname)
22
Using Batches
P91

• 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

23
Using Scripts
P94

• Contain Saved Statements
• Can Be Written in Any Text Editor
• Save by using .sql file name extension
• Execute in SQL Query Analyzer or osql Utility
• Use to Recreate Database Objects or to Execute
  Statements Repeatedly

24
Using Transactions

• Processed Like a Batch
• Data Integrity Is Guaranteed
• Changes to the Database Are Either Applied
  Together or Rolled Back

BEGIN TRANSACTION UPDATE savings SET amount
(amount - 100) WHERE custid 78910
ltRollback transaction if errorgt UPDATE checking
SET amount (amount 100) WHERE custid
78910 ltRollback transaction if errorgt COMMIT
TRANSACTION
25
Recommended Practices
26
Review

• Designing Enterprise Application Architecture
• SQL Server Programming Tools
• The Transact-SQL Programming Language
• Elements of Transact-SQL
• Additional Language Elements
• Ways to Execute Transact-SQL Statements

27
Lab Programming SQL Server
28
Lab Programming SQL Server

• Exercise 1
• Pages 61-65 Navigating SQL Query Analyzer
  Running a Query
• Exercise 2
• Creating Executing DDL, DCL, and DML
  Statements Pages 70-75
• Exercise 3
• Using Transact-SQL Syntax Elements to Create a
  Script Pages 85-88