codefluent

Codefluent tablediff.sql bloat

I am using CodeFluent for some time now and I am really, really happy with it. However, the tablediff that is generated seems to contain unnecessary changes every time. Does anyone know if these changes can be turned off?

  1. All [nvarchar] (max) columns are set to [nvarchar] (max) every time, even if they are already [nvarchar] (max).
  2. All defaults seem to be set every time, even if they are already there. In our diff there are almost 100 _trackLastWriteTime and _trackCreationTime.
  3. Constraints are dropped and added

Why is this a problem?

  1. Since the tablediff is so large it is hard to see and verify the 'real' changes
  2. Executing the tablediff takes much longer, especially on a large database
  3. The dropping of constraints reverses changes we made to primary keys and indexes, sometimes even resulting in creating only a heap.
Solution

  • The goal of the generated diffs script is to get the right database schema after building the model during the development phase. It can contain useless statements as long as the final database schema is ok.

    To upgrade the database in a production environment you should use the SQL Server Pivot Script producer. The Pivot Script producer generates an XML file (or a zip file) that contains the list of tables, stored procedures, functions, etc. of the database. The Pivot Runner read this file, and create or upgrade a database to the state describe in the XML file. As it generates and executes SQL statements directly (without generating a script file), the PivotRunner does less operations than the diffs script.

    If needed, you can have full control on what the pivot runner does: https://stackoverflow.com/a/36426389/2996339

    This schema shows the development and deployment workflow: