Ask for Best Server Configuration

Dear All,
This is my SQL Server 2000 hardware Configuration:
-RAID 5
-4 HD, each 20 GB
* 1 HD for Drive C
* 3 HD for Raid5 with Fault Tolerance 20 GB
I have OLTP Database that has been running for 1.5 years. In the
database, there is a heavily accessed table that contain more than 1
million rows with several non-cluster indexes belonging to that table.
My question are:
-Is it neccessary to place log files and data files on distinct drive?
Why?
-Is it neccessary to place heavily accessed table and non-cluster
indexes belonging to that table on difference filegroups? And How'
Thanks
Robert LieSee Inline responses:

> -Is it neccessary to place log files and data files on distinct drive?
> Why?
Necessary, No....A good Idea, Yes.
By segregating specific IO Tasks to different logical drives, you redue IO
Saturation (allowing multiple disks, controllers, etc to work
simultaneously).

> -Is it neccessary to place heavily accessed table and non-cluster indexes
> belonging to that table on difference filegroups? And How'
Again, NOT Necessary, but often a good idea.
You would use the "ON [FILEGROUP]" clause in your DDL Commands
Greg Jackson
PDX, Oregon|||Robert Lie wrote:
> Dear All,
> This is my SQL Server 2000 hardware Configuration:
> -RAID 5
> -4 HD, each 20 GB
> * 1 HD for Drive C
> * 3 HD for Raid5 with Fault Tolerance 20 GB
>
> I have OLTP Database that has been running for 1.5 years. In the
> database, there is a heavily accessed table that contain more than 1
> million rows with several non-cluster indexes belonging to that table.
> My question are:
> -Is it neccessary to place log files and data files on distinct drive?
> Why?
It helps, especially for an OLTP system. RAID 5 is the worst performing
raid option for writing. When you consider that log files and tempdb
deal primarily with writing, further burdeoning the data drive doesn't
make sense. It would probably be better for your system to use a
mirrored array instead of a raid 5 array w/ 3 HDs for the log. You
really need to mirror the OS drive as well. You lose that drive and your
entire server is down for days.

> -Is it neccessary to place heavily accessed table and non-cluster
> indexes belonging to that table on difference filegroups? And How'
You don't have that option really given your current design. If you
think about writing to a table and to an index, you can get better
performance if those objects are on two different arrays because they
can be written to simultaneously. There is a filegroup option when
creating the index that determines its data file location.
I might consider making your main data raid 5 array 5 disks, use a
mirrored array for the logs and tempdb, and add a drive to mirror the
OS.
OTOH, if you're not having any performance or capacity issues, then you
might want to leave the design as is.
David Gugick
Imceda Software
www.imceda.com|||Ok Thanks fo your sugestion.
Do you know any web site that explain about RAID on Windows 2000 Server
in more detail?
Thanks
David Gugick wrote:
> Robert Lie wrote:
>
>
> It helps, especially for an OLTP system. RAID 5 is the worst performing
> raid option for writing. When you consider that log files and tempdb
> deal primarily with writing, further burdeoning the data drive doesn't
> make sense. It would probably be better for your system to use a
> mirrored array instead of a raid 5 array w/ 3 HDs for the log. You
> really need to mirror the OS drive as well. You lose that drive and your
> entire server is down for days.
>
>
> You don't have that option really given your current design. If you
> think about writing to a table and to an index, you can get better
> performance if those objects are on two different arrays because they
> can be written to simultaneously. There is a filegroup option when
> creating the index that determines its data file location.
> I might consider making your main data raid 5 array 5 disks, use a
> mirrored array for the logs and tempdb, and add a drive to mirror the OS.
> OTOH, if you're not having any performance or capacity issues, then you
> might want to leave the design as is.
>|||Robert Lie wrote:
> Ok Thanks fo your sugestion.
> Do you know any web site that explain about RAID on Windows 2000
> Server in more detail?
>
RAID is a harware implementation, separate from the OS. There is a web
site that advocates not to use RAID 5, but I don't remember the address.
See if SQLServerPerformance.com has some answers.
David Gugick
Imceda Software
www.imceda.com|||Hello Robert,
Performance Tuning Guide from MS press shud be a good start.
Create the objects on that filegroup. If the table has a Clustered Index,
drop and recreate the Clustered Index on the desired File Group.
[Is it neccessary to place heavily accessed table and non-cluster
indexes belonging to that table on difference filegroups? And How']
Log file is write intensive - "Sequentially". It is best kept on its own
drive (RAID 1)
[Is it neccessary to place log files and data files on distinct drive? >
Why?]
Are all the Indexes being used. Sometimes, we create Indexes and over a
period of time
we loose track of its usage and as a consequence this reduces the
performance during
writes with respect to any unused Index.
[with several non-cluster indexes belonging to that table.]
Gopi
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23$Nv$csSFHA.580@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> This is my SQL Server 2000 hardware Configuration:
> -RAID 5
> -4 HD, each 20 GB
> * 1 HD for Drive C
> * 3 HD for Raid5 with Fault Tolerance 20 GB
>
> I have OLTP Database that has been running for 1.5 years. In the database,
> there is a heavily accessed table that contain more than 1 million rows
> with several non-cluster indexes belonging to that table.
> My question are:
> -Is it neccessary to place log files and data files on distinct drive?
> Why?
> -Is it neccessary to place heavily accessed table and non-cluster indexes
> belonging to that table on difference filegroups? And How'
> Thanks
> Robert Lie|||Try to google for RAID - that will give you a number of hits. You can also
start with this one http://www.acnc.com/raid.html that gives you a brief
introduction to the various RAID types.
Regards
Steen
Robert Lie wrote:[vbcol=seagreen]
> Ok Thanks fo your sugestion.
> Do you know any web site that explain about RAID on Windows 2000
> Server in more detail?
> Thanks
>
> David Gugick wrote:

Ask for Best Server Configuration

Dear All,
This is my SQL Server 2000 Hardware Configuration:
-RAID 5
-4 HD, each 20 GB
* 1 HD for Drive C
* 3 HD for Raid5 with Fault Tolerance 20 GB
I have OLTP Database that has been running for 1.5 years. In the
database, there is a heavily accessed table that contain more than 1
million rows with several non-cluster indexes belonging to that table.
My question are:
-Is it neccessary to place log files and data files on distinct drive?
Why?
-Is it neccessary to place heavily accessed table and non-cluster
indexes belonging to that table on difference filegroups? And How'
Thanks
Robert LieSee Inline responses:
> -Is it neccessary to place log files and data files on distinct drive?
> Why?
Necessary, No....A good Idea, Yes.
By segregating specific IO Tasks to different logical drives, you redue IO
Saturation (allowing multiple disks, controllers, etc to work
simultaneously).
> -Is it neccessary to place heavily accessed table and non-cluster indexes
> belonging to that table on difference filegroups? And How'
Again, NOT Necessary, but often a good idea.
You would use the "ON [FILEGROUP]" clause in your DDL Commands
Greg Jackson
PDX, Oregon|||Robert Lie wrote:
> Dear All,
> This is my SQL Server 2000 Hardware Configuration:
> -RAID 5
> -4 HD, each 20 GB
> * 1 HD for Drive C
> * 3 HD for Raid5 with Fault Tolerance 20 GB
>
> I have OLTP Database that has been running for 1.5 years. In the
> database, there is a heavily accessed table that contain more than 1
> million rows with several non-cluster indexes belonging to that table.
> My question are:
> -Is it neccessary to place log files and data files on distinct drive?
> Why?
It helps, especially for an OLTP system. RAID 5 is the worst performing
raid option for writing. When you consider that log files and tempdb
deal primarily with writing, further burdeoning the data drive doesn't
make sense. It would probably be better for your system to use a
mirrored array instead of a raid 5 array w/ 3 HDs for the log. You
really need to mirror the OS drive as well. You lose that drive and your
entire server is down for days.
> -Is it neccessary to place heavily accessed table and non-cluster
> indexes belonging to that table on difference filegroups? And How'
You don't have that option really given your current design. If you
think about writing to a table and to an index, you can get better
performance if those objects are on two different arrays because they
can be written to simultaneously. There is a filegroup option when
creating the index that determines its data file location.
I might consider making your main data raid 5 array 5 disks, use a
mirrored array for the logs and tempdb, and add a drive to mirror the
OS.
OTOH, if you're not having any performance or capacity issues, then you
might want to leave the design as is.
David Gugick
Imceda Software
www.imceda.com|||Ok Thanks fo your sugestion.
Do you know any web site that explain about RAID on Windows 2000 Server
in more detail?
Thanks
David Gugick wrote:
> Robert Lie wrote:
>> Dear All,
>> This is my SQL Server 2000 Hardware Configuration:
>> -RAID 5
>> -4 HD, each 20 GB
>> * 1 HD for Drive C
>> * 3 HD for Raid5 with Fault Tolerance 20 GB
>>
>> I have OLTP Database that has been running for 1.5 years. In the
>> database, there is a heavily accessed table that contain more than 1
>> million rows with several non-cluster indexes belonging to that table.
>> My question are:
>> -Is it neccessary to place log files and data files on distinct drive?
>> Why?
>
> It helps, especially for an OLTP system. RAID 5 is the worst performing
> raid option for writing. When you consider that log files and tempdb
> deal primarily with writing, further burdeoning the data drive doesn't
> make sense. It would probably be better for your system to use a
> mirrored array instead of a raid 5 array w/ 3 HDs for the log. You
> really need to mirror the OS drive as well. You lose that drive and your
> entire server is down for days.
>> -Is it neccessary to place heavily accessed table and non-cluster
>> indexes belonging to that table on difference filegroups? And How'
>
> You don't have that option really given your current design. If you
> think about writing to a table and to an index, you can get better
> performance if those objects are on two different arrays because they
> can be written to simultaneously. There is a filegroup option when
> creating the index that determines its data file location.
> I might consider making your main data raid 5 array 5 disks, use a
> mirrored array for the logs and tempdb, and add a drive to mirror the OS.
> OTOH, if you're not having any performance or capacity issues, then you
> might want to leave the design as is.
>|||Robert Lie wrote:
> Ok Thanks fo your sugestion.
> Do you know any web site that explain about RAID on Windows 2000
> Server in more detail?
>
RAID is a harware implementation, separate from the OS. There is a web
site that advocates not to use RAID 5, but I don't remember the address.
See if SQLServerPerformance.com has some answers.
--
David Gugick
Imceda Software
www.imceda.com|||Hello Robert,
Performance Tuning Guide from MS press shud be a good start.
Create the objects on that filegroup. If the table has a Clustered Index,
drop and recreate the Clustered Index on the desired File Group.
[Is it neccessary to place heavily accessed table and non-cluster
indexes belonging to that table on difference filegroups? And How']
Log file is write intensive - "Sequentially". It is best kept on its own
drive (RAID 1)
[Is it neccessary to place log files and data files on distinct drive? >
Why?]
Are all the Indexes being used. Sometimes, we create Indexes and over a
period of time
we loose track of its usage and as a consequence this reduces the
performance during
writes with respect to any unused Index.
[with several non-cluster indexes belonging to that table.]
Gopi
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23$Nv$csSFHA.580@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> This is my SQL Server 2000 Hardware Configuration:
> -RAID 5
> -4 HD, each 20 GB
> * 1 HD for Drive C
> * 3 HD for Raid5 with Fault Tolerance 20 GB
>
> I have OLTP Database that has been running for 1.5 years. In the database,
> there is a heavily accessed table that contain more than 1 million rows
> with several non-cluster indexes belonging to that table.
> My question are:
> -Is it neccessary to place log files and data files on distinct drive?
> Why?
> -Is it neccessary to place heavily accessed table and non-cluster indexes
> belonging to that table on difference filegroups? And How'
> Thanks
> Robert Lie|||Try to google for RAID - that will give you a number of hits. You can also
start with this one http://www.acnc.com/raid.html that gives you a brief
introduction to the various RAID types.
Regards
Steen
Robert Lie wrote:
> Ok Thanks fo your sugestion.
> Do you know any web site that explain about RAID on Windows 2000
> Server in more detail?
> Thanks
>
> David Gugick wrote:
>> Robert Lie wrote:
>> Dear All,
>> This is my SQL Server 2000 Hardware Configuration:
>> -RAID 5
>> -4 HD, each 20 GB
>> * 1 HD for Drive C
>> * 3 HD for Raid5 with Fault Tolerance 20 GB
>>
>> I have OLTP Database that has been running for 1.5 years. In the
>> database, there is a heavily accessed table that contain more than 1
>> million rows with several non-cluster indexes belonging to that
>> table. My question are:
>> -Is it neccessary to place log files and data files on distinct
>> drive? Why?
>>
>> It helps, especially for an OLTP system. RAID 5 is the worst
>> performing raid option for writing. When you consider that log files
>> and tempdb deal primarily with writing, further burdeoning the data
>> drive doesn't make sense. It would probably be better for your
>> system to use a mirrored array instead of a raid 5 array w/ 3 HDs
>> for the log. You really need to mirror the OS drive as well. You
>> lose that drive and your entire server is down for days.
>> -Is it neccessary to place heavily accessed table and non-cluster
>> indexes belonging to that table on difference filegroups? And How'
>>
>> You don't have that option really given your current design. If you
>> think about writing to a table and to an index, you can get better
>> performance if those objects are on two different arrays because they
>> can be written to simultaneously. There is a filegroup option when
>> creating the index that determines its data file location.
>> I might consider making your main data raid 5 array 5 disks, use a
>> mirrored array for the logs and tempdb, and add a drive to mirror
>> the OS.
>> OTOH, if you're not having any performance or capacity issues, then
>> you might want to leave the design as is.

Ask for Best Server Configuration

Dear All,
This is my SQL Server 2000 Hardware Configuration:
-RAID 5
-4 HD, each 20 GB
* 1 HD for Drive C
* 3 HD for Raid5 with Fault Tolerance 20 GB
I have OLTP Database that has been running for 1.5 years. In the
database, there is a heavily accessed table that contain more than 1
million rows with several non-cluster indexes belonging to that table.
My question are:
-Is it neccessary to place log files and data files on distinct drive?
Why?
-Is it neccessary to place heavily accessed table and non-cluster
indexes belonging to that table on difference filegroups? And How?
Thanks
Robert Lie
See Inline responses:

> -Is it neccessary to place log files and data files on distinct drive?
> Why?
Necessary, No....A good Idea, Yes.
By segregating specific IO Tasks to different logical drives, you redue IO
Saturation (allowing multiple disks, controllers, etc to work
simultaneously).

> -Is it neccessary to place heavily accessed table and non-cluster indexes
> belonging to that table on difference filegroups? And How?
Again, NOT Necessary, but often a good idea.
You would use the "ON [FILEGROUP]" clause in your DDL Commands
Greg Jackson
PDX, Oregon
|||Robert Lie wrote:
> Dear All,
> This is my SQL Server 2000 Hardware Configuration:
> -RAID 5
> -4 HD, each 20 GB
> * 1 HD for Drive C
> * 3 HD for Raid5 with Fault Tolerance 20 GB
>
> I have OLTP Database that has been running for 1.5 years. In the
> database, there is a heavily accessed table that contain more than 1
> million rows with several non-cluster indexes belonging to that table.
> My question are:
> -Is it neccessary to place log files and data files on distinct drive?
> Why?
It helps, especially for an OLTP system. RAID 5 is the worst performing
raid option for writing. When you consider that log files and tempdb
deal primarily with writing, further burdeoning the data drive doesn't
make sense. It would probably be better for your system to use a
mirrored array instead of a raid 5 array w/ 3 HDs for the log. You
really need to mirror the OS drive as well. You lose that drive and your
entire server is down for days.

> -Is it neccessary to place heavily accessed table and non-cluster
> indexes belonging to that table on difference filegroups? And How?
You don't have that option really given your current design. If you
think about writing to a table and to an index, you can get better
performance if those objects are on two different arrays because they
can be written to simultaneously. There is a filegroup option when
creating the index that determines its data file location.
I might consider making your main data raid 5 array 5 disks, use a
mirrored array for the logs and tempdb, and add a drive to mirror the
OS.
OTOH, if you're not having any performance or capacity issues, then you
might want to leave the design as is.
David Gugick
Imceda Software
www.imceda.com
|||Ok Thanks fo your sugestion.
Do you know any web site that explain about RAID on Windows 2000 Server
in more detail?
Thanks
David Gugick wrote:
> Robert Lie wrote:
>
> It helps, especially for an OLTP system. RAID 5 is the worst performing
> raid option for writing. When you consider that log files and tempdb
> deal primarily with writing, further burdeoning the data drive doesn't
> make sense. It would probably be better for your system to use a
> mirrored array instead of a raid 5 array w/ 3 HDs for the log. You
> really need to mirror the OS drive as well. You lose that drive and your
> entire server is down for days.
>
> You don't have that option really given your current design. If you
> think about writing to a table and to an index, you can get better
> performance if those objects are on two different arrays because they
> can be written to simultaneously. There is a filegroup option when
> creating the index that determines its data file location.
> I might consider making your main data raid 5 array 5 disks, use a
> mirrored array for the logs and tempdb, and add a drive to mirror the OS.
> OTOH, if you're not having any performance or capacity issues, then you
> might want to leave the design as is.
>
|||Robert Lie wrote:
> Ok Thanks fo your sugestion.
> Do you know any web site that explain about RAID on Windows 2000
> Server in more detail?
>
RAID is a harware implementation, separate from the OS. There is a web
site that advocates not to use RAID 5, but I don't remember the address.
See if SQLServerPerformance.com has some answers.
David Gugick
Imceda Software
www.imceda.com
|||Hello Robert,
Performance Tuning Guide from MS press shud be a good start.
Create the objects on that filegroup. If the table has a Clustered Index,
drop and recreate the Clustered Index on the desired File Group.
[Is it neccessary to place heavily accessed table and non-cluster
indexes belonging to that table on difference filegroups? And How?]
Log file is write intensive - "Sequentially". It is best kept on its own
drive (RAID 1)
[Is it neccessary to place log files and data files on distinct drive? >
Why?]
Are all the Indexes being used. Sometimes, we create Indexes and over a
period of time
we loose track of its usage and as a consequence this reduces the
performance during
writes with respect to any unused Index.
[with several non-cluster indexes belonging to that table.]
Gopi
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%23$Nv$csSFHA.580@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> This is my SQL Server 2000 Hardware Configuration:
> -RAID 5
> -4 HD, each 20 GB
> * 1 HD for Drive C
> * 3 HD for Raid5 with Fault Tolerance 20 GB
>
> I have OLTP Database that has been running for 1.5 years. In the database,
> there is a heavily accessed table that contain more than 1 million rows
> with several non-cluster indexes belonging to that table.
> My question are:
> -Is it neccessary to place log files and data files on distinct drive?
> Why?
> -Is it neccessary to place heavily accessed table and non-cluster indexes
> belonging to that table on difference filegroups? And How?
> Thanks
> Robert Lie
|||Try to google for RAID - that will give you a number of hits. You can also
start with this one http://www.acnc.com/raid.html that gives you a brief
introduction to the various RAID types.
Regards
Steen
Robert Lie wrote:[vbcol=seagreen]
> Ok Thanks fo your sugestion.
> Do you know any web site that explain about RAID on Windows 2000
> Server in more detail?
> Thanks
>
> David Gugick wrote:

Arithmic Overlflow on division ?

Hi there,
we are experiencing some very unexptected behaviour here (SQL 2k, latest
SPs, different types of hardware).
Since our application is doing approximations we are trying to get as much
detail as possible from the calculations. However, instead of getting better
results with Numeric(38,25) variables, we are running into Arithmetic
Overflows!!?
As one query can say so much more than a thousand words : please try this :
DECLARE @.x numeric(25, 15)
DECLARE @.y numeric(25, 15)
SELECT @.x = 10000000, -- 10 Million
@.y = 1.021208219120586
SELECT x = @.x, y = @.y
SELECT result = @.x / @.y
SELECT inverse = @.y / @.x
SELECT result_via_inv = Convert(Numeric(25, 15), 1) / (@.y / @.x)
As you will see, this gives me some numbers.
Now replace the Numeric() definitions to something more precise (eg. 38, 25)
Can anyobdoy explain this ? I've found that using 25,15 sometimes gives me
overflows too due to the fact that the amounts involved sometimes go over
10^10. Should I use floats and live with the 'loss of precision' ?
Thanks.
Cu
RobySee "Precision, Scale, and Length" in BOL.

> SELECT result = @.x / @.y
The p ans s of the result will be (38, 38), equal p and s. Whatever number
that you try to cast to numeric(p, s) where p = s, will give you an error
except for zero.
-- error
select cast(1 numeric(38, 38))
-- error
select cast(1 numeric(12, 12))
-- no error
select cast(1 numeric(38, 38))

> SELECT inverse = @.y / @.x
The result is zero and zero can be cast to numeric(38, 38)

> SELECT result_via_inv = Convert(Numeric(25, 15), 1) / (@.y / @.x)
Because @.y / @.x is zero, then you have "Divide by zero error encountered."
error.
May be I am wrong here, so I will call for help from Steve Kass.
AMB
"deroby" wrote:

> Hi there,
> we are experiencing some very unexptected behaviour here (SQL 2k, latest
> SPs, different types of hardware).
> Since our application is doing approximations we are trying to get as much
> detail as possible from the calculations. However, instead of getting bett
er
> results with Numeric(38,25) variables, we are running into Arithmetic
> Overflows!!?
> As one query can say so much more than a thousand words : please try this
:
>
> DECLARE @.x numeric(25, 15)
> DECLARE @.y numeric(25, 15)
> SELECT @.x = 10000000, -- 10 Million
> @.y = 1.021208219120586
> SELECT x = @.x, y = @.y
> SELECT result = @.x / @.y
> SELECT inverse = @.y / @.x
> SELECT result_via_inv = Convert(Numeric(25, 15), 1) / (@.y / @.x)
> As you will see, this gives me some numbers.
> Now replace the Numeric() definitions to something more precise (eg. 38, 2
5)
> Can anyobdoy explain this ? I've found that using 25,15 sometimes gives me
> overflows too due to the fact that the amounts involved sometimes go over
> 10^10. Should I use floats and live with the 'loss of precision' ?
> Thanks.
> Cu
> Roby|||Correction,
-- error
select cast(1 numeric(38, 38))
-- error
select cast(1 numeric(12, 12))
-- no error
select cast(0 numeric(38, 38))
AMB
"Alejandro Mesa" wrote:
> See "Precision, Scale, and Length" in BOL.
>
> The p ans s of the result will be (38, 38), equal p and s. Whatever number
> that you try to cast to numeric(p, s) where p = s, will give you an error
> except for zero.
> -- error
> select cast(1 numeric(38, 38))
> -- error
> select cast(1 numeric(12, 12))
> -- no error
> select cast(1 numeric(38, 38))
>
> The result is zero and zero can be cast to numeric(38, 38)
>
> Because @.y / @.x is zero, then you have "Divide by zero error encountered."
> error.
>
> May be I am wrong here, so I will call for help from Steve Kass.
>
> AMB
> "deroby" wrote:
>|||Interesting, another one for the favorites !
So in order to get this working I should make sure the resuling p & s are
what they are to be in the result column.
I should be able to figure that out =)
Thanks !
Cu
Roby
"Alejandro Mesa" wrote:
> Correction,
> -- error
> select cast(1 numeric(38, 38))
> -- error
> select cast(1 numeric(12, 12))
> -- no error
> select cast(0 numeric(38, 38))
>
> AMB
> "Alejandro Mesa" wrote:
>|||I think that my comment was wrong. I know that the result of @.x / @.y can not
be represented in the result precision and scale, but I do not know those
values. If you reduce the scale or cast one of the variables to float, then
you do not get the error.
AMB
"deroby" wrote:
> Interesting, another one for the favorites !
> So in order to get this working I should make sure the resuling p & s are
> what they are to be in the result column.
> I should be able to figure that out =)
> Thanks !
> Cu
> Roby
> "Alejandro Mesa" wrote:
>|||Hi Alejandro,
Here is my best shot at explaining what deroby sees here:
There are 772 decimal types in SQL Server, and the typing
of expressions involving decimals is tricky business!
The topic "Precision, Scale, and Length" describes
the rules for typing arithmetic on decimal types.
The type of <decimal value 1> <operator> <decimal value 2>
is based on the types, not the values, in the expressions.
Since the result of d1/d2 can be much larger than d1 or
require more places after the decimal point than d1, an
attempt is made to choose a result type that avoids both
overflow and inaccuracy. This is not always possible.
For small cases, there is little problem. The result
of numeric(8,4)/numeric(8,4) could range from around
0.000000000001 to 100000000000.0, and the result type
for this expression is numeric(21,13), which avoids
overflow and maximizes correctness, at least by avoiding
truncation to zero. (Note that no decimal type can exactly
represent 1.0/3.0, so we can't have complete accuracy.)
But for something like numeric(38,15)/numeric(38,15), it
is nearly impossible to account for all possibilities.
The result could range in size from around 10^-15/10^23 to 10^23/10^-15,
which is about 76 orders of magnitude. SQL Server tries harder
to avoid overflow than to be accurate (perhaps since overflow causes
a run-time error, but loss of accuracy doesn't). There is only
a "safety" of never reducing the scale below 6 if it
should really be larger. It turns out the result type for
this quotient in this case is numeric(38,6). This type cannot
represent the reciprocal of 10000000, so it is rounded to zero.
You can see what the result precisions and scales are by
converting to varbinary and inspecting the first two bytes
of the result:
DECLARE @.x numeric(25, 15)
DECLARE @.y numeric(25, 15)
SELECT @.x = 10000000 -- 10 Million
SELECT @.y = 1
SELECT
@.y / @.x as quotient,
cast(@.y / @.x as varbinary) as binRep,
cast(substring(cast(@.y / @.x as varbinary),1,1) as tinyint) as prec,
cast(substring(cast(@.y / @.x as varbinary),2,1) as tinyint) as scale
go
DECLARE @.x numeric(38, 15)
DECLARE @.y numeric(38, 15)
SELECT @.x = 10000000 -- 10 Million
SELECT @.y = 1
SELECT
@.y / @.x as quotient,
cast(@.y / @.x as varbinary) as binRep,
cast(substring(cast(@.y / @.x as varbinary),1,1) as tinyint) as prec,
cast(substring(cast(@.y / @.x as varbinary),2,1) as tinyint) as scale
Steve Kass
Drew University
Alejandro Mesa wrote:
>See "Precision, Scale, and Length" in BOL.
>
>
>The p ans s of the result will be (38, 38), equal p and s. Whatever number
>that you try to cast to numeric(p, s) where p = s, will give you an error
>except for zero.
>-- error
>select cast(1 numeric(38, 38))
>-- error
>select cast(1 numeric(12, 12))
>-- no error
>select cast(1 numeric(38, 38))
>
>
>The result is zero and zero can be cast to numeric(38, 38)
>
>
>Because @.y / @.x is zero, then you have "Divide by zero error encountered."
>error.
>
>May be I am wrong here, so I will call for help from Steve Kass.
>
>AMB
>"deroby" wrote:
>
>|||Steve,
Thanks a lot for the explanation and the tip.
Alejandro Mesa
"Steve Kass" wrote:

> Hi Alejandro,
> Here is my best shot at explaining what deroby sees here:
> There are 772 decimal types in SQL Server, and the typing
> of expressions involving decimals is tricky business!
> The topic "Precision, Scale, and Length" describes
> the rules for typing arithmetic on decimal types.
> The type of <decimal value 1> <operator> <decimal value 2>
> is based on the types, not the values, in the expressions.
> Since the result of d1/d2 can be much larger than d1 or
> require more places after the decimal point than d1, an
> attempt is made to choose a result type that avoids both
> overflow and inaccuracy. This is not always possible.
> For small cases, there is little problem. The result
> of numeric(8,4)/numeric(8,4) could range from around
> 0.000000000001 to 100000000000.0, and the result type
> for this expression is numeric(21,13), which avoids
> overflow and maximizes correctness, at least by avoiding
> truncation to zero. (Note that no decimal type can exactly
> represent 1.0/3.0, so we can't have complete accuracy.)
> But for something like numeric(38,15)/numeric(38,15), it
> is nearly impossible to account for all possibilities.
> The result could range in size from around 10^-15/10^23 to 10^23/10^-15,
> which is about 76 orders of magnitude. SQL Server tries harder
> to avoid overflow than to be accurate (perhaps since overflow causes
> a run-time error, but loss of accuracy doesn't). There is only
> a "safety" of never reducing the scale below 6 if it
> should really be larger. It turns out the result type for
> this quotient in this case is numeric(38,6). This type cannot
> represent the reciprocal of 10000000, so it is rounded to zero.
> You can see what the result precisions and scales are by
> converting to varbinary and inspecting the first two bytes
> of the result:
> DECLARE @.x numeric(25, 15)
> DECLARE @.y numeric(25, 15)
> SELECT @.x = 10000000 -- 10 Million
> SELECT @.y = 1
> SELECT
> @.y / @.x as quotient,
> cast(@.y / @.x as varbinary) as binRep,
> cast(substring(cast(@.y / @.x as varbinary),1,1) as tinyint) as prec,
> cast(substring(cast(@.y / @.x as varbinary),2,1) as tinyint) as scale
> go
> DECLARE @.x numeric(38, 15)
> DECLARE @.y numeric(38, 15)
> SELECT @.x = 10000000 -- 10 Million
> SELECT @.y = 1
> SELECT
> @.y / @.x as quotient,
> cast(@.y / @.x as varbinary) as binRep,
> cast(substring(cast(@.y / @.x as varbinary),1,1) as tinyint) as prec,
> cast(substring(cast(@.y / @.x as varbinary),2,1) as tinyint) as scale
>
> Steve Kass
> Drew University
> Alejandro Mesa wrote:
>
>