Alter hashkey of a table

Question

I have a table like below which has a hashkey, this table has data in it, now I need to add additional col and include this column in the hashkey

Create TABLE foo(  userid INT,  usernm varchar(20),  hashkey AS CONVERT([varbinary]20),HASHBYTES('SHA1',isnull(rtrim(userid),'') + isnull(rtrim(usernm),'')  ) ) );

I altered this table to add dt date column

**alter table foo add dt datetime **

Now I want to alter hashkey to include this column, do I have to drop and create a table? Or I can alter the hash key without drop & Create table. I have data in this table, I am using SQL Server 2016SP3, the below statement is failing

alter table foo alter  hashkey AS CONVERT([varbinary]20),HASHBYTES('SHA1',isnull(rtrim(userid),'') + isnull(rtrim(usernm),''+isnull(rtrim(dt),'')

Answer 1

Unfortunately, you can't directly alter a computed column (like your hashkey column) in SQL Server. You must drop it first and then add it back with the new definition.

ALTER TABLE foo

DROP COLUMN hashkey;

ALTER TABLE foo

ADD hashkey AS CONVERT([varbinary](20), HASHBYTES('SHA1', ISNULL(RTRIM(CONVERT(NVARCHAR, userid)), '') + ISNULL(RTRIM(usernm),'') + ISNULL(CONVERT(NVARCHAR, dt),'')));

This will drop the hashkey column and then add it back, this time including the dt field in the calculation of the hash. Please note that the dt column has been converted to NVARCHAR to be able to append it to the string to hash.

Be aware that dropping a column will remove that column and its data from the table. However, since the hashkey column is a computed column, there is no permanent data stored in it, it's computed on the fly based on the other column values. Thus, dropping it and then adding it again will not lead to data loss.

Try to double-check any dependencies on that column before proceeding with this action. There might be other database objects (like views, stored procedures, etc.) relying on this column and they could break if the column is dropped. You should adjust these objects as needed after adding the column back with the new definition.