Really large integer constants - confused

I ran the following in SQL Server 2000 and 2005 with the same results.
select 1234567890123456 / 1234567890123
select 1234567890123456 / convert(bigint,1234567890123)
select convert(bigint,1234567890123456) /
convert(bigint,1234567890123)
1000.00000000036936
---
1000.00000000036936000332
1000
Everything I have found in Books on Line (both 2000 and 2005 versions)
says that talks about constants says those numbers are "integer
constants":
"integer constants are represented by a string of numbers not enclosed
in quotation marks and do not contain decimal points. integer
constants must be whole numbers; they cannot contain decimals."
Obviously these long "strings of numbers not enclosed in quotation
marks" and not containing decimal points are not being treated as
Integers.
Can anyone (1) explain what is going on, and (2) point me to where it
is documented?
Thanks!
Roy> 1000.00000000036936
This post is not going to be helpful at all. Just wanted to post my
observations. My first reaction was that it looks like they are implicity
being converted to FLOAT. But this is not true, since you actually lose
precision in that case:
SELECT CONVERT(FLOAT, 1234567890123456) / CONVERT(FLOAT, 1234567890123)
--
1000.00000000037
But take a look at implicit conversion to decimal:
select 1.0 * 1234567890123456 / 1234567890123
--
1000.000000000369360
Pretty darn close...|||If you do this
select
1234567890123456 as c1,
1234567890123 as c2,
convert(bigint,1234567890123456) as c3,
convert(bigint,1234567890123) as c4
into sometable
and then look at the columns in the new table
it appears to show that integers bigger than 'int'
are converted to numeric with differing precisions.
I'm not aware of this in BOL.|||> and then look at the columns in the new table
> it appears to show that integers bigger than 'int'
> are converted to numeric with differing precisions.
> I'm not aware of this in BOL.
More specifically, it looks like numeric literals exceeding 2147483647 are
converted to numeric(n), where n is the number of digits in the literal.
Consequently, the expression result is also numeric according to standard
data type precedence rules. The result is not an integer because
SELECT 1234567890123456 / 1234567890123
is functionally identical to:
SELECT
CAST(1234567890123456 AS numeric(16)) /
CAST(1234567890123 AS numeric(13))
and the result is numeric(30, 14) to allow for the remainder of the division
expression.
IMHO, this is contrary to the documentation and not intuitive. The final
result should be converted to an integer:
SELECT
CAST(
CAST(1234567890123456 AS numeric(16)) /
CAST(1234567890123 AS numeric(13))
AS numeric(38))
Hope this helps.
Dan Guzman
SQL Server MVP
<markc600@.hotmail.com> wrote in message
news:1140958196.462553.282950@.i40g2000cwc.googlegroups.com...
> If you do this
> select
> 1234567890123456 as c1,
> 1234567890123 as c2,
> convert(bigint,1234567890123456) as c3,
> convert(bigint,1234567890123) as c4
> into sometable
> and then look at the columns in the new table
> it appears to show that integers bigger than 'int'
> are converted to numeric with differing precisions.
> I'm not aware of this in BOL.
>|||>More specifically, it looks like numeric literals exceeding 2147483647 are
>converted to numeric(n), where n is the number of digits in the literal.
I am sure that is it, Dan. It appears that when they added bigint as
a data type they never got around to adjusting the code related to
integer constants.
I should check the docs for 7.0 next w to see if it is mentioned
there.
Do the MVPs still have a more direct line for reporting these sorts of
things than the general public?
Roy|||
Roy Harvey wrote:

>I am sure that is it, Dan. It appears that when they added bigint as
>a data type they never got around to adjusting the code related to
>integer constants.
>I should check the docs for 7.0 next w to see if it is mentioned
>there.
>Do the MVPs still have a more direct line for reporting these sorts of
>things than the general public?
>
Roy,
We have an inside channel, but the best route nowadays is the public
bug-reporting forum at http://lab.msdn.microsoft.com/ProductFeedback/.
I'd encourage you to file a bug report (if you think the behavior is wrong)
or a suggestion (to improve documentation or change behavior). The latter
might be more successful, since a bug report can be closed as "by design",
after which the request to improve documentation might more easily be lost.
There are two issues here: one is how literals are typed. That is a messy
issue, and not uniquely messy to T-SQL. I've reported some cases where
the same literal is typed differently in different contexts, but this is
not quite
as bad, and could be improved with better documentation. (BOL says that
a literal decimal has a decimal point, and that's not helpful here.)
The other issue is the internal/intermediate use of floating-point
arithmetic
in decimal calculations. That behavior (questionable in my estimation) also
causes other oddities, such as power(2.,57) ending up divisible by 10.
Since
DECIMAL is billed as an "exact numeric type" (though it's no more exact
that float - all types represent the values they represent exactly),
maybe someone
will care.
Here's a more glaring example of the first behavior:
select 2000000001/2 -- returns 1000000000
select 20000000001/2 -- returns 10000000000.500000
or select 1000000000/1000000001, 10000000000/10000000001
You can play around with this repro to see more:
declare @.s sql_variant
set @.s = 2147483648
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = 2147483647
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = -2147483647
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = -2147483648
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = cast(-2147483648 as int)
select @.s, sql_variant_property(@.s,N'BaseType')
set @.s = cast(25 as smallint)
Any change would be a breaking change, of course.
Steve Kass
Drew University
:

>Roy
>|||With later technologies like SQL Server 2005, you are empowered to submit
bug reports and suggestions directly via the Product Feedback Center at
http://lab.msdn.microsoft.com/productfeedback/. Documentation feedback can
be submitted via the Books Online. The SQL devs do an great job of
monitoring and acting on the feedback.
I submitted this as a bug
<http://lab.msdn.microsoft.com/Produ...BK46
489>.
Feel free to vote on it.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:611402d49jv1siu85i6a5crcmt69g22vtp@.
4ax.com...
> I am sure that is it, Dan. It appears that when they added bigint as
> a data type they never got around to adjusting the code related to
> integer constants.
> I should check the docs for 7.0 next w to see if it is mentioned
> there.
> Do the MVPs still have a more direct line for reporting these sorts of
> things than the general public?
> Roy|||Dan,
Thanks!
I visited the bug you created and validated and voted. I was going to
add a comment, but it said it would "Post In Newsgroup
microsoft.private.msdn.productfeedback.vb". I was thrown off by the
.vb ending, which sure sounds like a different product to me!
The note I was going to add was that the same applies to SQL Server
2000.
I will try to add feedback for the docs too, since that is where
something might actually be done.
Roy
On Sun, 26 Feb 2006 15:40:06 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>With later technologies like SQL Server 2005, you are empowered to submit
>bug reports and suggestions directly via the Product Feedback Center at
>http://lab.msdn.microsoft.com/productfeedback/. Documentation feedback can
>be submitted via the Books Online. The SQL devs do an great job of
>monitoring and acting on the feedback.
>I submitted this as a bug
><http://lab.msdn.microsoft.com/Produ...BK4
6489>.
>Feel free to vote on it.