Best Practices for SQL Server 2005

1. Best Practices for SQL Server 2005 Nauzad Kapadia [MVP] Quartz Systems nauzadk@quartzsystems.com

2. Session Objectives & Key Takeaways • Session Objective(s): • Developers: when and how to use CLR and XML for developing database applications • Key takeaways: • Use CLR and XML judiciously to complement T-SQL • CLR is not a black box • XML is not a panacea

3. CLR Best Practices

4. Design Guidelines • T-SQL is best suited for data access • Familiar, simple programming model • Static compilation model • Optimized for data access • Managed code is all about procedural programming and computation • IL compiled to x86 code at runtime, easily outperforms interpreted T-SQL • Compute-intensive business logic encapsulated as functions

5. Usage GuidanceT-SQL vs. CLR • CLR is not replacement for set-based query processing • Use SQL query language whenever possible • Take advantage of SQL Server 2005 query language extensions • Use CLR functions (scalar, table-valued and aggregate) to accomplish more in a single query • SQL queries which call into functions that package procedural logic • Faster than T-SQL user-defined functions

6. if @bin is null return null declare @len int, @b tinyint, @lowbyte tinyint, @hibyte tinyint, @index int, @str nchar(2), @result nvarchar(4000) set @len = datalength(@bin) set @index = 1 set @result = '0x' while @index <= @len begin set @b = substring(@bin, @index, 1) set @index = @index + 1 set @lowbyte = @b & 0xF set @hibyte = @b & 0xF0 if @hibyte > 0 set @hibyte = @hibyte / 0xF set @result = @result + ((case when @hibyte < 10 then convert(varchar(1), @hibyte) when @hibyte = 10 then 'A' when @hibyte = 11 then 'B' when @hibyte = 12 then 'C' when @hibyte = 13 then 'D' when @hibyte = 14 then 'E' when @hibyte = 15 then 'F' else 'Z' end) + (case when @lowbyte < 10 then convert(varchar(1), @lowbyte) when @lowbyte = 10 then 'A' when @lowbyte = 11 then 'B' when @lowbyte = 12 then 'C' when @lowbyte = 13 then 'D' when @lowbyte = 14 then 'E' when @lowbyte = 15 then 'F' else 'Z' end)) end TSQL if (value == null) return null; StringBuilder sb = new StringBuilder(); foreach (byte b in value) sb.Append(b.ToString("X2")); return sb.ToString(); C# TSQL Versus C# For LogicConverting Binary To Hex

7. SELECT * FROM T_CUSTOMERS WHERE C_CITY = @name TSQL private static readonly SqlDefinition x_definition; string query = “SELECT * FROM T_CUSTOMERS WHERE C_CITY = @name”; SqlCommand cmd = new SqlCommand(); cmd.CommandText = query; cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 128)); x_definition = new SqlDefinition(cmd); SqlExecutionContext sec = SqlContext.GetConnection().CreateExecutionContext(x_definition); sec.SetSqlString(0, "ExecutePredefinedQuery"); SqlContext.GetPipe().ExecuteReader(sec); C# TSQL Versus C# For Data Access

8. Usage Guidance (Cont.) • Use CLR for: • Procedural and computation-intensive logic • Logic that will benefit from use of data structures not supported in T-SQL (e.g. arrays) • Take advantage of .NET Framework library • Use in-proc ADO.NET data access judiciously • SqlReader faster than T-SQL cursors for forward-only, read-only row navigation • T-SQL better suited for procedures with mostly data access statements

9. Performance Comparison for Common Scenarios • Sending Results to the Client • Submitting SQL statements to the server • Forward-only, Read-only row navigation • Row-Navigation with updates

10. Performance Guidelines • Use set-based processing with T-SQL SELECT, INSERT, UPDATE and DELETE statements whenever possible. • If the procedure is simply a wrapper for accessing the base table, it should be written in T-SQL. • If the procedure involves primarily forward-only, read-only row navigation from a result set and some processing involving each row, writing in CLR is most likely more efficient. • If the procedure involves both significant data access and computation and logic, consider separating the procedural code as a CLR that calls into a T-SQL procedure that does most of the data access.

11. Performance Guidelines.. Cont’d • Transitioning to/from runtime is fast • Use managed table valued functions • If rows can be consumed as soon as the are produced, use managed TVFs • Use user defined aggregates • Orders magnitude faster than server or client cursor solutions • Data Access inside SQL process • Use SqlDefinition (prepared commands) rather than SqlCommand

12. Usage guidance: CLR as replacement for XPs • Benefits: • Easier in-proc data access: No need to use loop-back connections and binding sessions • Better administrative control: 3 permission buckets. • Reliable and scalable • Faster • Caveat: Reduced performance compared to XPs for scenarios involving frequent manged-native transitions

13. Usage Guidance Mid-tier vs. server • CLR support does not mean move all business logic to server • Traditional tradeoffs still apply • reduced network data movement and round trips vs. added load on database server • CLR makes language choice not be a factor in deciding code location • Candidate for moving to server: • Frequent round-trips • Process large amount of data while needing a small portion of it for application use

14. Usage Guidance Procedures vs. TVFs • Composability requirements • Source of the data being returned • Need for side-effecting operations • Strong typing and number of result sets

15. SummaryProcedures vs. TVFs

16. Usage guidanceUDTs and UDAggs • Use UDTs for small types with little or no structure E.g. UTF8String, Zipcode, DateWithTimeZone, DateWithCalendar • Not intended for object persistence, complex structured types • Use user-defined aggregates to replace cursor navigation with single query for custom aggregations

17. Typical usage scenarios • Data validation: • Use scalar functions and CHECK constraints instead of client-side validation or multi-statement logic in triggers E.g. String validation using Regular Expressions • Benefits • Centralized validation • Easy to write, efficient

18. Typical usage scenarios (contd.) • Converting multi-valued arguments in scalar form into a set of rows • Use table-valued function Benefits • Overcome lack of support for table-valued arguments • Streaming: faster than T-SQL TVFs that are spooled into temp-tables • Rows can be produced without materializing entire result set

19. CREATE PROC InsertOrder(@Orderid int, @CustomerID, @OrderDate datetime, @LineItems nvarchar(4000)) AS BEGIN … INSERT LineItems SELECT @OrderID, li.* FROM GetLineItems(@LineItems) li … END Cracking scalar into rows - Example

20. Typical usage scenarios contd. • Read Files, Web Service, external data source etc. • TVFs Not suited for data access SELECT * FROM GetLinesFromFile(‘c:\My Documents\Sales.txt’)

21. XML Best Practices

22. Outline of XML Best Practices • XML support in SS2K5 engine • Scenarios for XML usage • Property management • Data exchange • Content management • Demos to illustrate the ideas • Summary

23. XML data in SS2K5  Why? • XML data is becoming more pervasive • XML not restricted to client-side only • Enterprises are using XML representation for modeling data with flexible or rich structure • Users were storing XML data in files and text columns • Tags used for rich search and query processing • Adv of DB support for XML data model … • XML data/XML schema management, querying • Hierarchy and document order are preserved • XML schemas for applications are often complex • Avoids complex mapping and high re-assembly cost

24. What did we do? • Unified relational and XML store • Utilizes relational infrastructure • Storage engine and query processor • Administrative functionality for XML data • XML data interoperates with relational data • Introduce XML as data modeling needs arise • No disruption of existing system

25. Server-side XML Support • Client access using ADO.NET, OLE DB, SOAP

26. Best PracticesData Modeling Issues • When to choose XML data type • Stick with relational model for structured data with known schema • Use [n]varchar(max) if query/update not required • Data Modeling using XML Data Type • XML data granularity (impacts concurrency) • Property promotion (computed column, prop table) • Typed XML yields optimizations • XML blob and index are appropriately typed • Type inference used in query optimizations

27. Using specific markup <item type="book"><title>Writing Secure Code</title></item> <item type="DVD"><title>The Godfather</title></item> <book><title>Writing Secure Code</title></book> <DVD><title>The Godfather</title></DVD> • Query Plans are more efficient • Number of Rows in the xml index cut down from 4 to 3 <book title="Writing Secure Code"/> <DVD title="The Godfather"/> • Query Plans are most efficient • Number of Rows in the xml index cut down from 4 to 2

28. Typed v/s UnTyped XML • Element and attribute values in untyped XML are stored internally as Unicode strings. Operations on them require data conversion to the appropriate type. • (/book/price)[1] > 19.99

29. Property PromotionDatabase Design • Store object properties as XML data in XML data type columns • Properties of different object types in same XML column • Search across all object types • Promote frequently-queried properties • Single valued  computed columns • Multi-valued  property table • Index promoted properties • Index the XML columns

30. © 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.