SQL Server bookmarks

I checked my bookmarks on the Internet tonight.

The use of non-clustered indexes uses bookmarks to find bookmark Lookup

Clustered index does not use bookmarks for search

My understanding:

Clustered index: place the content in the book into the bookmarks, specifying that all the content (the whole row of records) of the clustered index table column is placed in the bookmarks first.

Query:When querying, you can directly search for bookmarks Based on the bookmarks directory. Because the content is directly stored in the bookmarks, the query speed is very fast.

Modify data: If you want to modify data, the database first finds the bookmarks, then modifies the contents in the bookmarks, and then finds the contents of the books based on the contents in the bookmarks, then modify the content in the book (that is, you need to modify the content twice)

Because the contents in books are also stored in bookmarks.

Non-clustered index: the content in the book is not placed in the bookmarks, but the pointer.

Query:The database first finds the bookmarks, and then finds the content in the books based on the content in the bookmarks, so it is slower than the clustered index. However, because the bookmarks are added like clustered indexes, therefore, queries are faster than those without indexes.

Modify data: the database first finds the bookmarks, then finds the contents of the books based on the contents of the bookmarks, and then modifies the contents of the books because the bookmarks do not store the contents of the books, therefore, the modification speed is faster than that of clustered indexes.

 

Another benefit of discovering clustered indexes is that the blocking probability is less than that of non-clustered indexes.

For example, I have two tables: employeea table and employeeb table. Both tables have two columns: employeeid and name.

The clustered index created in the employeea table is on the employee ID, and the non-clustered index created in the employeeb table is on the employee ID !!!!!!!!!!!!!!!!!

Assume that both tables have three records, and the data is the same.

Employeeid name

1 James

2 xiaofang

3 Xiaoqing

 

Now when I update the employeeb table in a new session (Session 1)

Begin tran

Update employeeb table set name = 'huahua 'Where employeeid = 2

This SQL statement is not submitted

Then in another session (Session 2) the content in the select employeeb table

Begin tran

Select * From employeeb table where employeeid = 3

At this time, the session 2 that queries the table 'employeeb' will be blocked, because it will not continue until the session commits the transaction, because the session is on the record with 'employeeid = 2 '.

The exclusive lock is added, so Session 2 cannot continue searching. As long as the session commits a transaction, Session 2 can find the record with employee ID = 3 and display it.

The clustered index won't have this problem. Why?

As mentioned earlier:

Clustered index: place the content in the book into the bookmarks, specifying that all the content (the whole row of records) of the clustered index table column is placed in the bookmarks first.

Non-clustered index: the content in the book is not placed in the bookmarks, but the pointer.

If it is a clustered index, you need to find the content of the record whose employee ID is 3. You just need to read the content from the bookmarks, instead of reading the data on the data page.

Because the update statement needs to read data on the data page, it does not affect each other. However, non-clustered indexes need to be read from books (data pages)

So everyone willMutual BlockingBecause Session 1 locks the employee ID = 2 record. If you do not submit the record, Session 2 cannot read the content and display it.

 

The above is my plain understanding. If you have any mistakes, I hope you can correct o

I am preparing for training. I will go to work tomorrow.

 

Postscript: Today I read the p294 page in Microsoft sqlserver enterprise platform management practice. This page describes the lock to be applied for an insert action.

The focus is on the last sentence:The only difference is that an RID lock must be applied for in the heap structure. Because the real data is not on the index, but on the heap.

This statement further demonstrates my point of view:

Clustered index: place the content in the book into the bookmarks, specifying that all the content (the whole row of records) of the clustered index table column is placed in the bookmarks first.

Non-clustered index: the content in the book is not placed in the bookmarks, but the pointer.

 

Because the example in the book uses a clustered index table and a non-clustered index table for insert operations, it compares non-clustered index and non-clustered index for insert operations to apply for the lock

If you are interested, please read this book !!