virtual column make sqlserver using function index

In sqlserver, it is impossible that if we want to create an function index. Doesn`t means we can not ?

Father said there always a way. That is virtual column.

Here is an exmple:

Sample 1

create table #checkdistribute ([CELL_ID] [varchar](20) NOT NULL)

create table #checkdistribute1 ([CELL_ID1] [varchar](2) NOT NULL)

INSERT #checkdistribute (CELL_ID) select CELL_ID from T with(nolock)

create NONCLUSTERED INDEX IDX_checkdistribute_cellid on #checkdistribute (CELL_ID asc)

INSERT #checkdistribute1 (CELL_ID1) select SUBSTRING(CELL_ID,1,2) from #checkdistribute

Although we generated an index but it still can not be use,. Because , Yes, SUBSTRING

Yes, Tables Scan.

Just wait. we also have a hope.

Sample 2

create table #checkdistribute ([CELL_ID] [varchar](20) NOT NULL,[CELL_ID_F] AS SUBSTRING(CELL_ID,1,2) )

create table #checkdistribute1 ([CELL_ID1] [varchar](2) NOT NULL)

INSERT #checkdistribute (CELL_ID) select CELL_ID from T with(nolock)

create NONCLUSTERED INDEX IDX_checkdistribute_cellid on #checkdistribute ([CELL_ID_F] asc)

INSERT #checkdistribute1 (CELL_ID1) select CELL_ID_F from #checkdistribute

Yes, [CELL_ID_F] AS SUBSTRING(CELL_ID,1,2), that is virtual column. It can makes using index