Ú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.

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.

Comments (3) -

  • Kovács Zsolt

    2/22/2013 6:56:45 PM | Reply

    Ez a rész érdekes:
    "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."

    Még nem teszteltem ki, de érdekel, hogy mi történik SQL 2012-ben, ha létrehozom az új oszlopot default constraint-el, majd 1-2 sor insertje a táblába, és ezután megváltoztatom a default constraint-et. Gondlom ezután valahol le kell tárolni az a meglévő sorokhoz tartozó default constraint értéket, illetve az ezután bekerülő új sorokhoz is az új default constraint értékét. De remélem, hogy nem akkor kezdi el kiírni a régi sorokhoz tartozó értéket Laughing

  • Kovács Zsolt

    2/24/2013 7:39:44 PM | Reply

    Hali!

    SQL 2012-vel ez az oszlop hozzáadásos módszer alkalmas lehet archive adatbázisok "tömörítésére"? Smile Gondolok itt arra, hogy a azokat az oszlopokat, amikben kicsi a szelektivitás, utólag adom a táblához és a szükséges sorokon futtatok egy update-ot?

  • Berke János

    2/24/2013 8:01:28 PM | Reply

    Szia Zsolt,
    Érdekes a felvetésed, hogy mi van akkor ha változik a default constraint definiciója. Ki fogom próbálni és frissítem a bejegyzést Smile
    Második kérdésedre a válasz: attól függ Smile Ha sok oszlopod van, akkor érdemes a SPARSE beállításban is gndolkodni, olyankor nincs egyáltalán NULL bitmap, ha jól emlékszem.

Add comment