Dei decimal e dei bytes regalati…

Mi chiedo quale sia il senso di definire, sulla struttura di una tabella, una colonna DECIMAL(3,0).

Sostanzialmente un attributo in grado di memorizzare cifre comprese tra -999 e 999.

 

La domanda, che dovrebbe guidare ogni scelta in fase di modellazione, è: qual è il tipo più piccolo in grado di memorizzare l’informazione più grande?

 

In questo caso il tipo SMALLINT, che consente una memorizzazione da -2^15 (-32,768) a 2^15-1 (32,767), sarebbe tranquillamente sufficiente.

La differenza? Lo storage necessario a memorizzare lo stesso dato (5 bytes per il DECIMAL, contro 2 bytes per lo SMALLINT).

 

 DECLARE @d decimal( 3 , 0 ) = -999;
DECLARE @s smallint = -999;

SELECT 
    'Decimal' as DataType,
    @d as Value, 
    DATALENGTH( @d ) as DataLength
UNION
SELECT
    'Smallint',
    @s , 
    DATALENGTH( @s );

 

Se provassimo a fare una simulazione di comparazione, utilizzando due tabelle modellate con tre colonne ciascuno e 500.000 righe, la differenza sarebbe ancora più evidente:

 USE tempdb;
GO

CREATE TABLE testDecimal
(
    myColumn1 decimal( 3 , 0 ),
    myColumn2 decimal( 3 , 0 ),
    myColumn3 decimal( 3 , 0 )
);
CREATE TABLE testInt
(
    myColumn1 smallint,
    myColumn2 smallint,
    myColumn3 smallint
);
GO

;WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
        FROM L5)
INSERT testDecimal
SELECT TOP (500000) 1,2,3 FROM Nums
GO

;WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
        FROM L5)
INSERT testInt
SELECT TOP (500000) 1,2,3 FROM Nums
GO

SELECT
      object_name(object_id) as objName,
       record_count,
      page_count as data_page_count,
      avg_record_size_in_bytes 
  FROM sys.dm_db_index_physical_stats
    ( DB_ID( ) , OBJECT_ID( 'testDecimal' ) , -1 , NULL , 'DETAILED' )
UNION
SELECT
      object_name(object_id) as objName,
       record_count,
      page_count as data_page_count,
      avg_record_size_in_bytes 
  FROM sys.dm_db_index_physical_stats
    ( DB_ID( ) , OBJECT_ID( 'testInt' ) , -1 , NULL , 'DETAILED' );
GO

DROP TABLE testDecimal;
DROP TABLE testInt;
GO

 

Il suggerimento?

Evitare di sprecare bytes inutilmente…

Comments

• Anonymous
  June 03, 2013
  Le best practice di design (e anche di programmazione) non sono più di moda...