CNUG Day of .NET: October 30th

1. CNUG Day of .NET: October 30th SQL Server 2005: A Developers Introduction Shawn Wildermuth Magenic Technologies, Inc.

2. Agenda • Who Am I? • Database Engine Enhancements • CLR Integration • InProc Managed Provider • CLR Type System • XML Changes • XML Type System • Improved Tools • Service Broker • Changes to Managed Provider (Out of Proc)

3. Who Am I? • Shawn Wildermuth (shawnw@magenic.com) • Senior Consultant with Magenic Technologies • http://adoguy.com • C# MVP • INETA Speaker • Book Author • “Pragmatic ADO.NET”; • Co-author of “Programming SQL Server 2005” • Editor of http://ONDotnet.com • This Presentation can be found at: • http://adoguy.com/presentations

4. Company Background 175+ consultants 10 years in business Regional offices Boston, Atlanta, Minneapolis, San Francisco, Chicago Gold Certified in E-Business, Business Intelligence and Microsoft Business Solutions .NET Experts Authored 40+ books on .NET Working with .NET since alphas Microsoft Server Products Experts: Sharepoint Portal, BizTalk, SQL Server, Commerce Server, Content Management Server Several development efforts for a variety of Microsoft’s divisions Who is ESS Magenic

5. SQL Server 2005 • New Version of SQL Server • Planned to be deliver in 2nd half of 2005 (with crossed fingers) • More than just .NET integration • All features noted here are valid in Beta 2 • MS can pull/add features at any time though • What didn’t make it • No row-level security (boo…hiss) • New temporal datatypes (Date, Time, UDT Time)

6. Engine Enhancements • New DataTypes • XML Datatype • CLR DataTypes

7. Engine Enhancements (2) • T-SQL Changes • New Exception Handling (TRY…CATCH) • Changes to TOP syntax • Including calculated TOP numbers and use in UPDATE and DELETE • Common Table Expressions (CTE) • Clearer way to make local Temp Tables and recursive queries • New APPLY clause • Allows a table-valued function to be called on all rows of a table • New Security Context • E.g. CREATE PROC spRunMe WITH EXECUTE AS OWNER • DDL Triggers • Allows CREATE/ALTER/DROP commands

8. Engine Enhancements (3) • Data Storage Changes • Materialized Views • Allows Custered Indexes on Views to pre-compute a view • Persisted Computed Columns • Unified Large Object Types (e.g. varchar(max) instead of text) • Added ALTER INDEX • Non-Key columns can be added to indexes (partially clustered)

9. Engine Enhancements (4) • Full-Text Engine • Now has DDL statements • e.g. CREATE FULLTEXT INDEX … • Can backup and restore catalogs • Can include catalogs in attach/detaching of databases • Supports Internationalization • New Thesaurus support

10. Engine Enhancements (5) • Other engine changes: • MARS Support (multiple resultsets from a single connection) • HTTP SOAP Access through new ENDPOINTS • Like SQL XML but supported through DDL statements • Database Mirroring • Way to handle fail-over without clustering • Support for Catalog Views • Can now query TABLES, VIEWS, INDEXES, etc. • Supports backward compatible sys.systables, but is depreciated

11. CLR Integration • Consists of two pieces: • Managed Code • Managed Types • Improved Hosting Environment • Hosting was created for an ASP.NET environment • SQL Server’s requirements are completely opposite

12. CLR Integration • Managed Code • Stored Procedures/Functions class Foo { public static void Method1() { } } CREATE ASSEMBLY Utilities FROM ‘C:\assemblies\utilities.dll’ CREATE PROCEDURE Method1 AS EXTERNAL NAME Utils:Foo::Method1 exec Utils:Foo::Method1

13. CLR Integration (2) • Other Managed Code • Managed Triggers • Can be managed code like sprocs and functions • Custom Aggregates • Managed Code that aggregate rows in different ways • Finally a way to write your own MAX, AVG, etc. aggregates

14. In Proc Managed Provider • SQL Server Managed Provider for Managed Code • Used inside the Server for access to data: //using System.Data.SqlServer; SqlConnection conn = SqlContext.GetConnection() SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * from authors"; SqlDataReader r = cmd.ExecuteReader(); while (r.Read()) { Console.WriteLine("{0} ", r["au_lname"]); } r.Close();

15. CLR Datatypes • Can store .NET Types in database tables • Information can be indexed • Lots of limitations • Must support the concept of NULL. • Must support conversion to and from a string representation. • Must define the mechanism with which its state is serialized. • Must have a default (parameterless) constructor. • Must conform to SQL Naming rules (128 character maximum). • Cannot be more than 8K in size.

16. XML Changes • FOR XML Changes • Now Supports XSD • Can return new XML datatype • More support for handling of nulls in XML creation

17. XML Changes (2) • XML Datatype • Allowed for local variables as well as column types • Supports XPath/XQuery searches • Supports in-line modification of XML documents • No need to get entire document just to add/delete/modify an element • Support multiple types of XML Indexes • By Path, node name or property names

18. XML Changes (3) • Supports Typed XML • Supports specifying an XSD as an XML type • Will validate XSD on storing XML data in tables • Not as memory restrictive as CLR Types (can be over 8K)

19. Service Broker • Reliable Messaging System inside the Database • Built-in support for asynchronous operations • Guaranteed delivery like MSMQ • Endpoints do not have to exist on local machine

20. Service Broker (2) • Service Broker Components • Messages • Bits of information that describe an operation • Contracts • An agreement to what a message will consist of • Queues • FIFO stores for messages • Services • Code that handles a message out of a Queue

21. Service Broker (3) • Other Service Broker Services • Conversation Management (discussions between services) • Transactional Support • Orchestration of Multiple Conversations • Security

22. Questions? Questions?