On Tuesday, 12 March 2013 21:36:36 UTC, Erland Sommarskog  wrote:   
   > (rja.carnegie@gmail.com) writes:   
   > > I have data to work on spread through many tables in many databases,   
   > > with names such as [BO-Stud18].[dbo].[Calendar_2012], where all   
   > > "Calendar" tables have the same structure, or more or less so.   
   > > Sometimes these tables are dropped and re-created.  This isn't an ideal   
   > > design, but I'm kind of stuck with it.   
   >   
   > I would probably start there, as home-built partitioning tends to be   
   > painful. Only if you have really good reasons, like the need to   
   > quickly age out all data, you should do things like this. And you   
   > should use partitioned views in this case.   
      
   Well, I know it's wrong, but, as I said, stuck with it.   
   This is for EIS and is a ludicrous way to set things up,   
   but I don't have authority to change it.  Up to now I   
   haven't even got the server (2005, Standard) brought   
   up to the latest service pack - but we had a particularly   
   bad Friday, so, if I get the chance, what version   
   should I ask for?   
      
   > My strong preference is to keep all database code under version   
   > control. The [way] we organise tables, is that the table   
   > definition itself is in one file [with its] PK constraints   
   > and CHECK constraints.  However, foreign keys are in a   
   > separate file.  The indexes are also in a separate file.   
   >   
   > I don't like putting index definitions in stored procedures   
   > like you are planning, because all the T-SQL trees will hide   
   > the index forest for you.   
      
   What I've got now is worse, though; any program that runs   
   slow is liable to have been changed to create table indexes   
   on the spot.  Having /one/ procedure that creates all   
   table indexes - being called separately for each table -   
   will keep things more coherent, and maybe even create a   
   presumption that a table will /have/ indexes, which   
   doesn't cross some of our developers' minds when they (!)   
   take it into their head to create one.  On the other   
   hand, my boss likes under pressure to use the tuning tool   
   on a query and then create all of the indexes that it   
   proposes, at once, which I /think/ is wrong?   
      
   Apparently the best rule is not to be us.   
      
   By the way, are there any tips for Statistics?   
   I hope I've got this straight: they reflect the   
   distribution of data values in the table, but are not   
   necessarily current.  I suspect that some or all of   
   the statistics that spontaneously appear on tables   
   ought to be considered for creating an index instead   
   (containing statistics?), although I also suppose that   
   then it would happen automatically.  And we have found   
   that some ugly queries perform far better after executing   
   "UPDATE STATISTICS ... WITH FULLSCAN" on every table   
   in one or more databases, but, since /my/ duties   
   tend to involve table that get emptied or dropped and   
   recreated every night, for the EIS, we'd need to do   
   that ideally just after loading all the data into a   
   table and - maybe? - before creating indexes on it.   
   Would it improve the indexes too, or just create   
   a useless additional workload?   
      
   I assume that "FULLSCAN" makes statistics better.   
   And larger.   
      
   I'm considering creating a design, similar to one that   
   I've got that just hits all tables, that could allow   
   tables to be pre-chosen to get this done every night or   
   every week or once or never, and do it either by   
   calling it on one table during murky overnight   
   processing, or on any nominated tables that it hasn't   
   been done for by the end - as logged in another table -   
   and promote tables to having it done earlier if the   
   overnight processing itself seems likely to benefit   
   from that.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)