System.Numerics SQL 2012

Question

I've got an assembly in SQL 2008 R2, it uses Oyster / IntX (from CodePlex.com) to do bitwise on very large varchar fields.. and it's been working pretty well... it's a tiny bit slow..

So I want to change this to native BigInteger class from System.Numerics and use SQL 4.0 / SQL 2012 natively.. I Just having a tiny bit of trouble getting my hands on SQL 2012 CLR information..

I just can't figure out how to get System.Numerics uploaded to SQL Server, I keep on getting this message

Msg 6596, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because assembly 'System.Numerics' is a system assembly. Consider creating a user assembly to wrap desired functionality.

Or maybe my algorithm just sucks, I don't know.. I think that I basically need to reinvent the wheel-- provide my own bitwise operations (because it's not built in).. I've had to do that enough.. If I want to check to see if 4 is in 3213456, then I divide by 8 and see if the result is greater than 4.. I don't know where I came up with that algorithm, but it's working well enough- it's just SLOW.

PS – note, I have created this as a varchar(875), and it supports up toe 875 bits- which is enough for us for the forseeable future – because I want to be able to include it as an index, I’ll probably drop that requirement eventually.

-------------------------------------------------------
Option Explicit On

Imports Oyster.Math.IntX

Public Class Class1
-------------------------------------------------------
Public Function TestClass() As String

Dim X As New Oyster.Math.IntX

Return "Hello World"

End Function
-------------------------------------------------------
Public Function fnIntToCSV(ByVal J As Long) As String

Dim I As Integer
Dim thePower As Long
Dim S As Text.StringBuilder = New Text.StringBuilder


For I = 0 To 30
thePower = 2 ^ I
If J And (thePower) Then
S.Append(I)
S.Append(";")
End If
Next I

Return S.ToString

End Function
-------------------------------------------------------
Public Function fnIntXToCSV(ByVal J As Oyster.Math.IntX) As String

Dim I As Integer
Dim thePower As Oyster.Math.IntX
Dim remainder As Oyster.Math.IntX
Dim S As Text.StringBuilder = New Text.StringBuilder

For I = 0 To 875

If I > 0 Then
thePower = Oyster.Math.IntX.Pow(2, I)
Else
thePower = 1
End If

If I > J Then Exit For
If thePower > J Then Exit For

remainder = J Mod thePower * 2

If remainder >= thePower Then
'strOut = strOut & thePower.ToString & ";"
S.Append(I.ToString)
S.Append(";")
End If

Next I

Return S.ToString

End Function
-------------------------------------------------------
Public Function fnCSVToIntX(ByVal strIn As String) As Oyster.Math.IntX

Dim I As Integer
Dim C As Integer
Dim J As Oyster.Math.IntX = 1
Dim K As Oyster.Math.IntX = 0

Dim str As String()

str = Split(strIn, ";")

C = UBound(str)


For I = 0 To C
'If IsNumeric(Trim(str(I))) = False Then
' GoTo ReturnFailed
'End If

If Len(str(I) & "") > 0 Then
J = Oyster.Math.IntX.Parse(str(I))
K = K + Oyster.Math.IntX.Pow(2, J)
End If
Next

Return K

End Function
-------------------------------------------------------
Public Function fnIntXToBit(ByVal J As Oyster.Math.IntX, BitLocation As Integer) As Boolean

Dim thePower As Oyster.Math.IntX

If BitLocation > 0 Then
thePower = Oyster.Math.IntX.Pow(2, BitLocation)
Else
thePower = 1
End If


If J Mod thePower * 2 > thePower Then
'strOut = strOut & thePower.ToString & ";"
Return 1
Else
Return 0
End If

End Function
-------------------------------------------------------
Public Function fnIntXToBit_Old(ByVal J As Oyster.Math.IntX, BitLocation As Integer) As Boolean

Dim thePower As Oyster.Math.IntX
Dim theRemainder As Oyster.Math.IntX

If BitLocation > 0 Then
thePower = Oyster.Math.IntX.Pow(2, BitLocation)
Else
thePower = 1
End If

If BitLocation > J Then Return 0
If thePower > J Then Return 0

theRemainder = J Mod thePower * 2

If theRemainder > thePower Then
'strOut = strOut & thePower.ToString & ";"
Return 1
Else
Return 0
End If

End Function



End Class

 

Answer 1

Yep, that's the error you get. Not sure what the difference is between this system assembly and System.Net.dll, which catalogs with permission_set = unsafe and a warning that it's untested. But the error suggests that the SQL Server code is looking for specific system assemblies that they won't allow in SQL Server with CREATE ASSEMBLY. That's the first time I've run across that type of assembly (maybe they didn't want folks to confuse System.Numerics.BigInteger with System.Int64, which is "equivalent to" SQL Server's BIGINT data type), I'll see if I can find out why, no promises.

Did you try wrapping the functionality in a user assembly, as the error message suggests? Seems like that wouldn't help, if you need to reference System.Numerics.dll. Or use the equivalent of the System.Numerics.BigInteger class into a UDT (i.e. without relying on their code, reverse engineering would likely be frowned upon ;-)? 

Cheers, Bob