Error in deployment script for table rebuild with computed columns

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.
Tagged:

Answers

  • a.higginsa.higgins Posts: 90 Bronze 2
    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 BAlex B Posts: 1,131 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Alex BAlex B Posts: 1,131 Diamond 4
    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.


    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • a.higginsa.higgins Posts: 90 Bronze 2
    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 BAlex B Posts: 1,131 Diamond 4
    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.
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • a.higginsa.higgins Posts: 90 Bronze 2
    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 BAlex B Posts: 1,131 Diamond 4
    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!
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • a.higginsa.higgins Posts: 90 Bronze 2
    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 BAlex B Posts: 1,131 Diamond 4
    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:


    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.
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Alex BAlex B Posts: 1,131 Diamond 4
    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.
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • a.higginsa.higgins Posts: 90 Bronze 2
    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 BAlex B Posts: 1,131 Diamond 4
    You're most welcome, and I've passed on your feedback to the team as well :-)
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.