question

satya avatar image
satya asked

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.
t-sqlinterview-questions
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What do you mean by 'server variables'? Or is that part of the interview question?
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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!
0 Likes 0 ·
Thank you @kev Riley and @scot Hauder.
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, but we should not use rank functions. By using server varibales we need to generate the unique key. Its an interview question.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you @kev Riley, Is there any server variable which holds the previous result or previous output?
0 Likes 0 ·
Previous result/output of what?
0 Likes 0 ·
Previous query/statement result.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.