How to generate a unique record using server variables ?

Question

create table Data(col1 varchar(5),col2 varchar(5),col3 varchar(5),col4 varchar(5),Primary Key(col1,col2 ))

insert into Data select 'A','1','11','33' union all 
 select 'A','2','21','r3' union all 
 select 'B','1','2a','3g' union all 
 select 'C','1','2','f3'

Now I need to add a col0 to the data table by using generic script that to using server variables which should contain unique value for each value in col1.

for example in first and second rows col1 value is 'A' so for these two rows col0 value should be same let take 1, for third row col1 value is different so for this col0 value shoulbe different

Final table result should be

col0 col1 col2 col3 col4
1 A 1 11 33
1 A 2 21 r3
2 B 1 2a 3g
3 C 1 2 f3

Please help me on this.

Answer 1

0

As an aside, here is something interesting:

SELECT id,d2.*
FROM (SELECT NEWID()[id], col1 
      FROM Data
      GROUP BY col1)d
JOIN Data d2 ON (d2.col1 = d.col1)

Why isn't the id for both A rows the same? NEWID is being evaluated for each row of the outer query?

49F7918C-FB7A-43D6-9746-7FEAE021BD9F A 1 11 33
012BCFA9-D8C3-4BC0-96E3-9EB6F79AE338 A 2 21 r3
EE0454DB-33FC-48DC-9872-36F4711F1D72 B 1 2a 3g
B4B210E8-B964-4211-909E-D88C01A1CB37 C 1 2 f3

The query plan shows the output list from the subquery only contains col1. The compute scalar should not be the final step. I think it is strange but this works:

SELECT id,d2.*
FROM (SELECT MAX(CAST(NEWID() AS char(37)))[id], col1 
      FROM Data
      GROUP BY col1)d
JOIN Data d2 ON (d2.col1 = d.col1)

F6430623-22B3-486A-B7C3-22D9859C4331 A 1 11 33
F6430623-22B3-486A-B7C3-22D9859C4331 A 2 21 r3
BC9DD6DA-8DAE-4C43-88F5-9B8D2C4035F7 B 1 2a 3g
6C56249E-CF83-4057-BDEF-5BBEE763DBA9 C 1 2 f3

answered Aug 19 '12 at 10:32 PM

Scot Hauder
5.7k ● 13 ● 15 ● 18

Yes because you haven't explicitly specified the NEWID() as being 'in' the aggregation, the optimizer thinks it's fine (and probably cheaper) to calculate it outside of the sub query as there are (potentially) fewer invocations - by adding the explicit MAX() or by forcing the materialization (temp table etc) you ensure the behaviour is as you expect.

Good spot - it could catch many people out that one!

Aug 20 '12 at 08:47 AM Kev Riley ♦♦

Thank you @kev Riley and @scot Hauder.

Aug 21 '12 at 03:56 PM satya

(comments are locked)

Answer 2

0

I guess using DENSE_RANK() function is one way of doing it. For e.g.

DECLARE @Data table (col1 varchar(5),col2 varchar(5),col3 varchar(5),col4 varchar(5),Primary Key(col1,col2 ))

insert into @Data select 'A','1','11','33' union all 
 select 'A','2','21','r3' union all 
 select 'B','1','2a','3g' union all 
 select 'C','1','2','f3'

 SELECT DENSE_RANK() OVER (ORDER BY [col1]) col0,* 
 FROM @Data AS D

which gives the desired output.

answered Aug 08 '12 at 05:13 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

Yes, but we should not use rank functions. By using server varibales we need to generate the unique key.

Its an interview question.

Aug 18 '12 at 04:55 AM satya

(comments are locked)

Answer 3

0

If by server variables you mean some value that is specific to that server,e.g. server name then you can use

select cast(@@Servername as varchar(max)) + col1 as col0, * from Data

answered Aug 18 '12 at 07:58 AM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

Thank you @kev Riley,

Is there any server variable which holds the previous result or previous output?

Aug 19 '12 at 06:18 AM satya

Previous result/output of what?

Aug 19 '12 at 08:19 PM Kev Riley ♦♦

Previous query/statement result.

Aug 21 '12 at 03:54 PM satya

(comments are locked)

How to generate a unique record using server variables ?

Follow this question

Related Questions