we have below table and index structure. Create table Cust ( Id int, Name varchar(10), Address varchar(10), skno int, tran int, email varchar(10), Notes text ) and we have created below indexes Create clustered index idx_cust_id on cust(id) Create index idx_cust_name on cust(name) Query is: Select name, id,email from cust WHere name = 'value' This will lookup in clustered index for email. Select name, id,email,Address ,skno, tran,notes from cust WHere name = 'value' This will lookup in clustered index for email,,Address ,skno, tran,notes. What is the difference between a key lookup used for referring multiple columns or a key lookup referring single column from resource/performance perspective. Also, we tried solving this problem by adding all the selected columns in include list without the text column as we can't add it in include column. So, do we remove the other columns also from include column list as query will still lookup the clustered index for notes column or it will still increase some performance by adding the other column in the included columns.
Doing a key lookup for one value or twenty is kind of the same. You're going to have to re-traverse the clustered index to get the data you need. In terms of work on the system, it's roughly about the same. You can put the text column in the INCLUDE statement. It's going to make that index quite a lot larger and spread out at the leaf level of the index because of all the extra data. Seeks will be improved, but scans are going to suffer quite a lot. It's a trade-off that you'll have to experiment with to understand which is more painful on your system, the key lookup or the very large index.