0

I need to change the order of columns in my table (Oracle and SQL Server). Reason is that some of our software calls select * and insert without specifying columns. Refactoring will be too expensive so I need to solve it on the database level.

My idea is to create a new view which will encapsulate the table and just change the order of columns. Current table will be renamed and new view will have the original name. I found here that encapsulating a table with a view is some kind of database design pattern.

Here are my questions:

  • Do I lose performance when I encapsulate a table with a view and call the view only?
  • Do you see any risk with naming the view (there will not be V_ prefix)?
  • Do you see any other advantage / disadvantage in encapsulating a table with a view?
1

1 Answer 1

1

Well,

  • A view increases the overhead but if the view only contains a select with a different column ordering, the overhead will not be noticeable.
  • You can also rebuild the table with the column order you want (a rather expensive one time operation)
  • Most people agrees that views should follow the same naming standard as tables (i.e. no V_, or any other, special prefix)

Having said that, software should almost never do selects or inserts, without specifying the columns.

1
  • 1
    +1 for rebuilding. I've never gotten the impression of a consensus on view naming conventions, though the implications tend to be very dependent on the environment you're working in. Commented Mar 14, 2016 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.