dynamic update to SP

Question

dynamic update to SP

Viewing 3 posts - 16 through 17 (of 17 total)

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

ScottPletcher SSC Guru Points: 100942 More actions · Answer 1

Dennis Jensen wrote:

ScottPletcher that is not entirely true and that is not what I was talking to. I was looking at the bigger picture or the why are they creating these monthly tables with different names and what might they be doing with them short and long term. The ability of a Partition Table to effectively create actual physically different tables while making them appear as one single table has numerous benefits based upon the situation that it may be addressing. To me the constant creation of the same table using different table names to me sounds like ripe pickings for applying the Partitioning solution to. However, not knowing all the details I made the suggestion that it ought to be investigate as a potential smoother long term solution.
But yes there are many different ways to solve the same problem (many wasy to skin a cat) but if you are not aware of them because no one suggests them and you have not subtled across it yet well then that reduces the number of potential solutions and the one that gets eliminated might actually be the best solution. Speak to the problem and the potential problem as the solution may not be the band-aid you just slapped on the problem.

I believe what you are referring to is not a partitioned table but instead a partitioned view. IF the WHERE conditions are as I stated above, the performance of a single clustered table should still be overall better than for a partitioned view.

You might want to partition the clustered table itself if you have drastically different index rebuild requirements (assuming that you believe you need to do index rebuilds) and/or significantly different fillfactors.

I don't presume to know what their core problem(s) could be, I answered the q that was asked. We don't always automatically know better than the person asking the q what they need to do.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Bruin SSCrazy Points: 2005 More actions · Answer 2

Thanks for all replies!!

Will test out Emperor100 solution. Just need a way to update the sp's that use this table structure when new month starts.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

Bruin wrote:

Thanks for all replies!!
Will test out Emperor100 solution. Just need a way to update the sp's that use this table structure when new month starts.

The synonym suggestion avoids the use of dynamic SQL, and for that reason alone is the better solution, in my opinion.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.