sql server - join multiple nvarchar columns -

i have table this:

create table [dbo].[table](     [id] [int] identity(1,1) not null,     [a] [nvarchar](150) null,     [b] [nvarchar](150) null,     [c] [nvarchar](150) null,     [d] [nvarchar](150) null,     [e] [nvarchar](150) null,  constraint [con] primary key clustered  (     [id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]  go 

and performance inprovements join table.

option 1 - combine string nvarchar primary key , do:

source.[a] + source.[b] + source.[c] + source.[d] + source.[e] = table.pkstring 

to knowledge bad practice.

option 2 - use:

source.[a] + source.[b] + source.[c] + source.[d] + source.[e] = target.[a] + target.[b] + target.[c] + target.[d] + target.[e] 

option 3 - use:

source.[a] = target.[a] , ... source.[e] = target.[e] 

your option 1 won't work correctly treat ('ab','c') equal ('a','bc').

also columns nullable , concatenating null yields null.

you can't combine columns nvarchar primary key due nullability , without still @ risk of failure max length 1,500 bytes on max index key column size.

for similar reasons of length composite index using columns wouldn't work.

you create computed column uses 5 column values input calculate checksum or hash value , index however.

alter table [dbo].[table]   add hashvalue cast(hashbytes('sha1', isnull([a], '') + isnull([b], '')+ isnull([c], '')+ isnull([d], '')+ isnull([e], '')) varbinary(20));   create index ix   on [dbo].[table](hashvalue)   include ([a], [b], [c], [d], [e])  

then use in join residual predicate on other 5 columns in case of hash collisions.

if want null compare equal use

select *   [dbo].[table1] source        join [dbo].[table2] target          on source.hashvalue = target.hashvalue             , exists(select source.a,                               source.b,                               source.c,                               source.d,                               source.e                        intersect                        select target.a,                               target.b,                               target.c,                               target.d,                               target.e)  

note index created above reproduces whole table might want consider creating clustered instead if queries need covering.