Monday, 7 January 2013

Genenerating unique key for 3 columns

Using concatenation of three columns

CREATE TABLE myTable( f1 varchar(10), f2 varchar(10), f3 varchar (10), Keycol as f1+f2+f3)
GO

insert into myTable values(111, 222, 333);
insert into myTable values(111, 222, 333);
insert into myTable values(aaa, bbb, ccc);
insert into myTable values(aaa, bbb, ddd);
insert into myTable values(aaa, bbb, ccc);
insert into myTable values(111, 222, 333);

Result:

f1         f2         f3         KeyCol
-----------------------------------------
111      222      333      111222333
111      222      333      111222333
aaa      bbb     ccc       aaabbbccc
aaa      bbb     ddd     aaabbbddd
aaa      bbb     ccc       aaabbbccc
111      222      333      111222333

Using Checksum function


CREATE TABLE myTable( f1 varchar(10), f2 varchar(10), f3 varchar (10), Keycol as Checksum(f1+f2+f3));

insert into myTable values(111, 222, 333);
insert into myTable values(111, 222, 333);
insert into myTable values(aaa, bbb, ccc);
insert into myTable values(aaa, bbb, ddd);
insert into myTable values(aaa, bbb, ccc);
insert into myTable values(111, 222, 333);

Result:

f1         f2         f3         KeyCol
-----------------------------------------
111      222      333      1239501031
111      222      333      1239501031
aaa      bbb     ccc       2125954685
aaa      bbb     ddd     2125692521
aaa      bbb     ccc       2125954685
111      222      333      1239501031

  
Creating view for the same..

CREATE TABLE myTable( f1 varchar(10), f2 varchar(10), f3 varchar (10));

insert into myTable values(111, 222, 333);

CREATE VIEW v1 as select f1,f2,f3, Checksum(f1+f2+f3) as Keycol from myTable;
SELECT * FROM V1;

f1         f2         f3         KeyCol
-----------------------------------------
111      222      333      1239501031


No comments:

Post a Comment

Post a Comment