Greetings.
I need to convert some columns of type numeric(12, 0) to hold floating point
information scale and precision I can't determine in advance (customer data
can vary wildly) so I wanted to use the datatype that offers the maximum scale
and precision..
I'm targeting sql server 2005 systems (not 2000).
It seems my choices are real and float, and the docs seem to indicate that
float offers with widest ranges.
I'm trying out using the 'float' for the new data type as the default
precision is said to be 53.. Does this mean the total number of digits is up
to 53?
I don't know if there is anything else I need to take into account since these
two columns are part of a primary key, and I supposed, therefore, are indexed.
thanks
Jeff Kish
6  3664 
Jeff Kish (je*******@mro. com) writes:
I need to convert some columns of type numeric(12, 0) to hold floating
point information scale and precision I can't determine in advance
(customer data can vary wildly) so I wanted to use the datatype that
offers the maximum scale and precision..
I'm targeting sql server 2005 systems (not 2000).
It seems my choices are real and float, and the docs seem to indicate that
float offers with widest ranges.
I'm trying out using the 'float' for the new data type as the default
precision is said to be 53.. Does this mean the total number of digits
is up to 53?
The total number of binary digits in the mantissa. Which in decimal
terms means something like 14-16 digits in precision. The scale can
range from 1E308 to 1E-308.
I don't know if there is anything else I need to take into account since
these two columns are part of a primary key, and I supposed, therefore,
are indexed.
Putting a float into a primary key is definitely not recommendable. Float
is an approxamite data type, meaning the same decimal value can be
represented in more than one way, depending on how you arrived to the
result. It would be a (correct) knee-jerk reaction from anyone who
reviewed your schema to flag float values in a PK constraint as dubious.
Since I don't know your customer's data, it's difficult to say what
would be the best. But if all values are integer, that is there is no
decimal portion, decimal(38,0) is probably the best. On SQL 2005 SP2,
there is a new table option, "vardecimal storage format". When this is
in force, decimal values do not take more space than necessary. This
option is only available in Enterprise Edition.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
On Thu, 7 Jun 2007 21:54:25 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.sewrote:
>Jeff Kish (je*******@mro. com) writes:
>I need to convert some columns of type numeric(12, 0) to hold floating
point information scale and precision I can't determine in advance
(customer data can vary wildly) so I wanted to use the datatype that
offers the maximum scale and precision..
I'm targeting sql server 2005 systems (not 2000).
It seems my choices are real and float, and the docs seem to indicate that
float offers with widest ranges.
I'm trying out using the 'float' for the new data type as the default
precision is said to be 53.. Does this mean the total number of digits
is up to 53?
The total number of binary digits in the mantissa. Which in decimal
terms means something like 14-16 digits in precision. The scale can
range from 1E308 to 1E-308.
>I don't know if there is anything else I need to take into account since
these two columns are part of a primary key, and I supposed, therefore,
are indexed.
Putting a float into a primary key is definitely not recommendable. Float
is an approxamite data type, meaning the same decimal value can be
represented in more than one way, depending on how you arrived to the
result. It would be a (correct) knee-jerk reaction from anyone who
reviewed your schema to flag float values in a PK constraint as dubious.
Since I don't know your customer's data, it's difficult to say what
would be the best. But if all values are integer, that is there is no
decimal portion, decimal(38,0) is probably the best. On SQL 2005 SP2,
there is a new table option, "vardecimal storage format". When this is
in force, decimal values do not take more space than necessary. This
option is only available in Enterprise Edition.
thanks.
the data represents coordinates on images that can vary vastly in
scale and precision.
The primary key well... each row in the table represents a text
display on an image.. the row has columns for:
book
page
label
xcoord
ycoord
because the same label can appear several times on one page in a book,
the coords are included.
one page might vary from -1.234565 to 1.3234343 in extents, placing
all coordinates in that range, and another might be a different type
of image and range from -10245 to 10245.
it seems to work ok, though i understand it is not optimal.
it is of course, a legacy ...
thanks again
JEff
Jeff Kish (ki*******@char ter.net) writes:
the data represents coordinates on images that can vary vastly in
scale and precision.
The primary key well... each row in the table represents a text
display on an image.. the row has columns for:
book
page
label
xcoord
ycoord
because the same label can appear several times on one page in a book,
the coords are included.
one page might vary from -1.234565 to 1.3234343 in extents, placing
all coordinates in that range, and another might be a different type
of image and range from -10245 to 10245.
Could you have coordinates that are 1E12 or 1E-12 as well? I would
expect that is after all some practical limit. In this case you could use
something like decimal(20,10). Or may be varchar is an alternative? I
would definitely avoid float.
Yet an alternative is some roll-your-own decimal. That is, you would
save the coordinates as integer, and you would store the scale separately,
possibly in a table with the page as key.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
On Fri, 8 Jun 2007 21:07:04 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.sewrote:
>Jeff Kish (ki*******@char ter.net) writes:
>the data represents coordinates on images that can vary vastly in
scale and precision.
<snip>
>one page might vary from -1.234565 to 1.3234343 in extents, placing
all coordinates in that range, and another might be a different type
of image and range from -10245 to 10245.
Could you have coordinates that are 1E12 or 1E-12 as well? I would
expect that is after all some practical limit. In this case you could use
something like decimal(20,10). Or may be varchar is an alternative? I
would definitely avoid float.
thanks.
so avoid float because it is a non exact storage and that makes it dubious for
part of a key, or is it just because floats are inherently bad in a pk for
performance reasons?
>Yet an alternative is some roll-your-own decimal. That is, you would
save the coordinates as integer, and you would store the scale separately,
possibly in a table with the page as key.
I really would like to/need to stay with some standarad / built in type.
I'll take a peed at the decimal... maybe I can make due with them.
why are they better than floats for the pk cols?
regards
Jeff
Jeff Kish
Jeff Kish (je*******@mro. com) writes:
so avoid float because it is a non exact storage and that makes it
dubious for part of a key, or is it just because floats are inherently
bad in a pk for performance reasons?
The former. For performance it is as good as any other bit pattern. But that
is of little interest when you may fail read rows because your input bits
does not match the table bits.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jeff Kish wrote:
>
On Fri, 8 Jun 2007 21:07:04 +0000 (UTC), Erland Sommarskog
<es****@sommars kog.sewrote:
Jeff Kish (ki*******@char ter.net) writes:
the data represents coordinates on images that can vary vastly in
scale and precision.
<snip>
one page might vary from -1.234565 to 1.3234343 in extents, placing
all coordinates in that range, and another might be a different type
of image and range from -10245 to 10245.
Could you have coordinates that are 1E12 or 1E-12 as well? I would
expect that is after all some practical limit. In this case you could use
something like decimal(20,10). Or may be varchar is an alternative? I
would definitely avoid float.
thanks.
so avoid float because it is a non exact storage and that makes it dubious for
part of a key, or is it just because floats are inherently bad in a pk for
performance reasons?
Dubious for (part of) a key. See below
Yet an alternative is some roll-your-own decimal. That is, you would
save the coordinates as integer, and you would store the scale separately,
possibly in a table with the page as key.
I really would like to/need to stay with some standarad / built in type.
I'll take a peed at the decimal... maybe I can make due with them.
why are they better than floats for the pk cols?
The potential problem with floats is their inexact nature. Depending on
your hardware the value may differ (slightly). This can cause problems
when moving to a different server (which you might have to do if there
is a hardware problem).
Because of that you basically should not use them for keys.
HTH,
Gert-Jan
regards
Jeff
Jeff Kish
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Galina |
last post by:
Hello
I am trying to create a table, which includes a field type LONG. The
wizard hasn't allowed me to enter storage settings for this table. It
set the initial size is 64K and not available to change, next size 0K,
increase size by 0%.
Since I am going to copy data from an existing MS Access table into
this one, I know that the initial amount of records will be about
70000 and it will take about 7 Mb of disk space. I know it, because I...
|
by: Troels Arvin |
last post by:
Hello,
For some very data-intensive projects it's interesting how much space the
DBMS uses for the storage of data, so I'm investigating how space
efficient different DBMSes are.
In the PostgreSQL manual, it's written that values of the type INTEGER
take op four bytes. I was curious about how close to real-World this
number is, so I did a test: How much space does PostgreSQL use when
storing 100000 rows where each row consists of a...
|
by: Michele Guidolin |
last post by:
Hello to everybody.
I'm doing some benchmark about a red black Gauss Seidel algorithm with 2
dimensional grid of different size and type, I have some strange result
when I change the computation from double to float.
Here are the time of test with different grid SIZE and type:
SIZE 128 256 512
|
by: Stu Smith |
last post by:
A feature we'd like to see in VS 2004 is a compiler warning for equality
comparisons on floating values. Why? Because the behaviour allowed under the
ECMA spec is somewhat suprising. (Well it suprised us).
If you run the program below in debug mode (F5), it prints True. If you run
it in non-debug mode (Ctrl-F5), you get False.
We had a look at the ECMA spec (CLI - 12.1.3 line 35) and this behaviour
seems perfectly acceptable.
|
by: Jon Shemitz |
last post by:
Is there a difference between a constant like "12.34f" and "(float)
12.34"?
In principle, at least, the latter is a double constant being cast to
a float; while the two both generate actual constants, does the latter
ACTUALLY do a conversion at compile time? That is, are there constants
where
<constant>f != (float) <constant>
| | |
by: Namratha Shah \(Nasha\) |
last post by:
Hey Group,
After a long week end I am back again. Its nice and refreshing after a short
vacation so lets get started with .NET once again.
Today we will discuss about Isolated Storage. This is one of the topics
which I find interesting as I feel that it has a lot of practical usage or
applicability.
We all know that all applications need some storage space to archive certain
|
by: abdul_n_khan |
last post by:
I have a basic question related to datatype conversion. I am
multiplying currency to float datatype.
fltInterestRate=1.23333;
curAmount = 91000000;
curInterestAmount = curAmount * fltInterestRate
curInterestAmount should have 112233030
|
by: David Wade |
last post by:
Folks,
Well I am still dabling in the mire of math.h and getting on reasonably
well. A couple of questions.
Firstly when defining some of the extreme values in the many bits of code
seem to use "extern" definitions of various types. Is it OK to do something
like this in float.h:-
extern _ex_dbl_vals;
DBL_MAX _ex_dbl_vals;
|
by: Richard Harter |
last post by:
Apologies for the length - this post is best viewed with fixed font
and a line width >= 72.
Below is the source code for a C header file that provides a suite
of storage management macros. I am asking for comments on it. In
particular: Are there any gotchas that I have overlooked? Are
there any suggestions for improvements? Is there a generally
available superior packages to do the same thing with the same
general licensing? ...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
