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 Representation of Data

In MS Access, for numeric fields, the decimal places shown can be defined as "Auto" meaning that the database will determine the number of decimal places to show based on the content of the field (i.e. 1.0, 0.75, 1.125).

In SQL Server for the same field, it appears that decimal precision is hard coded resulting in a fixed representation (i.e. 1.000, 0.750, 1.125)

Is there a way to make the decimal representation in SQL Server more like Access where trailing zeros are truncated?

See SQL Server 2005 Books Online topic:

float and real (Transact-SQL)

http://msdn2.microsoft.com/en-us/library/ms173773.aspx

|||

Perfect.

thanks for your help.

Numeric Keys vs Varchar keys

Is there a significant performance advantage to using Numeric keys for FT indexing, both for retrieval and for indexing?
We currently use keys defined as Varchar(25) since we join our text tables to many other tables in doing queries. Would I get performance improvement if I added a new field, as Int and made it the Primary Key, but retained the current ID Column as the fo
reign key for joining to other tables?
We are SQLServer 2000 on Win2K server
Many thanks
Use int. The answer is not related to MSSearch or SQL FTS per say, but
rather your join conditions.
MSSearch locates a row by converting the value of the PK or unique index it
uses to identify the row it is extracting from the database to a hex value,
so it is immaterial what datatype you use or the length of it. Granted there
will be some slight performance implications in converting the PK or unique
index to varbinary(450) IIRC.
This varbinary identifier is stored within the catalog along with the id or
the table the row belongs to. When you issue a query the row the hit belongs
to is converted back from the varbinary(450) to the original datatype.
The real implication is in your joins. If you are joining on an int (and I
realize your other join conditions are on varchar(somethingbig)), your join
conditions will be more effecient on ints, as opposed to something larger,
and the indexes will also be more effecient.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Terry O'Brien" <TerryOBrien@.discussions.microsoft.com> wrote in message
news:8C2CEA6F-1FDA-4093-B766-24402F7BE6C8@.microsoft.com...
> Is there a significant performance advantage to using Numeric keys for FT
indexing, both for retrieval and for indexing?
> We currently use keys defined as Varchar(25) since we join our text tables
to many other tables in doing queries. Would I get performance improvement
if I added a new field, as Int and made it the Primary Key, but retained the
current ID Column as the foreign key for joining to other tables?
> We are SQLServer 2000 on Win2K server
> Many thanks
sql