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.