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

当前版本：Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64)   Sep  3 2014 04:11:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build : ) 表结构：CREATE TABLE [dbo].[Parameter](    [Guid] [uniqueidentifier] NOT NULL,    [SID] [varbinary](85) NULL,    [Meno] [nvarchar](500) NULL,    CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED ([Guid] ASC)) ON [PRIMARY]GOALTER TABLE [dbo].[Parameter] ADD CONSTRAINT [DF_Parameter_Guid]  DEFAULT (newsequentialid()) FOR [Guid]GO字段 [SID] 值为 user_sid(),最近处理数据时发现，该表的 [SID] 后两位为0的数都没有了！其实一直以来都没有！如 SID：0x010500000000000515000000169149CF26DB3E36F846EB9290E602本应为：0x010500000000000515000000169149CF26DB3E36F846EB9290E60200虽然少了后两位数，但是用 [SID] 关联和查询都正常，因此也没被发现。该表除了上面定义的主键、默认值外，没有其他约束、没有触发器，没有视图。而类似的其他表都是正常的！把该表的结构导出测试,也正常！开始以为是约束问题、或者程序问题。但是手动更改之后，发现问题还是在数据库这边，如下测试：UPDATE T1 set T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E60200  FROM MyDB.dbo.Parameter T1  WHERE T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E602（因为在生产库，没有增加触发器将 inserted.sid 输出。）当直接更新表的时候，结果还是一样，似乎后两位数值0被截取了或忽略了！更新执行计划如下：|--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:([MyDB].[dbo].[Parameter].[SID] as [T1].[SID] = [Expr1002]Compute Scalar(DEFINE:([Expr1002]=0x010500000000000515000000169149CF26DB3E36F846EB9290E602))[SID] 更新表表达式 [Expr1002]，而表达式 [Expr1002]的只为：0x010500000000000515000000169149CF26DB3E36F846EB9290E602也就是该标量计算的定义，直接把末尾两位数给去掉了！这是为什么？？！！如果末尾不为0,可以正常!UPDATE T1 set T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E60201 FROM MyDB.dbo.Parameter T1  WHERE T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E602如果末尾为0,后面的0都丢失.不是被截断。而其这样的表都是正常的！UPDATE T1 set T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9200000000 FROM MyDB.dbo.Parameter T1  WHERE T1.SID=0x010500000000000515000000169149CF26DB3E36F846EB9290E602

原因：

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

这时存储的问题，尾随0确实被截取了！

参考：（SET ANSI_PADDING）

          设置

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 OFFGO--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))GOSELECT SUSER_SID()GO--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF4010000--此时设置 ANSI_PADDING=OFF ，不被截断的情况，插入sid 正常SET ANSI_PADDING OFFGOINSERT INTO  [dbo].[Parameter]([Guid],[SID],[Meno])SELECT NEWID(),SUSER_SID(),NULLGO--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF4010000--在设置 ANSI_PADDING=ON 后，新插入 sid 后的0被截掉了SET ANSI_PADDING ONGOINSERT INTO  [dbo].[Parameter]([Guid],[SID],[Meno])SELECT NEWID(),SUSER_SID(),NULLGO--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401--上面结果查询：SELECT SID FROM  [dbo].[Parameter]WHERE SID=0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF4010000--0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401--此时设置 自动截取随尾空格。更改字段结果都被截取！SET ANSI_PADDING ONGOUPDATE [dbo].[Parameter]SET SID = 0x010500000000000515000000A2F58C0D34EC73663F42C1FCF4010000WHERE SID=0x010500000000000515000000A2F58C0D34EC73663F42C1FCF401GO--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 的任何应用程序都将产生错误。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。