SQL Server .NET Integration

 

PERFORMANCE:

CLR functions that are computing-intensive and that do not perform data access are better written in managed code

Managed code can significantly outperform cursor-based aggregation

Managed STVFs perform better than comparable extended stored procedure implementations

 

CREATE:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

 

Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _

    Public Shared Sub  StoredProcedure1 ()

        SqlContext.Pipe.Send(System.DateTime.Today.ToString())

    End Sub

End Class

 

DEPLOY:

CREATE ASSEMBLY assembly_name

[ AUTHORIZATION owner_name ]

FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] }

[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]

[ ; ]

 

INVOKE:

EXEC [dbo].StoredProcedure1

GO

 

ERROR: 

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

 

RESOLUTION:

sp_configure 'clr enabled', 1

go

reconfigure

go

 

References:

TUTORIAL - http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1

CREATE ASSEMBLY - http://msdn.microsoft.com/en-us/library/ms189524.aspx

CLR PERFORMANCE - http://msdn.microsoft.com/en-us/library/ms131075.aspx