SiteID |
%%physloc%% |
sys.fn_PhysLocFormatter(%%physloc%%) |
1 |
0xE900000001000000 |
(1:59648:0) |
23 |
0xE900000001000100 |
(1:59648:1) |
24 |
0xE900000001000200 |
(1:59648:2) |
|
...... |
|
149 |
0xE900000001007F00 |
(1:59648:127) |
150 |
0xE900000001008000 |
(1:59648:128) |
151 |
0xE900000001008100 |
(1:59648:33024) |
152 |
0xE900000001008200 |
(1:59648:33280) |
|
...... |
|
226 |
0xE90000000100CC00 |
(1:59648:52224) |
227 |
0xE90000000100CD00 |
(1:59648:52480) |
228 |
0x4B02000001000000 |
(1:587:0) |
229 |
0x4B02000001000100 |
(1:587:1) |
|
...... |
|
360 |
0x4B02000001007F00 |
(1:587:127) |
361 |
0x4B02000001008000 |
(1:587:128) |
362 |
0x4B02000001008100 |
(1:587:33024) |
363 |
0x4B02000001008200 |
(1:587:33280) |
|
...... |
|
422 |
0x4B0200000100BD00 |
(1:587:48384) |
423 |
0x4B0200000100BE00 |
(1:587:48640) |
424 |
0x3C05000001000000 |
(1:1340:0) |
425 |
0x3C05000001000100 |
(1:1340:1) |
|
...... |
|
552 |
0x3C05000001008000 |
(1:1340:128) |
553 |
0x3C05000001008100 |
(1:1340:33024) |
|
|
|
596 |
0x3C0500000100AC00 |
(1:1340:44032) |
597 |
0x9978000001000000 |
(1:39288:0) |
|
...... |
|
658 |
0x9978000001003D00 |
(1:39288:61) |
下面查看表NT_SiteInfo分配的數(shù)據(jù)頁(yè)情況。
dbcc ind(wjgk,nt_siteinfo,0)
PagePID |
IAMFID |
IAMPID |
PageType |
IndexLevel |
NextPagePID |
PrevPagePID |
238 |
NULL |
NULL |
10 |
NULL |
0 |
0 |
233 |
1 |
238 |
1 |
0 |
587 |
0 |
587 |
1 |
238 |
1 |
0 |
1340 |
233 |
1340 |
1 |
238 |
1 |
0 |
30873 |
587 |
30873 |
1 |
238 |
1 |
0 |
0 |
1340 |
Microsoft未公開的偽列%%physloc%%,類型為Binary(8),返回表中記錄的RowID,格式是:前4字節(jié)表示頁(yè)號(hào),中間2字節(jié)表示文件號(hào),最后2字節(jié)表示槽號(hào)。
對(duì)照上面的實(shí)際數(shù)據(jù),可以發(fā)現(xiàn)sys.fn_PhysLocFormatter
在解析記錄位置時(shí),既有采用高字節(jié)在前的BIG_ENDIAN格式,又有采用低字節(jié)在前的LITTLE_ENDIAN格式,造成采用高字節(jié)在前的BIG_ENDIAN格式解析的數(shù)據(jù)錯(cuò)誤:
頁(yè)號(hào)解析:
E9000000解析為59648(E900),錯(cuò)誤,實(shí)際應(yīng)為233(E9)
4B020000解析為576(24B),正確
3C050000解析為1340(53C),正確
99780000解析為39288(9978),錯(cuò)誤,實(shí)際應(yīng)為30873(7899)
槽號(hào)解析:
8000解析為128(0080),正確
8100解析為33024(8100),錯(cuò)誤,應(yīng)為129(0081)
下面給出錯(cuò)誤原因。
先看下sys.fn_PhysLocFormatter
函數(shù)的定義:
select OBJECT_DEFINITION(object_id('sys.fn_PhysLocFormatter')) go ------------------------------------------------------------------------------- -- Name: sys.fn_PhysLocFormatter -- -- Description: -- Formats the output of %%physloc%% virtual column -- -- Notes: ------------------------------------------------------------------------------- create function sys.fn_PhysLocFormatter (@physical_locator binary (8)) returns varchar (128) as begin declare @page_id binary (4) declare @file_id binary (2) declare @slot_id binary (2) -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot -- select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4))) select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2))) select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2))) return '(' + cast (cast (@file_id as int) as varchar) + ':' + cast (cast (@page_id as int) as varchar) + ':' + cast (cast (@slot_id as int) as varchar) + ')' end
再看下reverse函數(shù):
select reverse('工人') ---- 人工 (1 行受影響) select reverse('12345工人') --------- 人工54321 (1 行受影響) select reverse('工12345人') --------- 人54321工 (1 行受影響)
結(jié)論:問題出在reverse函數(shù)上。
reverse函數(shù)的作用是字符反轉(zhuǎn),而不是字節(jié)反轉(zhuǎn),當(dāng)遇到81-FE之間的字節(jié)時(shí),被認(rèn)為是雙字節(jié)字符而組合在一起參與反轉(zhuǎn)操作,造成了錯(cuò)誤。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,謝謝大家對(duì)腳本之家的支持。
標(biāo)簽:銅仁 益陽(yáng) 天水 來賓 防疫戰(zhàn)設(shè) 七臺(tái)河 宿州 威海
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《Sql server中內(nèi)部函數(shù)fn_PhysLocFormatter存在解析錯(cuò)誤詳解》,本文關(guān)鍵詞 Sql,server,中,內(nèi)部,函數(shù),PhysLocFormatter,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。