Transcript Course Slides Link - Talk-IT

SQL SERVER 2012
DEVELOPMENT
5 Day, Hands-On Course
Author
© David Ringsell
1
2. LANGUAGE FEATURES
SQL Server Programming Tools
Elements of Transact-SQL
SQL Server Object Names
Additional Language Elements
Local Variables
Operators
Functions
Ways to Execute Transact-SQL Statements
New Transact-SQL (T-SQL) Features
2
SQL Server Programming
Tools
SQL Server Management Studio
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
3
The Transact-SQL
Programming Language
SQL Server Implementation of Entry-Level
ANSI ISO Standard
Can Be Run on Any Entry-Level Compliant
Product
Contains Additional Unique Functionality
4
Elements of Transact-SQL
Data Control Language Statements
Data Definition Language Statements
Data Manipulation Language Statements
SQL Server Object Names
Naming Guidelines
5
Data Control Language
Statements
Set or Change Permissions
GRANT
DENY
REVOKE
By Default, Only sysadmin, dbcreator,
db_owner, and db_securityadmin Roles
Can Execute
6
Data Definition Language
Statements
Define the Database Objects
CREATE object_type object_name
ALTER object_type object_name
DROP object_type object_name
7
Data Manipulation
Language Statements
Use When Working with Data in the
Database
SELECT
INSERT
UPDATE
DELETE
8
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 (" ")
9
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
Stored procedures
Keep Object Names and User Names Unique
10
Additional Language
Elements
Local Variables
Operators
Functions
Function Examples
Control of Flow Language Elements
Comments
11
Local Variables
User-defined with DECLARE Statement
Assigned Values with SET or Select
Statement
DECLARE @vLastName
char(20),
@vFirstName varchar(11)
SET @vLastName = 'Dodsworth'
SELECT @vFirstName = FirstName
FROM Northwind..Employees
WHERE LastName = @vLastName
PRINT @vFirstName + ' ' + @vLastName
GO
12
Operators
Types of Operators
Arithmetic
Comparison
String concatenation
Logical
Operator Precedence Levels
13
Functions

Aggregate Functions
SELECT AVG (UnitPrice) FROM Products

Scalar Functions
SELECT DB_NAME() AS 'database'

Rowset Functions
SELECT *
FROM OPENQUERY
(OracleSvr, 'SELECT ENAME, EMPNO FROM SCOTT.EMP')
14
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:
European:
Japanese:
2000.03.22
1
22 Mar
1 2000 14:20:00:010
2000/03/22
15
Control of Flow Language
Elements
Statement Level
BEGIN…END blocks
IF…ELSE blocks
WHILE constructs
Row Level
IF USER_NAME() <> 'dbo'
BEGIN
RAISERROR('Must be sysadmin
to Perform Operation',
10, 1)
RETURN
END
ELSE
DBCC CHECKDB(Northwind)
CASE expression
16
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
17
Ways to Execute TransactSQL Statements
Dynamically Constructing Statements
Using Batches
Using Scripts
Using Transactions
Using XML
18
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
DECLARE @dbname varchar(30), @tblname varchar(30)
SET During
@dbname =Execution
'Northwind'
SET @tblname = 'Products'
EXECUTE
('USE ' + @dbname + ' SELECT * FROM '+ @tblname)
19
Using 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
20
Using Scripts
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
21
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
… <Rollback transaction if error>
UPDATE checking SET amount = (amount + 100)
WHERE custid = 78910
… <Rollback transaction if error>
COMMIT TRANSACTION
22
New Transact-SQL (T-SQL)
 Common Table Expressions (CTE)
 Useful for recursive queries
 Ranking Functions
 RANK, DENSE_RANK, ROW_NUMBER, PARTITION BY
 TOP(N)
 N can be a parameter or expression
 APPLY
 Run a query for each row and merge results
 PIVOT
 Rotate data into row headings
 FOR XML PATH
 Easier control over XML result
23
Try/Catch Error Handling
BEGIN TRY
-- Statement with error
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS Number,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS State,
ERROR_PROCEDURE() AS procedureName,
ERROR_LINE() AS Line,
ERROR_MESSAGE() AS messageText;
END CATCH
24
EXECUTE AS
Used to specify security context under which
stored procedure executes
WITH EXECUTE AS options:
SELF  the user who created the stored procedure
OWNER  the owner of the stored procedure
'user_name'  a specified user name
25
Snapshot Isolation Level
New isolation level for transactions
No locks placed when reading data
No dirty reads
Always see the last committed values
Uses tempdb to store row versions
Can significantly improve concurrency
26
Running CLR Code in SQL
Server 2012
 Overcomes limitations of Transact-SQL by hosting the
Common Language Runtime (CLR)
 Good for processor-intensive code
Transact-SQL best for DML
 Use a .NET Framework language to write:
Stored procedures
User-defined functions
Triggers
User-defined types
Aggregates
27
Recommended Practices
Keep Business Logic on the Server As Stored Procedures
Use ANSI SQL Syntax
Choose an Appropriate Naming Convention
Save Statements As Scripts and Comment Them Thoroughly
Format Transact-SQL Statements to Be Legible to Others
28
2. REVIEW
SQL Server Programming Tools
Elements of Transact-SQL
SQL Server Object Names
Additional Language Elements
Local Variables
Operators
Functions
Ways to Execute Transact-SQL Statements
New Transact-SQL (T-SQL) Features
29
CHAPTER CONTENTS
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SQL SERVER OVERVIEW
LANGUAGE FEATURES
DESIGN A DATABASE
IMPLEMENT TABLES
ACCESS AND MODIFY DATA
IMPLEMENT VIEWS
IMPLEMENT FUNCTIONS
IMPLEMENT TRIGGERS
IMPLEMENT STORED PROCEDURES
INDEXING TABLES
ACCESSING LINKED SERVERS
TRANSACTIONS AND LOCKS
30