Red Gate forums :: View topic - Stability of object ordering in generated scripts
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Stability of object ordering in generated scripts

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
cpdaniel



Joined: 05 May 2009
Posts: 19

PostPosted: Tue Mar 02, 2010 8:40 pm    Post subject: Stability of object ordering in generated scripts Reply with quote

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)?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Thu Mar 04, 2010 2:16 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
cpdaniel



Joined: 05 May 2009
Posts: 19

PostPosted: Sun Mar 07, 2010 4:02 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Mon Mar 08, 2010 6:23 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group