How to specify the type of the computed column?

Question

I have a table with a payment decimal(5, 2) column and I want to add a computed column to a table:

ALTER TABLE tbl
ADD colComputed AS (ROUND(payment , 0)) / 0.6)) PERSISTED

This works, however, colComputed ends up being numeric(12, 6).

I tried specifying ALTER TABLE tbl ADD colComputed decimal(5, 2) AS ..., but it appears to be invalid syntax. How can I force the computed column to be decimal(5, 2)?

Answer 1

You can cast in the definition of the column itself:

alter table tbl add colComputed as 
  cast( round(payment, 0) / 0.6 as decimal(5, 2)) persisted;

Demo on DB Fiddle:

create table tbl(id int primary key, payment decimal(12, 6));
insert into tbl values (1, 11), (2, 15);

alter table tbl add colComputed as 
  cast( round(payment, 0) / 0.6 as decimal(5, 2) ) persisted;
  
select t.*, round(payment, 0) / 0.6 rawComputed
from tbl t

id	payment	colComputed	rawComputed
1	11.000000	18.33	18.33333333
2	15.000000	25.00	25.00000000