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.
USE [master];
GO
IF DATABASEPROPERTY('TestDb', 'Version') > 0
BEGIN
ALTER DATABASE [TestDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [TestDb];
END
GO
CREATE DATABASE [TestDb];
GO
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:
USE [TestDb];
GO
CREATE TABLE [Table1]
(
col1 int identity(1,1) not null,
col2 char(1) not null DEFAULT 'a'
);
GO
SET NOCOUNT ON;
INSERT INTO [Table1] DEFAULT VALUES
GO 500
SET 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:
DBCC IND('TestDb','Table1', 0, 1)
Ennek az eredménye nálam az alábbi lett:
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType
1 79 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10
1 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:
DBCC TRACEON(3604);
DBCC PAGE('TestDb', 1, 78, 3);
Ennek eredménye – rövidített formában – az alábbi lett:
PAGE: (1:78)
BUFFER:
BUF @0x0000000084FBF440
bpage = 0x0000000084536000 bhash = 0x0000000000000000 bpageno = (1:78)
bdbid = 22 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 12272 bstat = 0xc0000b
blog = 0x2121bb79 bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000084536000
m_pageId = (1:78) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 9 m_slotCnt = 500 m_freeCnt = 1096
m_freeData = 6096 m_reservedCnt = 0 m_lsn = (27:154:3)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -1803265949
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x63 MIXED_EXT ALLOCATED 95_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 12
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
Memory Dump @0x0000000014F0A060
0000000000000000: 10000900 01000000 61020000 ††††††††††.. .....a...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 1
Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
col2 = a
Slot 1 Offset 0x6c Length 12
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
Memory Dump @0x0000000014F0A06C
0000000000000000: 10000900 02000000 61020000 ††††††††††.. .....a...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 2
Slot 1 Column 2 Offset 0x8 Length 1 Length (physical) 1
col2 = a
Slot 2 Offset 0x78 Length 12
.
.
.
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.
ALTER TABLE [Table1]
ADD [col3] int null
Na most megint nézzük meg a DBCC IND segítségével, hogy mi változott:
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType
1 79 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10
1 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:
Slot 0 Offset 0x60 Length 12
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
Memory Dump @0x000000000F74A060
0000000000000000: 10000900 01000000 61020000 ††††††††††.. .....a...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 1
Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
col2 = a
Slot 0 Column 3 Offset 0x0 Length 0 Length (physical) 0
col3 = [NULL]
Slot 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:
INSERT INTO Table1 (col2, col3) VALUES ('c',null);
INSERT INTO Table1 (col2, col3) VALUES (DEFAULT, 1);
Most megint megnézem a lapot, ahol az új adatok vannak:
DBCC PAGE('TestDb', 1, 78, 3);
Ennek mér érdekesebb az eredménye. Az utolsó 3 sorra vonatkozó információk az alábbiak:
Slot 499 Offset 0x17c4 Length 12
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
Memory Dump @0x000000000DC4B7C4
0000000000000000: 10000900 f4010000 61020000 ††††††††††.. .ô...a...
Slot 499 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 500
Slot 499 Column 2 Offset 0x8 Length 1 Length (physical) 1
col2 = a
Slot 499 Column 3 Offset 0x0 Length 0 Length (physical) 0
col3 = [NULL]
Slot 500 Offset 0x17d0 Length 16
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16
Memory Dump @0x000000000DC4B7D0
0000000000000000: 10000d00 f5010000 63d5df80 00030004 †....õ...cÕß.....
Slot 500 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 501
Slot 500 Column 2 Offset 0x8 Length 1 Length (physical) 1
col2 = c
Slot 500 Column 3 Offset 0x0 Length 0 Length (physical) 0
col3 = [NULL]
Slot 501 Offset 0x17e0 Length 16
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16
Memory Dump @0x000000000DC4B7E0
0000000000000000: 10000d00 f6010000 61010000 00030000 †....ö...a.......
Slot 501 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 502
Slot 501 Column 2 Offset 0x8 Length 1 Length (physical) 1
col2 = a
Slot 501 Column 3 Offset 0x9 Length 4 Length (physical) 4
col3 = 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.
USE [TestDb];
GO
CREATE TABLE [Table3]
(
col1 int identity(1,1) not null,
col2 char(1) not null DEFAULT 'a'
);
GO
ALTER TABLE [Table3]
ADD [col3] int not null;
GO
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.
USE [TestDb];
GO
DROP TABLE [Table3];
GO
CREATE TABLE [Table3]
(
col1 int identity(1,1) not null,
col2 char(1) not null DEFAULT 'a'
);
GO
INSERT INTO [Table3] DEFAULT VALUES;
GO
ALTER TABLE [Table3]
ADD [col3] int not null;
GO
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:
ALTER TABLE [Table3]
ADD [col3] int not null DEFAULT 99;
GO
Így már sikerült. Igen ám, de ez nem volt ingyen! Nézzük csak meg, hogy mi van az adatot tartlamazó lapon.
DBCC IND('TestDb','Table3', 0, 1);
GO
DBCC PAGE('TestDb', 1, 89, 3);
GO
A kimenet:
PAGE: (1:89)
BUFFER:
BUF @0x0000000083FA0B40
bpage = 0x000000008301E000 bhash = 0x0000000000000000 bpageno = (1:89)
bdbid = 22 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 43655 bstat = 0xc0000b
blog = 0x12121bbb bnext = 0x0000000000000000
PAGE HEADER:
Page @0x000000008301E000
m_pageId = (1:89) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 32 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040025088
Metadata: PartitionId = 72057594038976512 Metadata: IndexId = 0
Metadata: ObjectId = 69575286 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 9 m_slotCnt = 1 m_freeCnt = 8078
m_freeData = 112 m_reservedCnt = 0 m_lsn = (27:251:65)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 16
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16
Memory Dump @0x000000000CDEA060
0000000000000000: 10000d00 01000000 61630000 00030000 †........ac......
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 1
Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
col2 = a
Slot 0 Column 3 Offset 0x9 Length 4 Length (physical) 4
col3 = 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:
--SQL 2012-n futtatni
USE [master];
GO
IF DATABASEPROPERTY('TestDb', 'Version') > 0
BEGIN
ALTER DATABASE [TestDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [TestDb];
END
GO
CREATE DATABASE [TestDb];
GO
USE [TestDb];
GO
CREATE TABLE [Table1]
(
col1 int identity(1,1),
col2 char(1) DEFAULT 'a'
);
GO
INSERT INTO [Table1] DEFAULT VALUES;
GO
DBCC IND('TestDb','Table1', 0, 1);
GO
DBCC TRACEON(3604);
DBCC PAGE('TestDb', 1, 93, 3);
GO
ALTER TABLE [Table1]
ADD [col4] int not null DEFAULT 99;
GO
DBCC PAGE('TestDb', 1, 93, 3);
GO
Itt megint érdekes helyzet állt elő. Az új oszlop értéke nem foglal fizikailag helyet lásd alább:
PAGE: (1:93)
BUFFER:
BUF @0x0000000080067C80
bpage = 0x00000001E161C000 bhash = 0x0000000000000000 bpageno = (1:93)
bdbid = 8 breferences = 1 bcputicks = 0
bsampleCount = 0 bUse1 = 44320 bstat = 0x9
blog = 0x15ab215a bnext = 0x0000000000000000
PAGE HEADER:
Page @0x00000001E161C000
m_pageId = (1:93) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 84 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594043432960
Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 0
Metadata: ObjectId = 245575913 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 9 m_slotCnt = 1 m_freeCnt = 8082
m_freeData = 108 m_reservedCnt = 0 m_lsn = (33:87:24)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 540036734 DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 12
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 12
Memory Dump @0x000000000D4AA060
0000000000000000: 10000900 01000000 61020000 .. .....a...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
col1 = 1
Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
col2 = a
Slot 0 Column 3 Offset 0x0 Length 4 Length (physical) 0
col4 = 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.