Bad design to compare to computed columns?

Question

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

That is a horrible design. Having column names that contain numbers is almost always a sign for bad table design. What are those fields for? That looks like you are putting attributes in a part. That would be a m to n relation and be needing another table. — juergen d, May 14 '20 at 14:31
No no, my field names are things like MakeId, ModelId, field_X is just for example here. The record is for an "application" (Part + Vehicle information) — Developer Webs, May 14 '20 at 14:36
This sounds like you want to do a CHECKSUM_BINARY or HASHBYTE: https://blog.greglow.com/2018/07/02/sql-finding-rows-that-have-changed-in-t-sql-checksum-binary_checksum-hashbytes/ — JMabee, May 15 '20 at 12:14
@JMabee That's not indexed though is it? In 24 million records I want to find maybe the 3 or 4 that match a given value (generated by C#). — Developer Webs, May 15 '20 at 12:39
You can index it yes. Here is a good example of doing just that: http://craftydba.com/?p=3005 — JMabee, May 15 '20 at 14:33

score 1 · Accepted Answer · answered May 14 '20 at 14:39

1

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!

answered May 14 '20 at 14:39

juergen d

201,996
37
293
362

I would agree, but I'm not allowed to change the design. They want to show the records in JqGrid (60-odd columns), and they don't want to pinwheel the results. So I could make a view, but it would jave to have 60 joins. Further, we have generic C# code that will retrieve results and allow for filtering, etc, but it has to read from a single table or view. Also, how would I compare two separate parts table records to make sure they are the same records (same number of attributes and same values for those attributes) – Developer Webs May 14 '20 at 14:42
1

Ouch. Good luck with that! – juergen d May 14 '20 at 14:43
Let's say I used that design. Is there an efficient/fast way using SQL to see if two parts have the same values for the same attributes given your schema? – Developer Webs May 14 '20 at 18:12

Bad design to compare to computed columns?

1 Answers1