Több tucat jelöltet interjúztattam MS SQL Server-es poziciókra és sajnos csak nagyon kevesen válaszoltak helyesen erre a kérdésemre: vajon egy clustered index-el ellátott táblában az adatok fizikai sorrendje ugyan az e mint az index logikai sorrendje?
Miért fontos ez? Hivatkoznék egy régebbi
bejegyzésemre, ahol az volt a feladat, hogy az utolsó 10 sort kellett megjeleníteni.
Itt felmerült az ORDER BY használata, illetve nem használata. Az SQL Server csak akkor garantálja a lekérdezés sorrendjét, ha megadom az ORDER BY-t, egyébként nem. Az SQL Server fizikailag sem garantálja a rekordok sorrendjét, mint ahogy ez az alábbi példából is ki fog derülni.
Első körben hozzunk létre egy adatbázist és egy táblát egy clustered index-el. Az egyszerűség kedvéért az adat és a tábla egy page-n el fog férni:
USE [master];
GO
IF DATABASEPROPERTY (N'rowordertest', 'Version') > 0
DROP DATABASE [rowordertest];
GO
CREATE DATABASE [rowordertest];
GO
USE [rowordertest];
GO
CREATE TABLE t1 (c1 INT, c2 VARCHAR (10));
CREATE CLUSTERED INDEX t1c1 ON t1 (c1);
GO
Következő lépésben létrehozok sorokat a táblában:
INSERT INTO t1 VALUES (2, REPLICATE ('b', 10));
INSERT INTO t1 VALUES (3, REPLICATE ('c', 10));
INSERT INTO t1 VALUES (4, REPLICATE ('d', 10));
INSERT INTO t1 VALUES (5, REPLICATE ('e', 10));
GO
Szándékosan kihagytam az 1-es értéket a c1 oszlopban. Most nézzük meg, hogy mi került letárolásra és hogyan. Ehhez először tudnunk kell a page-et, mely a
DBCC IND segítségével megálapítható, majd a
DBCC PAGE segítségével belenézünk a page-be:
DBCC IND ('rowordertest', 't1', 1);
GO
DBCC TRACEON (3604);
GO
DBCC PAGE ('rowordertest', 1, 153, 3);
GO
DBCC TRACEOFF (3604);
GO
Az eredmény az alábbi (nem a teljes kimenetet másoltam ide):
Slot 0 Offset 0x60 Length 27
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
Memory Dump @0x4221C060
00000000: 30000800 02000000 03000002 0011001b †0...............
00000010: 00626262 62626262 626262†††††††††††††.bbbbbbbbbb
Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0
UNIQUIFIER = 0
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
c1 = 2
Slot 0 Column 2 Offset 0x11 Length 10 Length (physical) 10
c2 = bbbbbbbbbb
.
.
.
Slot 1 Offset 0x7b Length 27
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
Memory Dump @0x4221C07B
00000000: 30000800 03000000 03000002 0011001b †0...............
00000010: 00636363 63636363 636363†††††††††††††.cccccccccc
Slot 1 Column 0 Offset 0x0 Length 4 Length (physical) 0
UNIQUIFIER = 0
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
c1 = 3
Slot 1 Column 2 Offset 0x11 Length 10 Length (physical) 10
c2 = cccccccccc
.
.
.
Slot 3 Offset 0xb1 Length 27
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
Memory Dump @0x4221C0B1
00000000: 30000800 05000000 03000002 0011001b †0...............
00000010: 00656565 65656565 656565†††††††††††††.eeeeeeeeee
Slot 3 Column 0 Offset 0x0 Length 4 Length (physical) 0
UNIQUIFIER = 0
Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4
c1 = 5
Slot 3 Column 2 Offset 0x11 Length 10 Length (physical) 10
c2 = eeeeeeeeee
A
DBCC PAGE printopt 3 esetén mindig a logikai sorrend szerint mutatja egy page tartalmát (a Slot Array mindig logikai sorrend szerint van rendezve) .
Vegyük észre, hogy a c1 = 2 sor a 0x60, a c1 = 5 pedig a 0xb1 offset-nél kezdődik. Most adjuk hozzá a c1 = 1 sort is és ismét nézzük meg a page tartalmát:
INSERT INTO t1 VALUES (1, REPLICATE ('a', 10));
GO
DBCC TRACEON (3604);
GO
DBCC PAGE ('rowordertest', 1, 153, 3);
GO
DBCC TRACEOFF (3604);
GO
Slot 0 Offset 0xcc Length 27
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
Memory Dump @0x41A8C0CC
00000000: 30000800 01000000 03000002 0011001b †0...............
00000010: 00616161 61616161 616161†††††††††††††.aaaaaaaaaa
Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0
UNIQUIFIER = 0
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
c1 = 1
Slot 0 Column 2 Offset 0x11 Length 10 Length (physical) 10
c2 = aaaaaaaaaa
Elvileg a c1 = 1 lenne a logikailag az első rekord az indexben, de ahogy ez látszik is, fizikailag nem az! Tehát a válasz: a fizikai sorrend nem garantált, hogy ugyan az mint a logikai sorrend! Ez látható is a printopt 2 és 1 esetén is:
DBCC TRACEON (3604);
GO
DBCC PAGE ('rowordertest', 1, 153, 2);
GO
DBCC TRACEOFF (3604);
Memory Dump @0x4221C000
4221C000: 01010400 00400001 00000000 00000800 †.....@..........
4221C010: 00000000 00000500 1c000000 0f1fe700 †..............ç.
4221C020: 99000000 01000000 1c000000 48000000 †............H...
4221C030: 12000000 00000000 00000000 00000000 †................
4221C040: 01000000 00000000 00000000 00000000 †................
4221C050: 00000000 00000000 00000000 00000000 †................
4221C060: 30000800 02000000 03000002 0011001b †0...............
4221C070: 00626262 62626262 62626230 00080003 †.bbbbbbbbbb0....
4221C080: 00000003 00000200 11001b00 63636363 †............cccc
4221C090: 63636363 63633000 08000400 00000300 †cccccc0.........
4221C0A0: 00020011 001b0064 64646464 64646464 †.......ddddddddd
4221C0B0: 64300008 00050000 00030000 02001100 †d0..............
4221C0C0: 1b006565 65656565 65656565 30000800 †..eeeeeeeeee0...
4221C0D0: 01000000 03000002 0011001b 00616161 †.............aaa
4221C0E0: 61616161 61616100 00000200 00080001 †aaaaaaa.........
Row - Offset
4 (0x4) - 177 (0xb1)
3 (0x3) - 150 (0x96)
2 (0x2) - 123 (0x7b)
1 (0x1) - 96 (0x60)
0 (0x0) - 204 (0xcc)
A példához és a magyarázatokhoz Paul Randal erre vonatkozó
bejegyzését használtam fel,
előzetes engedélyével. Nem akartam újra feltalálni a dolgokat, ráadásul a leghitelesebb forrásból származó információkat használtam fel.