SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using CLR function


Using CLR function

Author
Message
naveenreddy.84
naveenreddy.84
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 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,
Ale Pelc
Ale Pelc
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1359 Visits: 585
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
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36198 Visits: 9518
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."
naveenreddy.84
naveenreddy.84
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 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,
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36198 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36198 Visits: 9518
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."
naveenreddy.84
naveenreddy.84
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 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
Florian Reischl
Florian Reischl
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9173 Visits: 3934
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
sudevpdas
sudevpdas
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 186
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search