Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 What data tape in SQL allows 98.3 or 103.4

Author  Topic 

Michael71
Posting Yak Master

126 Posts
Posted - 2006-10-26 : 14:40:27
I want to store 98.3, 0.4 or 103.4 like numbers in a SQL database, but I'm having a hard time selecting the correct data type. Also what should be the precision and scale. What is a precision and scale? Please help......

int
varchar
decimal
numeric

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-10-26 : 14:44:52
Data type should be decimal.

From SQL Server Books Online:

quote:

decimal and numeric
Numeric data types with fixed precision and scale.

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.



If you'll only have nnn.n, then decimal(4,1) would be used.

Tara Kizer
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-27 : 00:53:06
Or smallmoney/money. Smallmoney/money is an integer scaled 10000 times, so it is fast to use.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-10-27 : 05:14:52
what do you mean by "int scaled 10000" times peter?




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-27 : 05:19:00
Smallmoney is as fast as any other integer when calculating, even with 4 "decimals", becuase smallmoney is really a integer which is scaled down 10000 times.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-10-27 : 05:22:28
emm... as far as i know int can't have decimals...
so you're saying that it's an int and the decimal place is stored somewhere else?



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-27 : 05:24:36
No. It has something to do with the internal binary representation of the "int".
Taken from Books Online
quote:
smallmoney

Monetary data values from - 214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Storage size is 4 bytes.
Recognize those limits? (Ignore comma and point and focus on the digits only).
Tests I've made indicates that smallmoney is as fast as int when using in calculations.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-10-27 : 05:28:36
yes i do

ahhh.. now i get it.
decimal place is fixed. it always has 4 decimals... DOH...

it takes 12.3 as 12.3000




Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-27 : 05:36:09
Yes, and still only occupies 4 bytes, as DECIMAL occupies at least 8 bytes.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2006-10-27 : 05:39:11
interesting. nice to know. thanx.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-10-27 : 07:53:26
I stay away from smallmoney because there are too many situations where the limit of 214,748.3648 becomes an issue, especially when you are doing a SUM.






CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-10-27 : 07:58:57
quote:
Originally posted by Peso

Yes, and still only occupies 4 bytes, as DECIMAL occupies at least 8 bytes.


Peter Larsson
Helsingborg, Sweden

Not always. From BOL on decimal and numeric:

Precision	Storage bytes 
1 - 9		5 
10-19		9 
20-28		13 
29-38		17


CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-27 : 08:00:50
Only of the sum exceeds max value for MONEY it will.

Taken from BOL
quote:
Return Types [for SUM]
Returns the summation of all expression values in the most precise expression data type.
Expression result               Return type 
integer category                int 
decimal category (p, s)         decimal(38, s) 
money and smallmoney category   money 
float and real category         float

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-10-28 : 02:15:22
"I stay away from smallmoney because there are too many situations where the limit of 214,748.3648 becomes an issue, especially when you are doing a SUM"

And its either demeaning for salary, or just too small ...
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site