none
Difference between create table and alter table – upgrade process.

    Question

  • Hi

    Let use this example to present my issue.

    I develop some application that from time to time is released to the customers. This app has database and in this db I have table Users with two columns: Id and Name.

    There might be situation when I will have to change somehow this table e.g. add new column SecondName. In such case the app in old customers will have to be upgraded to new version without losing any data. So I will have to deliver sql script that adds this column to the Users table.

    I might have also some new customers that did not have my app before so for these customers sql script that creates whole table with all 3 columns could be used.

    I would like to avoid maintain two “streams” for sql scripts one for upgrade and one for clean installation and have only upgrade scripts. In such case for new customers I would create table with two columns (Id and Name) and next run sql script with alter table that adds the new column “SecondName”.

    The question is if there is any difference e.g. in performance when I create table with only “CREATE TABLE” statement and when I create table  using CREATE TABLE only for Id and Name and ALTER TABLE that adds additional column. Maybe the table in scenario with CREATE and ALTER is slower even if ALTER is executed when the table is empty because it will be more defragmented?

    Regards


    kicaj

    Monday, February 18, 2013 11:43 AM

Answers

  • Hi Kicaj,

    For the empty tables there is no difference between creating table with 3 columns in compare with create/alter case you specified. Well, there is the difference - of course, it will take longer to run 2 statements but you would not notice it.

    As for the cases when table has some data and you choose between ALTER and CREATE/INSERT/RENAME pattern, the answer is, as usual, "it depends". There are different kind of alterations. Some of them are pure metadata changes  - for example adding new nullable variable width columns - and for those ones alter table would be much more efficient. Others could trigger scan of the data in addition to metadata changes (think about altering column to be not null). For them alteration would also be faster. Last group will require actual data changes - for example adding new fixed-width column. For those ones CREATE table could work better because it would reduce the fragmentation.

    A couple things you need to consider. First - table alteration never actually decreases the row size. Check this blog article: http://aboutsqlserver.com/2010/09/01/hidden-facts-about-table-alteration/

    And finally, security is the big question. When you recreate the object, you basically lose every permission assigned to the old object. This could be an issue with some installations if customer' DBA did some custom work. For those cases ALTER and CI Rebuild could work better


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Sunday, February 24, 2013 9:06 PM
  • Hello,

    First, I wouldn't be worried about performance if it is a small database and it's only used as part of the upgrade. If we were talking tables that were very large and couldn't be taken down as part of the upgrade it'd be another story. I would spend more time working on the data structures or data access layers instead of things like adding 'SecondName' than performance tuning your upgrade.

    If you don't want to create two different code paths for the isntaller, when you create the script use IF EXISTS for the objects. If the object exists, you can alter the table, if it doesn't exit you can create it. Pretty simple and efficient.

    Since you asked about performance, in terms of creating or altering a table. Depending on the DDL it might be a size of data operation in which every page will immediaetly need to be touched and all of the log for the transaction recorded. Creating a table is much more trivial as it's strickly a metadata operation until the first parts of data are put into it.

    -Sean


    Sean Gallardy | Blog | Twitter

    Sunday, February 24, 2013 9:31 PM
  • Sounds like you need to check out Microsoft SQL Server Data Tools.  It's a free, Visual Studio-based IDE for developing, testing and deploying your database design.  You don't need to maintain change scripts _at all_.  You use version control and just manage your schema.  You can take a version of your schema and deploy it against a database (SSDT will figure out the diff), or you can diff a version against a deployment target to generate a change script.

    See, eg:Database Schema Management & Deployment using SQL Server Data Tools (SSDT)


    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, February 24, 2013 10:20 PM

All replies

  • You can restore a template database to the new customers  so why use SQL Scripting? For the old customers you do need to have a script to add/edit the tables.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, February 18, 2013 12:07 PM
  • You did not answer on my question. The question is “The question is if there is any difference e.g. in performance when I create table…”.


    kicaj


    • Edited by kicaj Sunday, February 24, 2013 8:26 PM
    Sunday, February 24, 2013 8:26 PM
  • Hi Kicaj,

    For the empty tables there is no difference between creating table with 3 columns in compare with create/alter case you specified. Well, there is the difference - of course, it will take longer to run 2 statements but you would not notice it.

    As for the cases when table has some data and you choose between ALTER and CREATE/INSERT/RENAME pattern, the answer is, as usual, "it depends". There are different kind of alterations. Some of them are pure metadata changes  - for example adding new nullable variable width columns - and for those ones alter table would be much more efficient. Others could trigger scan of the data in addition to metadata changes (think about altering column to be not null). For them alteration would also be faster. Last group will require actual data changes - for example adding new fixed-width column. For those ones CREATE table could work better because it would reduce the fragmentation.

    A couple things you need to consider. First - table alteration never actually decreases the row size. Check this blog article: http://aboutsqlserver.com/2010/09/01/hidden-facts-about-table-alteration/

    And finally, security is the big question. When you recreate the object, you basically lose every permission assigned to the old object. This could be an issue with some installations if customer' DBA did some custom work. For those cases ALTER and CI Rebuild could work better


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Sunday, February 24, 2013 9:06 PM
  • Hello,

    First, I wouldn't be worried about performance if it is a small database and it's only used as part of the upgrade. If we were talking tables that were very large and couldn't be taken down as part of the upgrade it'd be another story. I would spend more time working on the data structures or data access layers instead of things like adding 'SecondName' than performance tuning your upgrade.

    If you don't want to create two different code paths for the isntaller, when you create the script use IF EXISTS for the objects. If the object exists, you can alter the table, if it doesn't exit you can create it. Pretty simple and efficient.

    Since you asked about performance, in terms of creating or altering a table. Depending on the DDL it might be a size of data operation in which every page will immediaetly need to be touched and all of the log for the transaction recorded. Creating a table is much more trivial as it's strickly a metadata operation until the first parts of data are put into it.

    -Sean


    Sean Gallardy | Blog | Twitter

    Sunday, February 24, 2013 9:31 PM
  • Sounds like you need to check out Microsoft SQL Server Data Tools.  It's a free, Visual Studio-based IDE for developing, testing and deploying your database design.  You don't need to maintain change scripts _at all_.  You use version control and just manage your schema.  You can take a version of your schema and deploy it against a database (SSDT will figure out the diff), or you can diff a version against a deployment target to generate a change script.

    See, eg:Database Schema Management & Deployment using SQL Server Data Tools (SSDT)


    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, February 24, 2013 10:20 PM