Renaming a table and replace with a view

Question

I have a need to incorporate data from two systems, one legacy and another pilot replacement.  There are many reports and application that interact with the data in the database.  There is a need to ‘filter’ the data so that most of the apps and reports only see data from the legacy system.  One idea is to rename two key tables and replace them with views of the same name with the same columns but with a where clause to filter out the pilot data.  All the apps (except two) that operate on data will use the views.  Selected apps (currently 2) will use the renamed tabled directly.  The idea is that all apps and reports will use the views by default because they will have the same name that the (renamed) tables used to have.   The question is can anyone see any pitfalls with this scenario such as foreign keys, indexes etc…?

---

john poirier

Answer 1

Hi John,

Based on your description here, i get the impression that your are not using stored procs. You might be better off by implementing a solution that replaces in line TSQL in the apps with stored procedures. This way, the interface of the apps with the database remains constant while the underlying structures ( tables, views, etc ) can be changed without affecting the apps. The stored procedures can have an effecting filter in the query and with appropriate indexing, you will get good performance. There are a ton of advantage of taking the stored proc route - http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/ 

Hope this helps !

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

Answer 2

Incorrect assumption.  There are hundreds of procs and views that I do not want to rewrite.

---

john poirier

Answer 3

Thanks for the clarification John.

In that case, i agree that a creating a view with the same name, after renaming the original table might work better for you. 

If you rename the table by using sp_rename , the PK and its associated index as well as the UK and its associated index will get re-pointed automatically. The constraints like DEFAULT & Check constraints will work as well. I am a little concerned about the FK referential integrity constraints and other indexes. They may not automatically re-point to the renamed table and will have to be handled manually.  It would be a good idea to test this out extensively first.

http://msdn.microsoft.com/en-us/library/ms188351.aspx 

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

Answer 4

What is UK?

---

john poirier

Answer 5

It is unique constraint.  Definitely needed if you are using surrogate keys (identity keys, typically, or guids) for your primary key.  I am pretty sure that using sp_rename should be fine, since constraints don't work textually, but rather work on id values (do some queries on the catalog tables like sys.foreign_keys and related ones and you will see how they work.)

-----------------------------------

I would agree that, if you are just changing the name of the objects (and not the structure) then views are a very viable solution, as the underlying objects will work just as they did.

The only caveat to the view plan could be some performance, wherein you are adding another layer of complexity to your code. By encapsulating into views, you could tip the scales of complexity on an object and end up with a less good plan.  If you do a reasonable job of performance testing, you should find the cases where performance might be affected, but it should be fine, I would expect. Like Sanil said test, test, test, test!

An alternative might be (particularly since you are using stored procedures) to use something like Red-Gate's Smart Rename feature from their SQL Prompt product http://www.red-gate.com/products/sql-development/sql-prompt/.  It will fix references in the objects as you rename them... Would be tedious still, but it could help you end up a lot closer to where you want to be, with the procedure interfaces changed... This has a caveat too though, in that the output columns in procedures would probably change, since it would expect that you want to rename column "fred" to "frederick", so unless you have it aliased as fred as fred (which would change to frederick as fred), that might not be possible.

---

Louis

Answer 6

Thank you Louis for your reply.  I have renamed the table and am testing.  There is no need to change the stored procedures because I want them to access the views.  Only new code will access the renamed tables directly.  I am most concerned about indexes and keys and so far I have not found a problem, but it still early.

---

john poirier