Options

Computed Column and persistence

Tim_CartwrightTim_Cartwright Posts: 8 New member
in SQL Compare
Recently we modified a computed column to add persistence. When we ran the comparison in Compare 12 it recreated the table which was horrible. I was very happy to see that in Compare 13 it dropped and re-added the table, except that is still not the best way to perform this operation. Can we have the compare use the bottom sql when persistence is changed instead of dropping and recreating the column?

Compare 13:
ALTER TABLE [dbo].[Foo] DROP
COLUMN [Bar]
GO
@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[Foo] ADD
[Bar] AS (CONVERT([bit],case  when [EffectiveDate]=[ExpirationDate] then (1) else (0) end,(0))) PERSISTED

Suggested SQL:
ALTER TABLE [dbo].[Foo] 
ALTER COLUMN ADD PERSISTED

OR:

ALTER TABLE [dbo].[Foo] 
ALTER COLUMN DROP PERSISTED
Tagged:
· Share on Twitter

Answers

Sign In or Register to comment.