numeric vs. money data types performance considerations or gotchas

Hi All,

We are doing some db cleanup and our monetary fields are not defined consistanly. Most are numeric and some are money data type.

Does anyone know of any performance considerations and/or gotchas regarding these two data types? We'd like to convert all monetary amounts to numeric(10,6) fields.

Any feedback would be greatly appreciated.

thanks, dave

This should have a problem if you are using SQL 4.2.1 or 6.5 and I don;t think you are using them so.

Here's an excerpt from Microsoft SQL Server Books Online:
"Monetary Data
Monetary data represents positive or negative amounts of money. In Microsoft? SQL ServerT 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead."

numeric data types in sqlserver

Whats the difference between int,float and decimal in sql server.
WHich data type takes how many bytes?
What data type i should use to store the following sets of data
1set--100000,854275,74892734
2set--6538726.98765923,762.659325
Thanks.int is for integers. 4-byte. Max value is 2^31. This looks adequate for your first set although if the numbers will exceed 2^31 you should use bigint (8 bytes)

float is for floating point numbers (non-integers). You will need floating point for the second set. I would suggest the double type for that set.

decimal, numeric, and float all support customizable precision levels. BOL has pretty good documentation on this.

numeric data types

Hi,

I have a lot of decimals in the flat files. So far the largest numbers map to a numeric(18,6).

My question is, is DT_NUMERIC the correct datatype for this data? In which case, what size do I need to set it? Right now it's 18. Couldn't find much info on this.

Thanks

DT_NUMERIC is correct. For the example you've given, set precision to 18 and scale to 6.|||SSIS data types: http://msdn2.microsoft.com/en-us/library/ms141036.aspx|||Is there a trick to doing this? I tried 18,6 and it said it was invalid.|||

More precisely, the error is:

18,6 is not a valid value for Int32.

I have DT_NUMERIC selected as data type.

|||

Have you set 18 in the precision box and 6 in the scale box or "18,6" in a single box? 18 and 6 are separate attributes.

Thanks.

sql

Number Data Types...

Hello all,

I'm kind of struggling with numeric data types...
I have read alot of material, and think I have a 'decent' grasp on the
various number data types and the effects of the scale and precision.

However, I want to store a value of '1.0', and every variation I try
still truncates the value.

Is there a way to store 1.0 in a number data type?

Thanks so much

Warren MWjWm (warren.medernach@.imaginit.ca) writes:
> I'm kind of struggling with numeric data types...
> I have read alot of material, and think I have a 'decent' grasp on the
> various number data types and the effects of the scale and precision.
> However, I want to store a value of '1.0', and every variation I try
> still truncates the value.
> Is there a way to store 1.0 in a number data type?

I'm not sure that I understand how 1.0 could be truncated. Maybe you
could illustrate with an example?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I suspect that you are confusing datatype with formatting. The integer 1
could be stored as ANY numeric datatype, but how that value is displayed
will be controlled by your client application, not by SQL Server.

--
David Portas
SQL Server MVP
--

Null value for Web Service task input

I'm attempting to use the Web Service task to call a method provided by a vendor. The inputs are all simple types so that helps a lot, but for one input it's a string array. If I enter a single string value for a given id using the array dialog, the method works fine.

However if I try to change the input to use a variable to provide the value it fails. I'm assuming that you can't create a variable to return a string array. Please let me know if this is the case or if I'm missing something obvious ;-)

In addition, is there a way to specify a null value for an input? Currently there doesn't appear to be a way to do this in SP2. I tried leaving the string empty which didn't work and I tried using a variable with expression "NULL(DT_WSTR, 1252)" but that failed too.

You should be able to store an array of strings in a variable typed as Object. Not sure how well that will pass to the web service though.|||

Thanks for your reply John. As you suggested, I changed the variable type to object and assigned a string array to it. I was skeptical that it would work, but when I used that in the Web Service it worked! So many thanks to you!

Also I discovered a way to pass null values for parameters which is a bit unorthodox. You check the variable option for the input, but you don't select a variable and then that parameter is excluded from the request.

However there does appear to be one final obstacle which is that the timeout setting doesn't appear to work. Someone with a similar issue posted it at http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=277689 and I have validated it.