Introduction to Threading in .Net

You Can Do Anything If You Think “Yukon” Presented by: Richard Broida Senior Architect

Agenda Overview of SQL Server 2005 Enhancements to Database Engine and T-SQL CLR Hosting Writing CLR functions, procedures and triggers Creating user-defined data types and aggregates Resources Q & A

History of Microsoft SQL Server 1988 – MS/Sybase version for OS/2 1993 – 4.21 for NT 3.1 1994 – MS/Sybase partnership ends 1995 – 6.0, major rewrite 1996 – 6.5 1997 – 6.5 Enterprise Edition 1998 – 7.0, total rewrite 2000 – 2000

SQL Server 2000 Has Lived to See … Windows XP and Windows 2003 Server .NET Framework 1.0 and 1.1 BizTalk 2000, 2002 and 2004 Commerce Server 2000 and 2002 CMS 2000 and 2003 SharePoint 2001 and 2003 Exchange Server 2000 and 2003 Oracle 9i and 10g DB2 8.1 and 8.2

New Features in SQL Server 2005 SQLCLR XML as Native Data Type Hosting Web Services Enhancements to T-SQL Client API Enhancements Service Broker Notification Services Enhanced Reporting Services Enhanced Analysis Services

SQL Server 2005 Editions Express Edition Replaces MSCE Freely downloadable and redistributable Workgroup Edition Less expensive than Standard Standard Edition Enterprise Edition Developer Edition

Where is It Now? First Technology Preview released at PDC in October 2003 Betas 1 and 2 Released in 2004 Most current version on MSDN is February 2005 Community Technology Preview A “Beta 3” was announced for April 2005 release, along with Beta 2 of Visual Studio 2005. Not out yet.

Big Disclaimer This presentation is based on the February 2005 Community Technology Previews of SQL Server 2005 and Visual Studio 2005. Anything and everything could change between now and the final release of these products. To the extent of such changes, the information in this presentation could end up wrong!

Beta/CTP Installation Tips Use a clean VM Or, completely remove old build before installing new one using \Setup Tools\Build Uninstall Wizard\sqlbuw.exe in installation media Install SQL Server and Visual Studio on separate VMs They tend to have incompatible builds of the CLR Even on separate VMs, not all Yukon/Whidbey combinations work together. These do: Yukon Beta 1 with Whidbey Beta 1 Yukon Dec CTP with Whidbey Oct CTP Yukon Feb CTP with Whidbey Feb CTP

Enhancements to the Database Engine and T-SQL

Does a SQL Server 2005 Programmer Need to Know T-SQL? Absolutely! SQLCLR relies on T-SQL for querying and updating the database T-SQL is still the fastest and most powerful for what it does New T-SQL enhancements reduce the situations where procedural code is necessary

Enhancements to the Database Engine SNAPSHOT Isolation Level Uses versioning instead of locks, like that “Greek” database Can provide better concurrency than traditional SERIALIZABLE and READ_COMMITTED Large Value Data Types VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) can hold up to 2 31 bytes Use instead of TEXT and IMAGE Statement-Level Recompilation for SPs

Enhancements to T-SQL TRY … CATCH Exception Handling With support for Transactions OUTPUT Command Use with INSERT, UPDATE and DELETE to save copies of the affected rows in a temporary table TOP Command Supported in INSERT, UPDATE and DELETE Quantity can be a calculated value PIVOT command Rotates rows into columns and columns into rows

Common Table Expressions The “WITH” Clause Creates a “virtual” table for use in a single query Often provides a simpler syntax than using a VIEW or subquery, and may be easier for Optimizer to optimize Allows recursive queries

Simple WITH Clause WITH BigSales(RepID) AS (SELECT RepId from Sales WHERE TotSales > 50000) SELECT ‘Big Seller’, * FROM SalesReps WHERE SalesReps.Id = BigSales.RepId UNION SELECT ‘Small Seller’, * FROM SalesReps WHERE SalesReps.Id NOT IN (SELECT RepId FROM BigSales)

Recursion Example: a “Linked List” Table: OrgChart ID Name ReportsTo 1 Dubya NULL 2 Bill 3 3 Rama 1 4 Sal 2 5 Jane 3 6 Shu 1

Query: How Many Levels from the Top Are You? Levels From Top Dubya 0 Rama 1 Shu 1 Bill 2 Jane 2 Sal 3

Performing the Query WITH LevelsDown (Id, Tot) AS ( SELECT Id, 0 FROM OrgChart WHERE ReportsTo is NULL UNION ALL SELECT OrgChart.Id, LevelsDown.Tot + 1 FROM LevelsDown JOIN OrgChart ON LevelsDown.Id = OrgChart.ReportsTo ) SELECT Name, Tot FROM OrgChart JOIN LevelsDown ON OrgChart.ID = LevelsDown.Id ORDER BY 2

New Ranking Commands Require an OVER clause to specify the sorting order ROW_NUMBER Inserts a column showing absolute position in the sort order RANK Assigns same value to all rows with same rank in the sort order DENSE_RANK Like RANK, but doesn’t leave “holes” NTILE Divides results into equal or near-equal divisions Great for efficient paging in a DataGrid

Adding Row Numbers to Query Output SELECT ROW_NUMBER() OVER( ORDER BY L ast N ame) AS RowNumber, F irst N ame, L ast N ame FROM P erson

Selecting the 78 th of 83 “Pages” of Data SELECT LastName, FirstName FROM ( SELECT NTILE( 83 ) OVER (ORDER BY LastName) AS PageNo, FirstName, LastName FROM Person ) AS TEMP WHERE TEMP. PageNo = 78

Overview of SQLCLR Write Procedures, Triggers and Functions in .NET languages to run in a CLR hosted by SQL Server Use ADO.NET data access classes to run T-SQL commands inside the server Create User-Defined Data Types that can be stored in database tables Write Aggregate functions to operate on UDTs

Procedural Database Code before SQLCLR T-SQL Clumsy syntax Slow when not dealing directly with the database Syntax unfamiliar to many programmers Extended Stored Procedures Write an external DLL in C Supported in SQL Server 2005, but likely to be deprecated Difficult to develop and test Risky, because external DLL runs in SQL Server’s address space Memory leaks Database corruption Security holes External Code in Data Access, Business Logic and/or Presentation layers

Benefits of SQLCLR Write in your favorite .NET language Initially supports C#, VB.NET and C++ Use any .NET development tools Use Framework Class Library or other libraries Computation-intensive code executes faster than T-SQL Stability and security of the CLR Use native SQL security, Code Access Security, or both

What is a CLR Host? Host: Any process that loads the CLR .NET Framework 1.x has three hosts: Command shell IIS Internet Explorer Can write other CLR hosts using the Hosting API .NET Framework 2.0 expands the Hosting API to accommodate the needs of SQL Server

Requirements for Hosting in SQL Server To maximize performance, SQL Server manages its own memory and threads without help from the NT Kernel SQL Server understands internal memory needs better than NT Kernel; can use memory more efficiently with less paging SQL Server uses cooperative multitasking to switch threads without kernel-mode context switching Or in “fiber mode”, SQL Server may use fibers instead of threads Requires multi-CPU machine

How SQL Server Implements CLR Hosting SQLCLR memory management is handled by SQL Server, not NT Kernel SQLCLR threads come from SQL Server thread pool and are cooperatively multitasked Or if SQL Server is in “fiber mode”, the CLR threads are actually fibers SQLCLR stores assemblies inside the database and loads them from there, not from the file system

Permission Sets Defined for SQLCLR Assemblies SAFE The default Restrictions to assure the stability and security of SQL Server EXTERNAL_ACCESS Can access external data sources UNSAFE No restrictions, except those imposed by .NET Framework on all managed code Similar risks as unmanaged External Stored Procedures

SAFE Permission Set Prohibited to Create threads Access external resources such file system, network, registry or environment variables Connect to external databases Call unmanaged code via PInvoke or RCWs Call portions of the Framework Class Library not marked as safe for SQL Server E.g., System.Windows.Forms, System.Security, System.Reflection

EXTERNAL_ACCESS Permission Set Permitted to Access external resources such as file system, network, registry or environment variables Connect to external databases Everything else prohibited same as SAFE

Some Consequences of SQL Server Hosting Static fields must be readonly After try{}, a finally{} block is not guaranteed to be called

How to Install an Assembly in SQLCLR Create the Assembly outside SQL Server SQL Server itself comes with no tools to write or compile assemblies. Can use Visual Studio, Framework SDK, or other tools SQL Server doesn’t need a Strong Name Enable SQLCLR on the server Install Assembly in SQL Server with CREATE ASSEMBLY Declare a procedure, function or trigger with CREATE [PROCEDURE|FUNCTION|TRIGGER] … EXTERNAL NAME …

Enabling SQLCLR on a Server CLR Execution is Disabled by Default To enable it, execute: Sp_configure ‘clr enabled’, 1 RECONFIGURE

Installing An Assembly CREATE ASSEMBLY MyAssembly FROM ‘C:\Projects\bin\MyAssembly.dll’ WITH PERMISSION_SET = SAFE ALTER ASSEMBLY MyAssembly FROM ‘C:\Projects\bin\MyAssembly.dll’ WITH PERMISSION_SET = SAFE DROP ASSEMBLY MyAssembly

Making the Assembly Usable from T-SQL CREATE PROCEDURE MyProcedure (@arg1 int, @arg2 varchar(20)) EXTERNAL NAME MyAssembly.[MyNameSpace.MyClass]. MyProcedure CREATE FUNCTION MyFunction (arg1 int) RETURNS int EXTERNAL NAME MyAssembly.[MyNameSpace.MyClass]. MyFunction

Viewing Assemblies in SQL Server’s Metadata To view metadata about assemblies installed in SQL Server SELECT * FROM Sys.Assemblies To view assembly code itself SELECT * FROM Sys.Assembly_Files To view procedures, functions and triggers defined from an assembly SELECT * FROM Sys.Assembly_References

Demonstration Creating a SQLCLR Function with Notepad Viewing assembly metadata

SQL Database Projects in Visual Studio 2005 Allow quick creation of classes for Stored Procedures, Functions, Triggers, UDTs and Aggregates One-click deployment of assembly into a database, no need to write T-SQL code SQL Script provided to run while debugging

Debugging SQLCLR Visual Studio 2005 Interactive Debugger can step through SQLCLR code Must have Visual Studio Remote Debugging Monitor running on remote SQL Server Microsoft has announced intention to release a free stand alone debugger sometime after SQL Server 2005 ships

Creating SQLCLR Functions, Procedures and Triggers Must be a static method with one of [SqlFunctionAttribute] [SqlProcedureAttribute] [SqlTriggerAttribute] Can belong to a class or struct, whose name isn’t important SQL doesn’t support overloading, so avoid overloaded method names

Using SQL Data Types in .NET SQL types don’t map perfectly to CTS types All SQL types can be NULL, but CTS value types can never be null SQL decimal has a broader range of values than CTS Decimal CTS Float and Double can hold the values Infinity and NAN, but SQL float and double cannot

System.Data.SqlTypes System.Data.SqlTypes implements SQL types in .NET SqlTypes should be used for Parameters to functions and stored procedures Return values from functions You can also use SqlTypes in code outside SQL Server

SqlType Examples CLR SQLType SQL String SqlString (n)(var)char, (n)text Int32 SqlInt32 int Double SqlDouble float Boolean SqlBoolean bit Byte[] SqlBinary (var)binary, image, timestamp DateTime SqlDateTime datetime Guid SqlGuid uniqueidentifier

Operations on SqlTypes Numerical SqlTypes support unary and binary arithmetic operators (+,-,* etc) SqlTypes have boolean IsNull property Use it instead of comparing a SqlType with CLR null SqlTypes support comparison operators (==,!=,<,> etc), but watch out for special rules when value = NULL SqlTypes have static Null method to create an instance with value = NULL SqlString has concatenation with +

Converting SqlType to CLR Type void foo( SqlInt32 sqlInt ) { Int32 clrInt; clrInt = sqlInt.Value; // or clrInt = (Int32)sqlInt; // but next is error, no implicit conversion clrInt = sqlInt; }

Converting CLR Type to SqlType void foo( Int32 clrInt ) { SqlInt32 mySqlInt; mySqlInt = new SqlInt32(clrInt); // or mySqlInt.Value = clrInt; // or mySqlInt = (SqlInt)clrInt; // ok, implicit conversion allowed this direction mySqlInt = clrInt }

Demonstration Creating a SQLCLR Function with Visual Studio 2005 Using SqlTypes

Accessing the Database from SQLCLR Code Database code written in T-SQL can issue SQL statements like SELECT, UPDATE and EXEC at any time SQLCLR code must go through an ADO.NET data provider

The System.Data.SQLServer Provider (as of Feb 2005 CTP) System.Data.SqlServer provides data access classes for use (only) within SQLCLR Connection objects aren’t needed Static SqlContext methods create commands, DataAdapters, etc In SAFE assembly, SQL Server won’t allow data access unless class has this attribute property: DataAccess=DataAccessKind.Read

Changes Coming Microsoft announced that in next pre-release of SQL Server 2005, System.Data.SqlServer will merge into System.Data.SqlClient To connect to the database from within SQLCLR, you will use a SQLConnection with this connection string: “context connection=true”

Data Access Example (Feb CTP) SqlCommand comm = SqlContext.CreateCommand(); comm.CommandText = “SELECT * FROM MyTable”; SqlDataReader reader = comm.ExecuteReader(); // use the reader …

Data Access Example (Coming Soon) SqlConnection conn = new SqlConnection(“context connection=true”) SqlCommand comm = new SqlCommand() comm.Connection = conn; comm.CommandText = “…”; conn.Open(); // use the command …

Splitting the SqlCommand SQLDefinition – the parts of the command that don’t change in multiple invocations SQLExecutionContext – the parts that do, such as the parameters These can yield better performance than a single SqlCommand

SqlPipe Returns a TDS (tabular data set) stream to the client Use to return the results of stored procedures to clients SqlDataReader reader = command.ExecuteReader(); SqlPipe pipe = SqlContext.GetPipe(); pipe.Send( reader );

Demonstration Executing SELECT statement inside a SQLCLR Function Putting It Together: Selecting a Random Row from a Table

User-Defined Data Types and Aggregates

Is SQL Server 2005 an Object-Oriented Database? No Will not store any arbitrary CLR type in the database Does not support inheritance or polymorphism It will store User-Defined Types, which emulate native SQL scalar types Though created in .NET, UDTs behave more like SQL types than OOP types

What is a User-Defined Data Type? A UDT is a .NET Class or Struct that can function as a SQL scaler type A scaler can be stored in a column of a table, or be the type of a variable Examples of built-in scaler types INT CHAR DATETIME

What Must a Scaler Be Able to Do? Mandatory Be serializable to/from a byte array Be serializable to/from a string Be able to equal NULL Optional, But Usually Necessary Support comparison operators (=,<,>, etc) Support numerical operators (+,-,*,/ etc) Support aggregation functions Be indexable

Creating a UDT Can be a Class or Struct Must have [SqlUserDefinedAttribute] Must implement INullable Must override ToString() Must have a static Parse() that converts a string to an instance of the UDT

Binary Serialization of UDTs Byte stream cannot exceed 8,000 bytes If [SqlUserDefinedAttribute] is set to Format.Native, SQL Server will handle serialization All UDT fields must be “blittable” I.e., fields that are simple value types Sort order must be based on order of the binary fields Use Format.Native whenever possible

Format.Native Serialization [Serializable] [SqlUserDefinedType (Format = Format.Native, IsByteOrdered = true)] [StructLayout(LayoutKind = Sequential)] struct MyUDT { … }

User-Defined Binary Serialization [Serializable] [SqlUserDefinedType( Format = Format.UserDefined, IsByteOrdered = true, IsFixedLength = true, MaxByteSize = 17)] struct MyUDT { … }

UDTs Outside the Database Clients outside the database will need UDT type information when they see a table with a UDT column, and they won’t get the info from SQL Server You’ll need to deploy the UDT’s assembly in the client’s folder, in the GAC, or elsewhere in the probe path This leads to possible version conflicts between assemblies in and out of database

What is an Aggregate? A class or struct whose methods implement a SQL aggregation function The aggregation function can be applied in a T-SQL statement to a database column Example: SELECT MyAgg.Sum(MyTable.UDTCol) FROM MyTable

Creating an Aggregate Class or struct must have [SerializableAttribute] Class or struct must have [SqlUserDefinedAggregateAttribute] Must provide four public methods: Init Accumulate Merge Terminate

Aggregate Attribute Properties IsInvariantToNulls The aggregate ignores null values. The optimizer can choose not to send nulls. IsInvariantToDuplicates The aggregate ignores duplicate values. The optimizer can choose not to send dupes. IsInvariantToOrder The result is unaffected by the order in which rows are processed. Optimizer can send them in any order IsNullEmpty Result is NULL if no rows are passed. Optimizer can choose not to create an aggregate at all.

Demonstration Creating a 2D Point Data Type And a String Aggregator

Microsoft SQL Server Summit 2005 With Karen Delaney, author of Inside SQL Server 2000 and Inside SQL Server 2005 May 17, 2005 Park Center III 6050 Oak Tree Blvd, Suite 300 Independence, Ohio 44131 Event code: 1032271939 To register call 1.877.673.8368 Free, but seating limited

Resources on the Web SQL Server 2005 Official Site http://msdn.microsoft.com/SQL/2005/default.aspx SQLJunkies http://www.sqljunkies.com/ SQL Server 2005 Hands-On Labs Online http://msdn.microsoft.com/sql/2005/2005labs/default.aspx

Conclusion SQL Server 2005 confirms Microsoft’s ongoing commitment to the product Gets closer to parity with rival databases SQLCLR has compelling advantages in some situations Intensive computations in memory Use of FCL library features In other situations, alternative designs, including T-SQL, may be better

Conclusion SQLCLR solutions require care to build and test SQLCLR developers will need mastery of both SQL and CLR

Introduction to Threading in .Net

More Related Content

What's hot

Similar to Introduction to Threading in .Net

More from webhostingguy

Introduction to Threading in .Net