Error in deployment script for table rebuild with computed columns

a.higgins · June 15, 2020 3:17PM

When attempting to deploy a change that

necessitated a table rebuild, and
included a computed field

My deployment script ran into an error: invalid column name "MyTable.MyColumn".

Investigation eventually revealed that the error was in the portion of the script that did the rebuild. What's happening is that the script creates a new table "RG_Recovery_1_MyTable" that includes the computed field, but as part of the computed field definition retains the table name of the original computation.

So, for example, when trying to rebuild a table named "FactChecklistCompletion" the script included the following (simplified):

CREATE TABLE dbo.RG_Recovery_1_FactChecklistCompletion<br>&nbsp;&nbsp;&nbsp; (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ChecklistCompletion_SK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL IDENTITY(1, 1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,ChecklistItem_SK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,Person_SK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,ChecklistItemCompletionDate&nbsp;&nbsp;&nbsp; DATETIME2(3) NULL<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,ChecklistItemCompletionDateKey AS (CONVERT(INT, CONVERT(CHAR(8), <b>FactChecklistCompletion.</b>ChecklistItemCompletionDate, (112)), (0)))<br>&nbsp;&nbsp;&nbsp; )<br>GO

Note that the definition of the ChecklistItemCompletionDateKey qualifies the attribute in the formula definition with the original table name, "FactChecklistCompletion".

Fixing this on my end involved manually editing the script to remove the table name from the computed column definition, at which point it ran successfully.

a.higgins · June 15, 2020 3:18PM

Since the code formatter appears not to have liked my line breaks, I'm pasting the relevant script here in an unformatted block:

CREATE TABLE dbo.RG_Recovery_1_FactChecklistCompletion
    (
        ChecklistCompletion_SK         INT          NOT NULL IDENTITY(1, 1)
       ,ChecklistItem_SK               INT          NOT NULL
       ,Person_SK                      INT          NOT NULL
       ,ChecklistItemCompletionDate    DATETIME2(3) NULL
       ,ChecklistItemCompletionDateKey AS (CONVERT(INT, CONVERT(CHAR(8), FactChecklistCompletion.ChecklistItemCompletionDate, (112)), (0)))
    )
GO

Alex B · June 17, 2020 10:43AM

Hi @a.higgins,

I've reproduced it here as well and raised the issue under internal reference SC-10863 with the developers. I will post here when I have an update on the issue, but for now as you have seen, removing the 2 part naming of the columns used in the computed column is what you will need to do to get around this.

Kind regards,
Alex

Alex B · June 19, 2020 10:13AM

Looking into this a bit more with the team - is there a reason you use the two part naming for the columns in the computed column? It doesn't seem like you can actually reference columns in another table in that way so it appears to be redundant.

a.higgins · June 19, 2020 11:44AM

Hey Alex - no particular reason specific to computed columns, we usually qualify object names (and use SQL Prompt to auto-format all code to use it) in general, which means it's probably getting introduced at some point in the development process and then just carried through to the table definition.

Alex B · June 30, 2020 9:23AM

I'm not able to get SQL Prompt to qualify the columns in the computed column. I have all three qualify options ticked, and in the Styles section I have "Qualify object names" ticked, but it doesn't qualify QtyAvailable and UnitPrice in the below table when I format this T-SQL

create table [dbo].[Products]
(
    [ProductID] int identity (1, 1) not null
   ,[QtyAvailable] smallint
   ,[UnitPrice] money
   ,[InventoryValue] as [QtyAvailable] * [UnitPrice]
);

Nor can I get it to insert the columns qualified by the table name when using auto-complete so I'm not sure if it's SQL Prompt - or if it was, if it still would be doing it.

It doesn't seem to remove it if it's there, but won't add it for me, so that may be the way forward here - to remove it so that going forward it doesn't cause a problem.

a.higgins · June 30, 2020 12:03PM

Alex, thanks for your support on this.

I'm guessing, given what you just laid out, that at some point in the development process someone wrote it as a SELECT statement, just to try out the logic of the expression, and then simply copied the expression into the CREATE or ALTER TABLE script.

At this point I'd say that it's more of a development thing and, unless SQL Compare can remove aliases during the table rebuild process, the best approach for my team going forward will be to find and remove those cases!

Thanks again for your help!

Alex B · July 3, 2020 10:04AM

Not a problem!

SQL Compare can't currently and shouldn't really be modifying the code without being told to do so. I suppose it could be an option to specify, but I don't think it would meet the threshold to make it as a new option (not enough people needing it essentially since they don't want to crowd the number of options there).

Take care!

a.higgins · July 6, 2020 12:10PM

Thanks Alex! I certainly understand your perspective on this, and agree that SQL Compare shouldn't be modify the code unnecessarily.

There definitely is an opportunity for a fix here, though, since legitimately-functioning code results in a deployment error when sent through the tool. I'm not necessarily pushing for a change that strips out the unnecessary qualified reference, but there's at least a known limitation of the tool that means "good but weird" code can't get deployed under certain circumstances.

Alex B · July 14, 2020 2:18PM

I've been thinking of how this would work... Since it's a computed column, we can't ALTER TABLE ALTER COLUMN to change it; we would have to DROP and re-ADD it.

Or we could just not include the computed column in the temp table, and then when it was renamed, we could also add the computed column back in using the appropriate table name prefix - though that wouldn't work if "force column order" was on and it was in the middle of the table (otherwise we're back to rebuilding the table again).

Having said all that, it looks like this doesn't even work in SSMS. It just throws this warning when the columns in the computed column are qualified by the table:

Image: https://redgatesupport.zendesk.com/attachments/token/Kq3YRkG5ze77YZpAjANIpaGty/?name=inline-356198467.png

If I remove [Products] qualification it then creates a similar script as Compare with the temp table and copying over data and renaming.

So I think that may be the best thing to do here. I'll feed that back to the developers and see what they think.

Alex B · July 16, 2020 10:26AM

I've spoken with the team again and, given the above, there's just not really a good choice of something to do here. Ideally, since even SSMS doesn't like the columns being qualified by the table as in this case, it wouldn't let it be created that way.

I'll see where best we could put a mention of this being an issue and try to get that updated.

a.higgins · July 16, 2020 12:21PM

Thanks again for all the research on this, Alex - what you're saying makes sense! Please, pass along my appreciation to your dev team, both in particular for their help on this issue and in general for their work on this fantastic product.

Alex B · July 17, 2020 12:50PM

You're most welcome, and I've passed on your feedback to the team as well :-)

Error in deployment script for table rebuild with computed columns

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk