Muting the Immutable

  • 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

  • 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.

    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)

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

    Best wishes,
    Phil Factor

  • 'Cornish' AS TheLanguage

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

  • 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!!!

  • 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

  • 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.

  • 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!!!

  • 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.

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

  • 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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

  • 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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

  • 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 21 total)

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