当前版本:
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]
GO
ALTER 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 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],[SID],[Meno])
SELECT NEWID(),SUSER_SID(),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 的任何应用程序都将产生错误。请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。