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

 

 

It works