Create a sample table and insert some rows into it as follows:
CREATE
TABLE
SampleTable
(ID
INT,Name
VARCHAR(100))
INSERT
SampleTable
VALUES(1,'A')
INSERT
SampleTable
VALUES(2,'B')
INSERT
SampleTable
VALUES(3,'C')
INSERT
SampleTable
VALUES(4,'D')
INSERT
SampleTable
VALUES(5,'E')
Now create a Non-Clustered Index either unique or non-unique. For now I'm creating a non-unique index.
CREATE
NONCLUSTERED
INDEX
NON_IX_ID
ON
SampleTable(ID)
Note a point here. I specified in the above statement that I'm creating a Non-Clustered Index. If you don't specify that keyword, Optimizer creates Non-Clustered Index by default.
CREATE INDEX NON_IX_ID ON SampleTable(ID)
This statement is equivalent to previous statement but specifying NONCLUSTERED is a good SQL practice.
Now query data from the table by turning ON the Actual Execution Plan.
SELECT
*
FROM
SampleTable
Now see the Actual Execution Plan, Optimizer performs Table scan instead of performing Index Scan.
Let's try for another alternative by including a query predicate.
SELECT
ID,Name
FROM
SampleTable
WHERE
ID=1
See the Execution Plan, even in this case optimizer performs Table Scan instead of Index Scan.
If you query only indexed column and if you include a predicate for that then Optimizer uses the index. Try running both the below queries.
SELECT
ID
FROM
SampleTable
SELECT
ID
FROM
SampleTable
WHERE
ID=1
See the Execution plan for both the above queries. Optimizer performs Index Scan and Index Seek operations respectively.
We may get surprised why our index is not being used when full table is queried. For that we can force the Optimizer to use our index using a hint. Let's do that now.
SELECT
*
FROM
SampleTable
WITH
(INDEX(NON_IX_ID))
See the Execution Plan now:
As we forced, Optimizer used index but as per its wish it also performed RID Lookup operation in the heap. This means it scanned the whole table along with scanning index. It looked every row in table and every row in Index and then performed Inner Join of both. The matching values are returned. This becomes a burden for optimizer every time we use a forcing hint.
Now let's analyze why Optimizer ignored the index and went for Table Scan. We'll analyze the costs incurred for Optimizer for doing both the operations.
In the above figures, the Cost of Nested Loop belongs to the query where we used hint to use our index. Carefully observe the values we got for I/O cost, Operator Cost and CPU Cost. Sum the costs of Heap, Index Scan and Nested loops and then compare the result value with Table Scan. We can notice that for Table Scan, the costs incurred are lesser. However, the job of Query Optimizer is to find the cheapest execution plan among all the available plans. So it chose to perform Table Scan rather than Index Scan. Here our table is a heap, so it performed RID Lookup. If there is a Clustered Index then Optimizer would perform Key Lookup which scans Clustered Index. The major difference in usage of Clustered Index and Non-Clustered Index is that Clustered Index stores the entire data row in its leaf nodes while Non-Clustered Index stores only the addresses of Data Rows in its leaf nodes.
To have our index used for any kind of querying, we should have INCLUDED COLUMNS for it. I'll write about this concept in my future posts.
Disadvantages of Non-Clustered Index
Non-Clustered Index has disadvantages and some limitations.
Disk Space
As I demonstrated above, for querying whole table, Optimizer performs Table Scan instead of Index Scan. In such cases Index doesn't come to work. This also consumes Disk Space. So before creating index data in your table should be analyzed whether index is needed for it.
Fragmentation
Generally in our computer disks, if we do more file deletions and creation there occurs a fragmentation of disk space. The same is the case with index. If more number of INSERT, UPDATE and DELETE operations are performed on the table with index then it results in Fragmentation of index. This fragmentation causes slow execution of query.
.png)
.png)