The internal function fn_PhysLocFormatter in SQL server has parsing error details,

The internal function fn_PhysLocFormatter in SQL server has parsing error details,

Preface

Some netizens pointed out that the internal function of fn_PhysLocFormatter in SQL Server 2012 has an error in parsing the location of the Data row record. For details, refer to: Ignore.

Next, we will first check the location of the Data Row Records in the NT_SiteInfo table.

select SiteID,%%physloc%%,sys.fn_PhysLocFormatter(%%physloc%%) from NT_SiteInfo

SiteID	% Physloc %	Sys. fn_PhysLocFormatter (% physloc %)
1	0xe90000000000000	(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	0x4b0200000000000	(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	0x3c0500000000000	)
425	0x3C05000001000100	)
	......
552	0x3C05000001008000	)
553	0x3C05000001008100	(: 33024)
596	0x3C0500000100AC00	(:44032)
597	Zero x 9978000001000000	(1: 39288: 0)
	......
658	0x9978000001003D00	(1: 39288: 61)

The following shows the data page allocated by NT_SiteInfo.

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 unpublished pseudo column % physloc %, type: Binary (8), returns the RowID recorded in the table, format: the first 4 bytes indicates the page number, 2 bytes in the middle indicates the file number, and the last 2 bytes indicates the slot number.

Based on the actual data above, we can findsys.fn_PhysLocFormatterWhen parsing the record location, both the BIG_ENDIAN format in front of the high byte and the LITTLE_ENDIAN format in front of the low byte cause data parsing errors in the BIG_ENDIAN format in front of the high byte:

Page Number Parsing:
The E9000000 is resolved to 59648 (E900). Error. The actual value must be 233 (E9)

4B020000 resolved to 576 (24B), correct

3C050000 resolution is 1340 (53C), correct

99780000 resolution is 39288 (9978), error, actual should be 30873 (7899)

Slot Number Parsing:

8000 resolved to 128 (0080), correct

8100 resolution is 33024 (8100), error, should be 129 (0081)

The following shows the cause of the error.

Let's take a look at it first.sys.fn_PhysLocFormatterFunction Definition:

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

Let's look at the reverse function again:

Select reverse ('worker') ---- Manual (one row affected) select reverse ('1970 workers') --------- manual 12345 (one row affected) select reverse ('worker 54321 people ') --------- 54321 workers (1 line affected)

Conclusion:The problem lies in the reverse function.

The reverse function is used to reverse the character rather than the byte inversion. When a byte between 8-Fe is encountered, it is considered as a dual-byte character and is combined to participate in the inversion operation, resulting in an error.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.