I know how to create mount point in windows 20003 cluster, I am not sure how
to set it up with SQL 2005 running on the cluster.
Does sql need to be dependant on any of the disks? I have tried looking for
a guide, but cannot find.
current setup active active cluster running. I need to add san space which
will hold the databases. The san will be carved up into drive letters. each
drive letter will hold 3 mount points.
ie.
node 1
J:-2 mount point
k:2 mount point
l:2 mount point
node 2-
r:-2 mount point
s:2 mount point
t:2 mount point
each node would be able to own the disk if the other node failed over.
any help is appreciate. I have tried books online etc.. cannot find a good
step by step.;
Vision,
there are three area's you want to get familiar with prior to setting up a
production cluster
1) how to setup mountpoints in a cluster
http://support.microsoft.com/kb/280297
2) Be aware of this one, I have seen this once or twice after installing
SP1, if you do not have the symptom you do not need the hotfix.
http://support.microsoft.com/kb/898790
3) SQL 2005 and mount points
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
http://support.microsoft.com/kb/819546
Test everything thoroughly before you put this into any production.
HTH,
_Edwin.
"vision" <vision@.discussions.microsoft.com> wrote in message
news:F0C6D7D8-F9F2-46DF-A128-2D384DACD40C@.microsoft.com...
> I know how to create mount point in windows 20003 cluster, I am not sure
how
> to set it up with SQL 2005 running on the cluster.
> Does sql need to be dependant on any of the disks? I have tried looking
for
> a guide, but cannot find.
> current setup active active cluster running. I need to add san space
which
> will hold the databases. The san will be carved up into drive letters.
each
> drive letter will hold 3 mount points.
> ie.
> node 1
> J:-2 mount point
> k:2 mount point
> l:2 mount point
> node 2-
> r:-2 mount point
> s:2 mount point
> t:2 mount point
> each node would be able to own the disk if the other node failed over.
> any help is appreciate. I have tried books online etc.. cannot find a
good
> step by step.;
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
MonthName Chart Problem
I'm having a problem printing the name of the month on a monthly chart.
The data that I am attempting to chart includes a data point, a year (int, eg. 2006), and a month (int 1-12). A parameter is used to specify which months are included in the data. In some cases, we chart calendar years, in others we chart the previous 12 or 24 months. Each chart can include up to 4 years data. Each year is charted as a separate dynamic series. The data arrives sorted by year then month ascending.
When I chart calendar years, there's no problem. The data arrives sorted, and starts with January and continues through December.
The problem arises when I try to chart the Last 12 months. In this case, the data arrives sorted by year, then month - from June 2005 (6 / 2005) to May 2006 (5/2006). From June to December, the month names are printed correctly. However, from January to May, only numbers are printed.
The dataset code for the chart portion of the report follows below. I've honestly tried everything I can think of, including decoding and passing the full month name to the chart for labels. Your expert advice is very much appreciated.
Thanks!
Dataset Code:
<DataSetName>main</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Years">
<GroupExpressions>
<GroupExpression>=Fields!YEAR.Value</GroupExpression></GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!YEAR.Value</SortExpression> <- Contains Year int
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression> <- Contains Month int
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!YEAR.Value</Label>
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>=Fields!METRIC_NM.Value</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
...
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Months">
<GroupExpressions>
<GroupExpression>=Fields!TIME_DIMENSION.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Fields!TIME_DIMENSION.Value)</Label> <-The inconsistent month label
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
...
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!TIME_DIMENSION.Value</Value>
</DataValue>
<DataValue>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Format>#,##0.00</Format>
</Style>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataLabel>
<Style>
...
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
...
</Chart>
I think this comes from the fact that you have a series grouping based on the year. You effectively have the first series for the months in the past year and the second series for the months of the current year:
2005: Jun Jul Aug Sep Oct Nov Dec (8) (9) (10) (11) (12)
2006: Jan Feb Mar Apr May
I bet you see the labels as shown in the "2005" line, right?
You can try removing the series grouping, and instead add two category groupings. The first category grouping is based on year, the second category grouping is based on the month.
-- Robert
|||
Thanks. You are of course entirely correct.
Eureka it works! The chart is alive. It's ALIVE!
MonthName Chart Problem
I'm having a problem printing the name of the month on a monthly chart.
The data that I am attempting to chart includes a data point, a year (int, eg. 2006), and a month (int 1-12). A parameter is used to specify which months are included in the data. In some cases, we chart calendar years, in others we chart the previous 12 or 24 months. Each chart can include up to 4 years data. Each year is charted as a separate dynamic series. The data arrives sorted by year then month ascending.
When I chart calendar years, there's no problem. The data arrives sorted, and starts with January and continues through December.
The problem arises when I try to chart the Last 12 months. In this case, the data arrives sorted by year, then month - from June 2005 (6 / 2005) to May 2006 (5/2006). From June to December, the month names are printed correctly. However, from January to May, only numbers are printed.
The dataset code for the chart portion of the report follows below. I've honestly tried everything I can think of, including decoding and passing the full month name to the chart for labels. Your expert advice is very much appreciated.
Thanks!
Dataset Code:
<DataSetName>main</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Years">
<GroupExpressions>
<GroupExpression>=Fields!YEAR.Value</GroupExpression></GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!YEAR.Value</SortExpression> <- Contains Year int
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression> <- Contains Month int
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!YEAR.Value</Label>
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>=Fields!METRIC_NM.Value</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
...
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Months">
<GroupExpressions>
<GroupExpression>=Fields!TIME_DIMENSION.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!TIME_DIMENSION.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Fields!TIME_DIMENSION.Value)</Label> <-The inconsistent month label
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
...
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!TIME_DIMENSION.Value</Value>
</DataValue>
<DataValue>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style>
<Format>#,##0.00</Format>
</Style>
<Value>=Fields!METRIC_SUMMARY_VALUE.Value</Value>
</DataLabel>
<Style>
...
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
...
</Chart>
I think this comes from the fact that you have a series grouping based on the year. You effectively have the first series for the months in the past year and the second series for the months of the current year:
2005: Jun Jul Aug Sep Oct Nov Dec (8) (9) (10) (11) (12)
2006: Jan Feb Mar Apr May
I bet you see the labels as shown in the "2005" line, right?
You can try removing the series grouping, and instead add two category groupings. The first category grouping is based on year, the second category grouping is based on the month.
-- Robert
|||Thanks. You are of course entirely correct.
Eureka it works! The chart is alive. It's ALIVE!
Monday, March 12, 2012
Monitoring Mount points
Hello,
We have a requirement to be able to monitor mount points. The xp_fixeddrives does not support mount point monitoring. Is there another way to do it. Does microsoft working on updating the xp_fixeddrives. Let me know if anyone has any ideas how to monitor mount points.
Thanks
xp_fixeddrives is an undocumented and unsupported sproc. I don't think MS will update it - they might just remove it without warning.
You can use Junction.exe and xp_cmdshell...
http://www.microsoft.com/technet/sysinternals/FileAndDisk/Junction.mspx
Monday, February 20, 2012
money and accuracy
What's the best way to avoid the problem of being off by a cent or 2 in
money calculations due to internal floating point rounding? Does using the
"money" datatype solve this alone? Do you need to use all "integer" data
types insetad and handle displaying the data appropriately manually? I don't
want to end up with reports where the grand total at the end of a column of
numbers ends up different then if you were to figure it out on a pocket
calculator. What's the best strategy for this?
Thanks,
KeithDon't use float or double datatypes for currency values !
There is a specific datatype (Called money, or for less range and
precision, smallmoney) that is specifically designed for currency values,
that does not have this issue.
"Keith G Hicks" wrote:
> What's the best way to avoid the problem of being off by a cent or 2 in
> money calculations due to internal floating point rounding? Does using th
e
> "money" datatype solve this alone? Do you need to use all "integer" data
> types insetad and handle displaying the data appropriately manually? I don
't
> want to end up with reports where the grand total at the end of a column o
f
> numbers ends up different then if you were to figure it out on a pocket
> calculator. What's the best strategy for this?
> Thanks,
> Keith
>
>|||Sorry - didn;t read yr entire post unti lafter Isent last ... Yes, to answe
r
your second question - Money solves this alone.
And No, to yr 3rd ? YOu don;t need to use integral datatypes...
"Keith G Hicks" wrote:
> What's the best way to avoid the problem of being off by a cent or 2 in
> money calculations due to internal floating point rounding? Does using th
e
> "money" datatype solve this alone? Do you need to use all "integer" data
> types insetad and handle displaying the data appropriately manually? I don
't
> want to end up with reports where the grand total at the end of a column o
f
> numbers ends up different then if you were to figure it out on a pocket
> calculator. What's the best strategy for this?
> Thanks,
> Keith
>
>|||Keith
I'd recommend you using DECIMAL(18,3) datatype fo such things. For more
details please refer to the BOL
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:OxXTufzJFHA.2468@.TK2MSFTNGP10.phx.gbl...
> What's the best way to avoid the problem of being off by a cent or 2 in
> money calculations due to internal floating point rounding? Does using
the
> "money" datatype solve this alone? Do you need to use all "integer" data
> types insetad and handle displaying the data appropriately manually? I
don't
> want to end up with reports where the grand total at the end of a column
of
> numbers ends up different then if you were to figure it out on a pocket
> calculator. What's the best strategy for this?
> Thanks,
> Keith
>|||Would it also then make sense do convert numbers to integers when doing
calculations and then convert them back to decimal values when posting the
results back to the table or displaying results in calculated grid columns
or reports? This made a lot of sense to me.
Amount Each Number Purchased Total
$1.23 1000 $1230.00
$0.57 2000 $1140.00
$2.13 1500 $3195.00
----
4500 $7620.00
We all know that the end result if there are a lot of rows could be 7619.99
or 7620.02 (or some other value close to the correct total). In fact I've
seen results where $1.23 * 1000 = 1230.01. But by converting $1.23 to 123
before multiplying by 1000 and then converting 123000 to 1230.00 and also
doing the same thing for the grand total you would end up with 100% accuracy
at all times.
Is there a better way to handle this? Some applications and clients do not
demand that kind of accuracy. But some do. What I'm currently working on
does. Is there a better solution to the problem? I've heard some developers
don't even use decimal values in teh back end for storing money. They use
integers only and then display decimals where needed to avoid the entire
problem altogether. That sounds too complex.
Any other suggestions would be welcomed.
Keith :)|||> We all know that the end result if there are a lot of rows could be
7619.99
> or 7620.02 (or some other value close to the correct total)
If you use inexact numerics such as FLOAT or REAL then that can happen,
yes. Don't use FLOAT or REAL for currency amounts. Use NUMERIC instead.
> In fact I've
> seen results where $1.23 * 1000 = 1230.01
With what datatype? Not with NUMERIC.
> I've heard some developers
> don't even use decimal values in teh back end for storing money. They
use
> integers only and then display decimals where needed to avoid the
entire
> problem altogether. That sounds too complex.
None of that is necessary. You can experience rounding problems with
SQL Server's MONEY type because the result of a division may be a MONEY
with 4 decimal places of precision. That's why I repeat, NUMERIC is the
best type for currency amounts in most cases IMO.
If you are using NUMERIC and you still think you have a problem with
rounding then please come back with some code that will actually
reproduce the incorrect result you are getting.
David Portas
SQL Server MVP|||Thanks for the input David.
What about on the front end? I see your point if calculations are taking
place in a trigger or a stored procedure but what about data types in a
front end like VB or Delphi? Oftentimes calculations need to be done on the
front end. Does it matter what data type is used there? Regardless of what
datatype the actual column in the table is defined as, I assume the front
end datatype would have an impact as well.
Also, according to BOL, Numeric and Decimal are identical. Is this not
actually the case?
Keith
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110720541.051228.117300@.g14g2000cwa.googlegroups.com...
> We all know that the end result if there are a lot of rows could be
7619.99
> or 7620.02 (or some other value close to the correct total)
If you use inexact numerics such as FLOAT or REAL then that can happen,
yes. Don't use FLOAT or REAL for currency amounts. Use NUMERIC instead.
> In fact I've
> seen results where $1.23 * 1000 = 1230.01
With what datatype? Not with NUMERIC.
> I've heard some developers
> don't even use decimal values in teh back end for storing money. They
use
> integers only and then display decimals where needed to avoid the
entire
> problem altogether. That sounds too complex.
None of that is necessary. You can experience rounding problems with
SQL Server's MONEY type because the result of a division may be a MONEY
with 4 decimal places of precision. That's why I repeat, NUMERIC is the
best type for currency amounts in most cases IMO.
If you are using NUMERIC and you still think you have a problem with
rounding then please come back with some code that will actually
reproduce the incorrect result you are getting.
David Portas
SQL Server MVP|||> Regardless of what
> datatype the actual column in the table is defined as, I assume the front
> end datatype would have an impact as well.
Definitely. Do as much calculations as possible in SQL Server, and know your
dev tool datatypes and
select carefully!
> Also, according to BOL, Numeric and Decimal are identical. Is this not
> actually the case?
They are the same. Did you find something in the thread indicating something
else? (I browsed
through the thread and couldn't find such statement.)
They are not exactly the same in ANSI SQL, though. In ANSI SQL, the engine c
an give a higher
precision than asked for for DECIMAL. For numeric, you get what you ask for
(which is how SQL Server
does it for both DEC and NUM).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Keith G Hicks" <krh@.comcast.net> wrote in message news:%23SbC6I9JFHA.2212@.TK2MSFTNGP12.phx
.gbl...
> Thanks for the input David.
> What about on the front end? I see your point if calculations are taking
> place in a trigger or a stored procedure but what about data types in a
> front end like VB or Delphi? Oftentimes calculations need to be done on th
e
> front end. Does it matter what data type is used there? Regardless of what
> datatype the actual column in the table is defined as, I assume the front
> end datatype would have an impact as well.
> Also, according to BOL, Numeric and Decimal are identical. Is this not
> actually the case?
> Keith
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1110720541.051228.117300@.g14g2000cwa.googlegroups.com...
> 7619.99
> If you use inexact numerics such as FLOAT or REAL then that can happen,
> yes. Don't use FLOAT or REAL for currency amounts. Use NUMERIC instead.
>
> With what datatype? Not with NUMERIC.
>
> use
> entire
> None of that is necessary. You can experience rounding problems with
> SQL Server's MONEY type because the result of a division may be a MONEY
> with 4 decimal places of precision. That's why I repeat, NUMERIC is the
> best type for currency amounts in most cases IMO.
> If you are using NUMERIC and you still think you have a problem with
> rounding then please come back with some code that will actually
> reproduce the incorrect result you are getting.
> --
> David Portas
> SQL Server MVP
>|||> What about on the front end? I see your point if calculations are
taking
> place in a trigger or a stored procedure but what about data types in
a
> front end like VB or Delphi?
The results would obviously depend on how your language maps SQL's data
types onto its own. In an N-Tier application you should expect to do
all or most of your calculations in the data tier.
> Also, according to BOL, Numeric and Decimal are identical.
Yes, in SQL Server they are the same. As I suggested before, it would
be easier to understand your problem if you could post a working
example in TSQL. If your problem is in another language then you will
probably get more help posting to a more appropriate group.
David Portas
SQL Server MVP
--|||Just as an fyi, when looking at the money vs. float values in SQL Server,
money and decimaly do what a lot of people consider "normal rounding".
Basically, the .5 gets rounded up. Everything else is rounded down. The
float datatypes in SQL Server appears to use bankers rounding. Here is an
example:
DECLARE
@.deceven DECIMAL(4,3),
@.decuneven DECIMAL(4,3),
@.floateven FLOAT,
@.floatuneven FLOAT,
@.moneyeven MONEY,
@.moneyuneven MONEY
SELECT
@.deceven = 2.425,
@.decuneven = 2.435,
@.floateven = 2.425,
@.floatuneven = 2.435,
@.moneyeven = 2.425,
@.moneyuneven = 2.435
SELECT
ROUND(@.deceven,2),
ROUND(@.decuneven,2),
CAST(ROUND(@.floateven,2) AS DECIMAL(4,2)),
CAST(ROUND(@.floatuneven,2) AS DECIMAL(4,2)),
CAST(@.moneyeven AS DECIMAL(4,2)),
CAST(@.moneyuneven AS DECIMAL(4,2))
Not that anyone cares. :) You can lookup banker's rounding if you don't
know what it means. Also, remember that float and real are floating point
datatypes, so they should only be used when you know for a fact you need
floating point numeric data for calculations. Also, rounding between
different versions of SQL Server with floats and reals are different. blah,
blah, blah, blah, blah
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unBTEHAKFHA.336@.TK2MSFTNGP09.phx.gbl...
front
> Definitely. Do as much calculations as possible in SQL Server, and know
your dev tool datatypes and
> select carefully!
>
> They are the same. Did you find something in the thread indicating
something else? (I browsed
> through the thread and couldn't find such statement.)
> They are not exactly the same in ANSI SQL, though. In ANSI SQL, the engine
can give a higher
> precision than asked for for DECIMAL. For numeric, you get what you ask
for (which is how SQL Server
> does it for both DEC and NUM).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23SbC6I9JFHA.2212@.TK2MSFTNGP12.phx.gbl...
the
what
front
>
money calculations due to internal floating point rounding? Does using the
"money" datatype solve this alone? Do you need to use all "integer" data
types insetad and handle displaying the data appropriately manually? I don't
want to end up with reports where the grand total at the end of a column of
numbers ends up different then if you were to figure it out on a pocket
calculator. What's the best strategy for this?
Thanks,
KeithDon't use float or double datatypes for currency values !
There is a specific datatype (Called money, or for less range and
precision, smallmoney) that is specifically designed for currency values,
that does not have this issue.
"Keith G Hicks" wrote:
> What's the best way to avoid the problem of being off by a cent or 2 in
> money calculations due to internal floating point rounding? Does using th
e
> "money" datatype solve this alone? Do you need to use all "integer" data
> types insetad and handle displaying the data appropriately manually? I don
't
> want to end up with reports where the grand total at the end of a column o
f
> numbers ends up different then if you were to figure it out on a pocket
> calculator. What's the best strategy for this?
> Thanks,
> Keith
>
>|||Sorry - didn;t read yr entire post unti lafter Isent last ... Yes, to answe
r
your second question - Money solves this alone.
And No, to yr 3rd ? YOu don;t need to use integral datatypes...
"Keith G Hicks" wrote:
> What's the best way to avoid the problem of being off by a cent or 2 in
> money calculations due to internal floating point rounding? Does using th
e
> "money" datatype solve this alone? Do you need to use all "integer" data
> types insetad and handle displaying the data appropriately manually? I don
't
> want to end up with reports where the grand total at the end of a column o
f
> numbers ends up different then if you were to figure it out on a pocket
> calculator. What's the best strategy for this?
> Thanks,
> Keith
>
>|||Keith
I'd recommend you using DECIMAL(18,3) datatype fo such things. For more
details please refer to the BOL
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:OxXTufzJFHA.2468@.TK2MSFTNGP10.phx.gbl...
> What's the best way to avoid the problem of being off by a cent or 2 in
> money calculations due to internal floating point rounding? Does using
the
> "money" datatype solve this alone? Do you need to use all "integer" data
> types insetad and handle displaying the data appropriately manually? I
don't
> want to end up with reports where the grand total at the end of a column
of
> numbers ends up different then if you were to figure it out on a pocket
> calculator. What's the best strategy for this?
> Thanks,
> Keith
>|||Would it also then make sense do convert numbers to integers when doing
calculations and then convert them back to decimal values when posting the
results back to the table or displaying results in calculated grid columns
or reports? This made a lot of sense to me.
Amount Each Number Purchased Total
$1.23 1000 $1230.00
$0.57 2000 $1140.00
$2.13 1500 $3195.00
----
4500 $7620.00
We all know that the end result if there are a lot of rows could be 7619.99
or 7620.02 (or some other value close to the correct total). In fact I've
seen results where $1.23 * 1000 = 1230.01. But by converting $1.23 to 123
before multiplying by 1000 and then converting 123000 to 1230.00 and also
doing the same thing for the grand total you would end up with 100% accuracy
at all times.
Is there a better way to handle this? Some applications and clients do not
demand that kind of accuracy. But some do. What I'm currently working on
does. Is there a better solution to the problem? I've heard some developers
don't even use decimal values in teh back end for storing money. They use
integers only and then display decimals where needed to avoid the entire
problem altogether. That sounds too complex.
Any other suggestions would be welcomed.
Keith :)|||> We all know that the end result if there are a lot of rows could be
7619.99
> or 7620.02 (or some other value close to the correct total)
If you use inexact numerics such as FLOAT or REAL then that can happen,
yes. Don't use FLOAT or REAL for currency amounts. Use NUMERIC instead.
> In fact I've
> seen results where $1.23 * 1000 = 1230.01
With what datatype? Not with NUMERIC.
> I've heard some developers
> don't even use decimal values in teh back end for storing money. They
use
> integers only and then display decimals where needed to avoid the
entire
> problem altogether. That sounds too complex.
None of that is necessary. You can experience rounding problems with
SQL Server's MONEY type because the result of a division may be a MONEY
with 4 decimal places of precision. That's why I repeat, NUMERIC is the
best type for currency amounts in most cases IMO.
If you are using NUMERIC and you still think you have a problem with
rounding then please come back with some code that will actually
reproduce the incorrect result you are getting.
David Portas
SQL Server MVP|||Thanks for the input David.
What about on the front end? I see your point if calculations are taking
place in a trigger or a stored procedure but what about data types in a
front end like VB or Delphi? Oftentimes calculations need to be done on the
front end. Does it matter what data type is used there? Regardless of what
datatype the actual column in the table is defined as, I assume the front
end datatype would have an impact as well.
Also, according to BOL, Numeric and Decimal are identical. Is this not
actually the case?
Keith
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110720541.051228.117300@.g14g2000cwa.googlegroups.com...
> We all know that the end result if there are a lot of rows could be
7619.99
> or 7620.02 (or some other value close to the correct total)
If you use inexact numerics such as FLOAT or REAL then that can happen,
yes. Don't use FLOAT or REAL for currency amounts. Use NUMERIC instead.
> In fact I've
> seen results where $1.23 * 1000 = 1230.01
With what datatype? Not with NUMERIC.
> I've heard some developers
> don't even use decimal values in teh back end for storing money. They
use
> integers only and then display decimals where needed to avoid the
entire
> problem altogether. That sounds too complex.
None of that is necessary. You can experience rounding problems with
SQL Server's MONEY type because the result of a division may be a MONEY
with 4 decimal places of precision. That's why I repeat, NUMERIC is the
best type for currency amounts in most cases IMO.
If you are using NUMERIC and you still think you have a problem with
rounding then please come back with some code that will actually
reproduce the incorrect result you are getting.
David Portas
SQL Server MVP|||> Regardless of what
> datatype the actual column in the table is defined as, I assume the front
> end datatype would have an impact as well.
Definitely. Do as much calculations as possible in SQL Server, and know your
dev tool datatypes and
select carefully!
> Also, according to BOL, Numeric and Decimal are identical. Is this not
> actually the case?
They are the same. Did you find something in the thread indicating something
else? (I browsed
through the thread and couldn't find such statement.)
They are not exactly the same in ANSI SQL, though. In ANSI SQL, the engine c
an give a higher
precision than asked for for DECIMAL. For numeric, you get what you ask for
(which is how SQL Server
does it for both DEC and NUM).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Keith G Hicks" <krh@.comcast.net> wrote in message news:%23SbC6I9JFHA.2212@.TK2MSFTNGP12.phx
.gbl...
> Thanks for the input David.
> What about on the front end? I see your point if calculations are taking
> place in a trigger or a stored procedure but what about data types in a
> front end like VB or Delphi? Oftentimes calculations need to be done on th
e
> front end. Does it matter what data type is used there? Regardless of what
> datatype the actual column in the table is defined as, I assume the front
> end datatype would have an impact as well.
> Also, according to BOL, Numeric and Decimal are identical. Is this not
> actually the case?
> Keith
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1110720541.051228.117300@.g14g2000cwa.googlegroups.com...
> 7619.99
> If you use inexact numerics such as FLOAT or REAL then that can happen,
> yes. Don't use FLOAT or REAL for currency amounts. Use NUMERIC instead.
>
> With what datatype? Not with NUMERIC.
>
> use
> entire
> None of that is necessary. You can experience rounding problems with
> SQL Server's MONEY type because the result of a division may be a MONEY
> with 4 decimal places of precision. That's why I repeat, NUMERIC is the
> best type for currency amounts in most cases IMO.
> If you are using NUMERIC and you still think you have a problem with
> rounding then please come back with some code that will actually
> reproduce the incorrect result you are getting.
> --
> David Portas
> SQL Server MVP
>|||> What about on the front end? I see your point if calculations are
taking
> place in a trigger or a stored procedure but what about data types in
a
> front end like VB or Delphi?
The results would obviously depend on how your language maps SQL's data
types onto its own. In an N-Tier application you should expect to do
all or most of your calculations in the data tier.
> Also, according to BOL, Numeric and Decimal are identical.
Yes, in SQL Server they are the same. As I suggested before, it would
be easier to understand your problem if you could post a working
example in TSQL. If your problem is in another language then you will
probably get more help posting to a more appropriate group.
David Portas
SQL Server MVP
--|||Just as an fyi, when looking at the money vs. float values in SQL Server,
money and decimaly do what a lot of people consider "normal rounding".
Basically, the .5 gets rounded up. Everything else is rounded down. The
float datatypes in SQL Server appears to use bankers rounding. Here is an
example:
DECLARE
@.deceven DECIMAL(4,3),
@.decuneven DECIMAL(4,3),
@.floateven FLOAT,
@.floatuneven FLOAT,
@.moneyeven MONEY,
@.moneyuneven MONEY
SELECT
@.deceven = 2.425,
@.decuneven = 2.435,
@.floateven = 2.425,
@.floatuneven = 2.435,
@.moneyeven = 2.425,
@.moneyuneven = 2.435
SELECT
ROUND(@.deceven,2),
ROUND(@.decuneven,2),
CAST(ROUND(@.floateven,2) AS DECIMAL(4,2)),
CAST(ROUND(@.floatuneven,2) AS DECIMAL(4,2)),
CAST(@.moneyeven AS DECIMAL(4,2)),
CAST(@.moneyuneven AS DECIMAL(4,2))
Not that anyone cares. :) You can lookup banker's rounding if you don't
know what it means. Also, remember that float and real are floating point
datatypes, so they should only be used when you know for a fact you need
floating point numeric data for calculations. Also, rounding between
different versions of SQL Server with floats and reals are different. blah,
blah, blah, blah, blah
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unBTEHAKFHA.336@.TK2MSFTNGP09.phx.gbl...
front
> Definitely. Do as much calculations as possible in SQL Server, and know
your dev tool datatypes and
> select carefully!
>
> They are the same. Did you find something in the thread indicating
something else? (I browsed
> through the thread and couldn't find such statement.)
> They are not exactly the same in ANSI SQL, though. In ANSI SQL, the engine
can give a higher
> precision than asked for for DECIMAL. For numeric, you get what you ask
for (which is how SQL Server
> does it for both DEC and NUM).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23SbC6I9JFHA.2212@.TK2MSFTNGP12.phx.gbl...
the
what
front
>
Subscribe to:
Posts (Atom)