Quick Tip: Using large SQL databases with Kentico

By Tim Stauffer on February 04, 2016

Quick Tip: Using large SQL databases with Kentico

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

 

Share This Post:

Twitter Pinterest Facebook Google+
Click here to read more Tutorials posts
Start a Project with Us

About the author

Completely self-taught and a Jack of all trades, Tim’s the man when it comes to making things happen with websites and software. Given enough time, he can figure anything out. It makes him feel all warm and fuzzy inside when he makes something and others use it to make their lives better. We like his big heart. Tim enjoys “experimenting with food,” and is just a bit addicted to World War II movies.

View other posts by Tim

Subscribe to Email

Enter your email address to subscribe to the BizStream Newsletter and receive updates by email.