Megegyezik e a fizikai sorrend az index logikai sorrendjével?
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:
1USE [master];
2GO
3IF DATABASEPROPERTY (N'rowordertest', 'Version') > 0
4DROP DATABASE [rowordertest];
5GO
6
7CREATE DATABASE [rowordertest];
8GO
9
10USE [rowordertest];
11GO
12
13CREATE TABLE t1 (c1 INT, c2 VARCHAR (10));
14
15CREATE CLUSTERED INDEX t1c1 ON t1 (c1);
16GO
Következő lépésben létrehozok sorokat a táblában:
1INSERT INTO t1 VALUES (2, REPLICATE ('b', 10));
2INSERT INTO t1 VALUES (3, REPLICATE ('c', 10));
3INSERT INTO t1 VALUES (4, REPLICATE ('d', 10));
4INSERT INTO t1 VALUES (5, REPLICATE ('e', 10));
5GO
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:
1DBCC IND ('rowordertest', 't1', 1);
2GO
3DBCC TRACEON (3604);
4GO
5DBCC PAGE ('rowordertest', 1, 153, 3);
6GO
7DBCC TRACEOFF (3604);
8GO
Az eredmény az alábbi (nem a teljes kimenetet másoltam ide):
1Slot 0 Offset 0x60 Length 27
2Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
3Record Size = 27
4Memory Dump @0x4221C060
5
600000000: 30000800 02000000 03000002 0011001b †0...............
700000010: 00626262 62626262 626262†††††††††††††.bbbbbbbbbb
8
9Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0
10
11UNIQUIFIER = 0
12
13Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
14
15c1 = 2
16
17Slot 0 Column 2 Offset 0x11 Length 10 Length (physical) 10
18
19c2 = bbbbbbbbbb
20.
21.
22.
23Slot 1 Offset 0x7b Length 27
24
25Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
26Record Size = 27
27Memory Dump @0x4221C07B
28
2900000000: 30000800 03000000 03000002 0011001b †0...............
3000000010: 00636363 63636363 636363†††††††††††††.cccccccccc
31
32Slot 1 Column 0 Offset 0x0 Length 4 Length (physical) 0
33
34UNIQUIFIER = 0
35
36Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
37
38c1 = 3
39
40Slot 1 Column 2 Offset 0x11 Length 10 Length (physical) 10
41
42c2 = cccccccccc
43.
44.
45.
46Slot 3 Offset 0xb1 Length 27
47
48Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
49Record Size = 27
50Memory Dump @0x4221C0B1
51
5200000000: 30000800 05000000 03000002 0011001b †0...............
5300000010: 00656565 65656565 656565†††††††††††††.eeeeeeeeee
54
55Slot 3 Column 0 Offset 0x0 Length 4 Length (physical) 0
56
57UNIQUIFIER = 0
58
59Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4
60
61c1 = 5
62
63Slot 3 Column 2 Offset 0x11 Length 10 Length (physical) 10
64
65c2 = 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:
1INSERT INTO t1 VALUES (1, REPLICATE ('a', 10));
2GO
3DBCC TRACEON (3604);
4GO
5DBCC PAGE ('rowordertest', 1, 153, 3);
6GO
7DBCC TRACEOFF (3604);
8GO
1Slot 0 Offset 0xcc Length 27
2
3Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
4Record Size = 27
5Memory Dump @0x41A8C0CC
6
700000000: 30000800 01000000 03000002 0011001b †0...............
800000010: 00616161 61616161 616161†††††††††††††.aaaaaaaaaa
9
10Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0
11
12UNIQUIFIER = 0
13
14Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
15
16c1 = 1
17
18Slot 0 Column 2 Offset 0x11 Length 10 Length (physical) 10
19
20c2 = 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:
1DBCC TRACEON (3604);
2GO
3DBCC PAGE ('rowordertest', 1, 153, 2);
4GO
5DBCC TRACEOFF (3604);
1Memory Dump @0x4221C000
2
34221C000: 01010400 00400001 00000000 00000800 †.....@..........
44221C010: 00000000 00000500 1c000000 0f1fe700 †..............ç.
54221C020: 99000000 01000000 1c000000 48000000 †............H...
64221C030: 12000000 00000000 00000000 00000000 †................
74221C040: 01000000 00000000 00000000 00000000 †................
84221C050: 00000000 00000000 00000000 00000000 †................
94221C060: 30000800 02000000 03000002 0011001b †0...............
104221C070: 00626262 62626262 62626230 00080003 †.bbbbbbbbbb0....
114221C080: 00000003 00000200 11001b00 63636363 †............cccc
124221C090: 63636363 63633000 08000400 00000300 †cccccc0.........
134221C0A0: 00020011 001b0064 64646464 64646464 †.......ddddddddd
144221C0B0: 64300008 00050000 00030000 02001100 †d0..............
154221C0C0: 1b006565 65656565 65656565 30000800 †..eeeeeeeeee0...
164221C0D0: 01000000 03000002 0011001b 00616161 †.............aaa
174221C0E0: 61616161 61616100 00000200 00080001 †aaaaaaa.........
18
19Row - Offset
204 (0x4) - 177 (0xb1)
213 (0x3) - 150 (0x96)
222 (0x2) - 123 (0x7b)
231 (0x1) - 96 (0x60)
240 (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.