none
Persisted computed column based upon a CLR function

    Question

  • Hi

    We have a persisited computed column that references a CLR function for which the source is not referenced inside our database project (if that makes a difference)

    On every deploy, sqlpackage want to drop the column and re-add it , refreshing the objects that reference the table too.

    Is this expected ? As far as i am concerned there has been no change.

    Any thoughts ?


    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/

    Friday, March 16, 2012 3:26 PM

Answers

  • Hey Dave,

    To briefly address your second point, the nullability check-box being grayed out in the Table Designer is By Design, as the engine will automatically determine the nullability of the column based on the expressions used. (Unless of course you specify persisted and not null)

    Back to the root issue, however, I had a hunch when I first read the post that the column was indeed a nullable persisted computed column. There is currently a known bug that our deployment stack does not appropriately account for changes to the nullability of persisted computed columns thus causing the drop/add on the columns on deployment you are experiencing.

    Nevertheless, thank you for adding another report to the issue. Look for the fix in a future release of SSDT!

    Thanks,

    Adam


    Adam Mahood - Program Manager - SQL Server Data Tools

    Monday, March 19, 2012 8:24 PM

All replies

  • Hey Dave,

    Thanks for reporting this. I do have one quick question for you - Is this persisted computed column nullable?

    Thanks,

    Adam


    Adam Mahood - Program Manager - SQL Server Data Tools

    Sunday, March 18, 2012 10:03 PM
  • Hi Adam,

    Yes it is, and by changing it to 'non-null' the behaviour is not seen.  Unfortunatley for us , null is required here.

    Interestingly the table designer has the 'allow nulls' tick box greyed out.

    Dave


    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/

    Monday, March 19, 2012 10:51 AM
  • Hey Dave,

    To briefly address your second point, the nullability check-box being grayed out in the Table Designer is By Design, as the engine will automatically determine the nullability of the column based on the expressions used. (Unless of course you specify persisted and not null)

    Back to the root issue, however, I had a hunch when I first read the post that the column was indeed a nullable persisted computed column. There is currently a known bug that our deployment stack does not appropriately account for changes to the nullability of persisted computed columns thus causing the drop/add on the columns on deployment you are experiencing.

    Nevertheless, thank you for adding another report to the issue. Look for the fix in a future release of SSDT!

    Thanks,

    Adam


    Adam Mahood - Program Manager - SQL Server Data Tools

    Monday, March 19, 2012 8:24 PM
  • Adam,

    I can verify Daves findings. We have a few tables with persisted computed columns basically just adding a few pieces of meta data like computing the length of a VARBINARY(MAX) column for use higher up in the stack. Marking the computed column NOT NULL by manually changing the signature keeps the table(s) from being modified when comparing schemas. To circumvent the NULL issue for the computed column, use NULLIF() with a default value.

    CREATE TABLE [dbo].[file] ( [Id] UNIQUEIDENTIFIER NOT NULL, [FolderID] UNIQUEIDENTIFIER NOT NULL, [PathName] AS (([dbo].[fn_get_folder_path]([FolderID])+'/')+[Name]), [Name] NVARCHAR (256) NOT NULL, [Description] NVARCHAR (2048) NULL, [FileData] VARBINARY (MAX) NULL, -- TODO: Change computed column to datalength([filedata]) instead of using ISNULL. -- A bug in SSDT recreates objects with nullable computed columns [FileLength] AS (ISNULL(datalength([filedata]), 0)) PERSISTED NOT NULL, [ContentType] NVARCHAR (64) NULL, [PropertyXML] NVARCHAR (MAX) NULL, [Version] ROWVERSION NOT NULL );

    Is the bug fixed? if yes, could we get a hotfix somewhere (we're MSFT gold partners)?


    Anders Borum / SphereWorks


    Tuesday, March 27, 2012 9:18 AM
  • Looks like this is fixed in the nov 2012 release, thanks


    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/

    Monday, November 12, 2012 11:00 AM
  • Thanks for the update.

    A mental note to self (and others) is remembering to package the latest SqlPackage.exe and related assemblies when deploying to environment where SSDT is not installed (such as when using a custom installer).


    Anders Borum / SphereWorks

    Monday, November 12, 2012 11:37 AM