SQLServer varbinary 存储16进制末尾的"0"丢失

? ? ? ? ? 设置

char(n) NOT NULL 或 binary(n) NOT NULL

char(n) NULL 或 binary(n) NULL

varchar(n) 或 varbinary(n)

ON

填充原始值（char?列具有尾随空格的值，binary?列具有尾随零的值），使达到列的长度。

如果 SET ANSI_PADDING 为 ON，则遵从与?char(n)?或binary(n)?NOT NULL 相同的规则。

不剪裁插入?varchar?列中的字符值的尾随空格。 不剪裁插入?varbinary?列中的二进制值的尾随零。 不将值填充到列的长度。

OFF

填充原始值（char?列具有尾随空格的值，binary?列具有尾随零的值），使达到列的长度。

如果 SET ANSI_PADDING 为 OFF，则遵从与?varchar?或varbinary?相同的规则。

剪裁插入?varchar?列中的字符值的尾随空格。 剪裁插入?varbinary?列中的二进制值的尾随零。

模拟重现：

--	创建表时设置 ANSI_PADDING=OFF ，注意，是创建表前设置！！

SET ANSI_PADDING OFF
GO
--	DROP TABLE [dbo].[Parameter]
CREATE TABLE [dbo].[Parameter](
    [Guid] [uniqueidentifier] NOT NULL,[SID] [varbinary](85) NULL,[Meno] [nvarchar](500) NULL,CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED ([Guid] ASC)
)
GO

SELECT SUSER_SID()
GO
--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF4010000


--	此时设置 ANSI_PADDING=OFF ，不被截断的情况，插入sid 正常
SET ANSI_PADDING OFF
GO
INSERT INTO  [dbo].[Parameter]([Guid],[SID],[Meno])
SELECT NEWID(),SUSER_SID(),NULL
GO
--	0x010500000000000515000000A2F58C0D34EC73663F42C1FCF4010000



--	在设置 ANSI_PADDING=ON 后，新插入 sid 后的0被截掉了
SET ANSI_PADDING ON
GO
INSERT INTO  [dbo].[Parameter]([Guid],NULL
GO
--	0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401


--	上面结果查询：
SELECT SID FROM  [dbo].[Parameter]
WHERE SID=0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401
--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF4010000
--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401


--此时设置 自动截取随尾空格。更改字段结果都被截取！
SET ANSI_PADDING ON
GO
UPDATE [dbo].[Parameter]
SET SID = 0x010500000000000515000000A2F58C0D34EC73663F42C1FCF4010000
WHERE SID=0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401
GO
--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401
--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401


/*上面的环境与系统中遇见的一致了！*/

解决方法：

--按原来列定义alter table 
ALTER TABLE [dbo].[Parameter] ALTER [SID] [varbinary](85) NULL

--再将原字段 SID 末尾添加两位0更新会原表
update t set SID=convert(varbinary(85),stuff(UPPER(master.sys.fn_varbintohexstr(SID)),1,2,'0x')+'00',1)
FROM [dbo].[Parameter] t

注意：

官方建议始终将 ANSI_PADDING 设置为 ON。

在 MicrosoftSQL Server 的未来版本中，ANSI_PADDING 将始终为 ON，将该选项显式设置为 OFF 的任何应用程序都将产生错误。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

当前版本：
Microsoft SQL Server 2008 (SP4)- 10.0.6000.29X64   Sep  3201404:1134   
Copyright c1988-2008 Microsoft Corporation  
Enterprise Edition (64bit on Windows NT 5.2<>Build : 


表结构：
CREATETABLE[dbo].[Parameter](
    Guid]uniqueidentifierNOTNULL,SIDvarbinary](85Menonvarchar500
    CONSTRAINTPK_ParameterPRIMARYKEYCLUSTERED([ASC
ON[PRIMARY
GO
ALTERADDDF_Parameter_Guid  DEFAULTnewsequentialid())FOR
GO

字段 值为 user_sid(),最近处理数据时发现，该表的后两位为0的数都没有了！其实一直以来都没有！

如 SID：0x010500000000000515000000169149CF26DB3E36F846EB9290E602本应为：0x010500000000000515000000169149CF26DB3E36F846EB9290E60200虽然少了后两位数，但是用关联和查询都正常，因此也没被发现。该表除了上面定义的主键、默认值外，没有其他约束、没有触发器，没有视图。而类似的其他表都是正常的！把该表的结构导出测试,也正常！开始以为是约束问题、或者程序问题。但是手动更改之后，发现问题还是在数据库这边，如下测试：

UPDATE T1 set T1.=  
FROM MyDBParameter T1  
WHERE（因为在生产库，没有增加触发器将 insertedsid 输出。）当直接更新表的时候，结果还是一样，似乎后两位数值被截取了或忽略了！更新执行计划如下：|--Clustered Index Update(OBJECT:([MyDB].[dbo].[Parameter].[PK_Parameter] AS [T1]),OBJECT:([MyDB].[dbo].[Parameter].[IX_Parameter] AS [T1]),SET:([MyDB].[dbo].[Parameter].[SID] as [T1].[SID] = [Expr1002]))
  --Compute Scalar(DEFINE:([Expr1014]=[Expr1014]))--Compute Scalar(DEFINE:([Expr1014]=CASE WHEN [Expr1005] THEN (0) ELSE (1) END))
      --Compute Scalar(DEFINE:([Expr1002]=0x010500000000000515000000169149CF26DB3E36F846EB9290E602))
        --Table Spool
          --Top(ROWCOUNT est 0)
            --Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [MyDB].[dbo].[Parameter].[SID] as [T1].[SID] = 0x010500000000000515000000169149CF26DB3E36F846EB9290E602 THEN (1) ELSE (0) END))
              --Index Seek(OBJECT:([MyDB].[dbo].[Parameter].[IX_Parameter] AS [T1]),SEEK:([T1].[SID]=0x010500000000000515000000169149CF26DB3E36F846EB9290E602) ORDERED FORWARD)


看第一行和第四行：SET:([MyDBasT1=Expr1002Compute ScalarDEFINE]=0x010500000000000515000000169149CF26DB3E36F846EB9290E602))更新表表达式]，而表达式]的只为：也就是该标量计算的定义，直接把末尾两位数给去掉了！这是为什么？？！！如果末尾不为0x010500000000000515000000169149CF26DB3E36F846EB9290E60201 
如果末尾为都丢失.不是被截断。而其这样的表都是正常的！0x010500000000000515000000169149CF26DB3E36F846EB9200000000