An Example of a Data Page After Deleting a Record
When you delete record, MS SQL Server 6.5 writes to the appropriate cell a zero value, deletes the row, moves the other rows to avoid free space between them, and sets the new value into freeoff field of the page header. All rows are stored contiguously on the data page.
| DELETE FROM discounts WHERE discounttype = ‘Volume Discount’ |
This is the result from my computer:
| PAGE: Page found in cache. BUFFER: Buffer header for buffer 0xf96580 page=0x1181000 bdnew=0xf5a448 bdold=0xf5a448 bhash=0x0 bnew=0xf964c0 bold=0xf96640 bvirtpg=2540 bdbid=5 bpinproc=7 bkeep=0 bspid=0 bstat=0x1004 bpageno=488 PAGE HEADER: Page header for page 0x1181000 pageno=488 nextpg=0 prevpg=0 objid=544004969 timestamp=0001 0000519c nextrno=3 level=0 indid=0 freeoff=90 minlen=5 page status bits: 0x100,0x10,0x1 DATA: Offset 32 – 01181020: 0100017e 041a0049 6e697469 616c2043 …~…Initial C 01181030: 7573746f 6d657202 1707 ustomer… Offset 58 – 0118103a: 020201f4 01200043 7573746f 6d657220 ….. .Customer 0118104a: 44697363 6f756e74 38303432 031c1807 Discount8042…. OFFSET TABLE: Row – Offset 2 (0x2) – 58 (0x3a), 1 (0x1) – 0 (0x0), 0 (0x0) – 32 (0x20), |
See Figure 3:
Figure 3. After delete
An Example of an Insert on a Data Page
When you insert new record, MS SQL Server 6.5 scans the offset table from the end. If there is cell with zero value in it, then the offset of the new row will be inserted into this cell. If there is no cell with a zero value in it, then new cell will be added in the offset table, the new value will be written into freeoff field of the page header, and the nextrno field of the page header will be increased by 1.
| INSERT discounts values(‘Volume Discount’, NULL, 100, 1000, 6.7) |
This is the result from my computer:
| PAGE: Page found in cache. BUFFER: Buffer header for buffer 0xf96580 page=0x1181000 bdnew=0xf5a448 bdold=0xf5a448 bhash=0x0 bnew=0xf964c0 bold=0xf96640 bvirtpg=2540 bdbid=5 bpinproc=8 bkeep=0 bspid=0 bstat=0x1004 bpageno=488 PAGE HEADER: Page header for page 0x1181000 pageno=488 nextpg=0 prevpg=0 objid=544004969 timestamp=0001 0000519e nextrno=3 level=0 indid=0 freeoff=122 minlen=5 page status bits: 0x100,0x10,0x1 DATA: Offset 32 – 01181020: 0100017e 041a0049 6e697469 616c2043 …~…Initial C 01181030: 7573746f 6d657202 1707 ustomer… Offset 58 – 0118103a: 020201f4 01200043 7573746f 6d657220 ….. .Customer 0118104a: 44697363 6f756e74 38303432 031c1807 Discount8042…. Offset 90 – 0118105a: 0401019e 02200056 6f6c756d 65204469 ….. .Volume Di 0118106a: 73636f75 6e746400 e803051a 18161607 scountd……… OFFSET TABLE: Row – Offset 2 (0x2) – 58 (0x3a), 1 (0x1) – 90 (0x5a), 0 (0x0) – 32 (0x20), |
See Figure 4:
Figure 4. After insert
Learning how data is structured in a database helps to provide a solid understanding of how SQL Server works. And the more you know about the underlying architecture, the better position you will be in to performance tune your SQL Server databases.
]]>