Use a VIEW for all SELECTs on every TABLE for futureproofing?

  • What are the downsides of using a VIEW instead of the table direct?

    I'm thinking of a VIEW which is just a SELECT of all columns from the Table, no JOINS, but when the table is refactored can have additional computed columns for backward-compatibility

    Always using a VIEW would make it easy to refactor the table ... I could rename a column in the table and retain both old/new name in the View for a period of time. I could radically change a Status column's values in NewStatus and have the original Status column compute a backward compatible value.

    I have always found refactoring a table tiresome when major changes are made. After some years I decide that is a much better way to do it, some columns have become unnecessary, the naming on others (with benefit of hindsight or due to change in circumstances) is poor, or no longer in line with naming elsewhere, and I want some new columns too. (The "ADD COLUMNS" is easy of course)

    So generally I rename the table to [MyTable_V2], rename columns, change some of them to suffix with "_DEL" in readiness to delete them, and so on. Then I create a view [MyTable] to keep all the existing code happy and over time I change code to use [MyTable_V2]

    I prioritise changing UPDATE / DELETE code (which, for me, will only be in a couple of consistently named Sprocs 90+% of the time), and the SELECTs get changed later. I do a global find of the code and insert a comment "Change MyTable to MyTable_V2" so that any maintenance on that code can also make the change at that time (but I do not raise a ticket for each one)

    Eventually I can DROP VIEW [MyTable] ... but that rarely happens. It is kept alive simply because the effort to prove that it is no longer in use is not a priority. I would usually DROP VIEW [MyTable] on DEV to bring to light any remaining usage, but leave it on PRODUCTION "just in case"

    And then I forget that [MyTable] is now [MyTable_V2] and add to the problem by creating new code referencing the old name 🙁 That still works of course, so is hard to police.

    So perhaps instead of that future hassle I should use a VIEW from Day One, and then in future my View can have forward and backward compatible column names instead of me having to clutz with [MyTable] /[MyTable_V2]

    Will I notice any performance / query-plan difference between

    SELECT Col1, Col2 FROM MyTable_VIEW JOIN ... lots of other Table_VIEWs

    and the direct table equivalent? Particularly once the VIEW has got some backward compatible computed columns

    Any other downsides? (I won't be able to do UPDATE against the VIEW because of the computed backward compatible columns, and I definitely don't want to have an INSTEAD OF Trigger on every such View ... but as said my UPDATE / DELETE statements are few and in consistently named SProcs, so maintaining them when a table changed would be straightforward.

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Nothing wrong or problematic using views - or using views in this manner.  The problem with views comes into play when you have a view of a view of a view...and then join to another view of a view of a view...SQL can have a real hard time generating the execution plan and/or both views reference the same table(s) causing unnecessary reads.

     

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Ah, be careful, now... There actually IS a problem with making a view for every table... if you make a change to the table, you need to remember to "refresh" the view.  Please see the following URL for how to do that.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-refreshview-transact-sql?view=sql-server-ver15

    If, for example, you change the order of columns in a table, the view will actually display the wrong data for the columns that follow the change (from left to right on a previously unchanged table).  If they're all the same datatype, it will be a "silent failure" that wreak havoc on your results.

    My recommendation is that having a view per table is serious overkill even when it comes to security advantages and you should use an "overlying" view only when necessary.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • This myth goes back to the very early days of DB2. It was IBM's superstition for pretty much the same reason you gave. The truth is maintaining the views is only part of the problem. Because everyone would start doing their own views with their own names, the code quickly became a personalized mess for everyone working on the project. The execution plans were unreadable. After a few layers of views on views, nobody could quite follow what was going on. The best practice is based on the logical principle called "the law of identity", which states that to be is to be something in particular; to be nothing in particular or everything in general, is to be nothing at all. Logic doesn't like vague generic things, floating around randomly materializing and disappearing like magic. One name, following consistent rules (ISO 11179, for example), consistently used wherever the data element appears. This is not just good SQL; it's simply good software engineering.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Any issues with references can be mitigated by creating these views with schema binding.  That will mean that changes to the table cannot be made until that binding is removed - or the view dropped and then recreated.

    Changing column order isn't something that should be done either - always add new columns using ALTER TABLE ADD COLUMN.  In fact, you cannot change column order without recreating the table - and if you do that then it would be much easier to create a new table, migrate the data and recreate the view to use the new table.

    Based on the goal - which is to refactor a table - a view is really the only way to get that done, and the method that Kristen has outlined is the generally accepted method.  With that said - I would recommend looking at using synonyms in addition to the view.  If your code uses the synonym instead of the view or table directly - then changing it becomes much simpler, just drop the synonym and recreate it to point to the 'new' object whether that is a view or a table.

    The downside of using views is that someone can create a view on top of a view - and that can quickly become a nightmare.  In fact, you must carefully review the definition of a view to make sure it isn't self-referencing which can lead to a whole lot of other issues - or even where a JOIN is to a table that is already referenced in one of the views...which can cause circular references or worse.

    I missed the point about starting with a view from day one.  If the only reason for using the view is to allow for 'future' refactoring - then I would not recommend that approach.  If your system is being refactored that often - to the point where you have to add, remove or rename columns - there are probably much deeper issues that need to be addressed.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • There are a whole lot of things that SHOULDN'T be done to a table.  The fact the matter is, they DO happen in real life, Jeffrey, and you have to be able to handle them.  While having a view for every table might seem like a good "future proofing idea", I would limit it only to those things that are changing and then only to those tables that change in a fashion as to make it so that an overlying view is absolutely necessary.  It makes no sense to have view on tables that will never change and most will not change.  It reminds me of building C.R.U.D. for reference tables.  It makes no sense even under the claim of "future proofing".

    Adding "With Schema Binding" to the views will certainly remind you that you have a view to tear down and rebuild if you change the underlying table... and it'll usually be a view that actually provides no value because if you're writing front end code that can't withstand (for example) and additional column without breaking, then you have bigger problems to contend with.

    But, whatever.  Except for having a shedload of views cluttering up your database and having to maintain both a table and a view every time you need to make an otherwise trivial change to a table, there's no real harm, I suppose.  It just seems like putting a glass in a coffee cup just so you can claim you have a "handle" on things.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • If you reread what I wrote, you would see that I don't agree with creating a view for future proofing.

    I stated that using a view for refactoring is acceptable and generally the recommended approach.

    Including synonyms into the process could also help with refactoring.

    Schema binding would prevent someone from making a change to a table that is in that process as well.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    If you reread what I wrote, you would see that I don't agree with creating a view for future proofing.

    I stated that using a view for refactoring is acceptable and generally the recommended approach.

    Including synonyms into the process could also help with refactoring.

    Schema binding would prevent someone from making a change to a table that is in that process as well.

    Let's reread your original post, Jeffrey...

    Jeffrey Williams wrote:

    Nothing wrong or problematic using views - or using views in this manner.

    That's what I'm disagreeing with.  It creates unnecessary objects and work.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeffrey Williams wrote:

    I missed the point about starting with a view from day one.  If the only reason for using the view is to allow for 'future' refactoring - then I would not recommend that approach.  If your system is being refactored that often - to the point where you have to add, remove or rename columns - there are probably much deeper issues that need to be addressed.

    No, definitely not refactoring often 🙂

    Code base is 20 years old, some design decisions back then now need serious revamping. But if we do not have "Day One Views on All Tables" then I can't take advantage of that for Refactoring "when the time comes" and have to do it some other way (all the ways I have used are "hard", but maybe I will learn from this thread a better way. I'll pose the question at the bottom.)

    Changing column order isn't something that should be done either - always add new columns using ALTER TABLE ADD COLUMN.  In fact, you cannot change column order without recreating the table - and if you do that then it would be much easier to create a new table, migrate the data and recreate the view to use the new table.

    If I add a column that would be ADD COLUMN and not Re-Create Table (assuming no changes that require Re-Create Table). But ... column order IS important to us from a defensive programming perspective. The columns in our tables follow a convention. For example, all tables start with Create/Update Date/Person, and then the PKey. There are a number of columns that we "sometimes use", such as IsActive, which are always "in the same place". If we just bolt them in the middle / end it increases the chance that someone, doing maintenance of the code later or many-years-later, overlooks them.

    If I have to add one "common columns" later then it goes on the end, using ADD COLUMN, but that table is flagged for Re-Create at the earliest opportunity. But ... if all my access to the Table was via a VIEW then the column sequence of my VIEW could be changed willy-nilly ... so perhaps I wouldn't bother with the Re-Create at all.

    One of our 3rd party vendors has a whole load of "admin" columns in their tables - the usual Who/When, and also a Transaction No that ties all multi-table changes together, and some others. They added them to the end of their tables (fair enough) and since then "other columns have been added later". They added those Admin Columns to the FRONT of their Audit Tables so no easy way to just do a SELECT * UNION when I want a quick "data history". We work hard to avoid those sorts of snags with our tables because they lead to bugs at worst, and "increased cost of working" at the least.

    Jeff Moden wrote:

    If, for example, you change the order of columns in a table, the view will actually display the wrong data for the columns that follow the change (from left to right on a previously unchanged table).  If they're all the same datatype, it will be a "silent failure" that wreak havoc on your results.

    I've come across that, thanks for the reminder

    It makes no sense to have view on tables that will never change and most will not change.  It reminds me of building C.R.U.D. for reference tables.  It makes no sense even under the claim of "future proofing".

    The CRUD tables are a good point ... but (although very rare) even there I have split one-into-two, and combined two-into-one

    Except for having a shedload of views cluttering up your database and having to maintain both a table and a view every time you need to make an otherwise trivial change to a table, there's no real harm, I suppose.

    I did a quick stock-take

    SELECT	[Count]=COUNT(*), type_desc
    FROM sys.objects
    GROUP BY type_desc
    ORDER BY type_desc

    Compared to TABLES

    Views = 1x

    Sprocs = 6x

    If I did added "View for every Table" that would raise Views to 2x ... I think the "number of objects" would be tolerable (but that doesn't make it a good idea of course ...).

    My recommendation is that having a view per table is serious overkill even when it comes to security advantages and you should use an "overlying" view only when necessary.

    The code base is 20 years old, there are some tables which really really need refactoring but we are not getting around to it "because it is hard / time consuming / hard to justify" - i.e. "too costly", but not doing it is pretty costly too of course ....  If someone made a table yesterday and told me today they needed to refactor it "we would be having a conversation about that" 🙂 but when the need arises, the way we do it currently makes it seem like "blooming hard work" - in terms of fixing up existing code.

    Perhaps instead of my proposed solution I should ask the good folk here how do you handle refactoring a table?

    I'll try to think of some decent examples, perhaps you have better examples of where you have had to refactor in the past

    Old Status Column 1, 2, 3, 4, ... now needing to be split into two Status Columns (Status and Sub-Status, or Two different status codes for slightly different business purposes)

    Used a Natural Key as PKey (and FKey) assuming that data would be static ... later decided I needed an IDENTITIY and move the Natural Key to become a "non PKey modifiable field"

    Started off with ID and then we had a company merger / multiple office sites / whatever and now I need a GUID (maybe not to replace the ID as PKey but at the very least "additional to it" to allow MERGE of database)

    Had a Column called "Notes" in loads of tables and then decided that users freely editing them, and rewriting history!!, was a bad idea, plus would like consistent Person/Date/Text formatting, so moved ALL the Notes from ALL tables to a central (JOINed) NOTES table (e.g. Table/Person/Date/TextItem)

    My aim is to make any future "refactor" of the table easier - by having the view be able, at any future point, to provide both legacy and New Column names (and if necessary the Legacy columns will be via Computed values)

  • Kristen-173977 wrote:

    Perhaps instead of my proposed solution I should ask the good folk here how do you handle refactoring a table?

    My answer is, of course, "It Depends" on what the table is used for and how, etc , etc.  Sometimes, renaming the table and constructing an overarching view is just what the doctor ordered.  Other times, the table itself needs to be restructured and a view would just be another object in the database to keep an eye on.  Using an overarching view is not the answer to all problems for table refactoring.  It's not even close to being the norm where I work.

    As you can also see, opinions vary but I don't see the merit in automatically generating an overarching view for every table as a panacea for future-proofing.  Usually, we bite the bullet and, instead of using what I consider to be a "patch", we go for the "fix", which normally doesn't include a view.

    Of course, there are exceptions to every rule/notion because, truly, "It Depends" (especially if you have tables that have been "tibbled" with a tbl_ prefix. 😀 )

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden wrote:

    My answer is, of course, "It Depends" ...

    Indeed, and that's fair enough. But my current method may not be the smartest way (and having done it "my way" for decades it still bothers me that it is a pain in the posterior ... and has pitfalls that we sometimes fall into) so what I am after is to decide if Current Way is probably as good as it gets or whether VIEW for every TABLE might be better

    Scenario: I need to refactor a table, either modify some columns "irreversibly", or maybe split the table into two.

    My current method is to rename the table to MyTable_V2 (or MyTableA and MyTableB) and create a view for the original table name [MyTable]

    Then I find all the UPDATE/DELETE (for me that is usually centralised, with very few UPDATE occurrences in the code) and change them to use MyTable_V2

    Everything else continues to work because it references [MyTable] legacy view.

    But I don't want my code scattered with both [MyTable] and [MyTable_V2] so we then have a low priority task to convert the legacy code (and test it ... and deploy it ... which has a "cost").

    Typically what happens is that more urgent events overtake us and it doesn't get done.

    Then we have some other maintenance on an SProc, discover it is still using legacy [MyTable] and then have to "upgrade" that in order to do the actual maintenance. But knowledge of what the specifics of the original change were are by then "rusty" (and for any Newbie that has arrived this then becomes a Legacy Issue). Typically I look in the [MyTable] VIEW definition to see what the column-mapping is (and when I first create the legacy-VIEW I comment it with that future need in mind). All that takes time, and has a "cost" ... and probably we make some mistakes, caught in testing, and that has to be fixed too - more "cost"

    Then we get to the point where we think we have fixed all instances, and on DEV we DROP the legacy VIEW to expose any unexpected usage (and if we find any we fix it). But we leave the view on PRODUCTION "just in case" ... so we don't have a way of getting rid of it permanently (our QA/Rollout testing should find any usage ... but ... maybe a user wrote a report using the old name ... maybe "tough", and I expect i could use PROFILER to find those ... more "cost")

    We could do a "Shout Test" - DROP the legacy VIEW on production and listen down the corridor for anyone that shouts 🙂

    So ... I've all but got rid of it. [MyTable] legacy view is DROPPED on DEV. But now I have to remember that that specific table has a "_V2" suffix. Or maybe "_V3" by then. Whilst both the View and the _V2 exist it is not uncommon to accidentally use the old name (which still works of course) thereby accidentally creating more legacy code that has to be fixed at some point !!

    I've not tried Scott's suggestion (which triggered this thread) of "Have a VIEW for every TABLE", but it seems to me that would solve this conundrum "better"

    ... if you have tables that have been "tibbled" with a tbl_ prefix. 😀 )

    Well ... with an every-table-VIEW I could actually do that 🙂

    CUSTOMER = View

    T_CUSTOMER = Table - never directly accessed, except by a very few, centralised, UPDATE statements.

    No "_V2" stuff, no legacy stuff, just some "V1 legacy computed columns" in the CUSTOMER view (which I could comment out on DEV to force errors, same as dropping the [MyTable] legacy VIEW above

    No Legacy View / [MyTable_V2] code sort out. For any usage that reference columns that have not changed purpose there is no code change, so I just need to find any specific columns that are part of the change, and upgrade that code.

    What I suppose I am NOT seeing is any downside to doing this (other than proliferation of objects). Am I overlooking anything?

    Given that Scott mentioned it I have assumed that Performance of queries is not an issue (but taking Jeffrey's point into consideration that VIEWS on VIEWS is bad ... I have the potential to fall into that trap already, so its something we are aware of and careful to avoid)

    Perhaps I should try it on the next refactor

    Rename [MyTable] to [T_MyTable]

    Create VIEW [MyTable]

    and that will then be a permanent solution, rather than a short-term migration

  • I think you really need to look at SYNONYMS and use them in your code/refactoring processes.  If you have been good about schema qualifying your objects - it would be easier, but still should work if you use the default dbo schema.

    Example:

    Execute sp_rename 'dbo.MyTable', 'dbo.MyTable_v1';
    Go

    Create View dbo.MyTable_View
    As

    Select {full column list}
    From dbo.MyTable_v1;
    Go

    Drop Synonym If Exists dbo.MyTable;
    Create Synonym dbo.MyTable For dbo.MyTable_View;
    Go

    Now - all of your code is using the 'legacy' view without any code changes.

    Another example...full replacement of a table with a new table, no view required:

     Create Table dbo.MyTable_v2 (
    {new columns/structure}
    );
    Go

    Begin Transaction;

    Insert Into dbo.MyTable_v2 (...)
    Select ...
    From dbo.MyTable with (holdlock tablockx);

    Execute sp_rename 'dbo.MyTable', 'dbo.MyTable_v1';

    Drop Synonym If Exists dbo.MyTable;
    Create Synonym dbo.MyTable For dbo.MyTable_v2;

    Commit Transaction;
    Go

    Either way - using a synonym will give you the ability to reference the appropriate object, whether that is a new table or view.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 13 posts - 1 through 13 (of 13 total)

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