A senior moment?
-
Maybe I'm having a senior moment and have just forgotten something very basic about SQL Server 2000, but can someone remind me WHY the following simple statement:
SELECT 99/2
returns a result set of '49.0'?
Even SELECT CAST(99/2 AS Float) results in '49.0'.
Why would that be?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
try select 99.0/2.0
-
SELECT 99/2 for me returns a result set of '49', not '49.0'.
-
BP, you're right. I misquoted in my original post.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
Okay, I vaguely recall something now about SQL Server using the data types of origin in doing calculations. So when I entered
SELECT 99/2
SQL Server assumed I was doing Integer math and returned an Integer. Is that what I'm seeing and hearing?
So, if I have mixed data types [say, Dec(15,3) and an Int] for a calculation, in what data type will the result be passed in my code?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
99.0/2.0 = 99.0/2 = 99/2.0 = 49.500000
Highest precision seems to dictate the result data type.
-
FROM BOL:
This is the precedence order for the Microsoft® SQL Server™ 2000 data types:
- sql_variant (highest)
- datetime
- smalldatetime
- float
- real
- decimal
- money
- smallmoney
- bigint
- int
- smallint
- tinyint
- bit
- ntext
- text
- image
- timestamp
- uniqueidentifier
- nvarchar
- nchar
- varchar
- char
- varbinary
- binary (lowest)
from the Precence list your datatypes "if can be converted implicitly" (SEE CAST AND CONVERT IN BOL) will use the Highest !
* Noel -
Somebody correct me if I am wrong but I think that when you use 99/2, you get 49 because both numbers are specified as integer. Even though the answer requires greater precision, SQL Server will not automatically convert it for you to different data type. That is why if you try:
select cast(49.5 as int)
you get 49.
However, if you were to do this:
SELECT CAST(99 AS Float) / CAST(2 AS Float)
You get 49.5
HTH
Billy
-
When you look at / Divide in BOL you will find
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.
As 99 and 2 are interpreted by SQL Server as Integers the above applies. To force another result you can use something like
SELECT 99./2
----------
49.500000
(1 row(s) affected)
Basically the same as SELECT 99.0/2, but more for the lazy programmer.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Okay. I don't mean to be hard to get along with here, but I need to understand more clearly:
Here is a script from The Guru's Guide to Transact SQL--
SELECT Median = CASE (COUNT(*) % 2)
WHEN 0 THEN -- even number of values
(d.c1 + MIN(CASE
WHEN i.c1 > d.c1 THEN i.c1
ELSE NULL
END))/2.0 -- Here it indicates to use '2.0' (with the decimal)
ELSE
d.c1 -- odd number of values
END
FROM dist d
CROSS JOIN dist i
GROUP BY d.c1
HAVING COUNT(CASE
WHEN i.c1 <= d.c1 THEN 1
ELSE NULL
END)
= (COUNT(*)+1)/2 -- Here there is NO decimal, AND if I state this as '2.0' the script
-- fails to deliver a result set, but does NOT fail syntactically.
How do--or will--I know when I am getting erroneous results due to how values are stated in my scripts?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
The First /2.0 is a computed FORMULA for the median so it has to be DECIMAL or FLOAT
The Second is just a count of RECORDS(ROWS)!! so it can't be a record and a half then you use /2
HTH
* Noel -
I agree with your reasoning entirely. However, what I am uncertain regarding is WHY using '2.0' in dividing the COUNT() would result in an empty result set. Division by '2' or '2.0' should result the same value, should it not?
Again, I'm not trying to be obtuse here: Just trying to get my hands around the underlying logic.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
THE CLAUSE
COUNT(CASE
WHEN i.c1 <= d.c1 THEN 1
ELSE NULL
END)
RETURNS an INTEGER number
If you use these you are returning an integer number
= (COUNT(*)+1)/2 -- integer
if you do
= (COUNT(*)+1)/2.0 -- float
it will return a float
and in SQL 2.0 <> 2
so you get an empty recordset
Does that makes sense?
* Noel -
Well, it is getting clearer: Are you saying that 2.0 <> 2 in SQL Server because when SQL Server evaluates record counts, it is looking not ONLY the VALUE of the number but considers also some data type? To say that 2.0 <> 2 when they are evaluated as NUMBERS (of any data type--float, decimal, integer) seems strange. It is almost as though SQL Server is evaluating "2.0" as a STRING of some kind if it does NOT equal "2". Is that how SQL Server evaluates the results of a COUNT()?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
NOT ALL numbers can be EXACTLY REPRESENTED in the FLOAT format and you may get errors if you try to compare floats to integers.
The safest bet if to use the same TYPE for comparison purposes
* Noel
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply