Sql server內部函式fn_PhysLocFormatter存在解析錯誤(續)

Sql server內部函式fn_PhysLocFormatter存在解析錯誤，見http://space.itpub.net/81227/viewspace-751651

 

本文給出錯誤原因。

 

先看下sys.fn_PhysLocFormatter函式的定義：

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函式：

select reverse('工人')

----

人工

 

(1 行受影響)

 

select reverse('12345工人')

---------

人工54321

 

(1 行受影響)

 

select reverse('工12345人')

---------

人54321工

 

(1 行受影響)

結論：問題出在reverse函式上。

reverse函式的作用是字元反轉，而不是位元組反轉，當遇到81-FE之間的位元組時，被認為是雙位元組字元而組合在一起參與反轉操作，造成了錯誤。