Skip to content

SQL Source Control

Welcome to the SQL Source Control feature suggestion list. Find out more information about SQL Source Control at http://www.red-gate.com/products/sql-development/sql-source-control/.

If you have any questions, need help or have found a bug in SQL Source Control, please review our support information http://redgatesupport.red-gate.com/home.

To get new features, performance improvements and bug fixes as soon as they’re available, you may want to turn on frequent updates: http://www.red-gate.com/products/sql-development/sql-source-control/frequent-updates

SQL Source Control

Categories

JUMP TO ANOTHER FORUM

  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback

4 results found

  1. I have a single schema for all customers but different reference data for different customers. It would be nice if I could manage this all within SQL Source Control. This should also be understood by SQL Compare and my continuous integration system when it comes time to deploy this to my different environments and customers' sites.

    254 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)
    6 comments  ·  Static Data  ·  Flag idea as inappropriate…  ·  Admin →
    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    under review  ·  Kendra responded

    Hi all. Thank you for your votes and feedback on this issue over the years. Here is our current guidance for this suggestion:

    Post-deployment scripts give you flexibility for static data

    With SQL Source Control, you can now use a post-deployment script to “dynamically” deploy static data based on a factor such as @@SERVERNAME or other query-able conditions.

    SQL Source Control introduced pre- and post- scripts in v6.3.

    An example post-deployment script which shows how to control deployment of static data by environment is here: https://documentation.red-gate.com/soc7/common-tasks/working-with-pre-post-deployment-scripts/static-data

    If you make heavy use of Static Data, we have stronger support for this in SQL Change Automation.

    SQL Change Automation:

    • Supports column filtered static data tables in the SCA plugin in SSMS
      Supports multiple post-deployment scripts, in case there is a preference to manage static data tables in dedicated post-deployment scripts
    • Allows approaches like bulk loading larger static data tables by supporting SQLCMD
  2. Do not use transactions but use bulk insert in order to reduce the get latest time. A 'flush and refill' approach using bulk insert should be faster in hours with 10K plus records.

    A two processor, dual core computer with 8GB of RAM and a Windows experience index of 6.5 takes over 8 hours to get lates on 50K records.

    10 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)
    0 comments  ·  Static Data  ·  Flag idea as inappropriate…  ·  Admin →
    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    under review  ·  Kendra responded

    Thanks for this feedback and for the votes for this suggestion.

    For users who need to manage larger volumes of static data, we currently recommend versioning the database with SQL Change Automation. SQL Change Automation has stronger support for static data:

    • Supports column filtered static data tables in the SCA plugin in SSMS
    • Supports multiple post-deployment scripts, in case there is a preference to manage static data tables in dedicated post-deployment scripts
    • Allows approaches like bulk loading larger static data tables by supporting SQLCMD variables in migration and post-deployment scripts

    I do understand that you are looking for this feature in SQL Source Control, but wanted to surface this option for other readers who may be interested in either tool.

  3. I would like the ability to source control a subset of a table (perhaps using a view).

    I have inherited a database where lookup data is intermixed with user defined data. We reference the lookup data in the table using a code (VARCHAR) that is unique when another field is NULL, so it is possible to ensure that records are created/updated/deleted without concern for the primary key involved.

    For more information, you can refer to this forum post:
    http://www.red-gate.com/messageboard/viewtopic.php?p=50087

    636 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)
    30 comments  ·  Static Data  ·  Flag idea as inappropriate…  ·  Admin →
    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    under review  ·  Kendra responded

    Hi everyone. I have merged some User Voice items on this topic of “filtered” static data, as there was significant overlap. I want to share our current guidance on handling scenarios where you need to version a subset of the columns and/or rows in the table.

    With SQL Source control, the best option at this point is to use a post-deployment script for this purpose.

    SQL Source Control introduced pre- and post- scripts in v6.3.

    A post-deployment script gives you a good amount of flexibility over exactly which rows or columns of data you want to include in your project. Example post-deployment scripts for static data are here: https://documentation.red-gate.com/soc7/common-tasks/working-with-pre-post-deployment-scripts/static-data

    If you make heavy use of Static Data, we have stronger support for this in SQL Change Automation.

    SQL Change Automation:

    • Supports column filtered static data tables in the SCA plugin in SSMS
    • Supports multiple post-deployment scripts, in case there is…
  4. Currently, SQL Data files contain a bunch of insert statements, but the order of the insert statements can be inconsistent. For example, new records might get inserted on the top or the bottom. Also, multiple SET IDENTITYINSERT statements sometimes get created. This makes it difficuly to merge data changes between multiple branches in TFS Source Control. If the Insert statements had a consistent order and only included one SET IDENTITYINSERT diffs and merges would be easier.

    530 votes
    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    You have left! (?) (thinking…)
    37 comments  ·  Static Data  ·  Flag idea as inappropriate…  ·  Admin →
    How important is this to you?

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)
    under review  ·  Kendra responded

    Thanks for this suggestion and for the many comments and upvotes. I realize that this is a pain point.

    I have a few shorter-term workarounds to summarize as well as some information on the longer roadmap in this update. I know these shorter-term workarounds aren’t perfect (I summarize the pros and cons), but I’m posting them as they may help a few folks.

    Workaround 1) When data changes to static data need to be made, use a “relink the table” pattern
    One can “cleanly rescript” a static data table in SQL Source Control by:

    • Unlinking the static data table
    • Committing
    • Relinking the static data table
    • Committing

    Pro: This works with the GUI and requires no special knowledge or comfort with TSQL. This may help folks with just a few static data tables.
    Con: This requires extra steps and results in extra commits in the history, which I realize can…

  • Don't see your idea?