Muting the Immutable

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    There are plenty of ways of circumventing local restrictions of what you can, or can't, do in the database build process, but it would be much better if these arbitrary restrictions weren't there in the first place. The build script must provide whatever is necessary for the data. If we understand the data, its restrictions, ownership, security, constraints, mutability, and so on in all its richness and variety, and deal with this in the build script, then so many such problems just seem to evaporate.

    Agree. SSDT Database Projects are so close to realizing this point too and becoming the silver bullet for more people's Database Continuous Integration woes. The local XML model generated by the Database Project could (seemingly) be enhanced to support data in addition to schema and be part of the compare during the Build/Deploy.

    It just occurred to me that maybe that is too much to ask, i.e. parsing people's data scripts locally to insert them into the XML model. Maybe if the local model converted from an XML model to a full-fledged database hosted in LocalDB that could accept the data scripts and then be compared to the target for schema and data it would become a more attainable feat.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Jeff Moden

    SSC Guru

    Points: 994647

    From the Article:


    However, this doesn't suit some build systems, such as SSDT. It makes the mistake of believing that a build should contain only DDL.

    Heh... the faster we go, the behinder we get. 😀

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Phil Factor

    SSCoach

    Points: 19838

    Comments posted to this topic are about the item Muting the Immutable

    Best wishes,
    Phil Factor
    Simple Talk

  • david.dilworth

    Right there with Babe

    Points: 780

    'Cornish' AS TheLanguage

    Well there's something that you don't see every day 😉

  • Gary Varga

    SSC Guru

    Points: 82166

    It restricts the users to writing only DDL build scripts and spits out any DML it finds.

    I hate it when languages and systems do this type of restriction. It should be a warning. An advisory.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • J Livingston SQL

    SSC Guru

    Points: 51272

    david.dilworth (1/18/2016)


    'Cornish' AS TheLanguage

    Well there's something that you don't see every day 😉

    I think Phil meant "Welsh" not "Cornish"

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • qbrt

    SSCrazy

    Points: 2422

    I am in the process of transitioning our eCommerce database to the declarative model of SSDT to move the database development process into our CI pipeline. Using idempotent MERGE statements to manage static data that are executed during the post-deployment step. Works well so far. It would be nice if SSDT projects were enhanced to treat static data maintenance as part of the whole declarative process.

    This is the part I am not crazy about.

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'some_table_name')

    BEGIN

    MERGE <statement>

    END

    The SELECT statement in the EXISTS function uses strings to find the schema and the table objects. If the schema or the table name changes, the project will still compile but the MERGE statement will never execute. Hence the declarative nature of the process is ignored here.

    The SSDT tooling could be enhanced to recognize these statements. But having some type of editor or other tool that helps manage static data would be a great addition.

  • Gary Varga

    SSC Guru

    Points: 82166

    J Livingston SQL (1/18/2016)


    david.dilworth (1/18/2016)


    'Cornish' AS TheLanguage

    Well there's something that you don't see every day 😉

    I think Phil meant "Welsh" not "Cornish"

    Must have done as the Cornish equivalent of the Welsh dydd is dydh.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • dmbaker

    SSCertifiable

    Points: 5016

    So, does the ability to include "post deployment" tasks in your SSDT project not help to alleviate this somewhat? This lets you "seed" your database with reference data, at least that's what I use it for.

    It's not great as there's a big separation between your object definition and the "seed" data and it's a bit clunky, but it sort-of mostly works.

  • qbrt

    SSCrazy

    Points: 2422

    That's the point Phil is trying to make. Static data can be maintained by SSDT tooling, but as you said, a bit clunky.

  • Phil Parkin

    SSC Guru

    Points: 243681

    qbrt (1/18/2016)


    I am in the process of transitioning our eCommerce database to the declarative model of SSDT to move the database development process into our CI pipeline. Using idempotent MERGE statements to manage static data that are executed during the post-deployment step. Works well so far. It would be nice if SSDT projects were enhanced to treat static data maintenance as part of the whole declarative process.

    This is the part I am not crazy about.

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'some_table_name')

    BEGIN

    MERGE <statement>

    END

    The SELECT statement in the EXISTS function uses strings to find the schema and the table objects. If the schema or the table name changes, the project will still compile but the MERGE statement will never execute. Hence the declarative nature of the process is ignored here.

    The SSDT tooling could be enhanced to recognize these statements. But having some type of editor or other tool that helps manage static data would be a great addition.

    Can you explain a bit more why you are doing this? As this is a post-deployment script, can't you can rely on the fact that the target table will be present? And if it's not, for whatever reason, isn't seeing an error rather than silencing it (via non-execution) a better option?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • qbrt

    SSCrazy

    Points: 2422

    Phil Parkin (1/18/2016)


    qbrt (1/18/2016)


    I am in the process of transitioning our eCommerce database to the declarative model of SSDT to move the database development process into our CI pipeline. Using idempotent MERGE statements to manage static data that are executed during the post-deployment step. Works well so far. It would be nice if SSDT projects were enhanced to treat static data maintenance as part of the whole declarative process.

    This is the part I am not crazy about.

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'some_table_name')

    BEGIN

    MERGE <statement>

    END

    The SELECT statement in the EXISTS function uses strings to find the schema and the table objects. If the schema or the table name changes, the project will still compile but the MERGE statement will never execute. Hence the declarative nature of the process is ignored here.

    The SSDT tooling could be enhanced to recognize these statements. But having some type of editor or other tool that helps manage static data would be a great addition.

    Can you explain a bit more why you are doing this? As this is a post-deployment script, can't you can rely on the fact that the target table will be present? And if it's not, for whatever reason, isn't seeing an error rather than silencing it (via non-execution) a better option?

    Good question. And my first reaction was, well, that makes it idempotent. But, thinking a bit more, isn't the MERGE statement by itself already idempotent? Unlike a straight insert or delete statements. So, perhaps I need to rethink my approach.

  • Phil Parkin

    SSC Guru

    Points: 243681

    Merge can be idempotent. I would recommend doing the UPDATE part of the MERGE only if an update is required. I do this via the

    WHERE NOT EXISTS (SELECT source INTERSECT SELECT target)

    Technique, which handles differences and NULLS. Otherwise the UPDATE happens every time regardless.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • David.Poole

    SSC Guru

    Points: 75191

    I can understand doing this for a very limited number of datasets. I think it would create problems for Kimball data warehouse systems where we need slow changing dimensions. External standards data such as ISO and ABI datasets do change and you need control and auditability when someone changes it.

    I did do something like this with a union query to generate a huge range of numbers to act as a tally table but to be honest I've fallen back to creating a physical tally table instead. Even a few hundred thousand records in a tally table is no real burden, particularly if compression and column store indexes are options.

  • qbrt

    SSCrazy

    Points: 2422

    Phil Parkin (1/18/2016)


    Merge can be idempotent. I would recommend doing the UPDATE part of the MERGE only if an update is required. I do this via the

    WHERE NOT EXISTS (SELECT source INTERSECT SELECT target)

    Technique, which handles differences and NULLS. Otherwise the UPDATE happens every time regardless.

    In my case, I am using the post-deployment script feature of SSDT as part of the declarative maintenance of the database. That is, the MERGE statements are put in place to maintain the static data in their final form. So, the update statements must remain. These scripts are always part of the database project and there are no stacks of scripts to maintain that must be executed in a specific way depending on what version of the database is being upgraded. That's the thinking that led me to use the IF EXISTS() statement. Although, removing them (as you suggest) for MERGE statements makes sense to me. I suspect that is the type of tooling that Phil was also insinuating in his article. A tool that would tie the static data to the schema in a declarative way without having to use DML statements. I was thinking some type of editor that ties data to a table (data can be from different sources, such as JSON or XML files), this data gets compiled into the DACPAC (similar to a BACPAC) and the proper data sync process is determined during the deploy of the database. That way I don't have to worry about writing code and figure out how to sync data. The SSDT deploy tool will do that for me and maintain integrity of the data. Of course, this is for static data only that can be validated during compile/build time.

Viewing 15 posts - 1 through 15 (of 22 total)

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