How to prevent SQL Server from adding additional parenthesis and changing the definition of a column on it's own when executing a create table script

Question

We have to change the increment value and reseed the identity definition for columns on several tables within one of our databases.

 We accomplish this by using the, “alter table ‘X switch ‘Y“, command which allows us to merely switch the partition from one table to the other.  In order to do this, the table definitions must be identical.  Here’s what SQL is doing when we try and duplicate the [X] table in our DB.

 If you script out the definition of the [X] table, the computed column, [x_hash_id] looks like this, (CONVERT([tinyint],abs([x_id]%(16)),0)).

When we create the clone table, [X_new] using the script, the computed column, [x_hash_id] ends up looking like this, (CONVERT([tinyint],abs([x_id]%(16)),(0))).

 

ORIGINAL:  

(CONVERT([tinyint],abs([x_id]%(16)),0))

CLONE:

(CONVERT([tinyint],abs([x_id]%(16)),(0)))

 

When we run the “alter table ‘X switch ‘Y“, switch command the command fails.

ALTER TABLE SWITCH statement failed. Computed column 'x_hash_id' defined as '(CONVERT([tinyint],abs([x_id]%(16)),0))' in table 'somedb.dbo.x' is different from the same column in table 'somedb.dbo.x_new' defined as '(CONVERT([tinyint],abs([x_id]%(16)),(0)))'.

VERSION:

Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64)

May 29 2022 15:55:47

Copyright (C) 2019 Microsoft Corporation

Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

Answer 1

Do you know under which version of SQL Server this column was created?

I have run into similar issues when they changed how they store the expressions for computed columns, like in this example adding extra parentheses. And like in your case this caused grief, because where was code in a tool that compared the expressions as strings without parsing them. (Full disclosure: I am the author of that tool myself.)

I believe this was in the transition from SQL 2008 to SQL 2012. And indeed, when I run

CREATE TABLE alfa (x_id int IDENTITY (1, 1) NOT NULL,
                   somedata nvarchar(100) NOT NULL,
                   x_hash_id AS convert(tinyint, abs(x_id) % 16),
                   CONSTRAINT pk_alfa PRIMARY KEY (x_id)
)

SELECT definition FROM sys.computed_columns WHERE object_id = object_id('alfa')

on SQL 2008, I get:

(CONVERT([tinyint],abs([x_id])%(16),0))

Whereas if I run the same on later version, I get

(CONVERT([tinyint],abs([x_id])%(16)))

That is, no extra zero at all. And if I add that extra zero, it is bracketed in parens. (Which also happens on SQL 2008.)

You have two choices:

1. Drop the computed column before the switch and recreated afterwards.
2. Start SQL Server in single-user mode and edit the underlying system tables. I definitely recommend against this. (I believe that if you later open a support case, Microsoft may detect that you have been fiddling there, and at that point they may close the case and say that you are not supported.)