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