This is on of the greatest "Shawn Wildermuth" articles about SQL Server, I hope it will help many people intresting about it.
It's long some how, so i will split it into two to not make you feel boring while reading it..
Introducing SQL Server 2005's CLR Integration
T-SQL is great for database code, but writing procedural code in T-SQL has always been difficult. Invariably, your project includes a stored procedure or two, requiring some text parsing or complex math operations. Doing this in SQL Server has been difficult. Either you wrote T-SQL code, or you wrote an extended stored procedure/function and used COM to interoperate them. Neither was a good solution, but that is all we had.
In comes SQL Server 2005 with its CLR integration to alleviate these problems. By integrating the CLR, SQL Server 2005 allows you to deploy C# or VB.NET code that is used within the SQL Server process. This means that if you need complex procedural code, you can write it as managed code.
Integrating the CLR into SQL Server is not a step to eliminating T-SQL. As .NET developers, it may seem like a good idea to do all your database code in managed code, but this is not the case. Think of the CLR integration as just another tool in your toolbox. This is the hammer that, I suspect, will be used to hammer in nails, screws, and 2x4s in projects the next year. It will be overused. Don't let your project be the ones caught guilty of this.
Details of the CLR Integration
Integrating the CLR into SQL Server involves a number of different features. Many of those features allow developers to do things they have never had the opportunity to do before in SQL Server. But, before I discuss the features and how they work, it's important to consider some details on how the CLR is hosted in SQL Server.
Integrating the CLR into the SQL Server 2005 engine was not done in a trivial way. At the end of the day, SQL Server 2005 has to be a rock-solid implementation of the database. Any new feature has had to endure intense scrutiny for how it will impact the stability of SQL Server.
In the 1.x version of the CLR, the chief client for hosting the CLR was Internet Information Server (IIS). IIS is a peculiar beast. If it finds badly behaving code, it is happy to kill threads and processes and just restart the code. Any code living within IIS was free to allocate memory, threads or even new processes, as it saw fit. Unfortunately, in SQL Server this is the opposite of the needed requirements. If some piece of CLR code starts to act in a bad way, destroying the SQL Server process is the completely wrong thing to do. The health of the SQL Server processes are critical to the stability of the platform.
The 2.0 version of the hosting environment has many more ways to communicating with the host environment. These new communication mechanisms allow for SQL Server to be in control of key operations of the CLR. SQL Server may refuse to allow creation of new memory or new threads, and disallow the destruction of the host process. In addition, the CLR integration puts CLR code into a secure sandbox of operations to improve on the stability and security inside SQL Server.
Using Managed Code
Now that you have an assembly or two loaded, you want to know how to actually have code run within SQL Server. Within SQL Server, most types of code blocks that you are familiar with in T-SQL are supported in managed code:
Stored Procedures Functions Triggers
A new type of code is supported in SQL Server 2005 called a custom aggregation. This allows you to write code that supports aggregating data. You can do things like create a custom SUM or COUNT aggregation. You might create useful extensions to SQL Server, like Standard Deviations. I'll cover custom aggregations in more specifics below.
Using managed code within SQL Server 2005 requires three steps:
You must write the managed code and compile it into an assembly. You must install the managed code's assemblies into SQL Server 2005. You must use DDL statements to tie the managed code to named objects (Stored Procedures, functions, etc.)
I'll explain each of these steps.
Writing Managed Code
The easiest way to write managed code for SQL Server 2005 is to use Visual Studio 2005. As of the writing of this article, the full Beta 2 of Visual Studio is available; it works well with the SQL Server 2005 April(2005) CTP. To write managed code for SQL Server, you must have at least the Team Server edition of Visual Studio.
Using Visual Studio to write the managed code takes many of the details of deployment out of your hands, as it supports automatic deployment. This article ignores that fact; I'll explain how to write managed code and install it in SQL Server manually. Since your projects likely need Install scripts for managed code, this skill will soon be required for most projects.
For each type of managed code that is supported, there are related attributes that are used to decorate the code to help SQL Server know about specific behaviors about the managed code. These attributes include SqlProcedure, SqlFunction, SqlUserDefinedAggregate, SqlUserDefinedType and SqlMethod. Each of these attributes is explained below.
Stored Procedures
The most common and useful managed code in your own projects are probably stored procedures. Creating managed stored procedures have the following three requirements:
The containing class must be public. The exposed method must be public. The exposed method must be static.
That is all that is required. For example, to expose a simple stored procedure:
public class SqlClr {
[SqlProcedure]
public static void MyProc() {
// Put your code here
}
}Notice there is nothing especially different about this method from any other .NET code. The SqlProcedure attribute marks this code as a stored procedure. The attribute is not required, but is good form as **** of what code is used where. It is also used in Visual Studio to allow for the automatic deployment. The only parameter that it accepts is the name parameter that will rename the automatically deployed stored procedure:
public class SqlClr {
[SqlProcedure(Name="spMyProc")]
public static void MyProc() {
// Put your code here
}
}You can specify in, out, inout and return parameters as simple .NET parameters and return types:
// Input Parameter
[SqlParameter]
public static void InputProcedure(int number) {
}
// Output Parameter
[SqlParameter]
public static void OutputProcedure(out int number) {
number = 5;
}
// In/Out Parameter
[SqlParameter]
public static void InOutProcedure(ref int number) {
number = 4;
}
// Return Parameter
[SqlParameter]
public static int ReturnProcedure() {
return 3;
}Functions
Creating managed functions in SQL Server is just as simple as creating managed stored procedures, except that they must return a value:
[SqlFunction]
public static int Subtraction(int x, int y) {
return x - y;
}Some parameters that are useful to be in the SqlFunctionAttribute are as follows:
DataAccess: Determines if the function needs to read data in the database. If this is not specified, it is assumed that no data access is required. IsDeterministic: Used to declare that a function will always return the same result, regardless of any other state in SQL Server. IsPrecise: Used to declare if the result that is being returned is scientifically precise. Name: Used to annotate a function name to use, other than the managed method name.
Managed Triggers
Much like T-SQL, SQL Server 2005 allows you to create triggers in managed code. You must take special care to understand the performance implications in using managed triggers, as their performance is likely to be lower than similarly written T-SQL triggers.
Assuming you have taken the performance considerations into account, you would write triggers by simply annotating them with the SqlTrigger attribute. The SqlTrigger attribute requires two parameters:
Event: The event to fire the trigger for. This syntax is identical to the T-SQL syntax for the event name (e.g. FOR INSERT, INSTEAD OF DELETE, etc.). In addition, you can specify event names for DDL triggers (a new SQL Server 2005 feature) by specifying a DDL event name (e.g. FOR CREATE TABLE, FOR DROP USER, etc.). Target: The source of the event. Usually, this is a table or view name for DML triggers or a database name for DDL triggers.
NOTE: Using the SqlTrigger attribute is broken in the Beta 2 version of Visual Studio. You can write the triggers, but you must register them manually, and debugging is impossible at the moment.
Introducing SQL Server 2005's CLR Integration
In writing a managed trigger, your code may look something like this:
[SqlTrigger(Event = "FOR INSERT",
Name = "Person.trgOnNewContact",
Target = "Person.Contact")]
public static void AddContact() {
SqlTriggerContext ctx = SqlContext.TriggerContext;
if (ctx.TriggerAction == TriggerAction.Insert) {
string msg = "";
// Get the data associated with Event
for (int x = 0; x < ctx.ColumnCount; ++x) {
msg += string.Format("Column {0} {1} been updated{2}",
x,
(ctx.IsUpdatedColumn(x) ? "has" : "has not"),
Environment.NewLine);
}
// Use the message to log it somewhere
}
}Writing a trigger normally requires that you have some access to the data that was affected (in this case, the row being inserted). To access that information, SQL Server supports the SqlTriggerContext object that is used to get information about the trigger. With the SqlTriggerContext object (which can be retrieved from the SqlContext object), you can see what the trigger action was, and determine what columns were affected in the statement that caused the trigger to fire.
Managing Assemblies
Now that we have an assembly with our managed code, we need to install it inside of SQL Server. This is accomplished by using new DDL syntax:
CREATE ASSEMBLY <NAME>
FROM <path>
CREATE ASSEMBLY <NAME>
FROM <bytestream>Or:
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
CREATE ASSEMBLY ExampleYukon
FROM 0x4D5A90000300000004000000FFFF0000B8000000000000...The first syntax installs an assembly from a simple file path. The second syntax allows the assembly to be completely included in a script, by including the entire binary as a set of bytes.
By default, the assembly is installed with only permission to execute "safe" code. The CLR integration has the notion of three levels of execution permissions; these are enforced on the assembly level:
SAFE: Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop. EXTERNAL: 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.
These security levels are specified when assemblies are added to SQL Server by using the WITH PERMISSION syntax:
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
WITH PERMISSION_SET=SAFE
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
WITH PERMISSION_SET=EXTERNAL
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
WITH PERMISSION_SET=UNSAFEAs you might expect, the assembly DDL syntax also includes ALTER and DROP syntaxes:
-- Drops the Assembly from SQL Server
DROP ASSEMBLY FooThe DROP syntax takes the name of an assembly and drops it from SQL Server. Dropping an assembly will not work if any code is being referenced in the database.
-- Changes the Permission Set
ALTER ASSEMBLY Foo
WITH PERMISSION_SET=UNSAFE
-- Adds a debug file to allow debugging
-- Required if debugging CLR code in SQL Server 2005
ALTER ASSEMBLY FOO
ADD FILE FROM 'c:\ExampleYukonB2.pdb'The ALTER syntax allows you to change assembly settings (like the PERMISSION_SET) after installation of the assembly. In addition, you can add or remove files from the set of files that are used by the assembly. Most notable is the addition of debug files (.pdb's) into the database, if you want to debug CLR code.
Using DDL Statements
Now that your managed code's assembly is installed, you use DDL statements to map the managed code to SQL Server objects. Typically, the syntax traditional syntax for a SQL Server object (e.g. Stored Procedures) is extended to allow for this mapping.
The syntax for adding a managed stored procedure is as follows:
CREATE PROCEDURE <Procedure Name>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>For example:
CREATE PROCEDURE MyProc
AS EXTERNAL NAME ExampleYukon.SqlClr.MyProcThis simple syntax creates a new procedure called MyProc that maps to the static method on the SqlClr class called MyProc. Once you have done the mapping, calling the managed stored procedure is identical to calling any stored procedure:
exec MyProcIf the stored procedure has input, output, inout, or return parameters, the signature must match in the DDL statement. For example, to write the DDL for our example procedures from above:
CREATE PROCEDURE InputProcedure
@number int
AS EXTERNAL NAME ExampleYukon.SqlClr.InputProcedure
CREATE PROCEDURE OutputProcedure
@number int OUTPUT
AS EXTERNAL NAME ExampleYukon.SqlClr.OutputProcedure
CREATE PROCEDURE InOutProcedure
@number int OUTPUT
AS EXTERNAL NAME ExampleYukon.SqlClr.InOutProcedure
-- NOTE: You don't specify ReturnParameters
CREATE PROCEDURE ReturnProcedure
AS EXTERNAL NAME ExampleYukon.SqlClr.ReturnProcedureFunctions
Managed functions are very similar to stored procedures, in that the standard function syntax has been extended to support managed functions:
CREATE FUNCTION <Function Name>
(
<Parameter List>
)
RETURNS <Return Type>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>For example:
CREATE FUNCTION Subtraction
(
@x int,
@y int
)
RETURNS int
AS EXTERNAL NAME ExampleYukon.SqlClr.SubtractionAgain, calling the managed function would be identical to calling any function, once this mapping has been done:
SELECT Subtraction(5,3)Triggers
Lastly, mapping managed triggers to their SQL Server counterpart is simply a matter of specifying a new trigger with the external name:
CREATE TRIGGER <TriggerName>
ON <Table or View> <FOR|INSTEAD OF|AFTER> <INSERT|UPDATE|DELETE>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>For example:
CREATE TRIGGER AddContact
ON Person.Contact FOR INSERT
AS EXTERNAL NAME ExampleYukon.SqlClr.AddContactCreating DDL triggers is just as simple:
CREATE TRIGGER
ON <ALL_SERVER or DATABASE> <FOR or AFTER> <EventName>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>This allows you to create triggers on specific database events (like creating or dropping of tables, users, etc.). For example:
CREATE TRIGGER AddUser
ON DATABASE FOR CreateUser
AS EXTERNAL NAME ExampleYukon.SqlClr.AddUserIn this article, I gave you a taste of what is required to write managed stored procedures. In the next one, I'll show you the In-process Managed Provider to access database data, as well as custom aggregators and custom data types.
Hope you enjoy it
[SqlTrigger(Event = "FOR INSERT",
Name = "Person.trgOnNewContact",
Target = "Person.Contact")]
public static void AddContact() {
SqlTriggerContext ctx = SqlContext.TriggerContext;
if (ctx.TriggerAction == TriggerAction.Insert) {
string msg = "";
// Get the data associated with Event
for (int x = 0; x < ctx.ColumnCount; ++x) {
msg += string.Format("Column {0} {1} been updated{2}",
x,
(ctx.IsUpdatedColumn(x) ? "has" : "has not"),
Environment.NewLine);
}
// Use the message to log it somewhere
}
}Writing a trigger normally requires that you have some access to the data that was affected (in this case, the row being inserted). To access that information, SQL Server supports the SqlTriggerContext object that is used to get information about the trigger. With the SqlTriggerContext object (which can be retrieved from the SqlContext object), you can see what the trigger action was, and determine what columns were affected in the statement that caused the trigger to fire.
Managing Assemblies
Now that we have an assembly with our managed code, we need to install it inside of SQL Server. This is accomplished by using new DDL syntax:
CREATE ASSEMBLY <NAME>
FROM <path>
CREATE ASSEMBLY <NAME>
FROM <bytestream>Or:
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
CREATE ASSEMBLY ExampleYukon
FROM 0x4D5A90000300000004000000FFFF0000B8000000000000...The first syntax installs an assembly from a simple file path. The second syntax allows the assembly to be completely included in a script, by including the entire binary as a set of bytes.
By default, the assembly is installed with only permission to execute "safe" code. The CLR integration has the notion of three levels of execution permissions; these are enforced on the assembly level:
SAFE: Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop. EXTERNAL: 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.
These security levels are specified when assemblies are added to SQL Server by using the WITH PERMISSION syntax:
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
WITH PERMISSION_SET=SAFE
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
WITH PERMISSION_SET=EXTERNAL
CREATE ASSEMBLY ExampleYukon
FROM 'd:\ExampleYukon.dll'
WITH PERMISSION_SET=UNSAFEAs you might expect, the assembly DDL syntax also includes ALTER and DROP syntaxes:
-- Drops the Assembly from SQL Server
DROP ASSEMBLY FooThe DROP syntax takes the name of an assembly and drops it from SQL Server. Dropping an assembly will not work if any code is being referenced in the database.
-- Changes the Permission Set
ALTER ASSEMBLY Foo
WITH PERMISSION_SET=UNSAFE
-- Adds a debug file to allow debugging
-- Required if debugging CLR code in SQL Server 2005
ALTER ASSEMBLY FOO
ADD FILE FROM 'c:\ExampleYukonB2.pdb'The ALTER syntax allows you to change assembly settings (like the PERMISSION_SET) after installation of the assembly. In addition, you can add or remove files from the set of files that are used by the assembly. Most notable is the addition of debug files (.pdb's) into the database, if you want to debug CLR code.
Using DDL Statements
Now that your managed code's assembly is installed, you use DDL statements to map the managed code to SQL Server objects. Typically, the syntax traditional syntax for a SQL Server object (e.g. Stored Procedures) is extended to allow for this mapping.
The syntax for adding a managed stored procedure is as follows:
CREATE PROCEDURE <Procedure Name>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>For example:
CREATE PROCEDURE MyProc
AS EXTERNAL NAME ExampleYukon.SqlClr.MyProcThis simple syntax creates a new procedure called MyProc that maps to the static method on the SqlClr class called MyProc. Once you have done the mapping, calling the managed stored procedure is identical to calling any stored procedure:
exec MyProcIf the stored procedure has input, output, inout, or return parameters, the signature must match in the DDL statement. For example, to write the DDL for our example procedures from above:
CREATE PROCEDURE InputProcedure
@number int
AS EXTERNAL NAME ExampleYukon.SqlClr.InputProcedure
CREATE PROCEDURE OutputProcedure
@number int OUTPUT
AS EXTERNAL NAME ExampleYukon.SqlClr.OutputProcedure
CREATE PROCEDURE InOutProcedure
@number int OUTPUT
AS EXTERNAL NAME ExampleYukon.SqlClr.InOutProcedure
-- NOTE: You don't specify ReturnParameters
CREATE PROCEDURE ReturnProcedure
AS EXTERNAL NAME ExampleYukon.SqlClr.ReturnProcedureFunctions
Managed functions are very similar to stored procedures, in that the standard function syntax has been extended to support managed functions:
CREATE FUNCTION <Function Name>
(
<Parameter List>
)
RETURNS <Return Type>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>For example:
CREATE FUNCTION Subtraction
(
@x int,
@y int
)
RETURNS int
AS EXTERNAL NAME ExampleYukon.SqlClr.SubtractionAgain, calling the managed function would be identical to calling any function, once this mapping has been done:
SELECT Subtraction(5,3)Triggers
Lastly, mapping managed triggers to their SQL Server counterpart is simply a matter of specifying a new trigger with the external name:
CREATE TRIGGER <TriggerName>
ON <Table or View> <FOR|INSTEAD OF|AFTER> <INSERT|UPDATE|DELETE>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>For example:
CREATE TRIGGER AddContact
ON Person.Contact FOR INSERT
AS EXTERNAL NAME ExampleYukon.SqlClr.AddContactCreating DDL triggers is just as simple:
CREATE TRIGGER
ON <ALL_SERVER or DATABASE> <FOR or AFTER> <EventName>
AS EXTERNAL NAME <Assembly Identifier>.<Type Name>.<Method Name>This allows you to create triggers on specific database events (like creating or dropping of tables, users, etc.). For example:
CREATE TRIGGER AddUser
ON DATABASE FOR CreateUser
AS EXTERNAL NAME ExampleYukon.SqlClr.AddUserIn this article, I gave you a taste of what is required to write managed stored procedures. In the next one, I'll show you the In-process Managed Provider to access database data, as well as custom aggregators and custom data types.
Hope you enjoy it
