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 1Column '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 1Cannot 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