将列从 NVARCHAR 更改为 NBINARY [英] Alter column from NVARCHAR to NBINARY

问题描述

我不小心创建了一个类型错误的列NVARCHAR(用于存储密码盐)，我想将其转换为NVARBINARY.

I accidentally created a column with the wrong type NVARCHAR (for storing password salts) and I want to convert it to NVARBINARY.

我试过了

ALTER TABLE [dbo].[TableName]
    ALTER COLUMN [ColumnName] [varbinary] (20) NOT NULL
GO

但它说

不允许从数据类型 nvarchar 隐式转换为 varbinary.使用 CONVERT 函数运行此查询.

有没有办法做到这一点?CONVERT 似乎仅用于表达式，而不用于更改.

Is there a way to do that? CONVERT seems to be for expressions only, not for alterations.

推荐答案

改变的唯一方法是:

Create Table a (id int,blb Nvarchar(10))


insert into a Values
(1,'Test'),
(2,N'Test2');

BEGIN Transaction
ALTER TABLE a
ADD blb_New [varbinary] (20) NULL
GO

UPDATE a
SET blb_new = CAST(blb AS varbinary(20))
GO

ALTER TABLE a
DROP COLUMN blb
GO

EXEC sp_rename 'a.blb_new', 'blb', 'COLUMN'
GO
COMMIT Transaction


Select *,CAST(blb as Nvarchar(20)) from a

Drop Table a

这篇关于将列从 NVARCHAR 更改为 NBINARY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！