October 9, 2018 at 10:19 am
Jonathan AC Roberts - Tuesday, October 9, 2018 10:14 AMScottPletcher - Tuesday, October 9, 2018 9:50 AMJonathan AC Roberts - Tuesday, October 9, 2018 9:38 AMScottPletcher - Tuesday, October 9, 2018 9:27 AMAs to storage size, that's not the only consideration. You also need to consider vardecimal format, which SQL Server uses to reduce bytes. Not all decimal values automatically require all 5 bytes. Adding extra digits could cost you extra bytes, since the data will compress less.Although DATALENGTH returns different values depending on the size of the item stored. When the data is stored on a table it uses no less space. So the data doesn't seem to be compressed when added to a table.
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics;IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16
CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL)
INSERT INTO #TempNumerics16(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics16
See this link: https://www.sqlservercentral.com/Forums/FindPost1993475.aspxThat's not really logical. The vardecimal format must be able to save space in specific cases or it wouldn't exist. Thus, to me the general rule stays the same: don't add digits you don't really need to decimal values assuming they won't require extra byte(s), because they could.
vardecimal looks like it's an option that you have to enable:
https://technet.microsoft.com/en-us/library/bb508963(v=sql.90).aspx
Not any more, not for quite a few versions, iirc. That is, if you do row compression, you will automatically get vardecimal format, you don't have to enable it first, again, iirc.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2018 at 10:32 am
ScottPletcher - Tuesday, October 9, 2018 10:19 AMJonathan AC Roberts - Tuesday, October 9, 2018 10:14 AMScottPletcher - Tuesday, October 9, 2018 9:50 AMJonathan AC Roberts - Tuesday, October 9, 2018 9:38 AMScottPletcher - Tuesday, October 9, 2018 9:27 AMAs to storage size, that's not the only consideration. You also need to consider vardecimal format, which SQL Server uses to reduce bytes. Not all decimal values automatically require all 5 bytes. Adding extra digits could cost you extra bytes, since the data will compress less.Although DATALENGTH returns different values depending on the size of the item stored. When the data is stored on a table it uses no less space. So the data doesn't seem to be compressed when added to a table.
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics;IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16
CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL)
INSERT INTO #TempNumerics16(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics16
See this link: https://www.sqlservercentral.com/Forums/FindPost1993475.aspxThat's not really logical. The vardecimal format must be able to save space in specific cases or it wouldn't exist. Thus, to me the general rule stays the same: don't add digits you don't really need to decimal values assuming they won't require extra byte(s), because they could.
vardecimal looks like it's an option that you have to enable:
https://technet.microsoft.com/en-us/library/bb508963(v=sql.90).aspxNot any more, not for quite a few versions, iirc. That is, if you do row compression, you will automatically get vardecimal format, you don't have to enable it first, again, iirc.
So only if you have row compression on the table?
October 9, 2018 at 10:42 am
Jonathan AC Roberts - Tuesday, October 9, 2018 10:32 AMScottPletcher - Tuesday, October 9, 2018 10:19 AMJonathan AC Roberts - Tuesday, October 9, 2018 10:14 AMScottPletcher - Tuesday, October 9, 2018 9:50 AMJonathan AC Roberts - Tuesday, October 9, 2018 9:38 AMScottPletcher - Tuesday, October 9, 2018 9:27 AMAs to storage size, that's not the only consideration. You also need to consider vardecimal format, which SQL Server uses to reduce bytes. Not all decimal values automatically require all 5 bytes. Adding extra digits could cost you extra bytes, since the data will compress less.Although DATALENGTH returns different values depending on the size of the item stored. When the data is stored on a table it uses no less space. So the data doesn't seem to be compressed when added to a table.
IF OBJECT_ID ('tempdb..#TempNumerics','U') IS NOT NULL DROP TABLE #TempNumerics
CREATE TABLE #TempNumerics(n1 NUMERIC(18,2) NOT NULL)
INSERT INTO #TempNumerics(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics;IF OBJECT_ID ('tempdb..#TempNumerics16','U') IS NOT NULL DROP TABLE #TempNumerics16
CREATE TABLE #TempNumerics16(n1 NUMERIC(16,6) NOT NULL)
INSERT INTO #TempNumerics16(n1)
SELECT C FROM (VALUES
(42949672.95),
(42949672.96),
(-42949672.95),
(-42949672.96)
) T(C)SELECT n1,DATALENGTH(n1) DATALENGTH,LEN(n1)LEN
FROM #TempNumerics16
See this link: https://www.sqlservercentral.com/Forums/FindPost1993475.aspxThat's not really logical. The vardecimal format must be able to save space in specific cases or it wouldn't exist. Thus, to me the general rule stays the same: don't add digits you don't really need to decimal values assuming they won't require extra byte(s), because they could.
vardecimal looks like it's an option that you have to enable:
https://technet.microsoft.com/en-us/library/bb508963(v=sql.90).aspxNot any more, not for quite a few versions, iirc. That is, if you do row compression, you will automatically get vardecimal format, you don't have to enable it first, again, iirc.
So only if you have row compression on the table?
Idk for sure. But for SQL 2016+, data compression is available in Standard Edition too. Either way, it doesn't make sense to ignore it when it could be available later. Again, I don't understand the desire to store extra data you don't actually need anyway. If you truly require all the extra decimal places, sure, store them (you might even want to consider float), but don't store them just because "the data will take 5 bytes anyway". That's not necessarily true, so don't store it if you don't need it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2018 at 12:54 pm
ScottPletcher - Friday, October 5, 2018 2:36 PM
Ok. So just to make sure I understand what you are saying, let's say I have a column as decimal (5,2). How would the number 583.318834 be stored internally?
Who cares? I think you're missing the point about datatypes in the SQL standards. We never wanted to specify the physical implementations, just performance. Let's be honest, if we have something that's FLOAT, REAL or DOUBLE PRECISION, we know it's really an IEEE standard format in the implementation. Oh, although it doesn't seem to matter much anymore, there's technically a difference between real and float, too. But we specify how numbers behave in the SQL language standards.
The NUMERIC (s, p) standard does not allow any extra precision, and therefore any rounding of computations has to follow the rules expected for a fixed number of decimal places; that means basically behave like COBOL.
The DECIMAL (s, p) standard does have extra precision, so it tends to do a better job with multiplication and division, have fewer rounding errors and so forth. In short, it's preferred,if you need to do math, to use this data type. In SQL Server it doesn't matter, thanks to its UNIX background. It's a good idea to always use it so that you have to port your code to a different SQL engine, it will work better.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 9, 2018 at 1:25 pm
Vardecimal was deprecated:
| Vardecimal storage format | Use of vardecimal storage format was encountered. Use data compression instead. |
October 9, 2018 at 2:41 pm
| Vardecimal storage format | Use of vardecimal storage format was encountered. Use data compression instead. |
For direct use. But when you compress data, SQL uses the vardecimal format to reduce the size.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2018 at 8:59 pm
michael.leach2015 - Friday, October 5, 2018 7:18 PMScottPletcher - Friday, October 5, 2018 2:36 PM
Ok. So just to make sure I understand what you are saying, let's say I have a column as decimal (5,2). How would the number 583.318834 be stored internally?
Who cares? I think you're missing the point about datatypes in the SQL standards. We never wanted to specify the physical implementations, just performance. Let's be honest, if we have something that's FLOAT, REAL or DOUBLE PRECISION, we know it's really an IEEE standard format in the implementation. Oh, although it doesn't seem to matter much anymore, there's technically a difference between real and float, too. But we specify how numbers behave in the SQL language standards.
The NUMERIC (s, p) standard does not allow any extra precision, and therefore any rounding of computations has to follow the rules expected for a fixed number of decimal places; that means basically behave like COBOL.
The DECIMAL (s, p) standard does have extra precision, so it tends to do a better job with multiplication and division, have fewer rounding errors and so forth. In short, it's preferred,if you need to do math, to use this data type. In SQL Server it doesn't matter, thanks to its UNIX background. It's a good idea to always use it so that you have to port your code to a different SQL engine, it will work better.
The reason one should care is because understanding the physical implementation can explain both performance and accuracy abilities and deficits ESPECIALLY if you DO need to migrate to something where there IS a difference. Even you took the time to explain the difference between Numeric and Decimal in something that had absolutely nothing to do with SQL Server.
As for portability to a different SQL Engine, who cares? It doesn't happen to most people and when it does happen, they find out that true portability is a myth even if you've written totally ANSI/ISO compliant code because not every RDBMS follows the standards the same way. Use the power of the system that you have. If you need to migrate, then do the rewrites necessary to use the power of what you've migrated to. It shouldn't be a big change for the front end code because, if they've done things correctly there, it should all be abstract enough to not matter much what you do in the backend code.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 11, 2018 at 8:31 am
michael.leach2015 - Monday, October 8, 2018 9:43 PMJonathan AC Roberts - Monday, October 8, 2018 7:58 PMmichael.leach2015 - Monday, October 8, 2018 7:41 PMOk. So just to make sure I understand what you are saying, let's say I have a column as decimal (5,2). How would the number 583.318834 be stored internally?
If you were to store it as decimal(9,6) It would be stored as 583318834 (which can be stored in a 4-byte two's compliment integer) with the extra byte having a value that instructs the number is shifted right by 6 decimal places, giving 583.318834. So it would need 5 bytes total to store it.
If you store it as decimal(5,2) it would store is as 58332 (also in a two's compliment 4 byte integer) with the additional byte telling it to be shifted right by 2 decimal places, giving 583.32.
Both variables take up 5 bytes, so you might just as well use decimal(9,6) as decimal(5,2)
If you were to store 583.32 in a decimal(9,6) it would store it as 583320000 with the additional byte holding the instruction to shit right by 6 decimal places. giving 583.320000Awesome explanation. Thank you.
After checking a bit more I think I have to update my explanation. It's right to a certain extent but the integer representation isn't stored as two's compliment. It's actually stored as an unsigned binary integer with the sign bit stored in a different byte to the integer. It's also stored in a little-endian way meaning the most significant byte (or maybe even nibble) is at the end of the variable not the beginning as we would normally write it. I think this allows SQL Server to truncate the end of the variable to save space.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply