Indexes on computed Columns : Marking a CLR function as Deterministic

Came across this when trying to index a column.
This column is a little different in that it is defined as a computed column based on a common language runtime (CLR) function.

So, running >

CREATE INDEX ix_ipnumber
ON dbo.IPTest (IPNumber)

gave me this>

Msg 2729, Level 16, State 1, Line 1

Column 'IPNumber' in table 'dbo.IPTest' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

So I need to create a 'deterministic' function.
Setting the function properties isdeterministic:=true and isprecise:=True do the trick.

Partial Public Class IPConverter
 _
Public Shared Function CLR_IPToInteger(ByVal Expression As String) As Long

If IsDBNull(Expression) = False Then

Try
Dim IPSplit() As String
IPSplit = Expression.Split(".".ToCharArray())
Expression = IPSplit(3) + "." + IPSplit(2) + "." + IPSplit(1) + "." + IPSplit(0)

Dim IPAddress As System.Net.IPAddress = System.Net.IPAddress.Parse(Expression)
With IPAddress
Return (System.Convert.ToInt64(.GetAddressBytes(3)) << 24) Or (System.Convert.ToInt64

(.GetAddressBytes(2)) << 16) Or (System.Convert.ToInt64(.GetAddressBytes(1)) << 8) Or System.Convert.ToInt64

(.GetAddressBytes(0))
End With

Catch ex As Exception
Return 0I
End Try

Else
Return 0
End If

End Function

So, having compiled the assembly and recreating the CLR assembly and function (a reminder here)
I can replace the computed column using the revised function like this >

ALTER TABLE dbo.IPTest 
DROP COLUMN IPNumber 

ALTER TABLE dbo.IPTest 
ADD  IPNumber AS Common.CLR_IPToInteger(IPString)

Once again, we'll try that Index

CREATE INDEX ix_ipnumber
ON dbo.IPTest (IPNumber)

>

Msg 2798, Level 16, State 1, Line 1

Cannot create index or statistics 'ix_ipnumber' on table 'dbo.IPTest' because SQL Server cannot verify that key column 'IPNumber' is precise and deterministic. Consider removing column from index or statistics key, marking computed column persisted, or using non-CLR-derived column in key.

Nice, a helpful error message telling us EXACTLY what to try. Given I recreated my function using both 'isprecise' and 'isdeterministic' set, I'm missing PERSISTED, i.e. to make the calculated column physically part of the table.

ALTER TABLE dbo.IPTest 
DROP COLUMN IPNumber 

ALTER TABLE dbo.IPTest 
ADD  IPNumber AS Common.CLR_IPToInteger(IPString) PERSISTED

CREATE INDEX ix_ipnumber
ON dbo.IPTest (IPNumber)

Command(s) completed successfully. (that's success to you & me)

Index Links :
MSDN : Creating Indexes on Computed Columns
MSDN : CLR, Computed Columns and Indexability

CLR Links :
Regular Expressions using .NET Common Language Runtime integration in SQL Server 2005
MSDN : CLR Scalar-Valued Functions
MSDN : Managed Data Access Inside SQL Server with ADO.NET and SQLCLR
Solace : Creating and Enabling a CLR Function
Solace : VB.NET / SQL CLR / Decode an encoded URL

VB.NET / SQL CLR / Decode encoded URL

Immersing myself in VB for SQL CLR functionality I found this to Decode an encoded URL.

I've made one minor change from chr$ to chr to make it complient for .NET 3.5

Private Shared Function URLDecode(ByVal txt As String) As String
Dim txt_len As Integer
Dim i As Integer
Dim ch As String
Dim digits As String
Dim result As String

result = ""
txt_len = Len(txt)
i = 1
Do While i <= txt_len
' Examine the next character.
ch = Mid$(txt, i, 1)
If ch = "+" Then
' Convert to space character.
result = result & " "
ElseIf ch <> "%" Then
' Normal character.
result = result & ch
ElseIf i > txt_len - 2 Then
' No room for two following digits.
result = result & ch
Else
' Get the next two hex digits.
digits = Mid$(txt, i + 1, 2)
result = result & Chr(CInt("&H" & digits))
i = i + 2
End If
i = i + 1
Loop

URLDecode = result
End Function

Creating and Enabling a CLR Function.

As a very simple example of CLR integration I create a CLR Assembly function to be called from SQL here.

Prerequisite to this exercise > Enabling CLR Integration on SQL server.

Step 1 : Create a basic class in .NET -

• Start Visual Studio 2005
• File , New , Project
• Select 'Visual Basic' (left hand navigation pane) and 'Class Library' (right hand navigation pane).
• Select OK.

You are presented with a blank class template , which looks like this -


Way too much .net explanation would have to go here, but basic steps are >

• Name your class
• Name and code a function to be called from SQL (i've chosen a Roman Numerals one here).
• Save it!
• Give it a more sensible name -
  Rename ClassLibrary1 to SQL_CLR_Project
  (Do this in Solution Explorer on the right hand side)
• File , Save All

Step 2 : Compile the class to a .DLL file >

Change Project properties -

• Project menu , SQL_CLR_Project Properties
• Change Assembly Name - I chose 'CLRFunctionLibrary' (This will match the .dll name i.e CLRFunctionLibrary.dll will be generated).
• Change the Root Name Space. I changed this to 'SQLCLROne' to match the project.

Compile it -

• Build Menu - 'Build SQLCLROne'

Find the dll just created (CLRFunctionLibrary.dll). -

C:\!RD\Visual Studio 2005\Projects\Class Libraries\SQLCLROne\ClassLibrary1\obj\Debug


Step 3 : Register the assembly within SQL Server -

In TSQL -

CREATE ASSEMBLY [CLRFunctionLibrary]
FROM 'C:\!RD\Visual Studio 2005\Projects\Class Libraries\SQLCLROne\ClassLibrary1\bin\Debug\CLRFunctionLibrary.dll'
WITH PERMISSION_SET = SAFE
GO

These 2 queries verify the Assembly has been created, and will show other assemblies present -

SELECT * FROM sys.assemblies

SELECT * FROM sys.assembly_files

Step 4: Register the method within the assembly. -

CREATE FUNCTION functionName
 (@sqlVariable AS sqlDataType)
RETURNS sqlDataType
AS EXTERNAL NAME Assembly.[NameSpace.ClassName].FunctionName

CREATE FUNCTION dbo.fn_RomanNumeral
 (@number AS INTEGER)
RETURNS NVARCHAR(20)
AS EXTERNAL NAME CLRFunctionLibrary.[SQLCLROne.SQLCLROne].RomanNumeral

Notes :

1. I had to declare the Namespace when calling from the Assembly i.e the declaration became >
CREATE FUNCTION functionName
(@sqlVariable AS sqlDataType)
RETURNS sqlDataType
AS EXTERNAL NAME Assembly.[NameSpace.ClassName].FunctionName


2. 2 Use the type of NVARCHAR not VARCHAR to match with the output STRING declared in vb.net.

Now the function is created, you can see it like this -

SELECT * FROM sys.assembly_modules

and test it like this -

select dbo.fn_RomanNumeral(1066)
select dbo.fn_RomanNumeral(1976)
select dbo.fn_RomanNumeral(2007)

If you experience compatibility issues you can determine which version of .NET is installed by querying the sys.dm_os_loaded_modules DMV (dynamic management view).

SELECT *
FROM sys.dm_os_loaded_modules
WHERE [name] LIKE N'%\MSCOREE.DLL'

TSQL : Show Available CLR Objects

TSQL to show available CLR objects -

SELECT  objects.object_id
  ,schema_name(objects.schema_id) + '.' + objects.[name] AS [CLRObjectName]
  ,objects.type_desc as [CLRType]
  ,objects.create_date as [Created]
  ,objects.modify_date as [Modified]
  ,assemblies.permission_set_desc AS [CLRPermission]
FROM sys.objects objects
INNER JOIN
  sys.module_assembly_usages module_assembly_usages
       ON objects.object_id = module_assembly_usages.object_id
INNER JOIN
  sys.assemblies AS assemblies
       ON module_assembly_usages.assembly_id = assemblies.assembly_id

SQL 2005 : CLR - External Access permissions

Msg 6522, Level 16, State 1, Procedure SP_Backup, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "SP_Backup":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at DatabaseName.dbo.SP_Backup(Parameter1, Parmeter2, Parameter3...)

The CLR stored procedure is using XP_CMDShell to access the file system.

The permissions on the assembly therefore need to be increased to allow this.


Changing assembly permissions in Management Studio -

1) Navigate to (databasename) > Programmability > Assemblies > (expand)
2) Double click assembly name to get assembly properties.
3) Set permission set of assembly to 'external access'.


Changing assembly permissions in tsql -

GRANT EXTERNAL ACCESS ASSEMBLY TO AssemblyName