SQL Server Performance

Why avoid Dynamic SQL?

Discussion in 'General DBA Questions' started by eramgarden, Jun 5, 2006.

  1. eramgarden New Member

    I just found this out at my new job:
    Clients can have more than one database with the same schema. For some reason, they wanted their data separate..for example..clients wanted to have data related to their A-Division in one database, data related to their B-Division in another DB and so on. So they could have up to 999 databases at most!

    Anyway, so what they did here was to create ONE "Stored Proc" database that they give to the clients. All stored procs are created in that ONE DB..then the stored procs are dynamic and point to any database. "Database" is a parameter that's passed to the SPs.

    I know Dynamic stored procs are not precompiled. So that's one reason not to use them. Is there other reasons like security as not to use them?
  2. smy New Member

  3. eramgarden New Member

    Thanks..I'm looking for an alternative:

    So this is what we have now:
    A "utility" database that only holds Stored Procs. No user tables. The stored procs will all be dynamic as they need to be pointing to different databases with the same schema tho. For example, databaseA and databaseB and then the "utility" database holding the SPs. We pass the DB name to the SPs and then the SP can point to the right DB.

    This makes all the SPs dynamic.

    I know I can create the SPs in all DBs but what other alternatives are there besides the 2 mentioned?
  4. derrickleggett New Member

    Have one database. The division is an entity in the database. You can then have a normal environment that an educated person would design. Can you slap the person who wrote this? Is it an accounting database by any chance?


    When life gives you a lemon, fire the DBA.
  5. cmdr_skywalker New Member

    Well, having 999 databases is a lot to maintain. And SQL Server supports more than 999 dbs. You may want to resolve the data sharing/privacy issues between divisions and present to them that the scenario is a dba nightmare and will cost more money to maintain because of huge learning gap for during turn-over, hard to determine the error, a change in the metadata will mean doing to x number of databases, etc.<br /><br />What is your primary project constraint? If the client wants a separate databases for each div, then you may have to use a utility database containing stored procs just as you planned. It would be unwise to create a proc for each database. Doing so is just adding fuel to a burning database/job <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  6. FrankKalis Moderator

  7. eramgarden New Member

    It's a collection agency application..

    "you may have to use a utility database containing stored procs just as you planned."...

    That would make all the stored procs dynamic as we have to pass the database name to the stored proc. They've written 3 stored procs and they're all dynamic because of that reason (passing DB name to the SP).

    If I put the SPs in every DB..then that's going to be hard to maintain but avoid the Dynamic sql issue. I did suggest having one DB but management said no...

    Serioulsy, I have no other options besides : have them on another Db and make them dynamic OR have the SPs repeated in all the databases? No other way to have them in ONE database (as they have now) but avoid passing the database name to the SP which causes it to be dynamic?
  8. Adriaan New Member

    You can add the stored procedures to the MODEL database. This way, all new databases created on this SQL Server instance will contain those stored procedures. Also add the stored procedures to all existing databases.

    Now the client app can connect to whichever database, and you can assume that those stored procedures are available and are using the correct data, without having to resort to dynamic SQL.

    Of course you still have the DBA nightmare of maintaining 999 databases, logins, database roles, ...

    But at least you don't have to worry about dynamic SQL ...


    A more sensible approach would be to use a single database, where the main tables have an indexed column for filtering. Grant logins permissions only on views, which query the tables with filtering based on the current login --- of course you'll need some auxilliary tables to control the filtering.
  9. spacemonkey New Member

    If I had the same situation with management saying no to a single database, here is what I might do. You want to have the same stored procedures in each database. There are up to 999 databses for this beast of a setup and you certainly do not want to manually update each instance when a change is made. To avoid the dynamic input of the target DB, could you have an update stored procedure to make the changes for you?

    In order to get around the nightmare of updating all the possible instances of those procedures, you could keep the utility database as a source DB. Then have another stored procedure or set of stored procedures to drop/recreate those utility stored procedures in each user DB. When you make a change, you would just have to make it in the utility database and run the update stored procedure(s) to "deliver" it to all of the necessary databases.

    I do not know of a way to get around the dynamic input of the db with the utility database housing all the procedures. There is no other way the SP would know which db it should look at. Your best solution at this point is whatever makes your life easier and saves you the most time possible.

  10. eramgarden New Member

    You can add the stored procedures to the MODEL database. This way, all new databases created on this SQL Server instance will contain those stored procedures. Also add the stored procedures to all existing databases.

    So If I add them in the MODEL DB..I still need to add them to each single DB as well...right?

  11. Adriaan New Member

    No, only to existing databases. Any database that will be created after this point on the same server instance (with the same copy of MODEL) will contain those stored procedures. You can add tables, views, UDFs, too.
  12. spacemonkey New Member

    That is correct, anything you add to the model DB will be present in a newly created database. Any existing databases will remain unchanged though. Assuming updates will be made to the procedures at some point, you will need a strategy for updating all the instances of those procedures in each database.

  13. eramgarden New Member

    ah, I'm understand it now[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Let me ask this:<br /><br />If I create them in MODEL DB, then any new instance of DB creation (using that MODEL) will have the new stored procs. Right?<br /><br />My manager said ..initially..we give the clients a "database shell" for them to install. That includes the "utility DB". Anytime they want to create a new DB, they use that "shell". Now, creating the SPs in MODEL db or in the actual database...in this scenario..doesnt make any difference..correct?...I mean..we can have the SPs included in the actual database shell or in the MODEL.<br /><br /><b>In either case...the dynamic sql will be avoided</b> BUT still as spacemonkey mentions...there needs to be an strategy to maintain the SPs in case we need to change them.<br /><br />You know what I mean?? Having the SPs in MODEL OR in the actual each DB is better than having them in a "utility DB and using dynamic sql". Either case..when they create NEW databases...the stored procs will exist in the new instance of the database.<br /><br /><b> now, is there a benefit of adding them in MODEL than adding them in the actuall database? </b>
  14. Adriaan New Member

    You seem to be missing a point - perhaps confusing this with OOP inheritance stuff?

    When you create a new database, all objects that exist in MODEL are included in any database that you will create on this server instance from this point forward, until you drop the object from MODEL.

    If you already have databases on the server, then adding objects to MODEL does not automagically add those objects to the existing databases. Same for changes to these objects: you need to have.

    You cannot execute procedures that exist in MODEL within the context of your database: they must exist in your actual database.

    So it isn't really a "benefit" of having them in MODEL - using MODEL is only a way so you can be certain that the stored procedure will exist in any future database on the server.

    You will be not be using the procedures in MODEL - you will be using the procedures in your actual database.
  15. FrankKalis Moderator

    To put it simple: Each time a new database is created, SQL Server simply copies the model db and gives it another name.

    The model db is nothing but a template for any new db. Any object that exists at the time of db creation in model, will instantly be present in the newly created db.

    Frank Kalis
    Microsoft SQL Server MVP
    Heute schon gebloggt?http://www.insidesql.de/blogs
  16. eramgarden New Member

    <br />Yes, I finally understood it [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Have a question about what Spacemonkey said:<br /><br /><i>When you make a change, you would just have to make it in the utility database and run the update stored procedure(s) to "deliver" it to all of the necessary databases.</i><br /><br />I like that idea and i think my manager would go with this since he likes to use the utility database.<br /><br />Now how would I "deliver" the stored procs from tehe utility DB to the other DBs? sql job? write a .net exe? any ideas?<br />
  17. FrankKalis Moderator

    What about scripting the SP and applying the script to the other db's? <br />You can, of course, buy a third party tool for this. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  18. eramgarden New Member

    Yes, i can script it out ...but this is for clients and dont want to ask them to open QA and do this and that..

    wanted to have it run via a tool..a job or something else.

    Dont think my manager would go for buying a thrid party tool.
  19. ranjitjain New Member

    you can create your own VB GUI application which will take the server parameters and read the SQL script file from application installed path and will install the Stored procedures and functions and tables etc.
  20. FrankKalis Moderator

  21. eramgarden New Member

    I like the idea of OSQL and the VB GUI. I think my manager would go with either of them.

    Any examples, especially with the VB GUI...do I need to use the DTS object ( i remember few yrs ago, ex-coworker used DTS object in his VB6)..

    some ideas/steps would be great before i talk to my manager.

Share This Page