asked
Key Lookup
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.
Thanks Grant Sir for answering, However, SQL server won't allow text column in include in index definition. My concern is if Query optimizer will have to lookup the clustered index for the columns used in selection and selection include a text datatype column that can't be put in the include column. So even putting the other columns in the include list will not benefit as it is still looking up in the clustered index for text column. Please suggest.
My mistake. I'm used to using VARCHAR(MAX) now and not TEXT. Unless you want to change data types, you're stuck with the key lookup and the scans.
SO that means, we should not add the other columns in the include list of index as this will increase the size of index and will not suffice the purpose. Thanks.
The problem is the TEXT column. If you want to retrieve that, you have to go to the clustered index, no choice. You can add any number of columns to your include, but you'll still have to pay the cost of going to the clustered index as long as you want to retrieve that other column.
15 People are following this question.
