May 12, 2009 at 2:11 pm
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,
May 12, 2009 at 2:50 pm
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
May 12, 2009 at 2:56 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 9:15 pm
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,
May 12, 2009 at 9:47 pm
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
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 9:57 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 18, 2009 at 2:16 am
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
May 18, 2009 at 3:08 am
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
June 25, 2012 at 10:25 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply