August 19, 2011 at 5:02 am
ok. i got it...
it apply the AND logic not the binary addition logic.
0 0 0
0 1 0
1 0 0
1 1 1
1 --> 01
3 --> 11
01 = 1
2 --> 10
3 --> 11
10 = 2
3 --> 11
3 --> 11
11 = 3
4 --> 100
011
000 = 0
right?
But..how you know that we have to use "3" ?
Incase if 6 values come in the delimeter, which number do you use?
say for example,
create table W
(
ID int,
acc_no varchar(5),
val varchar(255)
)
insert into W
select 100,'a1','D,20987987.5%,2.4543%,2323,CREDIT,A account is being linked to B account'
union all
select 100,'a2','D,20987987.5%,2.4543%,2323,CREDIT,A account is being linked to B account'
union all
select 100,'b1','R,2.5%,2.4543%,2323,CREDIT,A account is being linked to B account'
union all
select 100,'b2','R,2.5%,2.4543%,2323,CREDIT,A account is being linked to B account'
union all
select 100,'b3','R,25%,2.4543%,2323,CREDIT,A account is being linked to B account'
union all
select 100,'c1','X,25%,2.4543%,2323,CREDIT,A account is being linked to B account'
union all
select 100,'c2','X,25%,2.4543%,2323,CREDIT,A account is being linked to B account'
Which number I have to use in the UPDATE?
karthik
August 19, 2011 at 7:11 am
karthikeyan-444867 (8/19/2011)
...But..how you know that we have to use "3" ?
...
You are right it's all to do with binary math and magic of some numbers in binary represented in binary system.
My first programming language is Assembly for IBM360/380 and I used to use punch cards (while studing and I do still keep box of them - they are perfect as bookmarks :hehe:). So, I still remember few things from binary math, also I was quite good in general math at school:-D
It will not work for all numbers. You can do this trick if you need to split 4, 8, 16, 32, etc items (numbers which binary representation has only one '1' eg. 4 - 100, 8 - 1000 etc.) using 3, 7, 15, 31, etc numbers (you can see that their binary representation contains only '1' eg. 3 - 111, 7 - 111 etc.). Basically applying "binary ADD" in the above cases we always guarantee consitent bit-shifting.
Honestly, you should use Jeff's method which will work for in all cases and is clear enough for all people 😉
August 19, 2011 at 7:42 am
EE,
Really you opened my eyes broadly...Thanks a lot...
do you mean if we have only 1 i.e
(only single '1')
10
100
1000
10000
(all '1')
1
11
111
1111
I feel little bit hard to grasp all your points.
what is the relationship between these two combination? could you please explain it?
I have compared the performance of your code and Jeff's code. it is identical.
create table #w
(
idn int IDENTITY,
id int
)
declare @i int
select @i = 1
while @i < 100000
begin
insert into #w
select 1
select @i = @i + 1
end
UPDATE #w SET id = idn % 4
Table '#w__________________________________________________________________________________________________________________000000000DBE'. Scan count 1, logical reads 221, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
UPDATE #w SET id = case when idn & 3 = 0 then 8 else idn & 3 end
Table '#w__________________________________________________________________________________________________________________000000000DBE'. Scan count 1, logical reads 221, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Query plan is also looks identical.
karthik
August 19, 2011 at 7:59 am
EE,
I am sorry..how should i call you? Eugene? thats why i used EE...
we will come out from this requirement and see something related to sequence number generation....
create table acc
(
id int,
acc varchar(255)
)
insert into acc
select 1, 'a1'
union
select 1, 'a2'
union
select 1, 'a3'
union
select 2, 'b1'
union
select 3, 'c1'
union
select 3, 'c2'
union
select 3, 'c3'
union
select 3, 'c4'
union
select 3, 'c5'
union
select 4, 'd1'
union
select 4, 'd2'
union
select 5, 'e2'
is it possible to generate the sequence number without using ROW_NUMBER () ? I mean can i use your method to generate the sequence number based on id, acc. I have not yet tried. I am curious to know whether i can apply your method. is there any other method available apart from ROW_NUMBER()?
karthik
August 19, 2011 at 8:27 am
karthikeyan-444867 (8/19/2011)
...I feel little bit hard to grasp all your points.
what is the relationship between these two combination? could you please explain it?
...
I have compared the performance of your code and Jeff's code. it is identical.
Query plan is also looks identical.
The first one for the second :-D: Both queries doing single update of the same column for all rows, so it's expected that query plan is the same. I don't know what will it be any difference in execution time if you run it on millions of records. Yes, I do use "case when", but logical AND is very cheap operation (as its using basic binary operation the same as for any + or -), Modulo operation (%) is more expensive as its need to use binary multiplication, so it may come slower. But most likely we are talking nanoseconds...
For the first your question...
I don't know how I can explain it in more details.
Look, all counting system have own tricks, for example in our common decimal system you may find magic of 3 and 7 numbers. So, take it as magic of binary system where bitwise operations (AND, OR XOR) are natural and allows to do for some numbers what I've done. If you want to learn a bit more you can look into wiki http://en.wikipedia.org/wiki/Binary_numeral_system
August 19, 2011 at 9:03 am
karthikeyan-444867 (8/19/2011)
...is it possible to generate the sequence number without using ROW_NUMBER () ? I mean can i use your method to generate the sequence number based on id, acc. I have not yet tried. I am curious to know whether i can apply your method. is there any other method available apart from ROW_NUMBER()?
Oops, you've posting too fast again ... :hehe:
I don't mind EE or Eugene both are fine 😀
Sorry, I do not understand what you mean by "generate the sequence number based on id, acc".
Do you want to generate sequence number and partition them by id in order of acc's?
Some thing like:
Id Acc SeqNo
1 a1 1
1 a2 2
1 a3 3
2 b1 1
3 c1 1
3 c2 2
If yes, my method has nothing to do with it, in SQL2005 and higher you can use ROW_NUMBER with PARTITION BY, in previous versions you can use one of the methods for calculating "runnign total" (quirky update is one of the Jeff Moden's faivoured one, you can find good article about it on this site
http://www.sqlservercentral.com/articles/T-SQL/68467/)
For the above example you could use "Triangular Join" method, but please note: it's not very good one in terms of performance for large datasets...
create table acc ( id int, acc varchar(2))
insert acc select 1, 'a1'
insert acc select 1, 'a2'
insert acc select 1, 'a3'
insert acc select 2, 'b1'
insert acc select 3, 'c1'
insert acc select 3, 'c2'
select acc.id, acc.acc,
(select SUM(1) from acc a1 where a1.id = acc.id and a1.acc <= acc.acc) as SeqNo
from acc
August 19, 2011 at 1:12 pm
Eugene Elutin (8/19/2011)
Jeff Moden (8/19/2011)
That's because someone made it difficult. See if you understand the following modifications...Not at all...
It was because someone wanted to turn 1,2,3,4,5,6,7,8,... into 1,2,3,4,1,2,3,4,...
not 0,1,2,3,4,5,6,7,... into 0,1,2,3,4,5,6,7,... 😀
If I'd used "simple" RN % 4, it would risk not to catch the attention of OP. :hehe:
Seriously, I just copied it from query I've written a day ago where records from staging table (populated by existing production process which I couldn't change) was required to be renumbered in exactly this way from 1-based identity. It is just pure coincidence..
Anyway, I guess OP works for very rich and and in the same time adventures organisation, as support for SQL2000 from MS is now cost quite a lot and not guaranteed in many aspects...
Ah... my apologies, Eugene. :blush: I didn't mean it to be a slight on anyone. It came out totally wrong.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2011 at 12:56 am
Incase if 6 values comes with the delimeter, I have no choice apart from ROW_NUMBER() PARTITION BY or SELECT INTO WITH IDENTITY , UPDATE WITH MODULO DIVISION ( UPDATE #t SET RN = idn % 6). right?
karthik
August 22, 2011 at 4:09 am
Yes, you are right. It is a best technique for this sort of task.
August 22, 2011 at 4:19 am
Eugene,
My hearty thanks to you for teaching me some good things 🙂 Thanks a lot..will meet you again in some other interesting thread...
karthik
August 22, 2011 at 4:21 am
Jeff,
I always learn something new from your reply(for my thread). I learnt something new from this post too. Thanks a lot. 🙂
karthik
August 28, 2011 at 5:18 pm
You bet, Karthik... thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply