Data Types with Computed Columns

Question

Post reply

Data Types with Computed Columns

c2k

Ten Centuries

Points: 1090
More actions
May 20, 2012 at 9:07 pm

#392297

I have a simple table with two columns containing integer values. I would like to compute a third column that is a ratio of the first two columns but I am finding myself in a conundrum with data types. If I use the integer data type my computed column comes up with a 0. If I use something like decimal(9,0) I get a number with too many characters to right of the decimal.
Column 1: 143445634 (decimal(9,0))
Column 2: 452674354 (decimal(9,0))
Column 3: (column 1 / column 2)
I would like the number in Column 3 to be presented to me with only 3 numbers to the right of the decimal….how can I accomplish this feat?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86958 More actions · Answer 1

Perhaps like this?

DECLARE @t TABLE (col1 DECIMAL(9,0), col2 DECIMAL(9,0))

INSERT INTO @t

SELECT 143445634, 452674354

SELECT col1, col2, CAST(1.*col1/col2 AS DECIMAL(4,3))

FROM @t

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

My recommendation is to not be concerned in the least with extra decimal places unless you're outputting to a file. As will all other formatting, it's usually best if you let the GUI format the final display value.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 3

c2k (5/20/2012)
I have a simple table with two columns containing integer values. I would like to compute a third column that is a ratio of the first two columns but I am finding myself in a conundrum with data types. If I use the integer data type my computed column comes up with a 0. If I use something like decimal(9,0) I get a number with too many characters to right of the decimal.
Column 1: 143445634 (decimal(9,0))
Column 2: 452674354 (decimal(9,0))
Column 3: (column 1 / column 2)
I would like the number in Column 3 to be presented to me with only 3 numbers to the right of the decimal….how can I accomplish this feat?

The formula for the precision and scale of the computed column can be found here: Precision, Scale, and Length (Transact-SQL)

In your case, using division, the computed precision is p1 - s1 + s2 + max(6, s1 + p2 + 1) = 9 - 0 + 0 + max(6, 0 + 9 + 1) = 19, and the computed scale is max(6, s1 + p2 + 1) = max(6, 0 + 9 + 1) = 10. So the final data type is decimal(19,10), as you can confirm by running this script:

USE tempdb;

CREATE TABLE #t

(

col1 decimal(9,0),

col2 decimal(9,0),

col3 AS col1 / col2

);

-- decimal (19,10)

EXECUTE sys.sp_columns

@table_name = N'#t',

@column_name = N'col3';

DROP TABLE #t;

If you require a particular precision and scale in the database (perhaps to reduce storage needs if the column is indexed or persisted), you should explicitly use ROUND (Transact-SQL) or CAST and CONVERT (Transact-SQL) in the computed column definition. Read the links carefully to ensure you get the rounding or truncation behaviour you need (it is not intuitive).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi