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: 101098 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bruin SSCrazy Points: 2058 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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin