In a previous post we covered some of the fundamentals about computed columns and we saw the benefits which they can bring for us. Once we’ve implemented them, one of the challenges we may have is if we need to make adjustments to their expression or the fields they reference.
Lets start off with a few records using the same table we started with last time, but we’ll include the basic computed columns from the outset:
CREATE TABLE #ComputedColumns
(
SalesDate DATE,
ProductID INT,
Quantity INT,
Cost DECIMAL(6, 2),
TotalCost AS Quantity * Cost,
IsRecentSale AS CASE
WHEN DATEDIFF(MONTH, SalesDate, GETDATE()) < 3
THEN 1 ELSE 0 END
);
INSERT INTO #ComputedColumns (
SalesDate, ProductID, Quantity, Cost
)
VALUES ('2021-01-01', 1, 2, 1.99), ('2021-04-01', 2, 9, 128.50),
('2021-07-01', 3, 19, 1.00), ('2021-10-01', 3, 26, 18.50),
('2021-12-01', 2, 102, 1050.00), ('2022-01-01', 3, 11, 1.10);
Altering a Computed Column
Using this example lets say that for the sales we wanted to add a 10% mark-up to the value of the products so for the total cost we want to add that logic into the computed column. We can try that with an ALTER statement as we would with a standard column:
ALTER TABLE #ComputedColumns
ALTER COLUMN TotalCost AS (Quantity * Cost) * 1.1;
Unfortunately this isn’t how we can update computed columns and we have an exception thrown:
Incorrect syntax near the keyword 'AS'
The only way in which we can alter a computed column is to drop it from the table and recreate it. As we weren’t using a persisted field this is purely a metadata operation however this wouldn’t be quite as easy if the values are persisted over a large table. The change would look something like this:
ALTER TABLE #ComputedColumns
DROP COLUMN TotalCost;
ALTER TABLE #ComputedColumns
ADD TotalCost AS (Quantity * Cost) * 1.1;
/* That's better! */
SELECT *
FROM #ComputedColumns
This would result in the column moving to the end of the table which may not be an issue but depending on how and what queries the table this could create downstream issues, so its worth testing it out. If we wanted to be really pedantic here we could also drop the IsRecentSale column and re-add that afterwards too. This would leave the table in the same order as when we started, and as its a computed column we won’t be losing data. This only works when the table has computed columns at the end of course.
An alternative to the above is to create a fresh table with the new schema and copy the data across followed by dropping the old table and renaming the new one in its place. This can be quite a long winded way especially if you have a number of indexes or foreign keys on the table which also need to be re-added too. This is the way that Management Studio would perform the update of a computed column definition via the table designer to ensure the ordering of columns is maintained.
Altering Dependant Columns
Its not just the computed columns which can be more awkward to adjust when they’re in the table too. Any column referenced by the computed one can’t simply be updated with a different data type, take the below for example:
ALTER TABLE #ComputedColumns
ALTER COLUMN Quantity BIGINT;
We’re now greeted with the following error being thrown, and we’d get something similar if we tried to drop the column:
The column 'TotalCost' is dependent on column 'Quantity'. ALTER TABLE ALTER COLUMN Quantity failed because one or more objects access this column.
Before we can modify it we need to remove the dependency. Unfortunately we’ll have to once again remove the computed column to allow us to update the data type for the Quantity field. Once that’s complete we can go and add the computed column back into place:
ALTER TABLE #ComputedColumns
DROP COLUMN TotalCost;
ALTER TABLE #ComputedColumns
ALTER COLUMN Quantity BIGINT;
ALTER TABLE #ComputedColumns
ADD TotalCost AS (Quantity * Cost) * 1.1;
Now we’ll have the data type updated and the computed column back in place. As with the prior example this will leave the columns slightly out of order (if that matters) and there’s still the option to do this via the creation of a new table.
Impact on Data Types
In the above examples we’ve seen the columns being dropped and recreated which might not be convenient for us but there’s a key reason why we need to do this – to ensure the computed column can update its data type. Its similar to how we’d need to drop a foreign key constraint if we wanted to change the primary key on a table, the engine wants to keep things consistent.
Previously we’d seen the data typing for the computed columns and how this is set based on the types used in the calculation. When we’re changing the calculation or the fields which this depends on then we need to also adjust the data type for the computed column too to reflect this. We can again check the data types for our columns with the following:
SELECT c.[name] [Column], t.[name] [DataType], c.[precision], c.scale
FROM tempdb.sys.columns c
INNER JOIN tempdb.sys.types t ON c.system_type_id = t.system_type_id
WHERE OBJECT_NAME(object_id, DB_ID('tempdb')) LIKE '#ComputedColumns%';
Our original version of the table had the TotalCost field as a DECIMAL(17, 2), but when we changed the definition to add the 10% mark-up it then changed to NUMERIC(20, 3). Once we’d changed the Quantity to be a BIGINT then this grows further up to NUMERIC(29, 3).
Again this is something where we can have downstream impact depending on what tools may be accessing this data. There may be adjustments needed to applications, ETL processes or reporting tools to make sure these new field sizes are preserved as well as preventing possible overflow or truncation errors as the results of the calculation change. As with other DDL operations its worth considering some relevant testing to cover these situations.
Andy Brownsword 