Is this good practice for using a view?

Question

Is this good practice for using a view?

raysteve43

Old Hand

Points: 333
More actions
December 3, 2013 at 9:29 am

#280753

We have a large lookup table with 200k records. This table is used frequently in a high volume database. We now have to update that table with new columns and data. We are concerned about how the update will affect production operations.
The idea has come to use a view that takes the place of the table. The view would make a decision based on a boolean value which actual table to point to. This would allow us to incorporate a second table with updated information. Then, switch the boolean value that tells the view to point to the new table.
Does that sound like a good idea? Why or why not?
Any help is much appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

raysteve43 (12/3/2013)
We have a large lookup table with 200k records. This table is used frequently in a high volume database. We now have to update that table with new columns and data. We are concerned about how the update will affect production operations.
The idea has come to use a view that takes the place of the table. The view would make a decision based on a boolean value which actual table to point to. This would allow us to incorporate a second table with updated information. Then, switch the boolean value that tells the view to point to the new table.
Does that sound like a good idea? Why or why not?
Any help is much appreciated.

That sounds like a very unfeasible idea. You can't have if statement or any other kind of control logic in a view.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

homebrew01 SSC Guru Points: 55539 More actions · Answer 2

How about some code before the view that uses SYNONYM for the variable tables, then select from the view using the synonym name.

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 3

Why not update your sproc with the logic needed? Is there a reason it can't go there? For that matter, why not in the app? Does it have to be on the DB side?

Mark

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 4

raysteve43 (12/3/2013)
We have a large lookup table with 200k records. This table is used frequently in a high volume database. We now have to update that table with new columns and data. We are concerned about how the update will affect production operations.
The idea has come to use a view that takes the place of the table. The view would make a decision based on a boolean value which actual table to point to. This would allow us to incorporate a second table with updated information. Then, switch the boolean value that tells the view to point to the new table.
Does that sound like a good idea? Why or why not?
Any help is much appreciated.

Is this just a one-time operation? Do you not have a maintenance window in which to do this? Adding columns will block all concurrent access to the table since it requires a Sch-M (schema modification) lock. Even so, adding columns and updating 200K rows should probably take a few seconds at most (unless there's some seriously convoluted logic and/or calculations required). Honestly, changing the schema of a production database while it's in operation seems a little risky.

If you have no other choice, here's one suggestion that suffers from a notable drawback. At one point in the process, there will be a very brief interval when an object that is referenced by queries will not exist and if those queries run during that time, they'll return errors. Also, I'm pretty sure this method will cause recompilation of all query plans that reference this lookup table, which could be a noticeable drag on performance until new plans are cached for all affected queries. You should test this in a non-production environment before trying it, of course.

Suggestion: Create a new version of the table with the additional columns (and a different name, of course) and new data. Execute the following:

EXECUTE sp_rename @object_name = 'oldTableName', @new_name = 'someOtherName'

GO

-- Here's the interval where oldTableName will not exist and queries looking for it will throw errors.

EXECUTE sp_rename @object_name = 'newTableName, @new_name = 'oldTableName'

GO

Good luck with this!

Jason

Jason Wolfkill

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

raysteve43 (12/3/2013)
We have a large lookup table with 200k records. This table is used frequently in a high volume database. We now have to update that table with new columns and data. We are concerned about how the update will affect production operations.

Stop guessing. Test it. Make a copy of the table and the indexes and do some performance testing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)