December 9, 2008 at 9:39 am
It is a clever solution to the design bug. Probably like many other readers, I'm trying to think of the underlying problem.
My 3rd thought was to see if all the DMO interfaces to the tables are through stored procedures. Then in a pinch you could always do the "If the customer is contained within the new group customer table, SELECT the data from there, otherwise get it from the appropriate individual table."
That way, you could theoretically migrate the tables a few customers at a time. Do a pilot run with a few customers to vet out any potential bugs, and then plan the migration. Since its dynamic SQL, you can slowly implement it to avoid the BIG BANG, if you want.
Ok, punch holes in this one.
December 9, 2008 at 9:45 am
Clever solution to a problem due to a bad design. Thanks for sharing.
December 9, 2008 at 11:08 am
henrik staun poulsen (12/9/2008)
Hi,Yes, that is also a solution, as pointed out on Erlands homepage (see above).
But to my management that was a more scary solution, so it was postponed.
The table-value UDF solution performs nicely, and does not require dynamic SQL, and is non-invasive. So it was acceptable to management.
Best regards,
Henrik
Just a thought...
This might be a great opportunity to convince management to allow some level of testing of a rewrite using the better ideas/suggestions provided not only here but everywhere your story has appeared. While the current solution works you never know when Microsoft may make a change that results in your solution no longer working. And while you always run the risk that anything can change, forcing one to make changes to their system, the chances of being in that kind of predicament is far greater for those with creative work-a-rounds in place then those systems more closely follow what is generally accepted as 'Best Practice'.
No one will meet the "Best Practices" outline %100 because there are too many variables in the Real World to always go by the book but the closer you are to meeting that ideal the less risk you have of being forced to rework your work-a-round down the road.
I mention this only because a former employer of mine found them in this exact situation. Something they had creatively put together was broken a few years later when a major software vendor (not Microsoft) made a significant change to the Requirements for using their software. The result was the company had to spend far more resources redoing the code again then they would have if they had done it right the first time around instead of looking for the work-a-round that they ended up using.
Just some food for thought. I know that like the rest of us you aren't sitting around each day waiting for something to do and so the idea of another major project is not something you'd be jumping to get.
Good luck.
Kindest Regards,
Just say No to Facebook!December 9, 2008 at 1:30 pm
Points for finding a solution to your design problem.
I hope to never have a client with such a disaster.
The more you are prepared, the less you need it.
December 9, 2008 at 3:03 pm
I think that it would be easier to add triggers to populate any changes to a new table. then the view just read from that one new table. no client codes changes.
December 10, 2008 at 12:58 am
Dear all,
First of all, thank you for the nice words. Writing up the article was a lot more work than I anticipated, but also a lot easier than I feared. My english teacher would have been surprised, but proud. Steve was really helpfull here.
Phil; yes, we tried nested views. But as __Celko__ found out (after a "few" mails), views are no more than "macros", that are resolved into base tables at compile time.
Yes, our solution do read the catalogue to get the list of tables needed. But it is some of our own tables that we read, so it did not make sense to show that.
andreq1, michaelwang; yes, that is one of the solutions that Erland Sommarskog lists, on one of his pages; http://www.sommarskog.se/dynamic_sql.html#Sales_yymm. If you have not read, printed and rehearsed his entire homepage, I think you should. Here it is mandatory reading.
Andrew; to have such a client is not a disaster, it is work. Albeit lots of it. š This client is friendly and is focused on obtaining the best solutions, so Iām sure we will end up with something near perfect at some point. But not without loosing the main focus; providing value for the (end) customer.
Best regards,
Henrik
January 5, 2010 at 12:18 am
Dear all,
According to j.summers in http://www.sqlservercentral.com/Forums/Topic618362-32-1.aspx#bm841385
Books OnLine for 2008:
Tables per SELECT statement : Limited only by available resources
More details on "Maximum Capacity Specifications for SQL Server" can be found here:
http://msdn.microsoft.com/en-us/library/ms143432.aspx
So now we could skip the UDF solution, and use nested views instead.
Nice to know we have more options now.
Best regards,
Henrik
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply