|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 11,884,
Visits: 22,833
|
|
Steve created a forum, might as well use it... The one question that none of the DBA's where I work have been able to answer is, why do we need CLR? What's it going to do for us that we couldn't do with TSQL or out in the application code? Has anyone come up with some good answers to this question?
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
|
Say Hey Kid
Group: General Forum Members
Last Login: Friday, November 06, 2009 3:08 AM
Points: 685,
Visits: 93
|
|
i think the purpose for the clr is to blur the divide between the database and the application program.
Everything you can imagine is real.
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 3,419,
Visits: 14,163
|
|
Grant,
Microsoft is just joining the party because Oracle have been running Java runtime in memory since oracle 8i or 9i and a .NET runtime albeit out of process is in development so Microsoft needed to add the .NET runtime in SQL Server. Another thing with LINQ coming in .NET 3.5, like XML before it what is a query could be anything.
Kind regards,
Gift Peddie
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 3:10 PM
Points: 1,244,
Visits: 859
|
|
CLR is there to allow you to do what you can not do in T-SQL. You should always prefer T-SQL over CLR when you can because it performs better. Perhaps in the near future, the CLR implementation will be more refined, but until then, I suggest using it only when you must.
My blog: SQL Soldier
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Program Manager: SQL Server Master Certification Program
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Thursday, September 11, 2008 4:03 AM
Points: 325,
Visits: 36
|
|
I agree with Mr. or Mrs 500 you should always use TSQL when doing data manipulation using DML, you should consider CLR functions when ur not doing DML, but rather thing like string concatenation or string manipulations.
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 6,627,
Visits: 7,334
|
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 11,884,
Visits: 22,833
|
|
Yeah, I've read a lot of that stuff. I was just wondering if anyone had come up with a compelling, real-world, example of using CLR and getting more or better functionality out of it that works better/faster/stronger than simply running the same code on an application server or a client. I have one, very small, example. Itzik Ben-Gan shows how to use CLR & common expressions available in .NET languages to clean all the parameters off of stored procedure calls in a trace file. It's nice, but it's hardly a good reason to run and spend time honing C# skills.
----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for Red Gate Software
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, February 15, 2009 2:20 PM
Points: 4,
Visits: 61
|
|
| I have used the CLR and extended stored procedures for a couple of reasons. They both are "Transaction related". I have code that allows me to log SQL execution without being affected by a rollback. The other is to do processing that was in a transaction where I needed to process external data while still having the ability to rollback the transaction if there was an error while doing so.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, July 06, 2007 3:11 AM
Points: 145,
Visits: 1
|
|
As Itzik also pointed out the CLR comes in handy if for calculations; things that have nothing to do with database access, but nevertheless need to be done in the db and not on the client. Probably we will not benefit from this in stored procedures and triggers, but I can imagine it in UDFs and maybe there exists a business case for CLR datatypes.
We are using a financial database with check digits for standard codes like ISIN and SEDOL. We need to implement check constraints that enforce them. I have implemented them both in TSQL and vb.net. I tested the checks in a loop and here we see a significant improvement: depending on the algorithm, the .net version runs twice to three times as fast.
Algorithm times CLR TSQL Perc
Sedol 1000 46 80 57,50
10000 376 810 46,42
100000 3726 8160 45,66
Isin 1000 60 156 38,46
10000 534 1826 29,24
100000 5380 16380 32,84
Of course for all data access I would certainly use TSQL.
The code: (the explanation of the algorithms can be found at Wikipedia.org)
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jan DV
-- Create date: 8/05/2007
-- Description: Check validity of an SEDOL code
-- Examples: 6870401, B06G5D9, 5330047
-- dbo.fn_IIM_IsValidSedol('B06G5D9') = 1
-- dbo.fn_IIM_IsValidSedol('6870401') = 1
-- =============================================
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fn_IIM_IsValidSedol' AND type = 'FN')
DROP FUNCTION dbo.fn_IIM_IsValidSedol
GO
CREATE FUNCTION fn_IIM_IsValidSedol
(
-- ISIN code to be verified
@sSedol char(7)
)
RETURNS int
AS
BEGIN
-- weight factor: 131739
DECLARE @sArrWeighting char(6)
DECLARE @nPos smallint
DECLARE @n smallint
DECLARE @nSum int
DECLARE @nCheckDigit smallint
DECLARE @nReturn int
-- check if datalength = 7
IF IsNumeric(RIGHT(@sSedol,1)) = 1
SELECT @nCheckDigit = CONVERT(smallint,RIGHT(@sSedol,1))
ELSE
SELECT @nCheckDigit = -1
SELECT @sSedol = UPPER(@sSedol), @nPos = 1, @nSum = 0
SELECT @sArrWeighting = '131739'
WHILE @nPos < 7
BEGIN
SELECT @n =
CASE WHEN ISNUMERIC(SUBSTRING(@sSedol,@nPos,1)) = 1
THEN CONVERT(smallint,SUBSTRING(@sSedol,@nPos,1))
ELSE ASCII(SUBSTRING(@sSedol,@nPos,1)) - 55
END
SELECT @nSum = @nSum + (@n * CONVERT(int,SUBSTRING(@sArrWeighting,@nPos,1)))
SELECT @nPos = @nPos + 1
END
-- return if checkdigit is correct or not
IF ((10 - (@nSum % 10)) = @nCheckDigit) SELECT @nReturn = 1 ELSE SELECT @nReturn = 0
RETURN @nReturn
END
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jan DV
-- Create date: 23/04/2007
-- Description: Check validity of an ISIN code
-- Examples: US0378331005, AU0000XVGZA3
-- dbo.fn_IIM_IsValidIsin('AU0000XVGZA3') = 1
-- dbo.fn_IIM_IsValidIsin('AU0000XVGZA3') = 1
-- =============================================
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'fn_IIM_IsValidIsin' AND type = 'FN')
DROP FUNCTION dbo.fn_IIM_IsValidIsin
GO
CREATE FUNCTION fn_IIM_IsValidIsin
(
-- ISIN code to be verified
@sIsin char(12)
)
RETURNS int
AS
BEGIN
DECLARE @nCheckDigit smallint
DECLARE @nCounter smallint
DECLARE @nASCII smallint
DECLARE @sArrNumbers varchar(24)
DECLARE @sChar char(1)
DECLARE @nEven smallint
DECLARE @nTotal int
SELECT @nCheckDigit = CONVERT(smallint,RIGHT(@sIsin,1))
-- convert chars to numeric, with an offset of 10
SELECT @sIsin = Upper(@sIsin), @nCounter = 1, @sArrNumbers = ''
WHILE @nCounter < 12 -- do not include the checkdigit
BEGIN
SELECT @sChar = SUBSTRING(@sIsin, @nCounter, 1)
SELECT @nASCII = ASCII(@sChar)
IF @nASCII > 64 AND @nASCII < 91
SELECT @sArrNumbers = @sArrNumbers + CONVERT(varchar(2), @nASCII - 55) -- offset 10
ELSE
SELECT @sArrNumbers = @sArrNumbers + @sChar
SELECT @nCounter = @nCounter + 1
END
-- if odd, then double the first numbers; else 2nd list.
SELECT @nEven = 1 - (DATALENGTH(@sArrNumbers) % 2)
SELECT @nCounter = 1, @nTotal = 0
WHILE @nCounter <= DATALENGTH(@sArrNumbers)
BEGIN
-- if a number has 2 digits, sum them seperately. Multiply the correct column. @nEven is used to shift the multiplication position.
SELECT @nTotal = @nTotal +
(((CONVERT(int, SUBSTRING(@sArrNumbers, @nCounter, 1)) * (((@nCounter + @nEven) % 2) + 1)) % 10) +
ROUND(CONVERT(int, SUBSTRING(@sArrNumbers, @nCounter, 1)) * (((@nCounter + @nEven) % 2) + 1) / 10,0))
SELECT @nCounter = @nCounter + 1
END
-- return if checkdigit is correct or not
IF ((10 - (@nTotal % 10)) = @nCheckDigit) SELECT @nTotal = 1 ELSE SELECT @nTotal = 0
RETURN @nTotal
END
The CLR code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
_
Public Shared Function fn_IIM_CLRCheckSedol(ByVal sSedol As SqlString) As SqlBoolean
Dim arWeighting() As Byte = {1, 3, 1, 7, 3, 9}
Dim arSedol(5) As Byte
Dim nCheckDigit As Byte
Dim nTotal As Byte = 0
Try
If IsNumeric(sSedol.ToString) And Len(sSedol.ToString) = 7 Then
Dim n As Byte
For n = 1 To 6
arSedol(n - 1) = CByte(Mid(sSedol.ToString, n, 1))
Next
nCheckDigit = CByte(Mid(sSedol.ToString, 7, 1))
' Sum the result
For n = 0 To 5
nTotal += (arSedol(n) * arWeighting(n))
Next
'Return true if there is a match with the checkdigit
Return (10 - (nTotal Mod 10)) = nCheckDigit
Else : Return False 'Sedol passed should be numerical
End If
Catch
Return False
End Try
End Function
End Class
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
_
Public Shared Function fn_IIM_CLRCheckISIN(ByVal spIsin As SqlString) As SqlBoolean
If Len(spIsin.ToString) = 12 Then
Dim arrNumbers(22) As Integer 'ISIN is 12 char, 22 is the max size the array can become because there is one checkdigit
Dim sIsin As String
Dim nPos As Integer = 1
Dim nArrPos As Integer = 0
Dim sChar As String
Dim nValue As Integer = 0
Dim nOdd As Integer = 0
Dim nCheckDigit As Integer
Try
'Make sure that all ascii values are in upper range
sIsin = spIsin.ToString.ToUpper
nCheckDigit = CInt(Mid(sIsin, 12, 1))
For nPos = 1 To 11
sChar = Mid(sIsin, nPos, 1)
If IsNumeric(sChar) Then
arrNumbers(nArrPos) = CInt(sChar)
nArrPos += 1
Else
nValue = Asc(sChar) - 55
If nValue > 9 Then
arrNumbers(nArrPos) = (nValue \ 10)
arrNumbers(nArrPos + 1) = (nValue Mod 10)
nArrPos += 2
Else
arrNumbers(nArrPos) = nValue
nArrPos += 1
End If
End If
Next
'If # elements are odd, multiply odd values by 2 else even values.
nOdd = (nArrPos Mod 2)
For nPos = 0 To (nArrPos - 1)
arrNumbers(nPos) = arrNumbers(nPos) * (((nPos + nOdd) Mod 2) + 1)
Next
' Add up the individual digits
nValue = 0
For nPos = 0 To nArrPos - 1
If arrNumbers(nPos) < 10 Then
nValue += arrNumbers(nPos)
Else
nValue += (arrNumbers(nPos) \ 10)
nValue += (arrNumbers(nPos) Mod 10)
End If
Next
'Return True if checkdigit matches
Return (10 - (nValue Mod 10) = nCheckDigit)
Catch
Return False
End Try
Else
Return False 'Lenghth of ISIN code is not 12
End If
End Function
End Class
And the test:
DECLARE @dStart datetime
DECLARE @dStop datetime
DECLARE @n int
DECLARE @bResult bit
SELECT @dStart = Getdate()
SELECT @n=0
WHILE @n < 100000
BEGIN
SELECT @bResult= dbo.fn_IIM_CLRCheckSedol('0263494')
SELECT @bResult= dbo.fn_IIM_CLRCheckSedol('A263494')
SELECT @n = @n + 1
END
SELECT @dStop = getdate()
SELECT datediff(ms, @dStart, @dStop) -- 46 ms in CLR version
--- Test ISIN
DECLARE @dStart datetime
DECLARE @dStop datetime
DECLARE @n int
DECLARE @bResult bit
SELECT @dStart = Getdate()
SELECT @n=0
WHILE @n < 100000
BEGIN
SELECT @bResult= dbo.fn_IIM_IsValidIsin('BE0003788057')
SELECT @bResult= dbo.fn_IIM_IsValidIsin('AU0000XVGZA3')
SELECT @n = @n + 1
END
SELECT @dStop = getdate()
SELECT datediff(ms, @dStart, @dStop)
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 3:10 PM
Points: 1,244,
Visits: 859
|
|
The lead developer at my former company had downloaded some canned code off of some website that used the CLR to access the Windows API for time zone information which gave us nice little functions that converted customer's time zones to our local time and back. They were nice little functions that slowed down the entire system because every time someone converted a time, they had to access the CLR which in turn accessed the Windows API. It also caused a memory leak when the system was under a heavy load that would silently kill connections and then eventually restart the SQL Service. I replaced it with a process that imports the same time zone info using xp_instance_reg_read once a day at 4 AM and then uses the data in the TimeZones table to make the same conversions without slowing anything down and without causing a memory leak. There's one real life example where the CLR should not have been used.
My blog: SQL Soldier
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Program Manager: SQL Server Master Certification Program
|
|
|
|
