Search code examples
sql-serverpersisted-column

Bad design to compare to computed columns?


Using SQL Server I have a table with a computed column. That column concatenates 60 columns:

CREATE TABLE foo 
(
    Id INT NOT NULL,
    PartNumber NVARCHAR(100),

    field_1 INT NULL,
    field_2 INT NULL,
    -- and so forth
    field_60 INT NULL,

    -- and so forth up to field_60
)

ALTER TABLE foo 
    ADD RecordKey AS CONCAT (field_1, '-', field_2, '-', -- and so on up to 60
                            ) PERSISTED

CREATE INDEX ix_foo_RecordKey ON dbo.foo (RecordKey);

Why I used a persisted column:

  • Not having the need to index 60 columns
  • To test to see if a current record exists by checking just one column

This table will contain no fewer than 20 million records. Adds/Inserts/updates happen a lot, and some binaries do tens of thousands of inserts/updates/deletes per run and we want these to be quick and live.

Currently we have C# code that manages records in table foo. It has a function which concatenates the same fields, in the same order, as the computed column. If a record with that same concatenated key already exists we might not insert, or we might insert but call other functions that we may not normally.

Is this a bad design? The big danger I see is if the code for any reason doesn't match the concatenation order of the computed column (if one is edited but not the other).

Rules/Requirements

  • We want to show records in JQGrid. We already have C# that can do so if the records come from a single table or view
  • We need the ability to check two records to verify if they both have the same values for all of the 60 columns

Solution

  • A better table design would be

    parts table
    -----------
    id
    partnumber
    other_common_attributes_for_all_parts
    
    
    attributes table
    ----------------
    id
    attribute_name
    attribute_unit (if needed)
    
    
    part_attributes table
    ---------------------
    part_id (foreign key to parts)
    attribute_id (foreign key to attributes)
    attribute value
    

    It looks complicated but due to proper indexing this is super fast even if part_attributes contain billions of records!