Showing posts with label numeric. Show all posts
Showing posts with label numeric. Show all posts

Thursday, February 16, 2012

about multiplication in sql

what is the value when running sql script?

select cast(cast(.0000006 as numeric(38,10)) * cast(1.0 as numeric(38,10)) as numeric(38,10))

the result is .0000010000.

why the result is not .0000006000?

It looks to be rounding it upto the nearest decimal place, as the result has one fewer zeros to the right of the decimal point. WHy it would do this I am not sure.|||

The reason the scale of the result has been reduced to 6 is interesting. You have asked SQL Server to multiply two numerics, each with 38 precision and 10 scale.

Conceptually, the result should be a numeric(77, 20). However, this is above SQL Server's maximum precision, so the result would need to be truncated.

An implementation decision was made to sacrifice some scale to gain more digits on the left side of the decimal point, up to a minimum of scale 6. So the numeric(77,20) is actually converted to numeric(38, 6). The same behavior happens for division.

That decision is based off the assumption that digits on the left side of the decimal point are usually more important than the ones on the right side. It can pay off in a different situation:

select cast(1234567890123456789012345678.0 as numeric(38,10)) * cast(10.0 as numeric(38,10))

Here, the extra scale was not actually important, so sacrificing it to get more digits on the left side of the decimal point paid off.

To keep SQL Server from guessing if the scale is needed or not, you need to type your numerics appropriately if possible. In your example:

select cast(cast(.0000006 as numeric(10,10)) * cast(1.0 as numeric(2,1)) as numeric(38,10))

Yields the appropriate results. The result of the multiplication is conceptually numeric(13,11) which is well within our limits.

I hope this helps, and let me know if this work around does not work for you.

-Mat

|||In addition to Matt's comments, please take a look at the "Precision, Scale, and Length" topic in Books Online. The url is mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_da-db_8rc5.htm. This topic explains the formula used to arrive at the resulting precision of arithmetic operations involving two numeric values.|||thanks for your help~|||

Thank you for your reply.

I can find 'tsqlref.chm' but not know how to go '/ts_da-db_8rc5.htm' .Can you tell me how to do.Thanks.


I know how to find.^_^

use the tool bar,GO-->URL.... to open 'Jump to URL'

|||If you have SQL Server 2000 Books Online installed then go to Go\Url menu option and paste the url. You can also search for this topic in MSDN.

Monday, February 13, 2012

About Identity Column

Dear all,
I use the identity property that makes generating unique numeric
values in my table "OrderDetail". Its primary keys include the column
"OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
that if the application keep going to use for many years. Is it possible the
identity value will be overflowed? or it will restart from 1 again
automatically after the overflow. And now, i make this column type is "Int".
Anyone can help? Thanks.
Best Rdgs
EllisYou'll get an overflow when the identity value exceeds the maximum value for
the data type If this is a possibility in your situation, consider using a
data type with a larger maximum value, such as bigint or decimal.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> Dear all,
> I use the identity property that makes generating unique numeric
> values in my table "OrderDetail". Its primary keys include the column
> "OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
> that if the application keep going to use for many years. Is it possible
> the
> identity value will be overflowed? or it will restart from 1 again
> automatically after the overflow. And now, i make this column type is
> "Int".
> Anyone can help? Thanks.
> Best Rdgs
> Ellis
>|||Many Thanks !! Otherwise I'll be in big trouble when the system get
overflow. But now, I can I solve this problem now? and I'm using int data
type, what's its maximum value of it?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> You'll get an overflow when the identity value exceeds the maximum value
for
> the data type If this is a possibility in your situation, consider using
a
> data type with a larger maximum value, such as bigint or decimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> > Dear all,
> >
> > I use the identity property that makes generating unique numeric
> > values in my table "OrderDetail". Its primary keys include the column
> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
wonder
> > that if the application keep going to use for many years. Is it possible
> > the
> > identity value will be overflowed? or it will restart from 1 again
> > automatically after the overflow. And now, i make this column type is
> > "Int".
> > Anyone can help? Thanks.
> >
> > Best Rdgs
> > Ellis
> >
> >
>|||Hello Ellis,
from books online:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
(2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
integer.
Regards,
Tomislav Kralj
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
> Many Thanks !! Otherwise I'll be in big trouble when the system get
> overflow. But now, I can I solve this problem now? and I'm using int data
> type, what's its maximum value of it?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum value
> for
>> the data type If this is a possibility in your situation, consider using
> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>|||Which means that if your IDENTITY is set with the default (1,1), you can
store one row per second for the next 68 years. Just to put things in
perspective :)
--
Jacco Schalkwijk
SQL Server MVP
"Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
> Hello Ellis,
> from books online:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
> integer.
>
> Regards,
> Tomislav Kralj
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>> Many Thanks !! Otherwise I'll be in big trouble when the system get
>> overflow. But now, I can I solve this problem now? and I'm using int data
>> type, what's its maximum value of it?
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum value
>> for
>> the data type If this is a possibility in your situation, consider
>> using
>> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique
>> > numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
>> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>>
>|||Or with bigint, you can store one mullion rows per second for 1142 years
:-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:e2g%23QR5LFHA.568@.TK2MSFTNGP09.phx.gbl...
> Which means that if your IDENTITY is set with the default (1,1), you can
> store one row per second for the next 68 years. Just to put things in
> perspective :)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
> news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
>> Hello Ellis,
>> from books online:
>> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
>> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
>> integer.
>>
>> Regards,
>> Tomislav Kralj
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>> Many Thanks !! Otherwise I'll be in big trouble when the system get
>> overflow. But now, I can I solve this problem now? and I'm using int
>> data
>> type, what's its maximum value of it?
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum
>> value
>> for
>> the data type If this is a possibility in your situation, consider
>> using
>> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique
>> > numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
>> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>>
>>
>