Index "INDEX" on table "TABLE" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

When I try to execute a simple SELECT statement, It's really slow and I found that the table performed TABLE SCAN.

But, The table also have a Nonclustered Index on it.

So, I try to force query optimizer to use the Index.

Select * from Tb_Sample with (Index =NCI_Id)
Go

oh. I got an error...

Msg 315, Level 16, State 1, Line 1
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

The reason behind are..
1. The Index (NCI_Id) may be DISABLED.
2. The Filegroup in OFFLINE(Where the actual Index resides).

1. The Index (NCI_Id) may be DISABLED(Scenario)

/*Disabling Index*/
Alter Index NCI_Id on Tb_Sample DISABLE
Go

/*Forcing Index*/
Select * from Tb_Sample with (Index =NCI_Id)
Go

It througs an error...
Msg 315, Level 16, State 1, Line 2
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

2. The Filegroup in OFFLINE(Where the actual Index resides)(Scenario)

/*Creating Index on different filegroup: IndexFilegroup*/
Create NonClustered Index NCI_Id on Tb_Sample(Id) on [IndexFilegroup]
Go
- File(Indexes) belongs to [IndexFilegroup] filegroup

/*Setting OFFLINE a particular file, which belongs to [IndexFilegroup] filegroup*/
Alter Database DatabaseName Modify File (Name=[Indexes],OFFLINE)
Go

Caution: It makes all the objects in the filegroup inaccessible, Only use when you have valid backup, Proper understanding/guidance

/*Forcing Index*/
Select * from Tb_Sample with (Index =NCI_Id)
Go

Now also, It througs an error...
Msg 315, Level 16, State 1, Line 2
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

So, Index can not be forced the query optimizer when the index in DISABLED or The filegroup is OFFLINE where the Index resides.

/*To Enable the DISABLED Index*/
Alter Index NCI_Id on Tb_Sample Rebuild
Go

/*To set ONLINE the Filegroup again*/
A file set to OFFLINE can only be set ONLINE by restoring the file from the backup..