Script to ALTER a PERSISTED column - can I check the current column design?

Question

Post reply

Script to ALTER a PERSISTED column - can I check the current column design?

edwardwill

SSCertifiable

Points: 5848
More actions
February 16, 2023 at 12:14 pm

#4150487
I need to create a script to alter a PERSISTED column.
BEFORE
```
[TotalHours] AS ([BaseHours]+[CarryForwardHours]+[ContinuousServiceHours]) PERSISTED,
```
AFTER
```
[TotalHours] AS ([BaseHours]+ISNULL([CarryForwardHours], 0)+ISNULL([ContinuousServiceHours], 0)) PERSISTED,
```
Because our database schema is in source control (git) I need to provide a POSTBUILD script to change the column design in upstream instances, but ideally I only want the script to run if the column design is the BEFORE value (i.e. only once per instance, and not every time the solution is deployed).
So my question is - is there a way to check the schema for a column? We already use a mechanism like this to check for the existence of the column:
```
So my question is - is there a way to check the schema for a column?  We already use a mechanism like this to check for the existence of the column:
 IF EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = @schema
    AND TABLE_NAME = @table
    AND COLUMN_NAME = @column)
    BEGIN 
```
I hope this is clear!
Thanks
Edward

February 18, 2023 at 8:44 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

Use sys.computed_columns. Maybe something like:

IF NOT EXISTS
(
   SELECT 1
   FROM sys.objects O
      JOIN sys.schemas S
         on O.schema_id = S.schema_id
      JOIN sys.computed_columns C
         ON O.object_id = C.object_id
   WHERE S.[name] = 'YourSchema'
      AND O.[name] = 'YourTable'
      AND C.[name] = 'TotalHours'
      AND C.[definition] = '(([BaseHours]+isnull([CarryForwardHours],(0)))+isnull([ContinuousServiceHours],(0)))'
)
BEGIN;
   ALTER TABLE Test DROP COLUMN IF EXISTS TotalHours;
   ALTER TABLE TEST
   ADD TotalHours AS (BaseHours + ISNULL(CarryForwardHours, 0) + ISNULL(ContinuousServiceHours, 0)) PERSISTED;
END;

edwardwill SSCertifiable Points: 5848 More actions · Answer 2

edwardwill

SSCertifiable

Points: 5848

February 16, 2023 at 1:53 pm

#4150542

Thank you Ken - that works a treat!

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

Good. Obviously in real life you will need to check for effects on indexes, permissions etc and sort them out.

Also, you probably should not be allowing these columns to be null in the first place. Jeff Moden has written about the problems with expansive updates.

This reply was modified 1 year, 9 months ago by Ken McKelvey.

edwardwill SSCertifiable Points: 5848 More actions · Answer 4

The columns were permitted to be null for good business reasons. I know that there are two (or possibly more) schools of thought about using null as a meaningful value, but I (and the business I work for) subscribes to a pragmatic view, and in this case allowing null models the real world in a useful way.

Anyway, my (your) script passed code inspection and is now in the solution so all is good ... until the next time!

aitorzur@hotmail.com SSC Rookie Points: 32 More actions · Answer 5

You may want to use "ALTER COLUMN" syntax rather than "ADD/DROP COLUMN" to preserve any security configuration the that could exist on the column.

ratbak Ten Centuries Points: 1358 More actions · Answer 6

You can't alter an existing computed column -- it has to be dropped & re-added.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 7

edwardwill wrote:

The columns were permitted to be null for good business reasons. I know that there are two (or possibly more) schools of thought about using null as a meaningful value, but I (and the business I work for) subscribes to a pragmatic view, and in this case allowing null models the real world in a useful way.

Not a pitch at you.. I just find it funny (it's not actually funny) how the "business" makes such decisions without understanding the frequently extreme ramifications of such things and then blame it on "pragmatism" instead of making the realization that it's a violation of even first normal form. It reminds me of the NULL enddate thing. It's the source issue of ton's of slow code. Worse yet, on things like the NULL enddate thing, they frequently top that off by insisting on the presence of a BIT column named IsActive or, worse yet, Is NotActive. 😀

I wonder how much code their change actually broke above and beyond the persisted column you posted about.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)