Transcript Module 9: Using Advanced Techniques

Module 9: Using Advanced
Techniques
Module 9: Using Advanced Techniques
• Considerations for Querying Data
• Working with Data Types
• Cursors and Set-Based Queries
• Dynamic SQL
• Maintaining Query Files
Lesson 1: Considerations for Querying Data
• Execution Plans
• Data Type Conversions
• Implicit Conversions
• Explicit Conversions with CAST and CONVERT
• Data Type Precedence
Execution Plans
• Shows how the Database Engine navigates tables and indexes
• View Estimated Execution Plan before query execution
• View Actual Execution Plan after execution
Generate Execution Plans with:
• SQL Server Management Studio
• T-SQL SET options
• SQL Server Profiler
Demonstration: Using Execution Plans
In this demonstration, you will learn how to:
• View estimated and actual execution plans
Data Type Conversions
Data Type Conversion scenarios
• Data is moved to, compared, or combined with other data
• Data is moved from a result column, return code, or output
parameter into a program variable
Implicit Conversion
• Transparent to the user
Explicit Conversion
• Uses CAST or CONVERT
Implicit Conversions
DECLARE @firstname char(10)
SET @firstname = 'Kevin'
SELECT FirstName, LastName FROM
Person.Person WHERE
@firstname = FirstName
Explicit Conversions with CAST and CONVERT
Using CAST
USE AdventureWorks2008;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO
Using CONVERT
USE AdventureWorks2008;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO
Data Type Precedence
Without explicit conversion, this statement fails
DECLARE @label varchar(12),
@pageno int
SET @label='Page Number '
SET @pageno = 1
Print @label + @pageno
With explicit conversion, this statement succeeds
DECLARE @label varchar(12),
@pageno int
SET @label='Page Number '
SET @pageno = 1
Print @label + CONVERT(varchar, @pageno)
Demonstration: Understanding Data Type
Conversion
In this demonstration, you will learn how to:
• Understand explicit and implicit data type conversions
• Use CAST and CONVERT to explicitly convert data types
Lesson 2: Working with Data Types
• Recommendations for Querying Date/Time Data
• Recommendations for Inserting Date/Time Data
• Implementing the hierarchyid Data Type
• Working with Hierarchies
Recommendations for Querying Date/Time Data
• Date/Time values can be queried using numeric operators
such as =, >, and < as well as date/time functions.
• When querying date/time data, care must be taken in
understanding the data type.
DATETIME, DATETIME2, & DATETIMEOFFSET data types
• Query conditions must include both date and time portions.
DATE data type
• Query conditions must include just the date portion.
TIME data type
• Query conditions must include just the time portion.
Recommendations for Inserting Date/Time Data
•Use correct format and language settings
•Use language independent formats for portability
SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = '12/31/2008'
SELECT @datevar
SET DATEFORMAT ydm
GO
DECLARE @datevar datetime
SET @datevar = ‘2008/31/12'
SELECT @datevar
SET DATEFORMAT ymd
GO
DECLARE @datevar datetime
SET @datevar = ‘2008/12/31'
SELECT @datevar
Demonstration: Working with Date/Time Data
In this demonstration, you will learn how to:
• Use the correct methods for working with various
date/time data types
Implementing the hierarchyid Data Type
CREATE TABLE Organization
(
EmployeeID hierarchyid,
OrgLevel as EmployeeID.GetLevel(),
EmployeeName nvarchar(50) NOT NULL
) ;
GO
Working with Hierarchies
CREATE CLUSTERED INDEX
Org_Breadth_First
ON
Organization(OrgLevel,
EmployeeID);
GO
CREATE UNIQUE INDEX
Org_Depth_First
ON
Organization(EmployeeID);
GO
Demonstration: Using the hierachyid Data Type
In this demonstration you will learn how to:
• Work with the hierarchyid data type
Lesson 3: Cursors and Set-Based Queries
• Understanding Cursors
• Cursor Implementations
• Using Cursors
• Understanding Set-Based Logic
Understanding Cursors
Cursors extend processing of result sets
• Allow positioning at specific rows
• Retrieve one or more rows from the current position
• Support data modification
• Support different levels of visibility
• Provide T-SQL statements access to data
Cursor Implementations
Implementation
Transact-SQL
Features
• Based on DECLARE CURSOR
• Implemented on server
• Based on API functions in OLE DB and
API Server
ODBC
• Implemented on server
• Implemented by Native Client ODBC
Client
and ADO
• Caches all result set rows on the client
Using Cursors
Process of using a cursor
• Associate and define characteristics
• Populate the cursor
• Retrieve rows in the cursor
• Modify data if needed
• Close and deallocate the cursor
DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor
CLOSE vend_cursor
DEALLOCATE vend_cursor
Demonstration: Working with Cursors
In this demonstration, you will learn how to:
• Use cursors to work with result sets
Understanding Set-Based Logic
Set-based logic
• SQL Server iterates through data
• Deals with results as a set instead of row-by-row
SELECT ProductID,
Purchasing.Vendor.VendorID, Name
FROM Purchasing.ProductVendor JOIN
Purchasing.Vendor
ON (Purchasing.ProductVendor.VendorID
= Purchasing.Vendor.VendorID)
WHERE StandardPrice > $10
AND Name LIKE N'F%'
GO
Demonstration: Using Set-Based Queries
In this demonstration, you will learn how to:
• Use a set-based query to replace a cursor
Lesson 4: Dynamic SQL
• Introducing Dynamic SQL
• Using Dynamic SQL
• Considerations for Using Dynamic SQL
Introducing Dynamic SQL
Dynamic SQL
• Allows query to be built using variables
• Places query into variable
SET @SQLString = N'SELECT @SalesOrderOUT
= MAX(SalesOrderNumber)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID';
Using Dynamic SQL
Using sp_executesql
sp_executesql [ @stmt = ] stmt
[
{, [@params=] N'@parameter_name data_type [ OUT |
OUTPUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]
Using EXECUTE
[ { EXEC | EXECUTE } ]
{ [ @return_status = ]
{ module_name [ ;number ] | @module_name_var }
[ [ @parameter = ] { value | @variable [ OUTPUT ]
| [ DEFAULT ] } ] [ ,...n ] [ WITH RECOMPILE ] }
[;]
Considerations for Using Dynamic SQL
Security Considerations:

SQL Injection

Security is checked for every object
Performance Considerations:

Query plan not reused frequently

Use sp_executesql for best chance at plan reuse

Performance related to complexity of query
Demonstration: Using Dynamic SQL
In this demonstration, you will learn how to:
• Build and execute a query that uses dynamic SQL
Lesson 5: Maintaining Query Files
• What Are Versioning and Source Control?
• Features of Team Foundation Server 2008
What Are Versioning and Source Control?
Versioning
• Provides a record of changes made
• Enables retrieval of previous versions
Source Control
• Enables development teamwork
• Source files can be checked in/out and merged
Other Benefits
• Provides a central location for source file storage
• Simplifies source file backup procedures
Features of Team Foundation Server 2008

Enabled support for Reporting Services on any server
and any port

Support for SQL Server 2008

New role for many operations activities
Lab: Using Advanced Querying Techniques
• Using Execution Plans
• Converting Data Types
• Implementing a Hierarchy
• Using Cursors and Set-Based Queries
Logon information
Virtual machine
NY-SQL-01
User name
Administrator
Password
Pa$$w0rd
Estimated time: 60 minutes
Lab Scenario
You are a Database Administrator at Adventure Works. As part
of an effort to analyze database performance, the Senior
Database Administrator has asked you to become familiar
with query execution plans. In order to do this, you need to
know how to view estimated and actual execution plans, as
well as save execution plans and view them in XML format.
You also need to familiarize yourself with implicit and explicit
data type conversions.
Also, you've been asked to create a hierarchy in the database
for the Sales department. To do this, you will implement a
table using the hierarchyid data type.
You also need to determine whether database updates are
more efficient using cursors or set-based queries. To do this,
you will use a cursor to update ListPrice data and a set-based
query to update StandardCost data in the Production.Product
table to see how the two approaches compare.
Lab Review
• What is required for a conversion to the XML data type to
succeed?
• How do you create the root node of a hierarchy?
• When using a cursor, how do you retrieve the next row in
the result set?
Module Review and Takeaways
• Review Questions
• Common Issues and Troubleshooting Tips
• Best Practices
Course Evaluation