Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2012-12-18 : 12:30:06
|
Is there any reason not to, i.e. routinely, use a VIEW instead of the table directly?For example say I haveCREATE VIEW MyTable_V1ASSELECT MyCol1, MyCol2, MyCol3, MyColAFROM dbo.MyTable then if I use this every time I want to include MyTable in a SELECT then I have the benefit of being able to adjust the VIEW if something changes.The most common change which this would help with is when a column is renamed. There are various possible reasons for wanting to rename a column, but I am a stickler for sticking to our naming convention, and I hate for columns to be left "as they were" just because it is too much hassle to change the code to make every consistent with the naming conventions.However, changing the name of a column frequently involves change lots of SProcs etc. and the rollout has to happen all-at-once to release those changesLets say that MyColA is renamed to MyColB. I can change my VIEW to become:CREATE VIEW MyTable_V1ASSELECT MyCol1, MyCol2, MyCol3, [MyColA] = MyColB, MyColBFROM dbo.MyTable and all old Sprocs, referring to "MyColA", will still work - as will any new Sprocs which will refer to "MyColB"Further more I can delay rolling out the actual column-rename to the underlying table on legacy system by creating this VIEW instead:CREATE VIEW MyTable_V1ASSELECT MyCol1, MyCol2, MyCol3, MyColA, [MyColB] = MyColAFROM dbo.MyTable and any new code, referring to "MyColB", will still work on the legacy systems - even though the column rename has not yet happened.So the nub of the question is: are there any GotCha's if I ALWAYS use a VIEW when I am selecting from a Table?(INSERT / UPDATE may have different needs, but Insert/Update is a tiny fraction of our codebase, compared to SELECT, so I can easily see my way to using physical table references in those Sprocs and fixing up those Sprocs as & when the underlying tables change.We already provide for Sprocs to have @Parameters for both @MyColA and @MyColB situations, and we COALESCE the two together to use one of them within the body of the Sproc, so I have a DEV process for handling that.Look forward to any opinions you may have, thanks  |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-18 : 14:00:47
|
| I am less enthusiastic about always using views to access data in tables for the following (and other reasons that I reserve the right to add later)-- Adds a level of indirection with no perceptible benefit (in my opinion)-- Changing the names of the columns is a very rare occurrence, at least in my limited experience, so establishing this policy for that purpose does not seem to be a weighty enough reason.-- It is easy for someone to violate the "always access only from view" rule, thus negating any value it may add.I can see value in views if security is a consideration, or if flattening normalized tables is a consideration, or if aggregations need to be persisted etc. But for avoiding the hassle of renaming, using views seems to be of questionable value.If you establish a policy that external clients are allowed to access data only through stored procedures, then if you do need to change a column, it is easy enough to search for all stored procedures that use that column.These are all, of course, my own view of the world - I am easily persuadable |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-18 : 14:59:53
|
| Good points "If you establish a policy that external clients are allowed to access data only through stored procedures, then if you do need to change a column, it is easy enough to search for all stored procedures that use that column."The problem we have is the time it takes to find & fix all those Sprocs. Its a major distraction, which is fine if we can schedule the time to do it, but every day things crop up that demand our time and I would prefer to be in a position where a column change does not require down-tools to fix every single occurrence before the application will work end-to-end again.Column name changes are not uncommon here. Its probably lack-of-forward-planning, but it strikes me that I could accommodate it better if I used Views than using the current "down tools until all changes made" approach(We use column names that are unique across the whole application, so for us Find&Replace is reasonably "guaranteed" process, I would imagine shops with column names like "Name" or "ID" would have a much harder time of it ...) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-18 : 15:02:53
|
quote:
Originally posted by tkizer
I don't really see a benefit to this unless it had more code in it, such as a WHERE clause or a JOIN
The point is solely that it enables a column name change to be performed over a period of time (old & new parts of the application continue to work with old/new names)Perhaps, in DEV, I should:Call the table "XXX_MyTable" and the view "MyTable" (as a SELECT MyCol1, MyCol2 FROM XXX_MyTable), and then when DEV is done and we DEPLOY I could drop the VIEW and rename the Table to the actual name.That might give me the flexibility I want in DEV, but have no unnecessary abstraction in Production. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-12-18 : 15:06:34
|
| I get it, it's just that you are going to have tons of objects. Not actually a DBA problem as who cares, but I know devs complain about having to scroll, lol. So if you've got a view that you regularly use that has 4 columns, but you need another column, now what? Just add the column to the view or create another view? If you just add a column to a view to deal with this, I would think you'd end up with a view that's basically the table.As far as renaming columns, we don't do it unless we are in the design phase. After that, it should be a painful process.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-12-18 : 15:08:35
|
quote:
Originally posted by Kristen
Good points <snip>The problem we have is the time it takes to find & fix all those Sprocs. <snip>
If that is a problem, then I'd suggest a good tool. I know there are many, I happen to use SQL Server Data Tools (SSDST) with visual studio and it has great functionality for "refactoring." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-18 : 15:22:27
|
quote:
Originally posted by Lamprey
If that is a problem, then I'd suggest a good tool
Good idea to look at available tools as we might be able to improve the process; we currently have home-grown tools that facilitate the job. Well ... I say "tools" its just some SQL statements!Old/New names are added to a "job" table.Job Table used to query specific tables in the database for all meta-data that references the names - a series of UPDATEs fixes all of them (this is structured data, there are no ambiguity issues etc).Output sp_rename commands to add to a "rollout" script file - so that makes it easy to actually change one/many column renames.Find all CMS etc. that includes the column (so all report template etc). These may well be ambiguous, so have to be reviewrd manually, and sometimes changed by handQuery the system tables for Procedures etc. for any use of the old column name. Sadly this isn't THAT smart - for example it will identify an Sproc as needing editing when the column name is only used in a commented-out section of code  |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2012-12-18 : 15:42:27
|
| I'm concerned that perhaps your column naming conventions are causing a lot of renaming.You don't, for example, include the column type in the column name, do you? |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-18 : 16:15:46
|
quote:
Originally posted by KristenSo the nub of the question is: are there any GotCha's if I ALWAYS use a VIEW when I am selecting from a Table?
You probably could get away with it performance-wise if you are not using any joins nor scalar functions.The Gothchas could be: double the work; every time you need a new column, you need to add it to both the table and the view. Everytime you need to select from a new column, the Developer will need to: - check the name in the view, - see what column it actually references - look up the actual column to see it's Datatype/LengthOne benefit to your approach could be security: when you want a group to access a table but to keep certain columns away from them.I see your point regarding changing a column and avoiding that change to cause everything to break. But this could cause confusion in the long run. Because you are labelling something one way in one place (i.e. the view) and giving that very same thing another label in another place (i.e. the table). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-18 : 18:27:15
|
quote:
Originally posted by ScottPletcher
I'm concerned that perhaps your column naming conventions are causing a lot of renaming.You don't, for example, include the column type in the column name, do you?
No, TYPE is not included in the column name.We use a table naming convention of XXX_YYY_TableName, where "XXX" is the "Module" and "YYY" a short mnemonic for the table. So, for example, Order Header and Order Item tables might be in the "Sales Order Processing (SOP)" module and we would thus have tables:SOP_ORDH_OrderHeaderSOP_ORDI_OrderItemAll columns within each table are prefixed with the XXX_YYY - so the columns in SOP_ORDH_OrderHeader:sop_ordh_IDsop_ordh_Date...and then in SOP_ORDI_OrderItem we would have a "join" reference to SOP_ORDH_OrderHeader:sop_ordi_sop_ordh_IDsop_ordi_Itemsop_ordi_Qtysop_ordi_PriceetcWhat tends to happen is we get a list of columns from an existing application and a definition of what they do. Normally these things are coming from Access or Excel even ... its a mess. We do our best to understand the existing application, with the client, before we start, and construct a schema to the best of our ability and knowledge.Once we start building the application it becomes apparent that what we thought was an isolate field is actually joined to some other table soxxx_yyy_FooBarneeds to be changed to becomexxx_yyy_aaa_bbb_IDand then we get things like today's cockup where:xxx_yyy_sys_user_IDis joined to a table sys_usr_User so we need to fix the typo and changexxx_yyy_sys_user_IDtoxxx_yyy_sys_usr_IDNo one spotted that, and it has been wrongly used on several different tables ... 5 tables to be changed, probably 50 Sprocs, loads of CMS references ... couple of days work, none of the application will work until it is all fixed, other developers will be getting errors whilst they try to do their DEV work.This isn't an issue in Production, this is just whilst we are messing around in DEV (I hadn't made this distinction in my own mine until having this conversation, but now I've realised that I think I have some workarounds available to me that will be for DEV only).So my solution is:1. Create XXX_YYY_MyView. Include both old and new columns names (i.e. selecting the Old physical column)XXX_YYY_MyView will just be a simple SELECT Col1, Col2, ... FROM XXX_YYY_MyTable2. Find all occurrences of xxx_yyy_sys_user_ID and change it to the correct name xxx_yyy_sys_usr_ID, and change the table from XXX_YYY_MyTable to XXX_YYY_MyView(Maybe there will be reasons to just change the Table name and leave the columns with the old name - the column name could be changed late - e.g. the speed with which I can get to (3). For example, if I change a column name in the Sproc I also have to change the Application side to match)Application will still work before, during, and after this change.3. change the table to rename the column, and change the VIEW to make both the Old and New column names both reference the new name in the table.The whole DEV application still works.4. At some point (maybe immediately) we can change XXX_YYY_MyView back to XXX_YYY_MyTable, or leave it in place in case of further changes. Either way, the column name change, and the view name reversion back to the table name, will be made before we get from DEV to QA - i.e. all the "transient" VIEWs will be removed. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-18 : 18:31:15
|
quote:
Originally posted by denis_the_thief
You probably could get away with it performance-wise if you are not using any joins nor scalar functions.
I think I would get the same execution plan for JOINs on simple Views that I would from using the tables direct? (but I haven't tried it )quote:
The Gothchas could be: double the work; every time you need a new column, you need to add it to both the table and the view.
Indeed. I think that may be made up by having zero "down time" in DEV whilst all the changes are rippled around the whole system. quote:
Everytime you need to select from a new column, the Developer will need to: - check the name in the view, - see what column it actually references - look up the actual column to see it's Datatype/Length
I don't see this being a problem (unless I've overlooked something?)The developer just uses the current details from the Table Schema. In fact s/he will be selecting from a View, but as far as s/he is concerned the column name, datatype, length etc. are all as-per the table's current schema. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-12-19 : 08:20:04
|
| It may take a while, and be a HUGE pain, but that is why DEV is DEV. I agree with tara that after the design phase, it should be painful to change a column. I see that what happened in your case was a typo error. Happens, hopefully not often.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-12-19 : 13:32:44
|
quote:
Originally posted by Kristen[brWe use a table naming convention of XXX_YYY_TableName, where "XXX" is the "Module" and "YYY" a short mnemonic for the table. So, for example, Order Header and Order Item tables might be in the "Sales Order Processing (SOP)" module and we would thus have tables:SOP_ORDH_OrderHeaderSOP_ORDI_OrderItem<snip>
Ack. There's your problem.. :)Obviously too late in the process to change, but the table name should not be included on any column expect for an identity column. (Yes this is a highly debated topic).But, as far as gotcha's go, I don't really see any major one; Just what others have already said. I, persoanlly, wouldn't go the view route to solve the "problem" as you've discribed it. I see, far too often, people implementing "work arounds" for things that shouldn't be an issue. If you need to change a column name and it affects 30 objects; so be it. But, abstracting that "change" I don't think helps. As Denis pointed out, the level of abstration just makes it more time consuming to figure out what the real column is. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-19 : 14:31:32
|
quote:
Originally posted by Kristen
quote:
Originally posted by denis_the_thief
You probably could get away with it performance-wise if you are not using any joins nor scalar functions.
I think I would get the same execution plan for JOINs on simple Views that I would from using the tables direct? (but I haven't tried it )[quote]
I have tried this (i.e. comparing a query containing a view and a query identical to that query but not containing the view) and yes, the performance/execution plans would be identical.But an issue could arise for example: - If the only view containing a certain table contains a join - and the Developer needs to write a select that only needs to access information from that table - and they are forced to use Views onlyIn this case their query would be slower since they would be forced to use something that contains a join but all they need is a straight select.I maybe be oversimplying things but this can relate to the poor performance of views: you use a view that does more that what you need (i.e. unneeded joins or scalar functions) so it is slower than nessessary. Of course in certain contexts the view does not do more than what you need or you wouldn't have created it with the joins or scalar functions in the first place. I find often views are used for good programming practices, i.e. resuse, which I can appreciate. But at some point you have a lot of queries, using views, that appear simple; but in reality these queries in their entirety is complex. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-19 : 14:34:26
|
quote:
quote:
Everytime you need to select from a new column, the Developer will need to: - check the name in the view, - see what column it actually references - look up the actual column to see it's Datatype/Length
I don't see this being a problem (unless I've overlooked something?)The developer just uses the current details from the Table Schema. In fact s/he will be selecting from a View, but as far as s/he is concerned the column name, datatype, length etc. are all as-per the table's current schema.
Not a big problem. But it is an extra look-up - instead of only considering the table, even simple queries must consider both the view and the table. Particularly since, as you mentioned, in some cases the view's column name and the corresponding table's column name are different. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-12-19 : 17:16:29
|
| >>Is there any reason not to...Over the years I have changed my mind about this question. Now I believe that there is no reason not to.Even if you create a one-to-one view for every table where each view has all table columns - there is no negative performance impact because the underlying plan hits the tables anyway. And that layer of abstraction gives you flexibility to change the definition of the view without impacting all the consuming code. And of course you can examine the resolved datatypes using object explorer or the information_schema views just like you do a table.One of the places we have used views for awhile is for references to tables in other databases. By using a local view in the consuming code it allows you to maintain a more steady (consistent) code base. If you need to change the source of that table content then a mod the view is all you need.Another tool we implemented recently to provide a layer of abstraction is synonyms. Again, in our case, this was for references to tables in other DBs. We had need to move some tables into a different databases from where they originated because we were restructuring some legacy apps to support DB Server scaling, fail-over, and disaster recovery. but the synonym allowed us to move the table with no changes necessary to all the consuming code.Be One with the OptimizerTG |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-12-20 : 10:10:19
|
quote:
Originally posted by Kristen
Good points "If you establish a policy that external clients are allowed to access data only through stored procedures, then if you do need to change a column, it is easy enough to search for all stored procedures that use that column."The problem we have is the time it takes to find & fix all those Sprocs. Its a major distraction, which is fine if we can schedule the time to do it, but every day things crop up that demand our time and I would prefer to be in a position where a column change does not require down-tools to fix every single occurrence before the application will work end-to-end again.Column name changes are not uncommon here. Its probably lack-of-forward-planning, but it strikes me that I could accommodate it better if I used Views than using the current "down tools until all changes made" approach(We use column names that are unique across the whole application, so for us Find&Replace is reasonably "guaranteed" process, I would imagine shops with column names like "Name" or "ID" would have a much harder time of it ...)
Now that I think a bit more about this. You are esentially using the views so you don't have to rewrite code when you want to change a table column from a bad name to a good name.So the net result is you have 1 good name (i.e. in the table) and one bad name (i.e. in the view). In total = 1 bad name.But if you kept that bad name in the table, you would have in total 1 bad name.So instead of having all these views, why not just keep the occasional bad name in your table? Since you can't get rid of the bad names in the Views anyways. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-20 : 12:01:04
|
Some very useful discussion, and ranging over a wider debate, thanks to you all for contributing quote:
Originally posted by Lamprey
Obviously too late in the process to change, but the table name should not be included on any column expect for an identity column. (Yes this is a highly debated topic).
As you say its debatable, but you state it like its an error of judgement that we made in the design process. This is not the case, it is a very deliberate decision (although I fully accept that there are other naming conventions and pros/cons to all of the various choices)For us:A column name is unique within the application. The column name is used in all @Variables and @Parameters that refer to it; it is used in all CMS templates (where we have a REPLACE syntax like {MyColumnName}), and it is used in all Application Code where that column is referenced (i.e. working variables and the like). No exceptions!.The upshot of that is that when xxx_yyy_InvoiceNumber changes from INT to BIGINT (say) then we can Find all occurrences of xxx_yyy_InvoiceNumber anywhere in the codebase and consider what change is necessary. If we had non-unique column names like [Name] we would have loads of false positives on such a Find and Fix mission.If we retrieve a MAX value for a column the naming convention is:SELECT [xxx_yyy_ColumnName_MAX] = MAX(xxx_yyy_ColumnName)If we have multiple columns referencing the same JOINed table (e.g. a Delivery Address and an Invoice Address) we name them xxx_yyy_aaa_bbb_ID_Delivery / xxx_yyy_aaa_bbb_ID_Invoiceso, again, a Find & Fix mission will locate this usage, and anywhere it is used in associated Application / CMS templates / etc.Thus, personally, I feel very strongly that your "Only Primary Keys should include the Table Name" naming convention is missing a huge opportunity for time saving when wild-spread changes become necessary. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-20 : 12:06:12
|
quote:
Originally posted by denis_the_thief
Now that I think a bit more about this. You are esentially using the views so you don't have to rewrite code when you want to change a table column from a bad name to a good name.So the net result is you have 1 good name (i.e. in the table) and one bad name (i.e. in the view). In total = 1 bad name.But if you kept that bad name in the table, you would have in total 1 bad name.
This isn't quite correct, although I've changed my stance slightly (having got a clearer view from the contributions to the discussion earlier, thanks ) so I'm moving the goal posts a bit !!My initial question was asking about having these "work around views" on a semi--permanent basis. I now see theme as only being a temporary tool during DEV to reduce downtime when a column rename would have a large impact on the database, and other developers in the project.The problem that I initially described is, as is typical of a forum postings, simpler than the real life need.What we actually have in real life is a core database, under development, and 4 additional applications/databases that re-use the code & schema from the core database.In building one of those child database applications we discover that there is a need for a change to the Core system. We want to make that change and roll it out to all 4 child database projects with the minimum downtime. The whole system is in DEV, so changes are likely to be fragile until shaken down but also because it is DEV a formal rollout plan is not called for - different if we were rolling out to LIVE production systems of course.So my refined process is now:1. Discover that a column name change is needed.2. Create a Temporary/DEV-only VIEW that has both Old Name and New column name.3. Convert all the existing code which references the Old Name column to use the View instead.3a. I may also choose to change the Old Name column to New Name at this stage, but either way I will change Table name to View Name. (At this stage the view is still referring to Old Name physical column, but the view allows reference to it by either New Name or Old Name)As each Sproc is changed there is no downtime, application continued to work. Other DEVs can do their stuff.3b. If I change Old Name column to New Name (at this stage) in the SProc I must also change the Client usage - e.g. CMS Templates and Application that used to expect OldName column and now gets NewName. If I do not want to do that work right now I can just change the Table Name to use the View Name instead - and make the Column Name change later.So, quickest, is "Change Table Name to View Name in every statement where OldName column is used"4. Then I can physically rename the column.4a. Change the VIEW so that it references the physical New Name column - but still provides it as both Old and Name names5. Now I can write new code that directly references that column, or I can write new code that uses the New Column name and the View.5a. Importantly I can "copy" any Sproc to another database which still has the old name, or one that has the new name, because the View will shield me from whether that database has been updated with the column rename as yet, or not.6. At some future point I will replace all reference to the Temporary DEV-only View with the physical table. I will only do that once OldName column no longer exists anywhere in the code base. That will be done before we move the code from DEV to QA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-20 : 12:11:25
|
quote:
Originally posted by TG
>>Is there any reason not to...Over the years I have changed my mind about this question. Now I believe that there is no reason not to.Even if you create a one-to-one view for every table where each view has all table columns - there is no negative performance impact because the underlying plan hits the tables anyway. And that layer of abstraction gives you flexibility to change the definition of the view without impacting all the consuming code.
It is very helpful to hear from someone who has real-world experience of that. I assumed that was the case, but short of actually spending the time to prove it for myself hearing that is indeed the case is a great help, and time saving. Thanks TG |
|
|
|
Previous Page&nsp;
Next Page
|
|
|
|
|
Views vs. Tables Gotchas?