Search notes:

SQL Server: Creating tables with computed columns

The following snippets try to demonstrate some possibilities and pitfalls when creating tables with computed columns.

Table with a computed column

create table math (
   val_1     decimal(6,2)  not null,
   op        char(1)       not null,
   val_2     decimal(6,2)  not null,
   ------
   result as case op 
     when '+' then val_1 + val_2
     when '-' then val_1 - val_2
     when '*' then val_1 * val_2
     when '/' then val_1 / val_2
   end
);

Github repository about-MSSQL, path: /sql/create/table/columns/computed/division-by-zero/create-table.sql

Inserting values into the table

insert into math values ( 3.2 , '+',  9.1 );
insert into math values (26.7 , '*', 13.5 );
insert into math values ( 4.62, '-',  7.7 );
insert into math values (81.3 , '/',  5.41);

Github repository about-MSSQL, path: /sql/create/table/columns/computed/division-by-zero/insert-values-1.sql

Note: Unlike virtual columns in Oracle, SQL Server does not require to specify the non-virtual columns explicitly.

Select the values

select * from math
--
-- val_1     op  val_2     result
-- --------  --  --------  ----------------
-- 3.20      +   9.10      12.300000000
-- 26.70     *   13.50     360.450000000
-- 4.62      -   7.70      -3.080000000
-- 81.30     /   5.41      15.027726432

Github repository about-MSSQL, path: /sql/create/table/columns/computed/division-by-zero/select-1.sql

Inserting more values

insert into math values ( 9.9, '/', 0)
insert into math values (18.8, '/', 2)

Github repository about-MSSQL, path: /sql/create/table/columns/computed/division-by-zero/insert-values-2.sql

Because the new inserted values cause the computed value to divide by zero, a select * from math will abort when the offending record is returned, the error message being Msg 8134, Level 16, … Divide by zero error encountered..

Index