x

How to generate a unique record using server variables ?

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.
more ▼

asked Aug 07, 2012 at 06:08 PM in Default

satya gravatar image

satya
361 18 18 22

What do you mean by 'server variables'? Or is that part of the interview question?
Aug 18, 2012 at 07:46 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

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
more ▼

answered Aug 19, 2012 at 10:32 PM

Scot Hauder gravatar image

Scot Hauder
6k 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, 2012 at 08:47 AM Kev Riley ♦♦
Thank you @kev Riley and @scot Hauder.
Aug 21, 2012 at 03:56 PM satya
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Aug 08, 2012 at 05:13 AM

Usman Butt gravatar image

Usman Butt
13.9k 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, 2012 at 04:55 AM satya
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 18, 2012 at 07:58 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

Thank you @kev Riley,

Is there any server variable which holds the previous result or previous output?
Aug 19, 2012 at 06:18 AM satya
Previous result/output of what?
Aug 19, 2012 at 08:19 PM Kev Riley ♦♦
Previous query/statement result.
Aug 21, 2012 at 03:54 PM satya
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x15

asked: Aug 07, 2012 at 06:08 PM

Seen: 881 times

Last Updated: Aug 21, 2012 at 03:56 PM