Using Convert function to change datatype of computed column

Question

I know about the question found at How do I control the datatype of a computed column?

I add a column by using the followed code. However, there is no datatype found in the Object Explorer > Design. I need the end result employment to show up Object Explorer as Employment numeric (9,0)

alter table dbo.equi add employment AS CAST((m1+m2+m3)/3 AS numeric(9));

I have three columns

m1 numeric (6,0)
m2 numeric (6,0)
m3 numeric (6,0)

So to follow the lead in the previous question, would I use something like this:

CONVERT(numeric(9,0), (
                        CONVERT(NUMERIC(6, 0), [M1])
                      * CONVERT(NUMERIC(6, 0), [M2])
                      * CONVERT(NUMERIC(6, 0), [M3]
                     )

score 0 · Answer 1 · answered Oct 02 '17 at 19:11

In your case you don't need the CONVERT(NUMERIC(6,0),...) because they are redundant as the columns are already NUMERIC(6,0), just use:

CONVERT(numeric(9,0), [M1] * [M2] * [M3])

Also, you don't need to lower the precision because the maximum precision of the operation is (6+6+6+1)=19 and it doesn't exceed the maximum precision of 38 that SQL Server supports (in the question that you links they need to do that because one of the columns have already a precision of 38, so any multiplication would exceed the maximum precision).

Using Convert function to change datatype of computed column

1 Answers1