Muting the Immutable

Question

Muting the Immutable

Phil Factor

SSC-Insane

Points: 20244
More actions
January 18, 2016 at 12:00 am

#314944

Comments posted to this topic are about the item Muting the Immutable
Best wishes,
Phil Factor

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

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

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: 1004402 More actions · Answer 2

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)

david.dilworth Right there with Babe Points: 780 More actions · Answer 3

'Cornish' AS TheLanguage

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

Gary Varga SSC Guru Points: 82166 More actions · Answer 4

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: 51550 More actions · Answer 5

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: 2737 More actions · Answer 6

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 More actions · Answer 7

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: 5139 More actions · Answer 8

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: 2737 More actions · Answer 9

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: 246977 More actions · Answer 10

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

qbrt SSCrazy Points: 2737 More actions · Answer 11

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: 246977 More actions · Answer 12

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

David.Poole SSC Guru Points: 76034 More actions · Answer 13

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.

LinkedIn Profile

qbrt SSCrazy Points: 2737 More actions · Answer 14

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.