Új oszlop hozzáadása egy táblához–második felvonás
A napokban írtam egy bejegyzést arról, hogy mi történik egy új oszlop hozzáadása során. Felmerültek további érdekes kérdések ezzel kapcsolatban:
- Hol tárolja a default constraint az értéket, ha az csak egy metaadat változás?
- Mi történik akkor, ha változtatok a default constraint definicióján? Mindkét kérdésre tudom a választ amit meg is osztanék.
Hol tárolja a default constraint az értéket, ha az csak egy metaadat változás?
Hát az adatbázisban ;-). De hol? Hát persze, hogy nem egy dokumentált helyen, hol máshol ;-). Lássuk is:
Hozzunk létre egy adatbázist, mint az előző bejegyzésben, majd egy teszt táblát két sorral.
1USE [master];
2GO
3
4IF DATABASEPROPERTY('TestDb', 'Version') > 0
5 BEGIN
6 ALTER DATABASE [TestDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
7 DROP DATABASE [TestDb];
8 END
9GO
10
11CREATE DATABASE [TestDb];
12GO
13
14USE [TestDb];
15GO
16
17CREATE TABLE [Table1]
18(
19 col1 int identity(1,1),
20 col2 char(1) DEFAULT 'a'
21);
22GO
23
24INSERT INTO [Table1] DEFAULT VALUES;
25GO 2
Ezek után nézzük meg, hogy a tábla melyik lapon van:
1DBCC IND('TestDb','Table1', 0, 1);
2GO
A lap számát felhasználva nézzük meg az adatokat a lapon:
1DBCC TRACEON(3604);
2DBCC PAGE('TestDb', 1, 93, 3);
3GO
A kimenete pedig:
1PAGE: (1:93)
2
3
4BUFFER:
5
6
7BUF @0x000000008008A800
8
9bpage = 0x00000001E2374000 bhash = 0x0000000000000000 bpageno = (1:93)
10bdbid = 8 breferences = 0 bcputicks = 0
11bsampleCount = 0 bUse1 = 24059 bstat = 0xb
12blog = 0x15ab21cc bnext = 0x0000000000000000
13
14PAGE HEADER:
15
16
17Page @0x00000001E2374000
18
19m_pageId = (1:93) m_headerVersion = 1 m_type = 1
20m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
21m_objId (AllocUnitId.idObj) = 84 m_indexId (AllocUnitId.idInd) = 256
22Metadata: AllocUnitId = 72057594043432960
23Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0
24Metadata: ObjectId = 245575913 m_prevPage = (0:0) m_nextPage = (0:0)
25pminlen = 9 m_slotCnt = 2 m_freeCnt = 8068
26m_freeData = 120 m_reservedCnt = 0 m_lsn = (33:92:3)
27m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
28m_tornBits = 0 DB Frag ID = 1
29
30Allocation Status
31
32GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
33PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
34ML (1:7) = NOT MIN_LOGGED
35
36Slot 0 Offset 0x60 Length 12
37
38Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
39
40Memory Dump @0x000000000D4AA060
41
420000000000000000: 10000900 01000000 61020000 .. .....a...
43
44Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
45
46col1 = 1
47
48Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
49
50col2 = a
51
52Slot 1 Offset 0x6c Length 12
53
54Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
55
56Memory Dump @0x000000000D4AA06C
57
580000000000000000: 10000900 02000000 61020000 .. .....a...
59
60Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
61
62col1 = 2
63
64Slot 1 Column 2 Offset 0x8 Length 1 Length (physical) 1
65
66col2 = a
Itt látható, hogy a col1=1 és 4 byte, col2 = a 1 byte az első sorhoz, majd col1 = 2 és col2 = a a második sor esetében. Most adjunk hozzá egy új oszlopot, alapértelmezett értékkel és nézzük meg újra a lapot:
1ALTER TABLE [Table1]
2ADD [col3] int not null DEFAULT 99;
3GO
4
5DBCC PAGE('TestDb', 1, 93, 3);
6GO
A kimenet:
1PAGE: (1:93)
2
3
4BUFFER:
5
6
7BUF @0x000000008008A800
8
9bpage = 0x00000001E2374000 bhash = 0x0000000000000000 bpageno = (1:93)
10bdbid = 8 breferences = 0 bcputicks = 0
11bsampleCount = 0 bUse1 = 24155 bstat = 0xb
12blog = 0x15ab21cc bnext = 0x0000000000000000
13
14PAGE HEADER:
15
16
17Page @0x00000001E2374000
18
19m_pageId = (1:93) m_headerVersion = 1 m_type = 1
20m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
21m_objId (AllocUnitId.idObj) = 84 m_indexId (AllocUnitId.idInd) = 256
22Metadata: AllocUnitId = 72057594043432960
23Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0
24Metadata: ObjectId = 245575913 m_prevPage = (0:0) m_nextPage = (0:0)
25pminlen = 9 m_slotCnt = 2 m_freeCnt = 8068
26m_freeData = 120 m_reservedCnt = 0 m_lsn = (33:92:3)
27m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
28m_tornBits = 0 DB Frag ID = 1
29
30Allocation Status
31
32GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
33PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
34ML (1:7) = NOT MIN_LOGGED
35
36Slot 0 Offset 0x60 Length 12
37
38Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
39
40Memory Dump @0x000000000D4AA060
41
420000000000000000: 10000900 01000000 61020000 .. .....a...
43
44Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
45
46col1 = 1
47
48Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
49
50col2 = a
51
52Slot 0 Column 3 Offset 0x0 Length 4 Length (physical) 0
53
54col3 = 99
55
56Slot 1 Offset 0x6c Length 12
57
58Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
59
60Memory Dump @0x000000000D4AA06C
61
620000000000000000: 10000900 02000000 61020000 .. .....a...
63
64Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
65
66col1 = 2
67
68Slot 1 Column 2 Offset 0x8 Length 1 Length (physical) 1
69
70col2 = a
71
72Slot 1 Column 3 Offset 0x0 Length 4 Length (physical) 0
73
74col3 = 99
Itt már látszik, hogy a default contraint-nak megfelelően a col3 = 99 és 0 byte a fizikai helyfogalása, mindkét sor esetében. Ha most hozzáadnék egy új sort, ott már fizikailag is lenne adat, nem metaadatként tárolná ennek az oszlopnak az értékét (lásd előző bejegyzésem). De hol tárolja ezt az információt? Ez a sys.sysseobjvalues rendszer táblában van tárolva. A tábla nem dokumentált, módosítása/lekérdezése támogatás/garancia elvesztésével járhat! Mivel a tábla az SQL Server “belső” működéséhez kell, ezért nem is érhető el csak úgy, csak a DAC – Dedicated Administrator Connection- segítségével. Az alábbi lekérdezés visszaadja, hogy melyik tábla, mely oszlopai esetén van csak metaadatként tárolva a hozzáadott oszlop alapértlmezett értéke:
1--USE ALT + Q + M to switch SQLCMD mode
2:CONNECT ADMIN:server\instance
3USE [TestDb]
4GO
5
6SELECT
7 O.[name] AS [table_name],
8 C.[name] AS [column_name],
9 DC.[name] AS [default_constraint_name],
10 DC.[definition] AS [current_default_constraint_definiton],
11 SE.[value] AS [meta_default_constraint_definition]
12FROM
13 sys.sysseobjvalues SE
14JOIN
15 sys.partitions P ON SE.[id] = P.[partition_id]
16JOIN
17 sys.objects O ON P.[object_id] = O.[object_id]
18JOIN
19 sys.columns C ON O.[object_id] = C.[object_id] AND SE.[subid] = C.[column_id]
20JOIN
21 sys.default_constraints DC ON C.[default_object_id] = DC.[object_id] AND C.[column_id] = DC.[parent_column_id]
A jelen állapotok szerint a kimenet:
| table_name | column_name | default_constraint_name | current_default_constraint_definiton | meta_default_constraint_definition |
|---|---|---|---|---|
| Table1 | col3 | DF__Table1__col3__117F9D94 | ((88)) | 99 |
A Table1 col3 oszlopának 99 az alapértelemeztt értéke. Ezt hivatkozza be minden egyes lekérdezéskor.
1SELECT * FROM Table1
2/*
3col1 col2 col3
41 a 99
52 a 99
6*/
Eddig meg is lennénk, már tudjuk, hogy hol tárolja le ezt az információt. De mi van akkor, ha változtatunk a definición?
Mi történik akkor, ha változtatok a default constraint definicióján?
Röviden: semmi :-). A default contraint megváltoztatása a törléséből és az újra létrehozásából áll. Nézzük meg a fenti táblánk esetében ez mit okoz. Változtassuk a 99-et 88-ra:
1USE [TestDb]
2GO
3
4ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [DF__Table1__col3__108B795B]
5GO
6
7ALTER TABLE [dbo].[Table1] ADD DEFAULT ((88)) FOR [col3]
8GO
Ezek után nézzük meg, hogy milyen étékek vannak a táblában. Miért érdekes ez? Hát változtattam a default constraint definición és esetleg a metaadat is változik vele. Hát nem! Ami eddig is 99 volt, az az is maradt.
1SELECT * FROM Table1
2/*
3col1 col2 col3
41 a 99
52 a 99
6*/
Adjunk hozzá egy új sort és nézzük meg az eredményt:
1INSERT INTO [Table1] DEFAULT VALUES;
2GO
3
4SELECT * FROM Table1
5/*
6col1 col2 col3
71 a 99
82 a 99
93 a 88
10*/
Látható, hogy a korábban létrehozott soraimnál maradt a 99 a col3 esetén és csak az újonnan hozzáadott sornál látszik a 88. Ez utóbbi esetben pedig már helyfoglalása is van – 4 byte, lásd DBCC PAGE kimenete:
1...
2Slot 2 Offset 0x78 Length 16
3
4Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16
5
6Memory Dump @0x0000000011C1A078
7
80000000000000000: 10000d00 03000000 61580000 00030000 ........aX......
9
10Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
11
12col1 = 3
13
14Slot 2 Column 2 Offset 0x8 Length 1 Length (physical) 1
15
16col2 = a
17
18Slot 2 Column 3 Offset 0x9 Length 4 Length (physical) 4
19
20col3 = 88
21...
Ha megnézzük ismét, hogy a sys.sysseobjvalues táblában mit látunk, akkor nincs meglepetés, a 99-es érték látszik, amellett, hogy a default constraint aktív értéke a 88.