I having a non-clustered indexes created in my SQL database server with Include keyword.
Please find my non-clustered index created for FACTORS table.
CREATE NONCLUSTERED INDEX [FACTORS_BKEY_PNO_IDX] ON [dbo].[FACTORS]
(
[BATCH_KEY] ASC,
[PART_NO] ASC
)
INCLUDE([FACTOR_NAME],[FACTOR_VALUE],[FACTOR_NAME_ETL],[INDUSTRY],[PROGRAM_ID],[PROGRAM_NAME]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
if am having a SELECT query like below will my indcxes FACTORS_BKEY_PNO_IDX cover my select query. ( i mean will this index will help my below select query to retrieve records faster or do i need to create another indexes with exact match of INCLUDE fields).
select BATCH_KEY , FACTOR_NAME , FACTOR_VALUE , INDUSTRY PART_NO , CREATED_TS , FACTOR_NAME_ETL , PROGRAM_ID , PROGRAM_NAME from FACTORS where BATCH_KEY and PART_NO order by PART_NO, FACTOR_NAME, FACTOR_VALUE
Advertisement
Answer
As the non-clustered index does not include all the fields in your SELECT query (e.g., it doesn’t have Industry) then it is not a covering index. Unfortunately, it will need to get that extra data from elsewhere e.g., by going back to the original table.
Indexes can help in two main ways
- a covering index which has all the columns you need for a query – where it can reduce the amount of data to be read
- pre-sorting the data allowing an index seek to occur
The fastest is when both of these occur e.g., it is sorted appropriately and it is a covering index. In your example, it looks like the index’s main fields are appropriate, but it isn’t a covering index.
The pre-sorts can still help even if it isn’t a covering index e.g., it uses the index to determine which rows to go back to the table to get the rest of the data from. It uses the index much like you would an index in a book – e.g., you use the index to find the relevant pages in the book, then go and read each page to get the information you need. In these cases, a smaller index (e.g., on just batch_key and part_no, no other fields) will function in the same way as the broader index you have. Indeed, a smaller index would be marginally better as it has less data to read (2 columns vs 8): because it has to go back to the table anyway to get the data; it may as well get the other columns from there too.
However, if the index indicates there are just too many rows for this, often the SQL engine will just read the whole table at once as if there wasn’t an index on there already.
Brent Ozar does a really good video on this called How to think like an SQL Server engine – given you’re using dbo. I’m guessing you’re using SQL Server. I strongly recommend watching it as I learned a lot about indexes etc from it, and you seem to have a good base level of knowledge to also gain a lot.
(PS – I’m guessing that the where BATCH_KEY and PART_NO is just a typo).