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

 

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 2

Ezek után nézzük meg, hogy a tábla melyik lapon van:

DBCC IND('TestDb','Table1', 0, 1);
GO

A lap számát felhasználva nézzük meg az adatokat  a lapon:

DBCC TRACEON(3604);
DBCC PAGE('TestDb', 1, 93, 3);
GO

A kimenete pedig:

PAGE: (1:93)


BUFFER:


BUF @0x000000008008A800

bpage = 0x00000001E2374000          bhash = 0x0000000000000000          bpageno = (1:93)
bdbid = 8                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 24059                       bstat = 0xb
blog = 0x15ab21cc                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x00000001E2374000

m_pageId = (1:93)                   m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
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 = 2                       m_freeCnt = 8068
m_freeData = 120                    m_reservedCnt = 0                   m_lsn = (33:92:3)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

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 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 1 Offset 0x6c Length 12

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 12

Memory Dump @0x000000000D4AA06C

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                            

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:

ALTER TABLE [Table1]
ADD [col3] int not null DEFAULT 99;
GO
 
DBCC PAGE('TestDb', 1, 93, 3);
GO

A kimenet:

PAGE: (1:93)


BUFFER:


BUF @0x000000008008A800

bpage = 0x00000001E2374000          bhash = 0x0000000000000000          bpageno = (1:93)
bdbid = 8                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 24155                       bstat = 0xb
blog = 0x15ab21cc                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x00000001E2374000

m_pageId = (1:93)                   m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
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 = 2                       m_freeCnt = 8068
m_freeData = 120                    m_reservedCnt = 0                   m_lsn = (33:92:3)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      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

col3 = 99                           

Slot 1 Offset 0x6c Length 12

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 12

Memory Dump @0x000000000D4AA06C

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 1 Column 3 Offset 0x0 Length 4 Length (physical) 0

col3 = 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:

--USE ALT + Q + M to switch SQLCMD mode 
:CONNECT ADMIN:server\instance
USE [TestDb]
GO

SELECT
	O.[name] AS [table_name],
	C.[name] AS [column_name],
	DC.[name] AS [default_constraint_name],
	DC.[definition] AS [current_default_constraint_definiton],
	SE.[value] AS [meta_default_constraint_definition]
FROM
	sys.sysseobjvalues SE
JOIN
	sys.partitions P ON SE.[id] = P.[partition_id]
JOIN
	sys.objects O ON P.[object_id] = O.[object_id]
JOIN
	sys.columns C ON O.[object_id] = C.[object_id] AND SE.[subid] = C.[column_id]
JOIN
	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,

SELECT * FROM Table1 
/*
col1	col2	col3
1	a	99
2	a	99
*/

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:

USE [TestDb]
GO

ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [DF__Table1__col3__108B795B]
GO

ALTER TABLE [dbo].[Table1] ADD  DEFAULT ((88)) FOR [col3]
GO

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.

SELECT * FROM Table1 
/*
col1	col2	col3
1	a	99
2	a	99
*/

Adjunk hozzá egy új sort és nézzük meg az eredményt:

INSERT INTO [Table1] DEFAULT VALUES;
GO

SELECT * FROM Table1 
/*
col1	col2	col3
1	a	99
2	a	99
3	a	88
*/

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:

...
Slot 2 Offset 0x78 Length 16

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 16

Memory Dump @0x0000000011C1A078

0000000000000000:   10000d00 03000000 61580000 00030000           ........aX......

Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4

col1 = 3                            

Slot 2 Column 2 Offset 0x8 Length 1 Length (physical) 1

col2 = a                            

Slot 2 Column 3 Offset 0x9 Length 4 Length (physical) 4

col3 = 88   
...

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.

Comments (1) -

  • Kovács Zsolt

    2/25/2013 7:09:56 PM | Reply

    Köszi a cikket, jól sikerült és az én kérdéseimet is megválaszoltad.
    Gratulálok hozzá! Smile

Add comment