Transaction groups remove columns from DB

Hi All,

I always seem to run into issues with my history transaction groups. Over time tags change and get added and removed. There is a 1024 column limit on tables in mssql. Right now I have a few transaction groups that are either 800 or 900 tags full but I cant add any more tags to them because the database table already has 1024 columns from table interactions over the past year.

It would be great if the transaction groups would remove the columns from the database when the tags are removed from the transaction groups.

right now I have been copying all of the column names into excel and then going line for line in the transaction group, removing them out of the DB as i go. The problem is that the transaction groups are 900 tags big, it takes forever.

Thanks!

This is a subject of convenience vs proper database design. Transaction Groups make it very convenient to create wide tables, but proper database design is to create long tables. You should consider redesigning your database. 900 columns in a table is way too much, you should consider breaking that up into multiple tables (convenient) or creating a long table (proper design).

Take a look at how the Ignition’s history tables are laid out, this will give you an idea on how to create a long table. Then you can use Ignition’s Block Transaction Group to populate your long table.

I’m pretty familiar with database normalization and the Normal Form rules, after searching Google and Stack Overflow I didn’t see anything regarding the number of columns as you suggest.

The only limitations or rules were around the database itself and how it handles the data inside the table along with the database limitation on the amount of columns itself.

I see no benefit of having 18 tables of 50 tags that all contain the same data.

The problem is not my columns, the problem is maintenance, If i have a transaction group of 10 tags, if i remove one from the group, it should remove it from the table in my opinion.

Few Links:
stackoverflow.com/questions/506 … ormal-form
stackoverflow.com/questions/147 … s-too-many

Thanks

Having Ignition delete a column from a table because a tag is no longer being used in a Transaction Group would not be a good idea. Just because a tag is no longer being logged doesn’t mean that the previously logged data is not longer needed.

You don’t necessarily have to create 18 tables with 50 tags, you could create a long table something like -

id  tagId  value  t_stamp

Granted it’s sometimes easier to whip up and work with a wide table, but I find there’s less maintenance with a long table.