SQL SERVER – Force Index Scan on Table – Use No Index to Retrieve the Data – Query Hint

Recently I received the following two questions from readers and both the questions have very similar answers.

Question 1: I have a unique requirement where I do not want to use any index of the table; how can I achieve this?

Question 2: Currently my table uses clustered index and does seek operation; how can I convert seek to scan?

First of all, I am not going to analysis their need of why, in fact, they want to convert seek to scan or use no index here. The requirement is strange as using no index or scanning large table may reduce the performance instead of improving it. We are not going to discuss the ‘It Depends’ condition where scan can be better than seek or seek can be better than scan. The User has to take his/her own decision after looking at performance which one is better for its need.

Now, let us see how we can force the use of no index or force a scan operation on clustered index. Scan operation on clustered index is, in fact, scanning the original table. When querying table sys.indexes and closely looking at the column type, it is found to contain various values. Value 0 (Zero) in column type indicates that it is a heap. A heap is defined as a table without index. We will use the value 0 on the table with index to simulate the effect of no index or force index scan where index seek is happening.

If any object has a clustered or nonclustered index on it, it will not have entry of index_id =0. The value 0(Zero) for index_id indicates the heap. You can find further details at sys.indexes.

Now, let us run the following query, and please note the usage of the Index hint of INDEX(0), it will force query to not to use any index or behave like a query on heap. The value which is passed to the function INDEX is the value of index_id.

USE AdventureWorks
GO
SELECT ProductID, ProductNumber, Color
FROM Production.Product
WHERE ProductID < 500
GO
SELECT ProductID, ProductNumber, Color
FROM Production.Product WITH (INDEX(0))
WHERE ProductID < 500
GO

Let us check the execution plan for this query batch, and we should able to see in the second query that the seek is converted to scan. This answers the question of how to convert seek to scan?

Now, once again, let us run the following similar query, where we can see how INDEX(0) is behaving.

USE AdventureWorks
GO
SELECT ProductID, ProductNumber
FROM Production.Product
WHERE ProductNumber = 'CR-7833'
GO
SELECT ProductID, ProductNumber
FROM Production.Product WITH (INDEX(0))
WHERE ProductNumber = 'CR-7833'
GO

It is clear from result that original index usage is replaced with Clustered Index Scan. Also, from the first example, we have seen that how it is forcing scan even though seek is possible. Well, this indicates that the basic index was not used. I would rather say that we get a result that is same as the result when there is no index on the table.

The strong argument here is that even though you use Index(0) or Index(1) in this later example, there is no difference in the result. The logical reasoning we can reach after considering the first example is that if the use of Index(1) was forcing index seek due to any reason, using Index(0) will eliminate the seek part and clustered index will be scanned just like it would have happened in the case of Heap. If table has clustered index, it is ordered in a certain way and it cannot be used like heap after having clustered index on it; however just like heap, the scan is pushed over it.

I want to know if community had ever faced the above two questions and also your take on these. Moreover, I have a gut feeling that there is something missing in the above explanation, and I request you all to help me complete the explanation.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)