As per my study, I found that a lot of people mention that the covering index would help to reduce the “key lookup” blocking. I was keen to test and understand it, but my testing did not show the expected result.
First I run the code below, I trying to update the table on the first transaction without commit and then on another session, I run a query to select the col2 and col3 from the tableA. According to the book, the second session will be block by the first session.
-- Create the table for testing CREATE TABLE (TableA) ( (col1) INT, (col2) INT, (col3) INT, (col4) CHAR(100) DEFAULT('abc') NOT NULL ); GO DECLARE @int INT; SET @int = 1; -- Load data into the table WHILE (@int <= 1000) BEGIN INSERT INTO (TableA) ((col1), (col2), (col3), (col4)) VALUES (@int*2, @int*2, @int*2, @int*2); SET @int = @int + 1; END GO CREATE CLUSTERED INDEX (cidx_TableA) ON (TableA) ((col1)); -- Create a non-clustered index CREATE NONCLUSTERED INDEX (idx_TableA_col2) ON (TableA) ((col2)); GO BEGIN TRANSACTION UPDATE (TableA) SET col3=999 WHERE Col2=4 --Rollback --ON another session, I query this. SELECT (col2),col3 FROM (TableA) WHERE (col2)=66 option (recompile)
The second session did using the key lookup to the Cluster index cidx_TableA and return the result, I expect it would no return any result because suppose it would lock by the update on first session. Why?
I am not yet create the covering index yet, because it did not make any blocking at all. So, did covering index really work?