{"id":483,"date":"2012-06-03T00:00:04","date_gmt":"2012-06-02T22:00:04","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=483"},"modified":"2026-05-02T14:31:12","modified_gmt":"2026-05-02T12:31:12","slug":"ou-est-mon-enregistrement","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/ou-est-mon-enregistrement\/","title":{"rendered":"O\u00f9 est mon enregistrement ?"},"content":{"rendered":"

Voici un petit article pr\u00e9sentant une fonction non document\u00e9e qui permet de localiser l’endroit o\u00f9 est stock\u00e9e une ligne donn\u00e9e. Cela peut notamment permettre de situer les autres lignes pr\u00e9sentes dans la m\u00eame page, et qui pourraient subir les effets d’un verrou de la page lors d’une modification.<\/p>\n

Pour rappel, les donn\u00e9es sont stock\u00e9es par SQL Server sous forme de pages de 8Ko. Cette taille est fixe; elle permet de comprendre certaines limites au niveau des types de donn\u00e9es (varchar(8000), nvarchar(4000), …), en sachant qu’un enregistrement ne peut pas \u00eatre \u00e0 cheval sur plusieurs pages (hormis pour certains types qui peuvent \u00eatre stock\u00e9s en dehors de la ligne elle-m\u00eame).<\/p>\n

A partir de SQL Server 2008, une nouvelle colonne cach\u00e9e est disponible pour permettre de situer la page dans laquelle se trouve chacun des enregistrements retourn\u00e9s.<\/p>\n

select *,%%physloc%% as [%%physloc%%] from HumanResources.Department<\/pre>\n
\"\"<\/a><\/p>\n
Bon, c’est vrai, vu comme \u00e7a, ce n’est pas tr\u00e8s parlant…<\/p>\n
Les 4 premiers octets correspondent \u00e0 l’identifiant de page, puis 2 octets pour l’identification du fichier, et enfin les deux derniers octets pour le slot, ce qui correspond en gros au num\u00e9ro d’enregistrement dans la page.<\/p>\n
Heureusement pour d\u00e9crypter cela, nous avons aussi droit \u00e0 une magnifique fonction qui nous permet d’avoir un affichage beaucoup plus lisible de la chose.<\/p>\n
select *,sys.fn_PhysLocFormatter(%%physloc%%) as [Emplacement] from HumanResources.Department<\/pre>\n
\"\"<\/a><\/p>\n
Nous constatons ici que les divers enregistrement se situent tous sur la page 665 du premier fichier de ma base. Allons donc y jeter un oeil :<\/p>\n
dbcc page (8,1,665,1)<\/pre>\n\n\n\n
PAGE: (1:665)<\/span>
\nBUFFER:<\/span>
\nBUF @0x0000000085FDBE80<\/span>bpage = 0x00000000859FC000 bhash = 0x0000000000000000 bpageno = (1:665)<\/span>
\nbdbid = 8 breferences = 0 bcputicks = 0<\/span>
\nbsampleCount = 0 bUse1 = 16708 bstat = 0xc00009<\/span>
\nblog = 0x3212159 bnext = 0x0000000000000000<\/span>PAGE HEADER:<\/span>
\nPage @0x00000000859FC000<\/span>m_pageId = (1:665) m_headerVersion = 1 m_type = 1<\/span>
\nm_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x220<\/span>
\nm_objId (AllocUnitId.idObj) = 135 m_indexId (AllocUnitId.idInd) = 256 <\/span>
\nMetadata: AllocUnitId = 72057594046775296 <\/span>
\nMetadata: PartitionId = 72057594044612608 Metadata: IndexId = 1<\/span>
\nMetadata: ObjectId = 757577737 m_prevPage = (0:0) m_nextPage = (0:0)<\/span>
\npminlen = 14 m_slotCnt = 16 m_freeCnt = 6456<\/span>
\nm_freeData = 1704 m_reservedCnt = 0 m_lsn = (41:2945:22)<\/span>
\nm_xactReserved = 0 m_xdesId = (0:1194) m_ghostRecCnt = 0<\/span>
\nm_tornBits = 263213638<\/span>Allocation Status<\/span>GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED <\/span>
\nPFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED<\/span>
\nML (1:7) = NOT MIN_LOGGED<\/span><\/p>\n
DATA:<\/span>
\nSlot 0, Offset 0x60, Length 93, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 93 <\/span>
\nMemory Dump @0x000000000CE3A060<\/span><\/p>\n
0000000000000000: 30000e00 01000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 0002002d 005d0045 006e0067 0069006e \u2020…-.].E.n.g.i.n <\/span>
\n0000000000000020: 00650065 00720069 006e0067 00520065 \u2020.e.e.r.i.n.g.R.e <\/span>
\n0000000000000030: 00730065 00610072 00630068 00200061 \u2020.s.e.a.r.c.h. .a <\/span>
\n0000000000000040: 006e0064 00200044 00650076 0065006c \u2020.n.d. .D.e.v.e.l <\/span>
\n0000000000000050: 006f0070 006d0065 006e0074 00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.o.p.m.e.n.t.<\/span><\/p>\n
Slot 1, Offset 0xbd, Length 93, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 93 <\/span>
\nMemory Dump @0x000000000CE3A0BD<\/span><\/p>\n
0000000000000000: 30000e00 02000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 0002002d 005d0054 006f006f 006c0020 \u2020…-.].T.o.o.l. <\/span>
\n0000000000000020: 00440065 00730069 0067006e 00520065 \u2020.D.e.s.i.g.n.R.e <\/span>
\n0000000000000030: 00730065 00610072 00630068 00200061 \u2020.s.e.a.r.c.h. .a <\/span>
\n0000000000000040: 006e0064 00200044 00650076 0065006c \u2020.n.d. .D.e.v.e.l <\/span>
\n0000000000000050: 006f0070 006d0065 006e0074 00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.o.p.m.e.n.t.<\/span><\/p>\n
Slot 2, Offset 0x11a, Length 71, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 71 <\/span>
\nMemory Dump @0x000000000CE3A11A<\/span><\/p>\n
0000000000000000: 30000e00 03000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 00020021 00470053 0061006c 00650073 \u2020…!.G.S.a.l.e.s <\/span>
\n0000000000000020: 00530061 006c0065 00730020 0061006e \u2020.S.a.l.e.s. .a.n <\/span>
\n0000000000000030: 00640020 004d0061 0072006b 00650074 \u2020.d. .M.a.r.k.e.t <\/span>
\n0000000000000040: 0069006e 006700\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.i.n.g.<\/span><\/p>\n
Slot 3, Offset 0x161, Length 79, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 79 <\/span>
\nMemory Dump @0x000000000CE3A161<\/span><\/p>\n
0000000000000000: 30000e00 04000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 00020029 004f004d 00610072 006b0065 \u2020…).O.M.a.r.k.e <\/span>
\n0000000000000020: 00740069 006e0067 00530061 006c0065 \u2020.t.i.n.g.S.a.l.e <\/span>
\n0000000000000030: 00730020 0061006e 00640020 004d0061 \u2020.s. .a.n.d. .M.a <\/span>
\n0000000000000040: 0072006b 00650074 0069006e 006700\u2020\u2020\u2020\u2020.r.k.e.t.i.n.g.<\/span><\/p>\n
Slot 4, Offset 0x1b0, Length 83, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 83 <\/span>
\nMemory Dump @0x000000000CE3A1B0<\/span><\/p>\n
0000000000000000: 30000e00 05000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 0002002b 00530050 00750072 00630068 \u2020…+.S.P.u.r.c.h <\/span>
\n0000000000000020: 00610073 0069006e 00670049 006e0076 \u2020.a.s.i.n.g.I.n.v <\/span>
\n0000000000000030: 0065006e 0074006f 00720079 0020004d \u2020.e.n.t.o.r.y. .M <\/span>
\n0000000000000040: 0061006e 00610067 0065006d 0065006e \u2020.a.n.a.g.e.m.e.n <\/span>
\n0000000000000050: 007400\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.t.<\/span><\/p>\n
Slot 5, Offset 0x203, Length 119, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 119 <\/span>
\nMemory Dump @0x000000000CE3A203<\/span><\/p>\n
0000000000000000: 30000e00 06000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 00020047 00770052 00650073 00650061 \u2020…G.w.R.e.s.e.a <\/span>
\n0000000000000020: 00720063 00680020 0061006e 00640020 \u2020.r.c.h. .a.n.d. <\/span>
\n0000000000000030: 00440065 00760065 006c006f 0070006d \u2020.D.e.v.e.l.o.p.m <\/span>
\n0000000000000040: 0065006e 00740052 00650073 00650061 \u2020.e.n.t.R.e.s.e.a <\/span>
\n0000000000000050: 00720063 00680020 0061006e 00640020 \u2020.r.c.h. .a.n.d. <\/span>
\n0000000000000060: 00440065 00760065 006c006f 0070006d \u2020.D.e.v.e.l.o.p.m <\/span>
\n0000000000000070: 0065006e 007400\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.e.n.t.<\/span><\/p>\n
Slot 6, Offset 0x27a, Length 69, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 69 <\/span>
\nMemory Dump @0x000000000CE3A27A<\/span><\/p>\n
0000000000000000: 30000e00 07000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 0002002b 00450050 0072006f 00640075 \u2020…+.E.P.r.o.d.u <\/span>
\n0000000000000020: 00630074 0069006f 006e004d 0061006e \u2020.c.t.i.o.n.M.a.n <\/span>
\n0000000000000030: 00750066 00610063 00740075 00720069 \u2020.u.f.a.c.t.u.r.i <\/span>
\n0000000000000040: 006e0067 00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.n.g.<\/span><\/p>\n
Slot 7, Offset 0x2bf, Length 85, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 85 <\/span>
\nMemory Dump @0x000000000CE3A2BF<\/span><\/p>\n
0000000000000000: 30000e00 08000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 0002003b 00550050 0072006f 00640075 \u2020…;.U.P.r.o.d.u <\/span>
\n0000000000000020: 00630074 0069006f 006e0020 0043006f \u2020.c.t.i.o.n. .C.o <\/span>
\n0000000000000030: 006e0074 0072006f 006c004d 0061006e \u2020.n.t.r.o.l.M.a.n <\/span>
\n0000000000000040: 00750066 00610063 00740075 00720069 \u2020.u.f.a.c.t.u.r.i <\/span>
\n0000000000000050: 006e0067 00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.n.g.<\/span><\/p>\n
Slot 8, Offset 0x314, Length 125, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 125 <\/span>
\nMemory Dump @0x000000000CE3A314<\/span><\/p>\n
0000000000000000: 30000e00 09000000 00001e92 00000400 \u20200… ……\u0092…. <\/span>
\n0000000000000010: 00020035 007d0048 0075006d 0061006e \u2020…5.}.H.u.m.a.n <\/span>
\n0000000000000020: 00200052 00650073 006f0075 00720063 \u2020. .R.e.s.o.u.r.c <\/span>
\n0000000000000030: 00650073 00450078 00650063 00750074 \u2020.e.s.E.x.e.c.u.t <\/span>
\n0000000000000040: 00690076 00650020 00470065 006e0065 \u2020.i.v.e. .G.e.n.e <\/span>
\n0000000000000050: 00720061 006c0020 0061006e 00640020 \u2020.r.a.l. .a.n.d. <\/span>
\n0000000000000060: 00410064 006d0069 006e0069 00730074 \u2020.A.d.m.i.n.i.s.t <\/span>
\n0000000000000070: 00720061 00740069 006f006e 00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.r.a.t.i.o.n.<\/span><\/p>\n
Slot 9, Offset 0x391, Length 109, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 109 <\/span>
\nMemory Dump @0x000000000CE3A391<\/span><\/p>\n
0000000000000000: 30000e00 0a000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 00020025 006d0046 0069006e 0061006e \u2020…%.m.F.i.n.a.n <\/span>
\n0000000000000020: 00630065 00450078 00650063 00750074 \u2020.c.e.E.x.e.c.u.t <\/span>
\n0000000000000030: 00690076 00650020 00470065 006e0065 \u2020.i.v.e. .G.e.n.e <\/span>
\n0000000000000040: 00720061 006c0020 0061006e 00640020 \u2020.r.a.l. .a.n.d. <\/span>
\n0000000000000050: 00410064 006d0069 006e0069 00730074 \u2020.A.d.m.i.n.i.s.t <\/span>
\n0000000000000060: 00720061 00740069 006f006e 00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.r.a.t.i.o.n.<\/span><\/p>\n
Slot 10, Offset 0x3fe, Length 135, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 135 <\/span>
\nMemory Dump @0x000000000CE3A3FE<\/span><\/p>\n
0000000000000000: 30000e00 0b000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 0002003f 00870049 006e0066 006f0072 \u2020…?.\u0087.I.n.f.o.r <\/span>
\n0000000000000020: 006d0061 00740069 006f006e 00200053 \u2020.m.a.t.i.o.n. .S <\/span>
\n0000000000000030: 00650072 00760069 00630065 00730045 \u2020.e.r.v.i.c.e.s.E <\/span>
\n0000000000000040: 00780065 00630075 00740069 00760065 \u2020.x.e.c.u.t.i.v.e <\/span>
\n0000000000000050: 00200047 0065006e 00650072 0061006c \u2020. .G.e.n.e.r.a.l <\/span>
\n0000000000000060: 00200061 006e0064 00200041 0064006d \u2020. .a.n.d. .A.d.m <\/span>
\n0000000000000070: 0069006e 00690073 00740072 00610074 \u2020.i.n.i.s.t.r.a.t <\/span>
\n0000000000000080: 0069006f 006e00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.i.o.n.<\/span><\/p>\n
Slot 11, Offset 0x485, Length 89, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 89 <\/span>
\nMemory Dump @0x000000000CE3A485<\/span><\/p>\n
0000000000000000: 30000e00 0c000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 00020037 00590044 006f0063 0075006d \u2020…7.Y.D.o.c.u.m <\/span>
\n0000000000000020: 0065006e 00740020 0043006f 006e0074 \u2020.e.n.t. .C.o.n.t <\/span>
\n0000000000000030: 0072006f 006c0051 00750061 006c0069 \u2020.r.o.l.Q.u.a.l.i <\/span>
\n0000000000000040: 00740079 00200041 00730073 00750072 \u2020.t.y. .A.s.s.u.r <\/span>
\n0000000000000050: 0061006e 00630065 00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.a.n.c.e.<\/span><\/p>\n
Slot 12, Offset 0x4de, Length 91, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 91 <\/span>
\nMemory Dump @0x000000000CE3A4DE<\/span><\/p>\n
0000000000000000: 30000e00 0d000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 00020039 005b0051 00750061 006c0069 \u2020…9.[.Q.u.a.l.i <\/span>
\n0000000000000020: 00740079 00200041 00730073 00750072 \u2020.t.y. .A.s.s.u.r <\/span>
\n0000000000000030: 0061006e 00630065 00510075 0061006c \u2020.a.n.c.e.Q.u.a.l <\/span>
\n0000000000000040: 00690074 00790020 00410073 00730075 \u2020.i.t.y. .A.s.s.u <\/span>
\n0000000000000050: 00720061 006e0063 006500\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.r.a.n.c.e.<\/span><\/p>\n
Slot 13, Offset 0x539, Length 147, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 147 <\/span>
\nMemory Dump @0x000000000CE3A539<\/span><\/p>\n
0000000000000000: 30000e00 0e000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 0002004b 00930046 00610063 0069006c \u2020…K.\u0093.F.a.c.i.l <\/span>
\n0000000000000020: 00690074 00690065 00730020 0061006e \u2020.i.t.i.e.s. .a.n <\/span>
\n0000000000000030: 00640020 004d0061 0069006e 00740065 \u2020.d. .M.a.i.n.t.e <\/span>
\n0000000000000040: 006e0061 006e0063 00650045 00780065 \u2020.n.a.n.c.e.E.x.e <\/span>
\n0000000000000050: 00630075 00740069 00760065 00200047 \u2020.c.u.t.i.v.e. .G <\/span>
\n0000000000000060: 0065006e 00650072 0061006c 00200061 \u2020.e.n.e.r.a.l. .a <\/span>
\n0000000000000070: 006e0064 00200041 0064006d 0069006e \u2020.n.d. .A.d.m.i.n <\/span>
\n0000000000000080: 00690073 00740072 00610074 0069006f \u2020.i.s.t.r.a.t.i.o <\/span>
\n0000000000000090: 006e00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.n.<\/span><\/p>\n
Slot 14, Offset 0x5cc, Length 107, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 107 <\/span>
\nMemory Dump @0x000000000CE3A5CC<\/span><\/p>\n
0000000000000000: 30000e00 0f000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 00020043 006b0053 00680069 00700070 \u2020…C.k.S.h.i.p.p <\/span>
\n0000000000000020: 0069006e 00670020 0061006e 00640020 \u2020.i.n.g. .a.n.d. <\/span>
\n0000000000000030: 00520065 00630065 00690076 0069006e \u2020.R.e.c.e.i.v.i.n <\/span>
\n0000000000000040: 00670049 006e0076 0065006e 0074006f \u2020.g.I.n.v.e.n.t.o <\/span>
\n0000000000000050: 00720079 0020004d 0061006e 00610067 \u2020.r.y. .M.a.n.a.g <\/span>
\n0000000000000060: 0065006d 0065006e 007400\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.e.m.e.n.t.<\/span><\/p>\n
Slot 15, Offset 0x637, Length 113, DumpStyle BYTE<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 113 <\/span>
\nMemory Dump @0x000000000CE3A637<\/span><\/p>\n
0000000000000000: 30000e00 10000000 00001e92 00000400 \u20200……….\u0092…. <\/span>
\n0000000000000010: 00020029 00710045 00780065 00630075 \u2020…).q.E.x.e.c.u <\/span>
\n0000000000000020: 00740069 00760065 00450078 00650063 \u2020.t.i.v.e.E.x.e.c <\/span>
\n0000000000000030: 00750074 00690076 00650020 00470065 \u2020.u.t.i.v.e. .G.e <\/span>
\n0000000000000040: 006e0065 00720061 006c0020 0061006e \u2020.n.e.r.a.l. .a.n <\/span>
\n0000000000000050: 00640020 00410064 006d0069 006e0069 \u2020.d. .A.d.m.i.n.i <\/span>
\n0000000000000060: 00730074 00720061 00740069 006f006e \u2020.s.t.r.a.t.i.o.n <\/span>
\n0000000000000070: 00\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.<\/span><\/p>\n
OFFSET TABLE:<\/span><\/p>\n
Row – Offset <\/span>
\n15 (0xf) – 1591 (0x637) <\/span>
\n14 (0xe) – 1484 (0x5cc) <\/span>
\n13 (0xd) – 1337 (0x539) <\/span>
\n12 (0xc) – 1246 (0x4de) <\/span>
\n11 (0xb) – 1157 (0x485) <\/span>
\n10 (0xa) – 1022 (0x3fe) <\/span>
\n9 (0x9) – 913 (0x391) <\/span>
\n8 (0x8) – 788 (0x314) <\/span>
\n7 (0x7) – 703 (0x2bf) <\/span>
\n6 (0x6) – 634 (0x27a) <\/span>
\n5 (0x5) – 515 (0x203) <\/span>
\n4 (0x4) – 432 (0x1b0) <\/span>
\n3 (0x3) – 353 (0x161) <\/span>
\n2 (0x2) – 282 (0x11a) <\/span>
\n1 (0x1) – 189 (0xbd) <\/span>
\n0 (0x0) – 96 (0x60)<\/span>
\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Nous retrouvons bien ici tous nos enregistrements, comme attendu.<\/p>\n
Il est important de bien noter que la page indiqu\u00e9e correspond \u00e0 l’index qui a \u00e9t\u00e9 utilis\u00e9 pour retourner les enregistrements. Il faut donc bien veiller par exemple \u00e0 ne pas pas esp\u00e9rer retrouver la page de l’index clustered si la requ\u00eate n’utilise qu’un index non clustered. En effet, chaque index utilise ses propres pages.<\/p>\n
Par exemple :<\/p>\n
select\r\n\td.DepartmentID,\r\n\td.GroupName,\r\n\tsys.fn_PhysLocFormatter(%%physloc%%) as [Emplacement]\r\nfrom HumanResources.Department d with (index(PK_Department_DepartmentID))\r\norder by d.DepartmentID\r\n\r\nselect\r\n\td.DepartmentID,\r\n\td.GroupName,\r\n\tsys.fn_PhysLocFormatter(%%physloc%%) as [Emplacement]\r\nfrom HumanResources.Department d with (index(AK_Department_Name))\r\norder by d.DepartmentID<\/pre>\n
Nous constatons que les pages ne sont pas identiques suivant l’index utilis\u00e9.<\/p>\n
\"\"<\/a><\/p>\n
Voil\u00e0 donc pour une petite pr\u00e9sentation de la colonne cach\u00e9e %%physlock%%, qui permet de localiser pr\u00e9cis\u00e9ment\u00a0o\u00f9 les donn\u00e9es sont rang\u00e9es. \u00a0Et le stockage des donn\u00e9es \u00e9tant au coeur de tout moteur, il est toujours int\u00e9ressant d’avoir le maximum d’informations \u00e0 son sujet…<\/p>\n","protected":false},"excerpt":{"rendered":"
Voici un petit article pr\u00e9sentant une fonction non document\u00e9e qui permet de localiser l’endroit o\u00f9 est stock\u00e9e une ligne donn\u00e9e. Cela peut notamment permettre de situer les autres lignes pr\u00e9sentes dans la m\u00eame page, et qui pourraient subir les effets … Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-483","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=483"}],"version-history":[{"count":20,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/483\/revisions"}],"predecessor-version":[{"id":1953,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/483\/revisions\/1953"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}