Reputation: 10327
If you set up a table's column to be a computed column whose Formula calls a Function, it becomes a pain to change that underlying Function. With every change, you have to find every single column whose Formula that references the Function, remove the reference, save the Table, alter the Function, add everything back, and save again. Even small changes are nightmares.
Can you tell SQL Server that you don't care that the Function is being referenced by Formulas and to just go ahead and change the underlying Function?
Additional Details: The computed column is not persisted or referenced by a FK constraint because it is non-deterministic. The function takes into consideration the current time. It's dealing with the question of whether a record is expired or not.
Upvotes: 10
Views: 12618
Reputation: 2022
I know this is late to the party but I was having this same issue today and didn't find anything which actually solves the issue so I quickly scripted one out.
Essentially it creates a temporary table holding the column info for each computed column using the function, drops the columns from the tables. You then update your function and let it recreate all the columns again with their definitions.
If you have to make changes to the parameters within the definitions (like I need to) you can simply script that part into where the definitions are created again.
If you have computed columns within indexes or other needs you can easily expand on the code but this was beyond the scope of my needs.
Hope it can be useful to someone else.
/* Create temporary table to hold definitions */
CREATE TABLE [#FUNCTION]
(
[TABLE_NAME] nvarchar(255) NOT NULL,
[COLUMN_NAME] nvarchar(255) NOT NULL,
[DEFINITION] nvarchar(255) NOT NULL
)
GO
/* Add data to temp table */
INSERT INTO [#FUNCTION] ( [TABLE_NAME], [COLUMN_NAME], [DEFINITION] )
SELECT TABLE_NAME, COLUMN_NAME, definition FROM INFORMATION_SCHEMA.COLUMNS
INNER JOIN sys.computed_columns ON ( object_id = object_id( TABLE_NAME ) AND name = COLUMN_NAME )
WHERE definition LIKE '%MyFunctionName%'
GO
/* Remove columns */
DECLARE @TABLE_NAME nvarchar(255)
DECLARE @COLUMN_NAME nvarchar(255)
DECLARE c_CursorName CURSOR LOCAL FOR SELECT [TABLE_NAME], [COLUMN_NAME] FROM [#FUNCTION]
OPEN c_CursorName
FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC( 'ALTER TABLE [' + @TABLE_NAME + '] DROP COLUMN [' + @COLUMN_NAME + ']' )
FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME
END
CLOSE c_CursorName
DEALLOCATE c_CursorName
GO
/* Update function */
-- Update function here
GO
/* Recreate computed columns */
DECLARE @TABLE_NAME nvarchar(255)
DECLARE @COLUMN_NAME nvarchar(255)
DECLARE @DEFINITION nvarchar(255)
DECLARE c_CursorName CURSOR LOCAL FOR SELECT [TABLE_NAME], [COLUMN_NAME], [DEFINITION] FROM [#FUNCTION]
OPEN c_CursorName
FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME, @DEFINITION
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC( 'ALTER TABLE [' + @TABLE_NAME + '] ADD [' + @COLUMN_NAME + '] AS ' + @DEFINITION )
FETCH NEXT FROM c_CursorName INTO @TABLE_NAME, @COLUMN_NAME, @DEFINITION
END
CLOSE c_CursorName
DEALLOCATE c_CursorName
GO
/* Remove temp table */
DROP TABLE [#FUNCTION]
GO
Upvotes: 2
Reputation: 61
Sorry for this late answer, but it can be useful.
You can use a dummy function for each computed column that will call your real function.
Example:
The computed column use the formula: dbo.link_comp('123')
This function forward the arguments and calls and return the function dbo.link('123') (Your real function)
Both functions just need to use the same arguments and return the same type.
Then, the function that is locked is dbo.link_comp and you can still ALTER dbo.link.
Also, if your function is called from other SQL, you can still use your real function name dbo.link, the dummy function dbo.link_comp is only for the computed column.
Upvotes: 6
Reputation: 21
Assume table T1 with columns C1, C2, C3, C4, C5 where C4 is a computed column
Also assume the the C4 references function OldFunc which you want to be replaced by NewFunc
First, Move non-computed columns from all rows to a temp table
Select C1, C2, C3, C5 into TmpT1 from T1
Go
Next, Delete all rows from T1
Delete From T1
go
Now You can Modify column C4
Alter table T1 alter column C4 as dbo.NewFunc()
Go
Now put the saved data back into the original table
Insert Into T1 (C1,C2,C3,C5) select C1,C2,C3,C5 from TmpT1
Now Delete The Temp Table
Drop Table TmpT1
Upvotes: 2
Reputation: 4301
You could change the column to be not-computed, and update it by TRIGGER.
Or you could rename the table to something else, drop the computed column, and create a VIEW in place of the original table (i.e. with the original table name) and including the "computed" column you need.
EDIT: note that this may mess with your INSERTs into the original table name (now a VIEW). Obviously you could keep the old table, drop the computed column, and create a separate VIEW that contained the computed column.
We've had to work around Computed Columns enough times to have decided they are more trouble than they gain. Fail-saf inserts(1), trying to insert into VIEWs onto tables with computed columns, things that require messing with SET ARITHABORT and so on.
(1) We have fail-safe inserts like:
INSERT INTO MyTable SELECT * FROM MyOtherTable WHERE ...
which are designed to fail if a new column is added one table and not the other. With Computed Column we have to explicitly name all columns, which loses us that safety net.
Upvotes: 0
Reputation: 432541
The consequences of the ALTER could be huge.
Have you indexed the columns? Used it in a view with schemabinding? Persisted it? Foreign key relationship to it?
What if the ALTER changes the datatype, NULLability or determinism?
It's easier to stop ALTER FUNCTION with dependencies than deal with so many scenarios.
Upvotes: 4
Reputation:
You could try with some good schema compare tool, that create the script for you :)
Upvotes: 0
Reputation: 755167
No, as far as I know, you cannot do this - you'll have to first remove all computed columns referencing a function, alter the function, and then recreate the computed columns.
Maybe MS will give us a "CREATE OR ALTER FUNCTION" command in SQL Server 2010/2011? :-)
Marc
Upvotes: 6