Practice #4

A clustered index determines the order in which rows are stored

• You can create only one clustered index per table
• A table without a clustered index is a heap

A non-clustered index stores pointers

• To the row ID of a heap, or the cluster key of a clustered index
• You can create multiple non-clustered indexes on a table

Insert a random data generated by a website, about 1000 data to an existing (new) Table with the following column

SalesOrderID, SalesName, SalesOrderDetailID, OrderQty, ProductID, UnitPrice

Then, we set the statistics of input output to ON using following command

SET STATISTICS IO ON;

Then we try to select the data using following Query

SELECT ProductID, OrderQty
FROM SalesOrderDetail
WHERE SalesOrderID = -- somenumber

Then we can see some Messages that shows how long it takes to get that data shown, and then click the Execution Plan button on the taskbar
Next, we try to Create a Clustered Index to the table

CREATE CLUSTERED INDEX idx_SalesOrderID on SalesOrderDetail(SalesOrderID)

Again, try to run the previous Query

SELECT ProductID, OrderQty
FROM SalesOrderDetail
WHERE SalesOrderID = -- somenumber

Now, you will see a lot of difference in logical reads section, and the Execution Plan will tell you that it uses the Clustered Index

Now we try Querying some other data

SELECT SalesOrderID FROM SalesOrderDetail WHERE ProductID = -- somenumber

But, again, when we see the Messages shows a greater number than before, because we just indexing the SalesOrderID . To optimize this further more, we add a new indexing, that's called Non-Clustered Index by using the following statements

CREATE NONCLUSTERED INDEX idx_ProductID ON SalesOrderDetail(ProductID)

Then, re-run the last query

SELECT SalesOrderID FROM SalesOrderDetail WHERE ProductID = -- somenumber

Then, you will see a more optimized query using Non Clustered indexing system.

Next, we try again another queries using column that has not been indexed

SELECT SalesOrderID, OrderQty FROM SalesOrderDetail WHERE ProductID = -- somenumber

Here, we will see in the execution plan that the query using both Clustered and Non-Clustered Index

So the query will try to Non-Clustered index then use that data to find the appropriate data in Clustered Index then use that info to get the OrderQty

So behind that, there is intelligent query that using both index to reduce reads and optimize the query performance as best as it can.