Bug in Build - Computed Column with DEFAULT Parameter

Matt Connolly

Hi,

I think I've spotted a bug in the Build process: I have a table with a computed column referencing a scalar function using a DEFAULT parameter:

*****************************************

CREATE FUNCTION [dbo].[DefaultParamTest](@I INT, @X INT = NULL)
RETURNS INT
AS BEGIN
SET @X = ISNULL(@X,1);
RETURN @I + @X;
END

GO

-- Columns

CREATE TABLE [dbo].[DefaultParamTestTable]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[ParamTest] AS ([dbo].[DefaultParamTest]([Id],DEFAULT))
) ON [PRIMARY]
GO
-- Constraints and Indexes

ALTER TABLE [dbo].[DefaultParamTestTable] ADD CONSTRAINT [PK_DefaultParamTestTable] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
GO

*****************************************

SQL Source Control has difficulty recognizing the DEFAULT keyword, but does correctly save the file to the repository (however it always flags it as a change, and shows the "latest source control version" as having the DEFAULT parameter missing entirely):

*****************************************

-- Columns

CREATE TABLE [dbo].[DefaultParamTestTable]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[ParamTest] AS ([dbo].[DefaultParamTest]([Id],))
) ON [PRIMARY]
GO
-- Constraints and Indexes

ALTER TABLE [dbo].[DefaultParamTestTable] ADD CONSTRAINT [PK_DefaultParamTestTable] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]
GO

*****************************************

In SQL CI, the build step fails with the following error:

failed: Incorrect syntax near ')'. Error executing the following SQL:
CREATE TABLE [dbo].[DefaultParamTestTable] ( [Id] [int] NOT NULL IDENTITY(1, 1),
[ParamTest] AS ([dbo].[DefaultParamTest]([Id],)) )

Note that the DEFAULT parameter is entirely missing, as in the "latest source control version" in SQL Source Control - but the file in the repository does in fact have the DEFAULT keyword in place.

If I change the computed column to [dbo].[DefaultParamTest]([Id],NULL), instead of [dbo].[DefaultParamTest]([Id],DEFAULT), everything works as expected, both in SQL Source Control and SQL CI.

Has anyone else seen this issue?

Thanks,

Matt

Rob C

Hi Matt,

Thanks for reporting this and for providing the scripts. I have been able to replicate the behaviour and I have filed a new bug report, with reference SC-7809. I will escalate this to the developers and let you know as soon as I hear any new information.

Many thanks,

Rob