Delete operation on the table

in today's article, I want to give you a quick demonstration of what happened in SQL Server when we deleted the record from the table. Let's start by creating a simple table with exactly 4 records inserted on the 8KB page.

1 --Create A simple table where 4 records fit onto 1 page2 CREATE TABLEtesttable3 (4Col1INT IDENTITY(1,1),5Col2CHAR( -)6 )7 GO

Next we insert 4 records so that a page just fills up.

1 --Insert 4 Records2 INSERT  intoTestTableVALUES3 (4     REPLICATE('1', -)5 ),6 (7     REPLICATE('2', -)8 ),9 (Ten     REPLICATE('3', -) One ), A ( -     REPLICATE('4', -) - ) the GO

To study the details of our heap tables, we use the DBCC page command to dump the allocated pages. So we also want to enable the 3604 trace flag so that SQL Server directly enters the result into our SSMs session window from the DBCC PAGE command:

1 -- Enable the Trace Flag 3604 2 DBCC TRACEON (3604)3GO

we can use the DBCC IND command to return all pages assigned to the specified table or index:

1 -- Retrieve All pages of the table 2 DBCC - 1 )3GO

As you can see from the output, 2 pages belong to our table: The data page itself, and the IAM (Index allocation map) page.

my page number is 118 and the page is dumped through the DBCC PAGE command:

1 -- Dump out one specific page 2 DBCC 1 118 2 )3GO

When you use the 3rd parameter of option 2 to pour, SQL Server returns your 16-page dump, including the so-called line offset array at the end of the page (row offset array), which does not affect the data in any way.

The row offset array points to the physical location on the page, where each record is stored. The 1th record is always stored directly at the page header offset of 0x60h. You will also see that the line offset array is inversely growing. Now let's delete the 2nd record from the table:

1 -- Delete A record from the table 2 DELETE  from testtable 3 WHERE = 2 4 GO

Usually here you will expect the records to be deleted from the page. But this is not the case: when you execute the DBCC PAGE command again, you will see the contents of the old record on the page or you can see it. The only thing SQL Server does during a delete operation is that the corresponding slot is not valid in the end-of-page offset array.

as you can see, the offset of the 2nd slot is 0x0, which is invalid, which means that our records were deleted. At the beginning of the page, you will always find the page header of bytes. Now let's delete the remaining 3 records from the table.  

1 -- Delete All the remaining records from the table 2 DELETE  from testtable 3 GO

When you view the page again with the DBCC PAGE command, you will see that the page is full or unchanged: every data in each record is physically present on the page! But each record in the row offset data points to an offset of 0x0, which means that each record is deleted. This has nothing to do with whether your table uses a clustered index--old data persists on the page.
The question now is, when does SQL Server initialize the page? When you insert a new record now, SQL Server overwrites the original content of the page. But in our case, this is just the physical part, where the 1th record is stored. You can still see the other "deleted" Record contents. When you look at the trailing array at the end of the page, you will see that it has been fully initialized by SQL Server and that you have only 1 slots in the row offset array:

The next time you authorize the program sysadmin privilege, consider the situation. Using the appropriate commands, these programs can still see data that has been "deleted".

thanks for the attention.

Delete operation on the table