Module 9: Using Advanced Techniques

1. Module 9: Using Advanced Techniques

2. Module 9: Using Advanced Techniques • Considerations for Querying Data • Working with Data Types • Cursors and Set-Based Queries • Dynamic SQL • Maintaining Query Files

3. Lesson 1: Considerations for Querying Data • Execution Plans • Data Type Conversions • Implicit Conversions • Explicit Conversions with CAST and CONVERT • Data Type Precedence

4. 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

5. Demonstration: Using Execution Plans In this demonstration, you will learn how to: • View estimated and actual execution plans

6. 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

7. Implicit Conversions DECLARE @firstname char(10) SET @firstname = 'Kevin' SELECT FirstName, LastName FROM Person.Person WHERE @firstname = FirstName

8. 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

9. 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)

10. 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

11. 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

12. 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.

13. Recommendations for Inserting Date/Time Data • Use correct format and language settings • Use language independent formats for portability SET DATEFORMAT mdy GO DECLARE @datevardatetime SET @datevar = '12/31/2008' SELECT @datevar SET DATEFORMAT ydm GO DECLARE @datevardatetime SET @datevar = ‘2008/31/12' SELECT @datevar SET DATEFORMAT ymd GO DECLARE @datevardatetime SET @datevar = ‘2008/12/31' SELECT @datevar

14. 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

15. Implementing the hierarchyid Data Type CREATE TABLE Organization ( EmployeeID hierarchyid, OrgLevel as EmployeeID.GetLevel(), EmployeeName nvarchar(50) NOT NULL ) ; GO

16. Working with Hierarchies CREATE CLUSTERED INDEX Org_Breadth_First ON Organization(OrgLevel, EmployeeID); GO CREATE UNIQUE INDEX Org_Depth_First ON Organization(EmployeeID); GO

17. Demonstration: Using the hierachyid Data Type In this demonstration you will learn how to: • Work with the hierarchyid data type

18. Lesson 3: Cursors and Set-Based Queries • Understanding Cursors • Cursor Implementations • Using Cursors • Understanding Set-Based Logic

19. 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

20. Cursor Implementations

21. 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

22. Demonstration: Working with Cursors In this demonstration, you will learn how to: • Use cursors to work with result sets

23. 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

24. Demonstration: Using Set-Based Queries In this demonstration, you will learn how to: • Use a set-based query to replace a cursor

25. Lesson 4: Dynamic SQL • Introducing Dynamic SQL • Using Dynamic SQL • Considerations for Using Dynamic SQL

26. 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';

27. 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 ] } [;]

28. 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 ü

29. Demonstration: Using Dynamic SQL In this demonstration, you will learn how to: • Build and execute a query that uses dynamic SQL

30. Lesson 5: Maintaining Query Files • What Are Versioning and Source Control? • Features of Team Foundation Server 2008

31. 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

32. 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 ü

33. Lab: Using Advanced Querying Techniques • Using Execution Plans • Converting Data Types • Implementing a Hierarchy • Using Cursors and Set-Based Queries Logon information Estimated time: 60 minutes

34. 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.

35. 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?

36. Module Review and Takeaways • Review Questions • Common Issues and Troubleshooting Tips • Best Practices

37. Course Evaluation