Creating SQL Server UserDefined Types with the 'Net CLR

1
Creating SQL Server User-Defined Types with
the.Net CLR

• Andrew Novick

2
Agenda

• Introduction
• Overview of .Net Programming in S2K5
• Creating User-Defined Types
• Consuming User-Defined Types
• Consuming UDTs from ADO.Net
• Pros and Cons of UDTs
• Resources

3
Introduction Andrew Novick

• Novick Software
• Business Application Development
• SQL Server and .Net specialization
• www.NovickSoftware.com
• Books
• Transact-SQL UDFs
• SQL 2000 XML Distilled

4
One Programmers Wishful Thinking
What Id really like is

• Create a object in either the client or the
  database
• Write business rules in my favorite language
• Business rules are enforced (efficiently) when
  the object was running in client, server, or
  mid-tier
• Save the object in the database
• Use SQL as a retrieval/update language whenever I
  want to. (i.e. in a Report Writer, external
  program)
• Database has high performance

5
Beta Based

• This presentation is based on beta code.
• .Net CLR Beta 2
• SQL Server 2005 Beta 2
• Everything is subject to change!

6
Why Use .Net in S2K5

• Replace Extended SPs with a safer alternative
• Replace COM automation sp_OA_
• Take advantage of .Net Framework classes
• Run compute intense algorithms
• Reuse code
• Use new capabilities not available in T-SQL
• User Defined Types
• User Defined Aggregates
• Programmer productivity

7
Types of .Net Code in S2K5

• Stored Procedures
• User Defined Functions
• User Defined Aggregates
• User Defined Types
• Triggers
• DTS Packages

8
CLR is Optional!

• Disabled by default
• You must turn it on to use it

EXEC sp_configure 'show advanced options' ,
'1'goreconfiguregoEXEC sp_configure 'clr
enabled' , '1'goreconfigure
9
Two Types of UDTs

• Alias Types
• CLR Types
• Both use the CREATE TYPE statement

10
Alias User Defined Types

• Alias Types
• These give an synonym name to a SQL type
• Have existed in SQL Server since Sybase
• Example

CREATE TYPE CompletionCODE FROM CHAR(2) NOT NULL
11
CLR User-Defined Types (UDTs)

• Created with the .Net CLR in VB.Net or C
• New to SQL Server 2005
• Example

CREATE Assembly myAssembly FROM
\\server\share\myAssembly.dll Go CREATE TYPE
myType EXTERNAL NAME myAssembly.myAssembly.myTyp
e go
12
Why CLR User-Defined Types

• Encapsulate application logic and data in the
  database.
• Benefits
• Programmer productivity
• Consistency over the long term

13
Steps to create a CLR UDT

• Create a Class in a .Net language (VB or C)
• Create the ASSEMBLY database object
• CREATE TYPE statement

14
CLASS or Structure

• Class is a reference type
• Structure is a Value type

15
Create your CLASS

• Required attributes

ltSerializable()gt _ ltMicrosoft.SqlServer.Server.Sql
UserDefinedType(Format.Native)gt
Required Interface INullable
Required Methods ToString Parse
16
Your Class

• Public and Private fields
• Static (Shared in VB) and instance members

17
SqlUserDefinedType Attribute

• Format Native, UserDefined
• IsByteOrdered
• IsFixedLength
• MaxByteSize
• Name
• ValidationMethodName

18
Assemblies

• .Net Code compiled into an IL DLL
• Assemblies must be added to S2K5 with
  CREATE ASSEMBLY
• Bits are stored in the databases
  sys.assembly_files table

CREATE ASSEMBLY my_assembly_name FROM
\\myserver\path\MyAssembly.dll
19
Assembly SecurityPERMISSION_SET

• SAFE
• EXTERNAL_ACCESS
• UNSAFE

20
PERMISSION_SET Safe

• May not access external resources registry, file
  system, or network
• May not use unmanaged code or PInvoke
• May access data using the current context but not
  via SQLClient or any other data provider
• No thread processing

21
PERMISSION_SET EXTERNAL_ACCESS

• May access external resourcesregistry, file
  system, network, environment variables
• May not use unmanaged code or PInvoke

22
PERMISSION_SET UNSAFE

• May access external resources
• May use unsafe code and PInvoke
• Can use SQLClient and other data providers
• Can use thread constructs
• This is no more unsafe than extended stored procs

23
CREATE TYPE statement
CREATE TYPE schema_name. type_name FROM
base_type ( precision , scale )
NULL NOT NULL EXTERNAL NAME
assembly_name .class_name

24
Type Name Rules

• Standard Rules for identifiers.
• Recommendation Keep It Simple

25
TYPE Permissions

• References
• Execute
• These apply to
• Assembly
• Type

26
REFERENCES Permission

• Required to use the type in a
• Column in a Table
• A static method in a Stored Procedure or UDF
• Creating a View using WITH SCHEMABINDING

27
EXECUTE Permission

• To use the type in a DML statement
• SELECT
• INSERT
• Update
• Delete

28
Code Access Security (CAS)

• Goal is to prevent the execution of code that
  could compromise SQL Server
• SQL Servers check code at assembly load time

29
Using Types in SQL

• CREATE TABLE Office (id int
  , location GEOPOINT)
• SELECT id, Location. Latitude
  Location.Longitude FROM Office

30
Type Considerations

• .Net value types dont represent NULL
• System.Data.SQLTypes represent NULL
• Use the SQLTypes when possible

31
User Defined Types

• Best used for scalar types
• Not ideally suitable for Classes

32
Best Practices For SQL/CLR

• Pick one .Net language
• Build assemblies with SQL Server in mind
• Use the most restrictive Permission Set
• Move as much .Net/CLR code to the middle tier as
  possible
• Test via the SQL Interface

33
Best Practices - UDTs

• UDTs should represent scalar objects
• Separate assembly to each group of UDTs
• Test extensively before deployment
• Keep careful track of the DLLs
• Use Source Control for them.

34
Why Use .Net in S2K5

• Take advantage of .Net Framework classes
• Run compute intense algorithms
• Reuse code
• Programmer productivity
• New capabilities not available in T-SQL
• User Defined Types
• User Defined Aggregates

35
Why .Net in S2K5

• Many tasks that were awkward or difficult to
  perform in Transact-SQL can be better
  accomplished by using managed code..."

36
Why Not use .Net
We used to joke that SQL stood for "Scarcely
Qualifies as a Language" because it has no I/O
and can't format output. Its math library is
limited because it isn't a computational
language. It doesn't do text searching, list
processing, or graphics. The only purposes of SQL
are data management and retrieval. Period.

Joe Cleko
37
Why Not Use .Net in S2K5 (2)

• Scalability
• Application servers MAY scale out easily
• Web servers USUALLY scale out easily
• Desktops ALMOST ALWAYS scale out
• Scaling up SQL Server is expensive

38
Why Not use .Net in S2K5 (3)

• Compare cost per CPU
  with software licenses

39
Resources

• First Look at SQL Server 2005 for Developers
• By Beauchemin, Berglund, Sullivan
• Published June 2004 (somewhat out of date)

40
Resources

• Weekly Newsletter about SQL Coding
• Theme for Sept-Oct is .Net CLR Programming

http//www.novicksoftware.com/coding-in-sql/codin
g-in-sql-signup.htm
41
Resources

• Download this presentation and samples from

http//www.novicksoftware.com/Presentations/clr-p
rogramming-dotnet-sql-server-2005-yukon/clr-dotnet
-sql-server-2005-yukon.htm
42
Mini Code Camp
SQL Server Programming From 2000 to 2005
Saturday October 22nd 2005 830 to 500 Adam
Mechanic Event Code 1032279560
43
New England Visual Basic Pro UG

• Focused on VB.Net development
• Meetings _at_ MS Waltham 1st Thursday - 615 to 830
• Coming up
• Oct. Developer Utility Round-Up
• Nov. Jesse Liberty
• Dec. Visual Studio 2005/SQL Server 2005
  Community Launch Event
• Jan Jason Beres

44
Thanks for Coming

• Contact me anovick_at_NovickSoftware.com
• http//www.NovickSoftware.com