Stability of object ordering in generated scripts

[cpdaniel]

A frequent complaint about the SQL Server Database Publishing wizard is that the ordering of objects in the output script is not stable over changes to the database. For example, dropping and re-creating an identical table will cause the defintion of that table to move in the generated script.

This, in turn, causes "false changes" in source control that make it difficult to discern what actually changed between two versions of a database script.

So far, based on very limited testing (a single trial), SqlPackager does not appear to suffer from this fate. But here's the question: Was this coincidental in my limited test, or does Sql Packager order the objects in the generated script in a way that's guaranteed to be stable across chages to the database like dropping and re-creating a table (or anything that results in apparent changes in object_id of the scripted objects)?

[Brian Donahue]

Hi Carl,

I'm sorry to say that you can't really depend on the order of the scripts produced by SQL Packager. If you introduce a new object that changes the dependency chain, the order of objects appearing in the creation and/or synchronization scripts is likely to change, because SQL Packager creates or alters objects in dependency order rather than forcing a drop/recreate.

I hope this explains the process for you.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[cpdaniel]

If I introduce a new dependency then I'm fine with having the order change. It's when I don't introduce new dependencies that order changes are bothersome. For example, add or remove the identity property from a table. This requires dropping and re-creating the table, giving it a new object_id.

So I guess the question for SQL Packager is: Is the order of object scripting between objects with no dependencies stable based on SQL-DDL characteristics, or does it depend on internal properties like object_id?

By the way, this is by no means a show-stopper - it's just something annoying in SQL DB Publisher that I'd like to get away from. For my current use, I wrote my own post-processor that parses the script and re-writes it in a stable order (and also removes the useless timestamps that sqlubwiz inserts).

[Brian Donahue]

SQL Packager queries the information schema directly, and many of the queries include an order by clause on the object_id. I would not count on consistent ordering of objects in the synchronization script.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521