1

I have a table with a PERSISTED and indexed calculated field (Test) based on the result of a function (MyFunctionTest), eg.:

CREATE TABLE [dbo].[TestTable] (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Foo] [int] NOT NULL,
    [Test]  AS ([dbo].[MyFunctionTest]([Foo])) PERSISTED,
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

MyFunctionTest is:

ALTER FUNCTION [dbo].[MyFunctionTest]
(
    @foo int
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    RETURN @foo * 2
END

If I try to alter the function, SQL server shows the error

Cannot ALTER 'dbo.MyFunctionTest' it is being referenced by object 'TestTable'.

The only way for alter the function seem create a new one an alter the the table.

I've also tried to remove WITH SCHEMABINDING

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
ar099968
  • 6,963
  • 12
  • 64
  • 127
  • Not really a question, more of a rant. Those are the rules - you can't change the function due to the dependency. – SMor Jan 16 '19 at 15:24

1 Answers1

1

You can drop the column, alter the function, and add the column back:

ALTER TABLE [dbo].[TestTable]
    DROP COLUMN [Test];

GO

ALTER FUNCTION [dbo].[MyFunctionTest]
(
    @foo int
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    RETURN @foo * 2
END
GO

ALTER TABLE [dbo].[TestTable]
    ADD [Test] AS ([dbo].[MyFunctionTest]([Foo])) PERSISTED

Creating a new function will also mean you need to drop the column and add it back since computed columns can't be altered.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • `Creating a new function will also mean you need to drop the column and add it back` in sqlserver 2005 i can alter computed column... – ar099968 Jan 16 '19 at 15:29
  • i'm on sql server 2005, i have altered a computed column with a new function – ar099968 Jan 16 '19 at 15:31
  • Official documentation starts at 2008 (the oldest currently supported version soon to be out of extended support) and memory is not what it used to be, so I really didn't know it's possible on 2005 version. You really should consider upgrading your sql server version to at least 2012. – Zohar Peled Jan 16 '19 at 15:35
  • Maybe it's the ide (sql server management studio) that "drop - alter" in background... upgrading, i would like, but it isn't my company :D – ar099968 Jan 16 '19 at 15:37