Stability of object ordering in generated scripts

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Stability of object ordering in generated scripts

Postby cpdaniel » Tue Mar 02, 2010 8:40 pm

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)?
cpdaniel
 
Posts: 19
Joined: Tue May 05, 2009 9:40 pm

Postby Brian Donahue » Thu Mar 04, 2010 2:16 pm

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
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby cpdaniel » Sun Mar 07, 2010 4:02 pm

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).
cpdaniel
 
Posts: 19
Joined: Tue May 05, 2009 9:40 pm

Postby Brian Donahue » Mon Mar 08, 2010 6:23 pm

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
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 1 guest

cron