SSDT and Dynamically generated SQL Server tables

  • Vince Poirier

    Right there with Babe

    Points: 772

    Hello,

    I'm using VS-2012 and SQL Server 2012.

    I have a database for which I dynamically generate tables to materialize complex views (performance issues).

    The result is a set of tables that will be maintained through a Stored Proc that adjusts the tables' structures based on the associated views structures (DROP/CREATE).

    There is also a layer of synonyms that will either point on the views or on the tables.

    The synonyms are consumed by an application.

    Should I put the dynamic tables and synonyms in my TFS SSDT solution ?

    Thanks

  • diklevich

    SSC Enthusiast

    Points: 126

    I am also interested in the topic.
    Is there any nice way to do it in SSDT?

  • Jason Selburg

    SSC-Insane

    Points: 24637

    If the objects' structure is dynamic via your application code then SSDT will be out of synch anytime you modify or create them. The only way is to perform a schema compare. Think of the database project as a completely different instance of your database, because it is. They are not connected and one is not aware of any changes in the other unless you trigger that synch/compare.

    I would assume/hope that your synonyms are statically named for the application if so, you can bring those into the project, however you'll likely need to set them to ignore Err 71502 - unresolved reference. But that will get tricky if those synonyms are also referenced elsewhere in your project.

     

    In short, it's not good practice from an SSDT point of view to have dynamic objects.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

Viewing 3 posts - 1 through 3 (of 3 total)

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