Új oszlop hozzáadása egy táblához
Milyen egyszerűnek hangzik, hogy egy új oszlopot hozzáadjak egy táblához, igaz? Sajnos ez nem feltétlen olyan könnyű dolog, mint amilyennek hangzik. SQL Server 2008 és SQL Server 2012 is másként kezeli ezt, bizonyos esetekben, de ne szaladjunk ennyire előre.
Annó írtam egy bejegyzést arról, hogy egy adatbázis fejlesztő mit nem csinál. Ebben volt két pont, ami további magyarázatra szorul:
- Soha nem tiltjuk a NULL értéket új oszlopokon. Az új oszlopokon mindig engedélyezzük a NULL értéket”: ezzel alapvetően nincs is bajom, ebben az esetben “csak” egy metaadat változás az ALTER TABLE
- “Ha kell az új oszlopnak érték, akkor használjunk DEFAULT CONSTRAINT-t.” : ez utóbbi esetben már vannak problémák, amikkel fogllakozni kell.Az SQL Server 2012 azért hozott újdonságot.
A fentiek mentén nézzük meg, hogyan is néz ez ki a valóságban: először egy SQL Server 2008 R2 (10.50.2500) instance segítségével fogom megvizsgálni.
NULLABLE oszlop hozzáadása
Az első esetben egy táblához hozzáadunk egy új oszlopot, amely enged NULL értékeket és nincs megadva DEFAULT constraint.
Hozzunk létre egy adatbázist TestDb néven.
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
Ezek után kell egy tábla, amin vizsgálni fogjuk az új oszlop hozzáadását. Majd létrehozunk 500 sort ebben a táblában:
1USE [TestDb];
2GO
3
4CREATE TABLE [Table1]
5(
6 col1 int identity(1,1) not null,
7 col2 char(1) not null DEFAULT 'a'
8);
9GO
10
11SET NOCOUNT ON;
12INSERT INTO [Table1] DEFAULT VALUES
13GO 500
14SET NOCOUNT OFF;
Most, hogy van némi adat a táblában, nézzük meg, hogy fizikailag ez hogyan van tárolva, illetve mennyi helyet foglal. Ehhez nem dokumentált parancsokat fogok használni (DBCC IND és DBCC PAGE). Először nézzük, meg, hogy az adataim, melyik lapon vannak:
1DBCC IND('TestDb','Table1', 0, 1)
Ennek az eredménye nálam az alábbi lett:
1PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType
21 79 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10
31 78 1 79 2105058535 0 1 72057594038779904 In-row data 1
Itt látható, hogy a 78-as lapon vannak az adataim; PageType = 1 az adat lapot jelenti. Most nézzük meg, hogy mit látunk az adott lapon az adatokból:
1DBCC TRACEON(3604);
2DBCC PAGE('TestDb', 1, 78, 3);
Ennek eredménye – rövidített formában – az alábbi lett:
1PAGE: (1:78)
2
3
4BUFFER:
5
6
7BUF @0x0000000084FBF440
8
9bpage = 0x0000000084536000 bhash = 0x0000000000000000 bpageno = (1:78)
10bdbid = 22 breferences = 0 bcputicks = 0
11bsampleCount = 0 bUse1 = 12272 bstat = 0xc0000b
12blog = 0x2121bb79 bnext = 0x0000000000000000
13
14PAGE HEADER:
15
16
17Page @0x0000000084536000
18
19m_pageId = (1:78) m_headerVersion = 1 m_type = 1
20m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
21m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
22Metadata: AllocUnitId = 72057594039828480
23Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 0
24Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
25pminlen = 9 m_slotCnt = 500 m_freeCnt = 1096
26m_freeData = 6096 m_reservedCnt = 0 m_lsn = (27:154:3)
27m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
28m_tornBits = -1803265949
29
30Allocation Status
31
32GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
33PFS (1:1) = 0x63 MIXED_EXT ALLOCATED 95_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 @0x0000000014F0A060
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 @0x0000000014F0A06C
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
67
68Slot 2 Offset 0x78 Length 12
69.
70.
71.
Itt pár dologra szeretném felhívni a figyelmet: a Page Header-ben a m_slotCnt = 500 azt jelenti, hogy 500 sorhoz tartozó adatok vannak a lapon. Ez nekünk jó hír, hisz tudjuk, hogy egy lapra ráfért az összes sor. Az első sornál – Slot 0 – látható, hogy a col1 oszlop 4 byte-ot foglal – Length 4 – és fizikailag is 4 byte területet igényel – (physical 4). Ez a col2 oszlopunk esetében 1-1 byte.
Ez eddig tök jó, de akkor most adjunk hozzá egy oszlopot, ahol a NULL érték engedélyezett és ne legyen alapértelmezett értéke.
1ALTER TABLE [Table1]
2ADD [col3] int null
Na most megint nézzük meg a DBCC IND segítségével, hogy mi változott:
1PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType
21 79 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10
31 78 1 79 2105058535 0 1 72057594038779904 In-row data 1
Látszólag semmi nem változott, ugyan úgy egy lapon vannak rajta az adatok. Most nézzük meg megint a 78-as lapot:
1Slot 0 Offset 0x60 Length 12
2
3Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
4
5Memory Dump @0x000000000F74A060
6
70000000000000000: 10000900 01000000 61020000 ††††††††††.. .....a...
8
9Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
10
11col1 = 1
12
13Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
14
15col2 = a
16
17Slot 0 Column 3 Offset 0x0 Length 0 Length (physical) 0
18
19col3 = [NULL]
20
21Slot 1 Offset 0x6c Length 12
Na ez érdekes. Nézzük csak meg jobban a Column 3 részt! Látszik a NULL érték, illetve az is, hogy 0 Byte fizikai helyfoglalása van. Ami persze nem teljesen igaz, mert a NULL BITMAP igenis foglal helyet ;-), de még mindig kevesebbet (1 bit / oszlop, de 2 byte maga a bitmap), mint az aktuális adattípus.
Nézzük csak meg jobban az első sort – Slot 0: 0000000000000000: 10000900 01000000 61020000 Ezt most szedjük apró darabjaira:
0x1000 ami valójában 0x0010 azaz 00000000 00010000 bináris, azaz 16 decimális , mivel az SQL Server little-endian tárolja az adatokat. Ennek alapján az első sorról az alábbiakat lehet megállapítani:
- 1 byte státusz információ – TagA: 0x00
- 1 byte státusz információ – TagB: 0x10
- 2 byte a fix méretű adatok tárolási információkhoz, 0x0009 azaz 0000000 00001001 azaz 9 byte-on van tárolva adat az adott rekordhoz, ha minden - oszlopnak adunk értéket, kivéve NULL.
- 9 byte adat: 0x00000001 azaz 00000000 00000000 00000000 00000001 azaz 1, vagyis a col1 = 1 ; 0x61 azaz 01100001 az ‘a’, vagyis col2 = a. Mivel a - 3. oszlop NULL, ezért csak 5 byte adat van tárolva, a col3 int típus 4 byte-ja nincs.
- 2 byte az oszlopok száma: 0x0002 ez pedig ugye decimális 2.
- 2 byte NULL BITMAP: 0x0000 Hmm, ez megint érdekes. 2 oszlopot mutat csak a lapon és a NULL BITMAP nem mutat NULL értéket. Ami, ha figyelembe vesszük, hogy 2 oszlopra vonatkozó adatok vannak, akkor még helyes is. Érdekes, hogy a NULL értékű új oszlopot nem mutatja.
Na akkor most létrehozok 2 új sort:
1INSERT INTO Table1 (col2, col3) VALUES ('c',null);
2INSERT INTO Table1 (col2, col3) VALUES (DEFAULT, 1);
Most megint megnézem a lapot, ahol az új adatok vannak:
1DBCC PAGE('TestDb', 1, 78, 3);
Ennek mér érdekesebb az eredménye. Az utolsó 3 sorra vonatkozó információk az alábbiak:
1Slot 499 Offset 0x17c4 Length 12
2
3Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
4
5Memory Dump @0x000000000DC4B7C4
6
70000000000000000: 10000900 f4010000 61020000 ††††††††††.. .ô...a...
8
9Slot 499 Column 1 Offset 0x4 Length 4 Length (physical) 4
10
11col1 = 500
12
13Slot 499 Column 2 Offset 0x8 Length 1 Length (physical) 1
14
15col2 = a
16
17Slot 499 Column 3 Offset 0x0 Length 0 Length (physical) 0
18
19col3 = [NULL]
20
21Slot 500 Offset 0x17d0 Length 16
22
23Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16
24
25Memory Dump @0x000000000DC4B7D0
26
270000000000000000: 10000d00 f5010000 63d5df80 00030004 †....õ...cÕß.....
28
29Slot 500 Column 1 Offset 0x4 Length 4 Length (physical) 4
30
31col1 = 501
32
33Slot 500 Column 2 Offset 0x8 Length 1 Length (physical) 1
34
35col2 = c
36
37Slot 500 Column 3 Offset 0x0 Length 0 Length (physical) 0
38
39col3 = [NULL]
40
41Slot 501 Offset 0x17e0 Length 16
42
43Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16
44
45Memory Dump @0x000000000DC4B7E0
46
470000000000000000: 10000d00 f6010000 61010000 00030000 †....ö...a.......
48
49Slot 501 Column 1 Offset 0x4 Length 4 Length (physical) 4
50
51col1 = 502
52
53Slot 501 Column 2 Offset 0x8 Length 1 Length (physical) 1
54
55col2 = a
56
57Slot 501 Column 3 Offset 0x9 Length 4 Length (physical) 4
58
59col3 = 1
Ennek is igen érdekes a kimenete. A Slot 499 esete megegyezik a fentebb kifejtett esettel. A Slot 500 esetében a 0x0004 azaz 00000100 00000000. Mivel kevesebb, mint 8 oszlopunk van, így az első byte figyelembevételével látszik, hogy a 3. oszlop értéke NULL. A Slot 501 esetén a 0x0000 ez az érték, ami azt jelenti, hogy nincs NULL érték ehhez a sorhoz, minden oszlopban az adattípusnak megfelelő érték van.
Látható, hogy ha NULLABLE oszlopot adok egy táblához, akkor az abban lévő NULL értékek csak metaadatként léteznek a táblában már létező sorokhoz. Az új sorok esetében rendesen bekerülnek az adatok.
NOT NULL oszlop hozzáadása
Erre két lehetőség van:
- a táblában még nincs semmi
- a tábla már tartalmaz adatot
Üres tábla esete
Ha a tábla még üres, akkor ez nem egy igazán problémás eset, igazság szerint sima ügy.
1USE [TestDb];
2GO
3
4CREATE TABLE [Table3]
5(
6 col1 int identity(1,1) not null,
7 col2 char(1) not null DEFAULT 'a'
8);
9GO
10ALTER TABLE [Table3]
11ADD [col3] int not null;
12GO
A fenti kód hiba nélkül le fog futni és a col3 oszlopot hozzáadja a Table3 táblához.
Adatokkal teli tábla esete
Itt már sokkal érdekesebb a helyzet. Kezdjük egy egyszerű esettel: a táblában már vannak adatok.
1USE [TestDb];
2GO
3
4DROP TABLE [Table3];
5GO
6
7CREATE TABLE [Table3]
8(
9 col1 int identity(1,1) not null,
10 col2 char(1) not null DEFAULT 'a'
11);
12GO
13INSERT INTO [Table3] DEFAULT VALUES;
14GO
15
16ALTER TABLE [Table3]
17ADD [col3] int not null;
18GO
Sajnos ebben az esetben az alábbi hibaüzenetet kapjuk:
Msg 4901, Level 16, State 1, Line 2 ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'col3' cannot be added to non-empty table 'Table3' because it does not satisfy these conditions.
Sajnos ez így nem fog menni. Próbáljuk meg az alábbi módon, adjunk neki alapértelmezett értéket:
1ALTER TABLE [Table3]
2ADD [col3] int not null DEFAULT 99;
3GO
Így már sikerült. Igen ám, de ez nem volt ingyen! Nézzük csak meg, hogy mi van az adatot tartlamazó lapon.
1DBCC IND('TestDb','Table3', 0, 1);
2GO
3DBCC PAGE('TestDb', 1, 89, 3);
4GO
A kimenet:
1PAGE: (1:89)
2
3
4BUFFER:
5
6
7BUF @0x0000000083FA0B40
8
9bpage = 0x000000008301E000 bhash = 0x0000000000000000 bpageno = (1:89)
10bdbid = 22 breferences = 0 bcputicks = 0
11bsampleCount = 0 bUse1 = 43655 bstat = 0xc0000b
12blog = 0x12121bbb bnext = 0x0000000000000000
13
14PAGE HEADER:
15
16
17Page @0x000000008301E000
18
19m_pageId = (1:89) m_headerVersion = 1 m_type = 1
20m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
21m_objId (AllocUnitId.idObj) = 32 m_indexId (AllocUnitId.idInd) = 256
22Metadata: AllocUnitId = 72057594040025088
23Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 0
24Metadata: ObjectId = 69575286 m_prevPage = (0:0) m_nextPage = (0:0)
25pminlen = 9 m_slotCnt = 1 m_freeCnt = 8078
26m_freeData = 112 m_reservedCnt = 0 m_lsn = (27:251:65)
27m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
28m_tornBits = 0
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 16
37
38Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16
39
40Memory Dump @0x000000000CDEA060
41
420000000000000000: 10000d00 01000000 61630000 00030000 †........ac......
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 0x9 Length 4 Length (physical) 4
53
54col3 = 99
Ez megint érdekes. Most a col3 értéke 4 byte-ot foglal el, és látszik, hogy fizikailag is létezik az adat. Lássuk csak ugyan ezt SQL Server 2012 esetén is:
1--SQL 2012-n futtatni
2USE [master];
3GO
4
5IF DATABASEPROPERTY('TestDb', 'Version') > 0
6 BEGIN
7 ALTER DATABASE [TestDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
8 DROP DATABASE [TestDb];
9 END
10GO
11
12CREATE DATABASE [TestDb];
13GO
14
15USE [TestDb];
16GO
17
18CREATE TABLE [Table1]
19(
20 col1 int identity(1,1),
21 col2 char(1) DEFAULT 'a'
22);
23GO
24
25INSERT INTO [Table1] DEFAULT VALUES;
26GO
27
28DBCC IND('TestDb','Table1', 0, 1);
29GO
30
31DBCC TRACEON(3604);
32DBCC PAGE('TestDb', 1, 93, 3);
33GO
34
35ALTER TABLE [Table1]
36ADD [col4] int not null DEFAULT 99;
37GO
38
39DBCC PAGE('TestDb', 1, 93, 3);
40GO
Itt megint érdekes helyzet állt elő. Az új oszlop értéke nem foglal fizikailag helyet lásd alább:
1PAGE: (1:93)
2
3
4BUFFER:
5
6
7BUF @0x0000000080067C80
8
9bpage = 0x00000001E161C000 bhash = 0x0000000000000000 bpageno = (1:93)
10bdbid = 8 breferences = 1 bcputicks = 0
11bsampleCount = 0 bUse1 = 44320 bstat = 0x9
12blog = 0x15ab215a bnext = 0x0000000000000000
13
14PAGE HEADER:
15
16
17Page @0x00000001E161C000
18
19m_pageId = (1:93) m_headerVersion = 1 m_type = 1
20m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200
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 = 1 m_freeCnt = 8082
26m_freeData = 108 m_reservedCnt = 0 m_lsn = (33:87:24)
27m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
28m_tornBits = 540036734 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
54col4 = 99
Ez egy újdonság az SQL Server 2012-ben, hogy pár kivételtől eltekintve, az új oszlopok alapértelemezett értékkekel történő hozzáadása csak metaadat változás. Megint csak akkor fog a lemezen helyet foglalni, fizikailag a rekordhoz beírásra kerülni, ha az új sor vagy egy meglévő sort frissítek.
Konklúzió
Érdemes odafigyelni az ALTER TABLE utasításra, sok kellementlenségtől kímélhetjük meg magunkat. mindig figyeljünk arra, hogy a megfelelő módon adjunk hozzá oszlopot egy táblához. Ha egy új oszlopnál engedélyezzük a NULL értéket, akkor verziótól függetlenül, ez “csak” egy metaadat változás. Ha az új oszlopban a NULL érték nem megengedtett, akkor üres tábla esetén nincs gond, adatokat tartalmazó tábla esetén meg kell adni alapértelmezett értékeket. Ebben az esetben úgy kell kezelni az oszlop hozzáadást, mint amikor INSERT vagy UPDATE műveleteket végzünk, minden sor frissítésre kerül a táblában. Ez idő és erőforrásigényes művelet. Bizonyos adattípusoknál – részleteket lásd BOL – illetve SQL Server 2012 Enterprise Edition, Enterprise Evaluation Edition és Developer Edition esetén ez is “csak” egy metaadat változtatás és pillanatok alatt végrehajtódik.