First published on MSDN on Sep 15, 2016
Recently I had a question regarding "What happens to records when they are deleted" – so I wanted to dig into what happens under the covers when just such a thing happens. The answer is somewhat simple – but the explanation is somewhat more complicated. So, I am breaking the answer up into 2 different parts. This first tip will look at how to locate a record from a table in the SQL data file. The next tip will look at what happens when a DELETE occurs. We have to be able to find the records to tell if they're there or not after a DELETE – which is why I'm breaking it up.
To begin I'll create a database for our tests.
USE master GO IF DB_ID('DeletedDB') IS NOT NULL BEGIN ALTER DATABASE DeletedDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE DeletedDB END GONext, I'll insert 100 records into a table named dbo.Sales. Any numeric columns from AdventureWorks I am going to cast as a character string. This just will make it easier to identify data when we look at it through a hex editor.
CREATE DATABASE DeletedDB GO USE DeletedDB GO SELECT TOP (100) SalesOrderNumber ,PurchaseOrderNumber ,AccountNumber ,CustomerID = CAST(CustomerID AS VARCHAR(12)) ,CreditCardApprovalCode ,CarrierTrackingNumber ,IDCol = CAST(SalesOrderDetailID AS VARCHAR(10)) INTO dbo.Sales FROM AdventureWorks2014.Sales.SalesOrderDetail s JOIN AdventureWorks2014.Sales.SalesOrderHeader h ON s.SalesOrderID = h.SalesOrderID ORDER BY SalesOrderDetailID ASC GO
ALTER TABLE dbo.Sales ALTER COLUMN IDCol VARCHAR(10) NOT NULL GO ALTER TABLE dbo.Sales ADD CONSTRAINT PK_Sales_IDCol PRIMARY KEY CLUSTERED (IDCol) GOI can use the handy function sys.fn_physlocformatter to query the table and identify specifically where a row is. The column 'Location(File:Page:Slot)' tells me that the row I have highlighted is on page 330. So, this is where our journey will start.
USE DeletedDB GO SELECT IDCol ,CarrierTrackingNumber ,'Location(File:Page:Slot)' = sys.fn_PhysLocFormatter(% % physloc % %) ,LockResource = % % lockres % % FROM dbo.Sales CROSS APPLY sys.fn_physLocCracker(% % physloc % %) x
SELECT * FROM sys.dm_os_buffer_descriptors WHERE page_id = 330 AND database_id = DB_ID('DeletedDB')
DBCC TRACEON(3604) DBCC PAGE(DeletedDB, 1, 330, 3) WITH TABLERESULTS
USE master GO ALTER DATABASE DeletedDB SET OFFLINE GOOnce the file is detached, I am going to open it with the XVI32.exe hex editor.
DECLARE @PageStart INT DECLARE @TargetPage INT = 330 DECLARE @PageSize INT = 8192 SELECT @PageStart = CAST(@TargetPage * @PageSize AS VARBINARY(100)) SELECT PageStart = @PageStart
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.