SQL Server Introduction Order: Level 13th, INSERT, UPDATE, delete

In levels tenth through 12, we looked at the internal structure of the index and the effect of changing the structure. In this article, continue to review the effects of insert,update,delete and merge. First, let's take a look at these four commands alone.

inserting insert

When inserting a row of data into a table, no matter whether the table is a heap table or a clustered index table, a portal will definitely be inserted in the table's index, with the exception of filtered indexes. In doing so, SQL Server uses the value of the index key from the root page to the leaf layer page, after reaching the leaf layer page, checks the free space of the page, and if there is enough free space, the new entry will be inserted in the appropriate location.

Eventually, SQL Server might attempt to insert portal information into a page that has no space. At this point, SQL Server queries the location structure to find a page with free space. Once found, it will do three things, each of which is related to the order of the index keys to be inserted:

Random sequence: Normally, SQL Server will move one half of the full page of the portal to an empty page, and then insert the new portal into the appropriate page, resulting in two pages that are used in half. If your app continues to insert data, but does not delete the data, the two pages will become full pages from half the state, then be divided into two half pages, and then become full pages again, so that the cycle repeats. The full rate per page is about 75%.

Sequential sequence: SQL Server discovers that a new entry needs to be inserted into the last face of a full page, creates a new page, inserts the new entry, and then creates a new page when the page is full again. Once a page is full, he is always full, so the internal fragments are small and not even.

Descending sequence: Conversely, if SQL Server discovers that a new entry needs to be inserted at the beginning of a full page, it will also create a new page, inserting a new entry, but because it is descending, the internal fragment is close to 100%.

Remove Delete

When a row is deleted from the table, the corresponding index entry is removed from the index. For each index, SQL Server navigates from the root page to the leaf layer page in order to find the portal. Once SQL Server discovers the portal, it does two things: delete the entry immediately, or set the tag at the head of the line, making the entry a ghost record, and deleting the ghost record when appropriate.

The Ghost record is ignored when it is queried. They are only physically present and logically no longer exist. The number of ghost record for an index can be obtained through the system function sys.dm_db_index_physical_stats.

SQL Server is not immediately deleted because of performance and concurrency management needs. Not only the performance of the removal itself, but also the subsequent transaction rollback performance is included. The above practice makes it easy to roll back a delete operation, compared to recreating records from the transaction log.

The following factors affect the process of deletion:

• If the row is locked, the deleted index becomes a ghost record.
• If the procedure you are performing requires locking 5000 rows of data, row-level locks are promoted to table-level locks.
• As a concurrency technique, the use of row versions can also cause ghost record to appear.
• Ghost Record is not deleted until the transaction is complete.
• The background thread of SQL Server Ghost-cleanup is responsible for deleting ghost record, but when it is deleted is not expected. The delete operation itself does not inform the GHOST-CLEANUP thread to do so, and subsequent page scans will add a page containing ghost record to a list, and the ghost-cleanup thread will process the list on a regular basis.
• The ghost-cleanup thread wakes up approximately every 5 seconds. It cleans up 10 pages at a time. These numbers can be set.
• You can force cleanup by sp_clean_db_free_space or sp_clean_db_file_free_space, which will delete the ghost record in the entire database or data file.

In other words, when you delete a data row, it is logically deleted. If they are not understood to be deleted, they will be deleted as long as SQL Server considers it to be secure.

Updating update

When updating data rows in a table, you need to modify the entry of the index. For each index entry, SQL Server performs an in-place update or is removed and then inserted. SQL Server will use an in-place update whenever possible. However, there are some situations that cannot be updated in place, and SQL Server will perform the delete immediately following the insert. Here are some reasons for this:

• Update to modify key columns, which causes the entry of the index to be reassigned.
• The update will modify many columns, causing the entry to be inappropriate on the current page.
• A trigger that has DML on the table.

If the modified column is part of an index key, the location of the entry must change. The entry is removed from the old location, and the entry is inserted in the new position in the order of the new key. In most cases, it will be removed and then inserted. If the new location and the old location are on the same page, there may be an in-place update. SQL Server runs two times from the root page to the leaf layer, one time to find the current entry position, one time to determine the new location of the entry.

If the modified column is part of a clustered index, all nonclustered indexes need to be updated because their labels are made up of the keys of the clustered index.

If you modify a part other than the index key, the location of the entry does not change. However, the size of the entry may change. If the page does not have enough space to store the new entry, the update becomes deleted and then inserted.

Merging merger

Merging operations were introduced in SQL Server 2008, which is powerful, flexible, and good. The merge operation produces insert, UPDATE, and DELETE statements. The merge is the same as the effect you write insert,udpate,delete, so it's not covered in this series.

MERGE target Table  USING source table   on   match condition   when then       statement   when   not  Then     

The above is the most basic syntax of the merge, when the statement executes according to the result of the match condition, if the match record is found in the target table then executes the statement after matched then, if no match is found, then executes the statement after not matched then. Note the source table can be a table, or it can be a sub-query statement.

In particular, the semicolon at the end of the merge statement cannot be omitted!

 as d USING       as  on=  d.productid                    INSERT(productid,productname,price)                     VALUES

MERGE productnew asd USING Product ass onS.productid=D.productid when  notMatched Then     INSERT(Productid,productname,price)VALUES(S.productid,s.productname,s.price) whenMatched Then     UPDATE SETD.productname=S.productname, D.price=S.Price;

One-time update index index-at-a-time update

When you perform an INSERT, update, or delete a row of the Statement action table, SQL Server will definitely modify the data and then modify the index. After executing the INSERT, update, and delete data, SQL Server has two choices:

• For each row, after the operation is done, the index is modified.
• For each row, after the operation is done, the modification information is suspended in a collection for each index. After all the rows have been executed, the collection is modified in the execution of the pending index.

The second is called "one-time update index", which is an option to insert, UPDATE, and delete operations.

The SQL Server query optimizer will decide which one to use to optimize performance. If you are modifying most of the rows in a table, you are likely to use the second type.

To prove this, we create a new table with two indexes.

 UseAdventureWorks;GO IF EXISTS(SELECT *         fromsys.objectsWHEREName= 'fragtestii'  andType= 'U') BEGIN   DROP TABLEdbo. fragtestii; END GO CREATE TABLEdbo. Fragtestii (Pkcolint  not NULL, Infocolnchar( -) not NULL    , CONSTRAINTPk_fragtestii_pkcolPrimary Key nonclustered(Pkcol)); GO CREATE INDEXIx_fragtestii_infocol ondbo. Fragtestii (Infocol); GO 

Executes a statement that inserts a record first.

INSERTVALUES (100000'XXXX'

Execution plan, only shows the process of inserting the data, and does not display information about the index update. This is because, in the case above, the update of the index is part of the row update.

At that time, when we inserted a lot of data, the execution plan would be different.

We first construct a fragtest table of 20,000 records and then bulk insert the fragtest data into the Fragtestii table.

CREATE TABLEdbo. Fragtest (Pkcolint IDENTITY(1,1) not NULL, Infocolnchar( -) not NULL    , CONSTRAINTPk_fragtest_pkcolPrimary Key nonclustered(Pkcol)); GO  DECLARE @index INT SET @index=0   while(@index<20000) BEGIN     INSERT  intoDbo. Fragtest (Infocol)VALUES('123')          SET @index=@index +1      END 

INSERT dbo. Fragtestii   SELECT  pkcol, Infocol   from

The execution plan is what it looks like, and it contains a lot of operations. A class of operations is inserting data into a table. There are two sorts, each containing an operation that inserts an index.

Although it is a complex execution plan, sorting and updating a suspended index is performed separately, but is also an efficient execution plan. The index is added sequentially, resulting in fewer fragments than the index is added.

Conclusion

Inserting a portal into an index results in three fragments, which depend on the order in which the portals are inserted.

Deleting a portal from the index, including removing it from the clustered index, may delete the entry immediately. You may also create a ghost record to make the index entry a tombstone. Ghost exists only in the leaf layer. SQL Server will not delete ghost record until after the transaction has completed.

The update index may be updated in place immediately, or it may be inserted after the deletion. If the table does not have a DML trigger, it is usually updated in place if the update does not reassign the entry, or increases the size of the entry.

If the data modification statement affects a large number of rows, SQL Server may choose to update the index once, modify the table first, and then update each index.

SQL Server Introduction Order: Level 13th, INSERT, UPDATE, delete