March 3, 2011 by SteveStedman
Default Values and Computed Columns
In SQL Server 2008, I was presented with the following error when attempting to create a table that has a default value that references other columns in the same table.
Msg 128, Level 15, State 1, Line 6 The name "Subtotal" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
The following CREATE TABLE throws the error.
CREATE TABLE orders (
[OrderId] INT IDENTITY,
[Subtotal] FLOAT,
[Tax] FLOAT,
[GrandTotal] FLOAT DEFAULT (subtotal + tax)
);
After looking it over, I realized that there are at least 2 other ways to fix this error.
- Use a computed column.
- Advantages: Easy to code, easy to follow, less disk space, less memory.
- Disadvantages: Slower because their values are recalculated every time they are referenced in a query.
- Use a PERSISTED computed Column
- Advantages: Easy to code, easy to follow.
- Disadvantages: Slows the insert or update. Takes up more disk space.
- Use a trigger
- Advantage: when the other columns are updated, the [GrandTotal] column gets updated.
- Disadvantages: Triggers are often overlooked. Slows the insert or update of that table. Takes up more disk space and more memory.
Here is how I would do it if it wanted to keep things simple, using a computed column:
CREATE TABLE orders (
[OrderId] INT IDENTITY,
[Subtotal] FLOAT,
[Tax] FLOAT,
[GrandTotal] AS (subtotal + tax) -- this is a computed column
);
INSERT INTO orders (subtotal, tax)
VALUES (10.30, 1.4),
(100.79, 18.2);
SELECT * FROM orders;
Which will produce the following output.
OrderId Subtotal Tax GrandTotal
----------- ---------------------- ---------------------- ----------------------
1 10.3 1.4 11.7
2 100.79 18.2 118.99
(2 row(s) affected)
The one additional thing I would do is add the PERSISTED parameter to the computed column.
CREATE TABLE orders (
[OrderId] INT IDENTITY,
[Subtotal] FLOAT,
[Tax] FLOAT,
-- persist the calculation
[GrandTotal] AS (subtotal + tax) PERSISTED
);
INSERT INTO orders (subtotal, tax)
VALUES (10.30, 1.4),
(100.79, 18.2);
SELECT * FROM orders;
Which will produce the following output.
OrderId Subtotal Tax GrandTotal
----------- ---------------------- ---------------------- ----------------------
1 10.3 1.4 11.7
2 100.79 18.2 118.99
(2 row(s) affected)
