2778A_09 Using Advanced Techniques

Lesson 1: Considerations for Querying Data
Execution Plans Data Type Conversions Implicit Conversions Explicit Conversions with CAST and CONVERT Data Type Precedence
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
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
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
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
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
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
Implementing the hierarchyid Data Type
CREATE TABLE Organization ( EmployeeID hierarchyid, OrgLevel as EmployeeID.GetLevel(), EmployeeName nvarchar(50) NOT NULL ) ; GO
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: 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
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
Understanding Cursors Cursor Implementations Using Cursors Understanding Set-Based rsors
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
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
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
Working with Hierarchies
CREATE CLUSTERED INDEX Org_Breadth_First ON Organization(OrgLevel, EmployeeID); GO
CREATE UNIQUE INDEX Org_Depth_First ON Organization(EmployeeID); GO
Recommendations for Querying Date/Time Data
Date/Time values can be queried using numeric operators
such as =, >, and < as well as date/time functions.
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
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
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