SQL Server 2008 for Developers

1. SQL Server 2008 for Developers UTS Short Course

2. Course Website • Course Timetable & Materials • http://www.ssw.com.au/ssw/Events/2010UTSSQL/ • Resources • http://sharepoint.ssw.com.au/Training/UTSSQL/

3. Course Overview

4. What we did last weekHigh availability • ? • What can go wrong? What can we do? • Implementing Database Snapshots • Configuring a Database Mirror • Partitioned Tables • SQL Agent Proxies • Performing Online Index Operations • Mirrored Backups

5. SQL CLR Integration

6. Agenda - CLR Integration • What is .NET? • What is CLR Integration? • Requirements on SQL box • Samples • Internals • CLR Integration: Pros • CLR Integration: Cons • Real world - When to use CLR Integration

7. What is .NET? • An application development platform from Microsoft • Tools, Languages, Runtime (Virtual machine), IDE, … • Rapidly develop secure and robust software • Web and Windows • Full support for object-oriented programming

8. .NET Overview • IL = Intermediate Language • CLR = Runtime

9. CLR • Common • Language • Runtime • = Virtual machine

10. .NET Framework • Evolution • The whole .NET FX • http://shrinkster.com/1515(PDF Poster)

11. What is CLR Integration? • Lets you write your database queries using .NET • Create and debug using VS 2008 IDE • Brand new in SQL 2005 (Standard and Express) • Support for large UDT (User defined types) in SQL 2008 (up to 2GB) • Support for multiple inputs on UDA (User defined aggregators) • e.g. string concatenator that takes in a column and separator char • Any .NET language (C#, VB, C++)

13. SQL CLR Project

14. CLR Integration • You can do the same thing as SQL Server using .NET code • Stored Procedures • Triggers • User-defined functions • User-defined types • Aggregate functions

15. Server - Enabling CLR Integration • Enabled on an instance (not per database) • SQL Script • Execute sp_configure ‘clr enabled’, ‘1’ • reconfigure

16. Stored Procedures

17. Sample public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void HelloWorld() { // Put your code here using (SqlConnectioncn = new SqlConnection("Context Connection=true")) { cn.Open(); SqlCommandcmd = new SqlCommand("SELECT * FROM DEMO", cn); SqlContext.Pipe.Send(cmd.ExecuteReader()); } }

18. What you do • Enable CLR • Create a new database project in Visual Studio • Create a new stored procedure in Visual Studio • Connect to current context using “Context Connection=true” • Add a simple SELECT statement • Deploy and run it

19. Internals • Assembly collated as set of files • Stored within SQL Server system tables • Assembly, references, program database (pdb), source files • Deployed to SQL Server • Manually • Catalogued with CREATE ASSEMBLY • Dropped with DROP ASSEMBLY • Automatically • Deployed from VS 2008

20. Security Levels • Safe (default) • Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop. • External_Access • Access is allowed to external systems, such as the EventLog, File System, and network. Still no access to unsafe code or interop code. • Unsafe • Access is not limited whatsoever. User-defined types

21. Custom CLR Functions

22. Sample public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static boolIsValidPostCode(string postcode) { return System.Text.RegularExpressions.Regex.IsMatch( postcode, ^(((2|8|9)\d{2})|((02|08|09)\d{2})|([1-9]\d{3}))$"); } };

23. What you do • Create IsValidPostCode in C# (.NET) • Deploy it to SQL Server • Run it

24. Stored procedure vs. Function • What is the difference? • Function • Base functionality • Independent of Database itself • Stored procedure • Many operations at once • Normally database specific

25. Triggers

26. Sample public partial class Triggers { [Microsoft.SqlServer.Server.SqlTrigger(Name = "EmailTrigger", Target = "Customers", Event = "FOR UPDATE")] public static void SalaryFraudTrigger() { SqlTriggerContext context = SqlContext.TriggerContext; using (SqlConnectioncnn = new SqlConnection("context connection=true")) { cnn.Open(); SqlCommand command = cnn.CreateCommand(); command.CommandText = "SELECT * FROM inserted"; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { for (intcolumnNumber = 0; columnNumber<context.ColumnCount; columnNumber++) { SqlContext.Pipe.Send(string.Format("Col: {0} = {1}", columnNumber, reader[columnNumber].ToString())); } }; reader.Close(); } } }

27. What you do • Create EmailTrigger in C# (.NET) • Deploy it to SQL Server • Test it

28. CLR Integration: Pros (Continued) • Take advantage of the powerful .NET Framework • .NET is a full-featured programming language • Supports things like “for each” loops, arrays, collections • Object Oriented programming model to organise your queries • Obtaining data from external resources • The File System • The Event Log • A Web Service • The Registry

29. CLR Integration: Pros • For complex calculations • Parsing strings (like the regular expression code) • User-defined types • Date, time, currency, and extended numeric types • Geospatial applications • Encoded or encrypted data (see books online) • User-defined aggregates • Powerful Intellisense and debugging • Generally faster • E.g. CLR aggregate 100x faster than cursor

30. CLR Integration: Cons (Continued) • NON MAINSTREAM • Lots of programming for simple operations • Some overhead in communicating with assemblies • Remember – T-SQL is designed and optimised for data, use it! • Not useful if your guys do not know any .NET • Potentially costly to rewrite logic • Companies (including us) have invested a lot in T-SQL

31. CLR Integration: Cons • There are some restrictions to observe when calling between T-SQL and SQL-CLR. • You must only use T-SQL supported data types (No streams) • You can't use inheritance or polymorphism • .NET cannot easily represent either VARCHAR or TIMESTAMP • .NET strings are Unicode, the equivalent of NVARCHAR • The CLR decimal type is not the same as SQL_DECIMAL

32. When to use CLR Integration • Do I need to manipulate data before it is displayed? • .NET code and SQLCLR • Do I need to do set-based operations such as pivoting? • T-SQL • Do I need to do extensive computation or custom algorithms? • .NET code and SQLCLR • Are my developers SQL gurus but .NET newbies? • T-SQL • Do I have loads of stored procs that are becoming hard to manage? • .NET code and SQLCLR

33. CLR Integration The Bottom Line Use T-SQL for all data operations Use CLR assemblies for any complex calculations and transformations

34. Quick tips • SQL Management Studio Shortcuts • Ctrl + L – Display query execution plan • F5 – Run/Show result grid • Rules for SQL Server http://www.ssw.com.au/SSW/Standards/default.aspx • SQL Server Cheat sheet http://www.pinaldave.com/sql-download/SQLServerCheatSheet.pdf http://www.addedbytes.com/cheat-sheets/sql-server-cheat-sheet/

35. Session 4 Lab • CLR Integration Download from Course Materials Site (to copy/paste scripts) or type manually: http://tinyurl.com/utssql2009

36. 3things… • EricPhan@ssw.com.au • http://ericphan.info • twitter.com/ericphan

37. 3things… • mehmet@ssw.com.au • http://blog.ozdemir.id.au • twitter.com/mozdemir_au

38. Thank You! Gateway Court Suite 10 81 - 91 Military Road Neutral Bay, Sydney NSW 2089 AUSTRALIA ABN: 21 069 371 900 Phone: + 61 2 9953 3000 Fax: + 61 2 9953 3105 info@ssw.com.auwww.ssw.com.au