SQL Server 2012 for Developers

1. SQL Server 2012 for Developers UTS Short Course

2. Mehmet Ozdemir – SA @ SSW w: blog.ozdemir.id.au | e: mehmet@ssw.com.au | t: @mozdemir_au • SQL Server, BI, Infrastructure • Specializes in • Application architecture and design • SQL Performance Tuning and Optimization • HyperV, SCVMM • Technology aficionado • Virtualization • Reporting/BI • Cubes

3. TSQL Part 2 and SQL CLR Integration

4. Course Website • http://sharepoint.ssw.com.au/Training/UTSSQL/Documents/Latest/02_TSQL_Enhancements.pptx • http://sharepoint.ssw.com.au/Training/UTSSQL/Documents/Latest/02_TSQL_Enhancements_Lab.doc • Course Materials

5. Last Week • SQL Management Studio • SQL Configuration Manager • Consoles • SQLCMD • PowerShell • SQL Profiler • SQL Database Tuning Advisor • T-SQL Part 1

6. Sessions

7. Last Week - Additional • How to setup maintenance plans over night • Database encryption • Data • Source code (Stored procs) • Best practices • Typical maintenance plans • Policies

8. Modify maintenance plan • 2nd page in wizard (new plan)

9. Database encryption Encrypting data - Transparent Data Encryption (TDE) http://msdn.microsoft.com/en-us/library/bb934049.aspx http://www.acorns.com.au/blog/?p=147 Encrypting Connections to SQL Server http://msdn.microsoft.com/en-us/library/ms189067.aspx Encrypting source code http://www.codeproject.com/KB/database/ProtectSQLCodeObject.aspx

10. Best practices - Security Security Best Practices http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc Security Best Practices Checklist http://technet.microsoft.com/en-us/library/cc966456.aspx

11. Homework? For me! Demo Central Management Servers.

12. Homework? Create a schema called Salary Create a table called Employees in Schema Create a user called Manager Give only manager permission to update/insert/delete in schema Create a user called Peter Give Peter only read to schema (=salary) Create a user Alice Deny everything for Alice in Salary

13. TSQL Part 2 Agenda • ColumnStore Indexes • ORDER BY OFFSET FETCH • New SQL2012 Analytical Functions • TRY CATCH THROW • TRY_CONVERT • Sequence Generator • CONCAT, EOMONTH, IIF

14. ColumnStore Indexes • New in SQL Server 2012 (Enterprise Edition feature) • Huge speed improvements for data warehouse workloads • Data for index is stored by column, whereas in a traditional index it is stored by row • Only one ColumnStore index per table • Once created READ-ONLY

15. ColumnStore Restrictions (no free lunch) • Only in Enterprise Edition • Read only index • Index cannot be altered, must be dropped and recreated • Can’t participate in replication • Only works with traditional SQL Datatypes, ie not CLR types, MAX datatypesetc • Doesn’t include sparse columns • Cannot act as Primary Key (PK) or Foreign Key (FK)

17. ORDER BY OFFSET (FETCH) • Typical problem web page search returns 500 results • You want page this to 10 items per page • How would you do write a query to do this?

18. ORDER BY OFFSET (FETCH) • Before offset and fetch we would use a Common Table Expression (CTE)

19. ORDER BY OFFSET (FETCH) • New way, much cleaner and nicer

20. New Analytical Functions • CUME_DIST computes the relative position of a specified value in a group of values • FIRST_VALUE, LAST_VALUE Returns the first/last value in an ordered set of values • LAG, LEADAccesses data from a previous/next row in the same result set without the use of a self-join • PERCENT_RANK Relative rank of row within ordered rows • PERCENTILE_CONT, PERCENTILE_DISC– can be used to determine median, but with additional flexibility

21. LEAD and LAG

22. The rest…

23. TRY CATCH THROW • Nice little enhancement to TRY CATCH which has been in SQL Server since 2005 • Can now THROW the error instead of using RAISERORR

24. TRY_CONVERT • TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. • If convert fails *GRACEFULLY returns a NULL (I love this!)

25. Sequence Generator • Table-independent identity values • Generate predictable number of values • No need to use unique identifier if we only need uniqueness in the database

26. Alternating ID’s

27. CONCAT, EOMONTH, IIF, CHOOSE • CONCAT, like using + operator but different handling of NULLs • EOMONTH, easy way of getting the End Of Month • IIF, CHOOSE

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

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

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

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

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

33. What is CLR Integration? • Lets you write your database queries using .NET • Create and debug using Visual Studio IDE • New since 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++)

35. SQL CLR Project

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

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

38. Stored Procedures

39. 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()); } }

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

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

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

43. Custom CLR Functions

44. 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}))$"); } };

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

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

47. Triggers

48. 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(); } } }

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

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