-> Create below table and supporting index,

create table Table1(
Col1 int,
Col2 varchar(255),
Col3 datetime,
Col4 char(10)
)

create index ix_test on Table1(Col2) INCLUDE(Col1,Col3,Col4)

-> Lets check the table properties. There is no clustered index and Table Table1 is a heap.

-> Insert few rows,

insert into Table1 values (1,'test',getdate(),'test1')
go 1000

-> Lets execute below query with execution plan enabled,

select Col1,Col2,Col3,Col4 from Table1 where Col2='Test'

-> Lets execute below query and see if we can get a table scan,

select Col1,Col2,Col3,Col4 from Table1 with (FORCESCAN) where Col2='Test' 

-> From above screenshot, we see that we have an index scan. But we were looking for a table scan.

-> Lets us check the sysindexes system table using below query and see what we have,

select object_name(object_id) TableName,name,index_id,type,type_desc from sys.indexes where object_id=OBJECT_ID('Table1')

-> Lets force Index_id 0 and see if we can get a table scan,

select Col1,Col2,Col3,Col4 from Table1 with (index(0)) where Col2='Test' 

-> There you go!!! we have a table scan.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.