SQL Server Performance Optimization Index (v)

Import Table structure:

SELECT * Into ProductCategory from AdventureWorksDW2014.dbo.DimProductCategoryselect * to Product from AdventureWorksDW2014.dbo.DimProduct

To turn on disk IO:

Set STATISTICS IO onselect englishproductname,standardcost,color,size,weight from productwhere size> ' M '--0.189 io : 251set Statistics IO off

Non-clustered index:

The statement created:
Create nonclustered index nc_product_size on product (size)

Execute the above query code again (three times times higher):

Set STATISTICS IO onselect englishproductname,standardcost,color,size,weight from productwhere size> ' M '   -- 0.054 io:19set Statistics IO off

To build an overlay index:

Create nonclustered index nc_product_size1 on product (size) include (englishproductname,standardcost,color,weight)

Execute the above statement again:

Set STATISTICS IO onselect englishproductname,standardcost,color,size,weight from productwhere size> ' M '   -- 0.003 io:2set Statistics IO off

The database will automatically select the index:

SQL Server Performance Optimization Index (v)