Sql-server – Computed Columns Or Views – Database Table Design

database-designdatabase-recommendationsql server

I want to store values in certain units along with their corresponding conversions. So for example, there would be following columns

  1. DateTime
  2. Millimetres
  3. Centimetres
  4. Metres

For a given datetime, the user will provide me a value in any one of the units. So I will need to calculate the rest by myself. Is this better to implement using computed columns or views? I suppose if I am using views or computed columns, I will need to always provide one specific unit as input which I can do in code before inserting. Is their any other way and what is the best way to implement such requirement?

Best Answer

You should store any value using its ISO measure unit. Calculations and conversions should be done at the client side. Don't expect any performance gain over client side conversion when using computed columns.

As far as passing in data in any measure unit, I advise against that design: make the client pass in the data in the ISO measure unit and do any conversion on the client.

If you insist passing data in any unit, this is how an INSTEAD OF trigger could work:

CREATE TABLE length_values (
    id int PRIMARY KEY,
    length_m decimal(21,6)
)
GO

CREATE VIEW v_length_values
AS
SELECT 
    id,
    length_m,
    (length_m * 1000) AS length_mm,
    (length_m / 1000) AS length_km
FROM length_values;
GO


CREATE TRIGGER TR_length_values ON v_length_values
INSTEAD OF INSERT, UPDATE
AS
BEGIN

    SET NOCOUNT ON;

    MERGE INTO length_values AS trg
    USING INSERTED AS src
        ON trg.id = src.id
    WHEN MATCHED THEN
        UPDATE
        SET length_m = COALESCE(src.length_m, src.length_mm / 1000, src.length_km * 1000)
    WHEN NOT MATCHED THEN
        INSERT (id, length_m)
        VALUES (id, COALESCE(src.length_m, src.length_mm / 1000, src.length_km * 1000));

END

Some data to test:

INSERT INTO v_length_values (id, length_km) VALUES (1, 20);
INSERT INTO v_length_values (id, length_m) VALUES (2, 20);
INSERT INTO v_length_values (id, length_mm) VALUES (3, 1);

Results:

+----+--------------+-----------------+----------------+
| id |   length_m   |    length_mm    |   length_km    |
+----+--------------+-----------------+----------------+
|  1 | 20000.000000 | 20000000.000000 | 20.00000000000 |
|  2 | 20.000000    | 20000.000000    | 0.02000000000  |
|  3 | 0.001000     | 1.000000        | 0.00000100000  |
+----+--------------+-----------------+----------------+
Related Question