Control storage of Data

Hi
From one of my co-workers I have got the question:
Is it possible to control the way that SQL Server stores
numeric data?
The case is when he e.g. want to store 0.5 (or in Denmark
0,5) - that is 1/2 (one half) - it is stored (as far as we
can se, in the tables as 0.5 (or as it's shown with the
Danish settings 0,5).
The team he belongs to also works with Oracle and they say
that it seems that Oracle stores the same data as .5
(og ,5) omitting the preceeding 0 (zero).
I can't find any "handles to turn in SQL Server", is there
any?
(Or maybe it is possible to change the Oracle way of
storing data?)
Yours truly
Jakob Persson
Numeric data isn't *stored* in that format at all. It's stored internally as
a binary number.
What you really want is to control the way it is displayed and entered on
the screen. How to control that formatting depends on your client
application or development platform not on SQL Server. Regional Settings are
usually determined by your Windows Control Panel settings.
David Portas
SQL Server MVP
|||J. Persson,
There are many different data types that you can use to represent a
numerical value.
bigint, int, smallint, tinyint
decimal, numeric (synonymous)
float, real (synonymous)
character types - char, varchar, etc
binary, varbinary
I would recommend you use the decimal data type to store a decimal
number. As David mentioned, there are two different elements to
retrieving numbers. The first one is the storage data type, and the
second is how this is represented in an application.
After you have retrieved your number using the most appropriate data
type, you can format it on the server-side using CAST or CONVERT, and
also in your client-side app.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
J. Persson wrote:
> Hi
> From one of my co-workers I have got the question:
> Is it possible to control the way that SQL Server stores
> numeric data?
> The case is when he e.g. want to store 0.5 (or in Denmark
> 0,5) - that is 1/2 (one half) - it is stored (as far as we
> can se, in the tables as 0.5 (or as it's shown with the
> Danish settings 0,5).
> The team he belongs to also works with Oracle and they say
> that it seems that Oracle stores the same data as .5
> (og ,5) omitting the preceeding 0 (zero).
> I can't find any "handles to turn in SQL Server", is there
> any?
> (Or maybe it is possible to change the Oracle way of
> storing data?)
> Yours truly
> Jakob Persson
>
|||Hi
Thank you for your reply.
I am aware of the facts your writing about, so I might
have to be a bit more specific.
My co-worker is writing an application that reads "raw
binary data" information directly from the SQL Server
transaction log file using MFC C++ (not "through the
tables" presented to us "normal database people" when we
use T-SQL, ADO.NET etc.).
Since my company plans to extend our application so it can
handle Oracle databases also, they have discovered (that's
what they have told me anyway) that there seems to be a
difference in how SQL Server and Oracle stores the data
binary (?). They think it may have a conection to how a
decimal value is stored when the value is btw. 1 and -1
(eg. does the binary pattern represent 0.5 or .5).
I hope this makes more sence.
yours truly
jakob Persson

>--Original Message--
>Numeric data isn't *stored* in that format at all. It's
stored internally as
>a binary number.
>What you really want is to control the way it is
displayed and entered on
>the screen. How to control that formatting depends on
your client
>application or development platform not on SQL Server.
Regional Settings are
>usually determined by your Windows Control Panel settings.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>
|||> there seems to be a
> difference in how SQL Server and Oracle stores the data
> binary (?).
That's not so surprising. Oracle supports non-Wintel platforms which have
different standards for binary numerics. Probably Oracle's internal
implementation changes depending on the platform but maybe there are some
things they have standardised across all OSs - I really don't know.
To answer your question, in SQL Server there is no method of controlling how
values are stored internally beyond using the standard datatypes.
Lumigent (www.lumigent.com) markets a Log Explorer product so it may help
you to check it out if you haven't already. Maybe it will help you analyze
the log data.
Hope this helps.
David Portas
SQL Server MVP