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?