SQL Search performance in 2 columns vs combined 1 column

Question

Using an MSSQL Server stored procedure, short table structure is

Id int, --PK
Code varchar(20),   --Indexed column
Oems varchar(max)   --Some rows will have thousands of oems

There are 500K records, every row has a Code. 400k rows have Oems. Sample data

Id   Code         Oems
-------------------------------------
1   ARD5612     ,7171833,B716382,324324,2423423,GB23434,00002334
2   80001627    ,99901811,1727282,...    

Current condition is (find all and only beginning of the key must match)

WHERE code LIKE @Key + '%' OR Oems LIKE '%,' + @Key + '%'

Key = 1234
Code = 123456 Find
Code = 001234 don’t find
Oems = ,12340000,kh77,062483 find
Oems = ,7777,5262,abc723,x1234 don’t find

So, my question is that should I combine these two columns and make the search over one single column or leave it as it is?

Does having an index on the Code column have a positive affect on search at all?

Thank you

Edit: As many suggested, if I convert Oems into a new table, that table will have 15M records. How is the new design will help performance?

Thanks

Answer 1

Thanks everyone. I inserted comma separated values into a new table with one value in each row, added an index and it worked perfectly.