How to get results fastly with 'column1+column2=@variable'

Question

I am using SQL Server 2016. I have a table more than 10m rows. When I want to search with the query show here, it is very slow.

CREATE TABLE dbo.table_name
(
    Col1 int NOT NULL,
    Col2 int NULL,
    Col3 char(2) NULL,
    Col4 char(15) NULL,
    Col5 varchar(8) NULL,
    Col6 varchar(12) NULL,
    Col7 varchar(8) NULL,
    Col8 int NULL,
    Col9 int NULL,
    Col10 varchar(16) NOT NULL
) ON PRIMARY

SELECT
    colum3 + column4 
FROM
    tablename 
WHERE
    column3 + column4 = @variable

What index method should I use to speed up this query? (of type varchar in two columns)

Answer 1

Maybe you could consider a computed column and indexing that.

ALTER TABLE dbo.tablename ADD concat_column AS
  CONCAT(column1 + column2);

CREATE INDEX ix_concat_column ON dbo.tablename(concat_column)
  INCLUDE ...

Not sure an index on the separate columns would be of much help, since it would still have to perform the concat on every pair (think 'Rhy' + 'thm' is the same as 'R' + 'hythm').