Is it relevant to put "version" on a separate sql server table?

Question

I have a table with several fields, this table almost never change but for one field, "version" which change very often.

Would it be relevant to put that single field into a separate table in order to reduce how often locks are put on the main table?

For instance I have a table tType and a table tEntry. Whenever I add/deleted/update any row of tEntry, I need to update the "version" field of tType. There might be thousand of rows inside tEntry for a single tType referenced row. Meaning the version number could change very often, though any other data of tType (such as name, id, etc.) doesn't change.

Is deadlocking actually an issue? I really don't think this is enough information to provide a helpful answer. How many records in your table? How often is it read? How often is it written to? What is your definition of very often, every minute? every hour? every day? every week? — GarethD, Sep 08 '14 at 10:14
But have you actually identified a performance issue related to this operation? — Nick.Mc, Sep 08 '14 at 10:50

score 1 · Answer 1 · edited May 23 '17 at 12:21

Your Referral to tType and tEntry sounds like you are implementing a key-value store in a rdbms. There are several discussions you can google about this topic. In the www there seems to be consesus, that cons overweight pros on that. An option would be to look at key value stores, no sql, multi column DBs, etc (wikipedia)...

The next "anti-pattern" I recognized is that you try to mix transactional data with 'master data' in the table tType. Try to avoid this, even if your selects get more uncomfortable and need to be tuned better. Keep off the version info from the tType, if this changes extremely often. Look here to get the concept: MySQL JOIN the most recent row only?

Is it relevant to put "version" on a separate sql server table?

1 Answers1