Tread carefully when editing data structure.
From time to time you may need to change the name of a column in your Kentico application. For me this usually happens when I made a typo during the design phase and realize later that I need to fix it. Kentico makes this very easy to do, if you do not have lots of data already in the table that may relate to a custom table, on-line form, or page type.
Kentico is, when you get down to basics, a wrapper for SQL calls to the database. When you change the name of a column in the Kentico form engine, an ALTER query is issued to the SQL server. Behind the scenes SQL does a DROP/CREATE. This mean all the rows in the table are touched. If this table has millions of rows this will take quite a while and cause the operation to timeout.
One way to avoid this timeout would be to just use SQL Server Management Studio (SSMS) to make the change. Don’t do this. Kentico does a great job at making the complexities of database design really simple. This simplification is done with metadata contained elsewhere within the system (basically the CMS_Class and/or CMS_Resource tables). If the metadata is out of sync with the database schema the space time continuum will be disrupted, and we all cease to exist. Well maybe not..., but Kentico will break and you will be a sad panda.
Because adding and removing columns does not trigger a table rebuild this is our solution:
- Create a new column with the proper name within Kentico. Be sure to use the exact same data type as the column that had the typo.
- Using SSMS, issue an update query to copy the old data to the new one.
- Delete the column with the typo.
NOTE: Because the update will create an exclusive lock on all the rows, this table will not be useable until the query completes. Depending on the row count this could be a few seconds to several minutes. The recommended way to do this is by using TOPN. For more information see http://sqlmag.com/t-sql/update-rows-large-tables-without-locking-out-users