Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using CLR function Expand / Collapse
Author
Message
Posted Tuesday, May 12, 2009 2:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 5:55 AM
Points: 56, Visits: 287
Hi All,
I have a Table1 containing bit values '0' and '1'.In need to combine this column value to a bit array say 11011 and perform a bitwise OR operation aganist a column in another table2 which stores correspoding bit sequence in varchar data type variable.

I can achieve the same through .net code. I,e creating a bit array from the column values of Table1 and store it into a bitarray type variable. And performing the bitwise operation aganist the another table's column.

Kindly let me know if there is a need for creating CLR function or is there a way to achieve the same Sql Server.

Regards,
Post #715449
Posted Tuesday, May 12, 2009 2:50 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 11, 2014 10:12 AM
Points: 1,109, Visits: 545
Hi,
If I correctly understood what you want to achieve, I think you can perform that using SQL, which I think will be better than deploying a CLR.

declare @value1 bit
declare @value2 varchar(10)
set @value1 = 0
set @value2 = '10001'
select @value1|cast(@value2 as int)

The result for that will be 10001.

Cheers


Alejandro Pelc
Post #715479
Posted Tuesday, May 12, 2009 2:56 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
The arithmetic and logical expressions to do this are pretty easy (it's "Power(..)", "|" and "+" mostly), however, what not clear is where the multiple bits are coming from in your example and how they should be orded and aligned. If you could provide a small example (say 3 or more bits) of the source table and rows and the desired result, I am sure that we can give you a good starting example.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #715486
Posted Tuesday, May 12, 2009 9:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 5:55 AM
Points: 56, Visits: 287
Hi,

My table1 consist of below values

ResID status(bit data type)
1 1
2 0
3 1

Table2 contains

Rule Rvalues(bit value stored as a string) status(bit)
1 101 0
2 110 1
3 001 0


In need to create an array of bits from the first table in a particular sequence order(asc order of ResID) i,e 101. This value i need to compare with each value in Table2 by performing the bitwise OR operation and the get the status from the table2. In our case final value i will get is status=0.

the values in table1 may increase to 100 as well.

This is summary of things that needs to be done:
1) Creating an array of bits.
2) Retrieving the values from table2 one by one in a butarray format.
3) Performing the bitwise OR operation.
4) fetch the exact status value from table2

Further, also let me know in which scenario the CLR based stored proceduers, triggers etc implemention are used.

Regards,
Post #715608
Posted Tuesday, May 12, 2009 9:47 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Well it still seems like something is missing, but this code does what you have specified:
;WITH cteBinary as (
Select
SUM(
Case [status]
When 1 Then Power(2, ResID)
Else 0 End
) as [Bitmask]
From Table1
)
SELECT
t2.Rvalue | b.BitMask as [Result]
, [status]
From Table2 t2
Cross Join cteBinary b



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #715617
Posted Tuesday, May 12, 2009 9:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
naveenreddy.84 (5/12/2009)
Further, also let me know in which scenario the CLR based stored proceduers, triggers etc implemention are used.

The three general cases for SQLCLR use are:

1) Access to external resources not accessible from T-SQL.

2) To insure a common codebase is used on both client and server.

3) When the computation per column or byte is fairly high.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #715625
Posted Monday, May 18, 2009 2:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 5:55 AM
Points: 56, Visits: 287
1) What scenario is best suited for implementing CLR triggers?
2) What are things that needs to be taken care of while implementing the CLR tiggers

Post #718863
Posted Monday, May 18, 2009 3:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Hi

naveenreddy.84 (5/18/2009)
1) What scenario is best suited for implementing CLR triggers?

As Barry already wrote, accessing external resources like a HTTP post/request.

2) What are things that needs to be taken care of while implementing the CLR tiggers

Threading



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #718891
Posted Monday, June 25, 2012 10:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 6, 2014 2:54 AM
Points: 29, Visits: 165
Hi,

I am exepriencing performance issues while performing bitwise operation in sql server. I am planning to use a .net clr function for the same. what are your thoughts on that?.

Thanks,
Sudhev Das
Post #1320999
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse