CODEWITHSUNDEEP
sqlsql-servermemory
Nooshin
Nooshin

Reputation: 953

How are the "money" and "decimal" data types in SQL Server stored in memory?

I want to know about the storage SQL Server data types in memory.

How is the money data type in SQL Server stored in memory? I know that money is stored in 8 bytes and smallmoney is stored in 4 bytes. But I don't know how?

For example when you have 123400.93 for the money, how is it stored in 8 bytes?

I have the same question about the decimal and DATE data types.

Especially for DATE, the format is YYYY-MM-DD, but how is it stored in 3 bytes? Is it stored as described here: http://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html or the number of days from a specific day is stored?

Upvotes: 9

Views: 6477

Answers (3)

ChrisLively
ChrisLively

Reputation: 88064

Just adding a bit here...

A single byte is made up of 8 bits. A bit can hold 2 values (0 or 1).

So 4 bytes is 32 bits (4 x 8). Which means the numeric range of what can be stored is from 0 to 2^32 which gives a total range of 4,294,967,296 values.

smallmoney is signed so we drop one of the bits to be used for the sign, which leaves 2^31, or 2,147,483,648 possible values and the +/- sign.

Now we take into account that the last 4 digits of a money type are always after the decimal point and we end up with a range of -214,748.3648 to 214,748.3647

Technically, money and smallmoney values are stored by flipping bits in a word or byte just like everything else. If you need more info, read http://computer.howstuffworks.com/bytes.htm

Or you might see this for the possible value range of money and smallmoney: http://technet.microsoft.com/en-us/library/ms179882.aspx

update
For the DATETIME data type, it's the same concept with a little twist. In MS SQL a DATETIME is stored using 2 numbers. The first is number of days since 1/1/1900 and the second is number of ticks since midnight: See http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server and When storing a datetime in sql server (datetime type), what format does it store it in?

Upvotes: 10

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

How are the "money" [...] data types in SQL Server stored in memory?

If you want to see how is stored a MONEY (8 bytes) value then you could execute following script step/step:

CREATE DATABASE TestMoneyDT;
GO
USE TestMoneyDT;
GO

CREATE TABLE dbo.MyMoney -- :-)
(
    Col1 CHAR(5) NOT NULL,
    Col2 MONEY NOT NULL,
    Col3 CHAR(5) NOT NULL
);
GO

INSERT dbo.MyMoney (Col1, Col2, Col3)
VALUES ('AAAAA',12345678.0009,'BBBBB');
GO

-- Install http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-sp_allocationmetadata-putting-undocumented-system-catalog-views-to-work/
EXEC sp_AllocationMetadata 'dbo.MyMoney'
GO
/*
Stored procedure output:

Object Name Index ID Alloc Unit ID     Alloc Unit Type First Page Root Page First IAM Page
----------- -------- ----------------- --------------- ---------- --------- --------------
MyMoney     0        72057594039697408 IN_ROW_DATA     (1:147)    (0:0)     (1:150)
*/

SELECT DB_ID() AS DBID
GO
/*
DBID
----
13
*/

-- Reading data from page (1:147) (file id 1, page number 147)
DBCC TRACEON(3604);         -- http://technet.microsoft.com/en-us/library/ms187329.aspx
DBCC PAGE(13, 1, 147, 3);   -- http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx
DBCC TRACEOFF(3604);        -- http://technet.microsoft.com/en-us/library/ms174401.aspx
GO

-- See [Memory dump @0x0000000014AEA060] of DBCC PAGE output
/*
Memory Dump @0x000000000E76A060

0000000000000000:   10001600 41414141 41e9f698 be1c0000 †....AAAAAéö.¾... 
0000000000000010:   00424242 42420300 00†††††††††††††††††.BBBBB...  

41414141 41 = AAAAA <- Col1 CHAR(5)
e9f698 be1c0000     <- Col2 MONEY take this string and run following script (look at SumOverAll values)
424242 4242 = BBBBB <- Col3 CHAR(5)
*/
GO

DECLARE @HexString VARBINARY(8) = 0xE9F698BE1C; -- One MONEY value consumes 8 bytes
WITH N10
AS 
(
    SELECT  *
    FROM    (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) x(Num)
)
SELECT  src.*, 
        SUM(src.IntValueMultipliedByte) OVER() AS SumOverAll
FROM
(
    SELECT  n.Num, 
            SUBSTRING(@HexString, n.Num, 2) AS HexValue,
            CONVERT(INT, SUBSTRING(@HexString, n.Num, 1)) AS IntValue,
            POWER(CONVERT(NUMERIC(38,0), 256), n.Num-1) AS Byte,
            CONVERT(INT, SUBSTRING(@HexString, n.Num, 1)) * POWER(CONVERT(NUMERIC(38,0), 256), n.Num-1) AS IntValueMultipliedByte
    FROM    N10 n
    WHERE   n.Num <= LEN(@HexString)
) src;
GO

/*
NumHexValue IntValue    Byte       IntValueMultipliedByte SumOverAll
----------- ----------- ---------- ---------------------- ------------
1  0xE9F6   233         1          233                    123456780009
2  0xF698   246         256        62976                  123456780009
3  0x98BE   152         65536      9961472                123456780009
4  0xBE1C   190         16777216   3187671040             123456780009
5  0x1C     28          4294967296 120259084288           123456780009
*/

Note: I used SQL2008R2.

Upvotes: 3

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

I think you may find this interesting Performance / Storage Comparisons : MONEY vs. DECIMAL

The person arguing for MONEY showed the space used by MONEY compared to the same information stored in a DECIMAL(20,4) column. Not all that surprisingly, the latter was slightly larger. But is that the whole story? No, for two reasons. One is that the performance of these choices was not compared, and the other is that DECIMAL(20,4) is not a very realistic requirement for storing currency data. Unless you are storing the pricing information for luxury yachts or aircraft carriers, in which case you can probably drop the decimal places altogether and use INT or BIGINT. For the rest of us, a better choice would be DECIMAL(8,2) or DECIMAL(10,2).

Also check this related question: Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?

Upvotes: 1

Related Questions