 |
(Sunil Agarwal) In the previous example, I described minimal logging while moving data from a source table into a heap. You may recall that it requires a X table lock to get minimal logging. Now, I will show you what happens when you move data from a source table into a btree. In order to get minimal logging with btree, the only requirements are
--Like minimal logging for heap, the database must be set to bulk-logged or simple recovery model
--The input data must be sorted in the index key order. Note, it does not require X tablelock or the btree to be empty. SQL Server accompolishes this by acquiring a X range lock. So for example, if you have an existing btree with values 1, 100, 1001, 1004. Any now you want to insert 500 rows with the key range from 110 thru 610, the SQL Server does that by acquring a X range lock between 100 and 1001 and accompolishes the load. The other transactions can access any data that is outside the range (100, 1001). |
