Post reply

Data-Tier Application - Prevent Column being added in the middle of tbl (dacpac)

  • October 8, 2019 at 12:26 am

    Go to Answer

    #3686849

    Question #1:

    Is there a setting that can prevent a column from being added in the middle of a table while developing in Visual Studio's Data-Tier Application (.dacpac) project tools?

    I understand that SSMS has a setting similar to the one I'm looking for which was discussed here:

    https://stackoverflow.com/questions/11802429/prevent-saving-changes-that-require-the-table-to-be-re-created-negative-effect

    Background:

    As a previous DBA turned Database Developer, I am learning more each day about the benefits of the scalable database approach using .dacpac deployment.  One of the dangers working with Database Projects in Visual Studio seems to be that a user can add a column in the middle of the table, and the project will move data around in the background using temp tables to perform this action.  While this may be a great and flexible option for small tables, when working with extremely large tables this can cause problems.  As a protective measure, I would like to force developers working on this database project to append columns at the end of the table, rather than be allowed to move columns around and have the project create temp tables and shuffle data around in the background to accommodate the request.  I'm curious if this is a setting that can be found at a Solution, Project, or Table level.

    Research performed so far:

    I've already reviewed the MS data-tier application docs here:

    https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-2017

    I've watched the Pluralsight video - "DevOps Skills for Developers with Visual Studio and TFS 2017" by Benjamin Day.  I've searched the SSC Forums, and done some "right clicking" and searched properties of the Solution and also of the Project (nothing obvious stood out).  And lastly I've done the usual Google-Fu looking beyond page 1 to see what if anything can be found.

    Thank you in advance to everyone in the community for your time and help!

  • October 9, 2019 at 1:10 am

    #3687222

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • October 9, 2019 at 2:23 pm

    Answer

    #3687409

    I don't think there is any setting for this. AFAIK, if you add column in the middle of the designer, this causes a table rebuild. If you add at the end, this doesn't.

    You can write a test to look for this and stop a build or raise a flag, but ultimately, you need to administratively control the developer. If they add a column anywhere but the end, it's a writeup. Second time, repeat. Third time, terminated.

  • October 9, 2019 at 4:32 pm

    #3687447

    I think you missed one of the important parts of the table in SQL and how it's different from a file. Please remember the phrase "logical is not the same as physical in RDBMS" and adjust your thinking away from punchcards.

    Columns have no order within the row the table by definition. Likewise, rows within a table have no order. We locate a row by using a key. We locate a column within a row by using a name. You'll find that most modern SQL engines take the varying length columns (VARCHAR(n), NVARCHAR(n), etc) and put them at the end of each physical storage record regardless of how they were declared in the DDL. This lets the SQL engine optimize the use of storage and not have to worry about having a variable-length field or padded field in the middle of a physical storage record.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • October 9, 2019 at 6:20 pm

    #3687483

    again you talk bollocks - OP is not worried about, neither does he/she cares, about how SQL decides to store the data - what matters, and it is a significant point on big tables, is that depending on where the column is added when doing a alter table it will either just perform a "alter table add column" or will go through the extents of creating a temporary table with the new column, copying all the data from the original table onto this temp table and then drop old one and rename the temp table.

    I'm sure that someone that considers himself so smart as yourself has enough brains to see what the issue is with this (even though you have shown time and time again that you do lack them).

    So

    alter table = 1 GB log space

    temp table + copy data = 400 GB log space + 400 GB data space + plus a significant amount of down time.

    So yes if forcing the developers to add the column to the end saves significant space and time then yes it is something important regardless of what the engine does with the data afterwards.

  • October 9, 2019 at 10:23 pm

    #3687590

    jcelko212 32090 wrote:

    Columns have no order within the row the table by definition. Likewise, rows within a table have no order.

    You know, if any of that were true, we wouldn't have to ever defrag indexes nor worry about which column was first in any of the indexes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 10, 2019 at 7:49 pm

    #3687849

    You're just being silly and trying to confuse implementation with language design.. What if I had an SQL implementation that used hashing instead of indexes? The order of the columns doesn't matter because a hashing algorithm will decide how to do it and even scarier to "index-minded people" is that a longer hash key usually works better, whereas an index should usually be built on the smallest set of columns.

    Remember, I worked on the SQL language. The consulting work I did with various engine implementations usually did not touch on performance. The rule was to get it right first and then get it fast.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • October 11, 2019 at 1:16 am

    #3687913

    Post removed... it's just not worth it anymore.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 11, 2019 at 9:16 pm

    #3688145

    Thank you @steve-2 Jones - SSC Editor.  We met a while back at a SQL Saturday in CA where I caught one of your  DevOps discussions (thank you).  I appreciate your thoughts, and was thinking the three strike path would be the likely reply for this.  As they say - "With great power..."   I am interested in learning more about creating tests to stop a build or raise a flag.  I'll work to educate myself on how that works next, and do my best to remember to report back to this thread if I make any discoveries worth mentioning.  Thanks again!

    • This reply was modified 5 years, 1 month ago by  mkimseytech.

  • October 11, 2019 at 9:18 pm

    #3688146

    Thank you @frederico_fonseca - I value and appreciate you adding context and your perspective, you were 100% correct *and on point with my OP.

    • This reply was modified 5 years, 1 month ago by  mkimseytech.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply