|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
View ExplainationLet me see if I have this straight;
A view is like a virtual table. Once a view is created, I can update and manipulate the view and it will always maintain those changes, yet not change the tables that the views are based upon. So, why use a view instead of creating another table to do the things your using the view to do? No, a view is a ... view... into the data which is stored in the table. the only thing stored when
you create a view is the SELECT statement - no data. When you execute a select statement against the view, SQL server combines that SELECT and the SELECT that the view is based on and optimizes the whole and then accesses the data in the table(s). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Job" <J**@nomail.com> wrote in message news:ed69wwYfFHA.2372@TK2MSFTNGP14.phx.gbl... > Let me see if I have this straight; > > A view is like a virtual table. Once a view is created, I can update and manipulate the view and > it will always maintain those changes, yet not change the tables that the views are based upon. > So, why use a view instead of creating another table to do the things your using the view to do? > So creating views on views is more efficient than creating views off of
tables? I did a small test and the view from the table was 10 seconds faster than the view off of a view (25 vs. 35). This was not a complex view, so maybe it becomes more efficient completely dependant upon what your asking? Show quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:uWVI%234YfFHA.352@TK2MSFTNGP09.phx.gbl... > No, a view is a ... view... into the data which is stored in the table. > the only thing stored when you create a view is the SELECT statement - no > data. When you execute a select statement against the view, SQL server > combines that SELECT and the SELECT that the view is based on and > optimizes the whole and then accesses the data in the table(s). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Job" <J**@nomail.com> wrote in message > news:ed69wwYfFHA.2372@TK2MSFTNGP14.phx.gbl... >> Let me see if I have this straight; >> >> A view is like a virtual table. Once a view is created, I can update and >> manipulate the view and it will always maintain those changes, yet not >> change the tables that the views are based upon. So, why use a view >> instead of creating another table to do the things your using the view to >> do? >> > Excerps form Books online
Sql Views http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_2d9v.asp A view can be thought of as either a virtual table or a stored query. SQL Server 2000 supports more complex types of INSERT, UPDATE, and DELETE statements that reference views. INSTEAD OF triggers can be defined on a view to specify the individual updates that must be performed against the base tables to support the INSERT, UPDATE, or DELETE statement. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_51va.asp A view does not exist as a stored set of data values in a database. A view acts as a filter on the underlying tables referenced in the view. A view acts as a filter on the underlying tables referenced in the view. Show quote "Job" wrote: > Let me see if I have this straight; > > A view is like a virtual table. Once a view is created, I can update and > manipulate the view and it will always maintain those changes, yet not > change the tables that the views are based upon. So, why use a view instead > of creating another table to do the things your using the view to do? > > > It's more like a virtual *read-only* table - you can't update a view
directly, although you can put a trigger on a view and update the underlying tables yourself. Show quote "Job" wrote: > Let me see if I have this straight; > > A view is like a virtual table. Once a view is created, I can update and > manipulate the view and it will always maintain those changes, yet not > change the tables that the views are based upon. So, why use a view instead > of creating another table to do the things your using the view to do? > > > Non calculated columns from the base (not joined) table can be updated.
Show quote "KH" <K*@discussions.microsoft.com> wrote in message news:1AF1B7A8-FFD6-4E4C-B7A4-4319DD2114CC@microsoft.com... > It's more like a virtual *read-only* table - you can't update a view > directly, although you can put a trigger on a view and update the underlying > tables yourself. > > > "Job" wrote: > > > Let me see if I have this straight; > > > > A view is like a virtual table. Once a view is created, I can update and > > manipulate the view and it will always maintain those changes, yet not > > change the tables that the views are based upon. So, why use a view instead > > of creating another table to do the things your using the view to do? > > > > > > Ok, now I'm a little more confused.
I have a table. From this table I create a view. If I update say a Units column does this not affect only the view? I'm effectivly changing the view but not the table. Show quote "Job" <J**@nomail.com> wrote in message news:ed69wwYfFHA.2372@TK2MSFTNGP14.phx.gbl... > Let me see if I have this straight; > > A view is like a virtual table. Once a view is created, I can update and > manipulate the view and it will always maintain those changes, yet not > change the tables that the views are based upon. So, why use a view > instead of creating another table to do the things your using the view to > do? > Some views can be updated and some cannot. An updateable view must be
on one and only one table, expose a key, have no computed columns and the columns not exposed in the view musts have default values. What happens is the UPDATE goes thru the VIEW to the base table, and changes the base table. The VIEW is then changed because the base table is changed. Think of an updatable view as being like a stencil that you spray paint thru. If you want to get really confused, thre is also a WITH CHECK OPTION on updatable views. This says take the WHERE clause and make sure that an update does not destroy the VIEW: CREATE VIEW NewYorkSalesmen AS SELECT * FROM Salesman WHERE city = 'New York'; UPDATE NewYorkSalesmen SET city = 'Boston'; and now the view is empty! CREATE VIEW NewYorkSalesmen AS SELECT * FROM Salesman WHERE city = 'New York' WITH CHECK OPTION; UPDATE NewYorkSalesmen SET city = 'Boston'; The view stays the same and the update fails. Ok,
I now understand how I'm able to update the table, thank you. Now in terms of what is the 'right' thing to do. Is it always better to create views and manipulate views of the data or say add a column to a table and use that? In terms of leaving the original data untouched, I can understand that. Is there any efficiency reasons not to use the tables, and use views of view etc? Meaning, if a view was a snapshot of a table but has a few added columns where calculations were put in place, is it better/faster to use the view or to add those columns to the table? Thanks for your info! Cheers, Job Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1120151818.800851.308270@f14g2000cwb.googlegroups.com... > Some views can be updated and some cannot. An updateable view must be > on one and only one table, expose a key, have no computed columns and > the columns not exposed in the view musts have default values. > > What happens is the UPDATE goes thru the VIEW to the base table, and > changes the base table. The VIEW is then changed because the base > table is changed. Think of an updatable view as being like a stencil > that you spray paint thru. > > If you want to get really confused, thre is also a WITH CHECK OPTION on > updatable views. This says take the WHERE clause and make sure that an > update does not destroy the VIEW: > > CREATE VIEW NewYorkSalesmen > AS > SELECT * > FROM Salesman > WHERE city = 'New York'; > > UPDATE NewYorkSalesmen SET city = 'Boston'; > > and now the view is empty! > > CREATE VIEW NewYorkSalesmen > AS > SELECT * > FROM Salesman > WHERE city = 'New York' > WITH CHECK OPTION; > > UPDATE NewYorkSalesmen SET city = 'Boston'; > > The view stays the same and the update fails. > Doesn't sound like you're getting it man :)
Views are not data, they are just basically stored SELECT queries. With a couple narrow exceptions you cannot update or insert into views. Even if you have an updatable view, the data shown by the view only changes because the data in the underlying table(s) changes. Exceptions include: - A trigger on a view - An updatable view as CELKO described - An indexed view Do your updates and inserts on tables; use views as a convenience for example to join domain-value lookup tables or include parent-level data with many-to relationhships, stuff like that. Show quote "Job" wrote: > Ok, > I now understand how I'm able to update the table, thank you. Now in terms > of what is the 'right' thing to do. Is it always better to create views and > manipulate views of the data or say add a column to a table and use that? > In terms of leaving the original data untouched, I can understand that. Is > there any efficiency reasons not to use the tables, and use views of view > etc? Meaning, if a view was a snapshot of a table but has a few added > columns where calculations were put in place, is it better/faster to use the > view or to add those columns to the table? > > Thanks for your info! > > Cheers, > > Job > > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > news:1120151818.800851.308270@f14g2000cwb.googlegroups.com... > > Some views can be updated and some cannot. An updateable view must be > > on one and only one table, expose a key, have no computed columns and > > the columns not exposed in the view musts have default values. > > > > What happens is the UPDATE goes thru the VIEW to the base table, and > > changes the base table. The VIEW is then changed because the base > > table is changed. Think of an updatable view as being like a stencil > > that you spray paint thru. > > > > If you want to get really confused, thre is also a WITH CHECK OPTION on > > updatable views. This says take the WHERE clause and make sure that an > > update does not destroy the VIEW: > > > > CREATE VIEW NewYorkSalesmen > > AS > > SELECT * > > FROM Salesman > > WHERE city = 'New York'; > > > > UPDATE NewYorkSalesmen SET city = 'Boston'; > > > > and now the view is empty! > > > > CREATE VIEW NewYorkSalesmen > > AS > > SELECT * > > FROM Salesman > > WHERE city = 'New York' > > WITH CHECK OPTION; > > > > UPDATE NewYorkSalesmen SET city = 'Boston'; > > > > The view stays the same and the update fails. > > > > > I understand that a view is simply a stored select query.
I also understand that a view is simply a look at the data that is in the table, where you can specify various 'filters' or joins. The whole virtual table thing caused me to rethink what a view really is as if you could manipulate the actual view with updates etc. However, thanks to all of your input I'm very clear on what a view is and isn't. So, I'm interested in understanding the best way to use tables. If you have a Codeand a Modifier of the code, and you combine them into a single value ie 909 = Code and A = modifier. If the Modifier is A or B then we will look at 909A and 909B as the correct code. If the Modifier is anything else, you just use the Code. Would you add a column to your table that creates this modified version of the code, or would you always combine them with the above logic each time as you queried your table? A similar question with year. If you have a field that gives you a period 200505, we almost always want to know the year left(period,4). Is it better to always calculate the year or add a column to the table to have the year static? Show quote "KH" <K*@discussions.microsoft.com> wrote in message news:254CA258-A345-47DF-959F-5A61D27F9DF0@microsoft.com... > Doesn't sound like you're getting it man :) > > Views are not data, they are just basically stored SELECT queries. > > With a couple narrow exceptions you cannot update or insert into views. > Even > if you have an updatable view, the data shown by the view only changes > because the data in the underlying table(s) changes. > > Exceptions include: > - A trigger on a view > - An updatable view as CELKO described > - An indexed view > > Do your updates and inserts on tables; use views as a convenience for > example to join domain-value lookup tables or include parent-level data > with > many-to relationhships, stuff like that. > > > > "Job" wrote: > >> Ok, >> I now understand how I'm able to update the table, thank you. Now in >> terms >> of what is the 'right' thing to do. Is it always better to create views >> and >> manipulate views of the data or say add a column to a table and use that? >> In terms of leaving the original data untouched, I can understand that. >> Is >> there any efficiency reasons not to use the tables, and use views of view >> etc? Meaning, if a view was a snapshot of a table but has a few added >> columns where calculations were put in place, is it better/faster to use >> the >> view or to add those columns to the table? >> >> Thanks for your info! >> >> Cheers, >> >> Job >> >> >> "--CELKO--" <jcelko***@earthlink.net> wrote in message >> news:1120151818.800851.308270@f14g2000cwb.googlegroups.com... >> > Some views can be updated and some cannot. An updateable view must be >> > on one and only one table, expose a key, have no computed columns and >> > the columns not exposed in the view musts have default values. >> > >> > What happens is the UPDATE goes thru the VIEW to the base table, and >> > changes the base table. The VIEW is then changed because the base >> > table is changed. Think of an updatable view as being like a stencil >> > that you spray paint thru. >> > >> > If you want to get really confused, thre is also a WITH CHECK OPTION on >> > updatable views. This says take the WHERE clause and make sure that an >> > update does not destroy the VIEW: >> > >> > CREATE VIEW NewYorkSalesmen >> > AS >> > SELECT * >> > FROM Salesman >> > WHERE city = 'New York'; >> > >> > UPDATE NewYorkSalesmen SET city = 'Boston'; >> > >> > and now the view is empty! >> > >> > CREATE VIEW NewYorkSalesmen >> > AS >> > SELECT * >> > FROM Salesman >> > WHERE city = 'New York' >> > WITH CHECK OPTION; >> > >> > UPDATE NewYorkSalesmen SET city = 'Boston'; >> > >> > The view stays the same and the update fails. >> > >> >> >> >> Meaning, if a view was a snapshot of a table but has a few added columns where calculations were put in place, is it better/faster to use the view or to add those columns to the table? << I would use the VIEW. The syntax is standard and portable. The VIEWis correct when it is invoked, whereas a table with extra columns has to be updated every time and you need extra CHECK() constraints; CREATE VIEW Packages (length, width, height, volume) AS SELECT length, width, height, (length * width * height) AS volumn FROM Boxes; versus: CREATE TABLE Packages (length INTEGER NOT NULL, width INTEGER NOT NULL, height INTEGER NOT NULL, volume INTEGER NOT NULL, <== extra storage CHECK (volume = length * width * height), <== extra work PRIMARY KEY (length, width, height) CHECK (width BETWEEN length AND height));  |
|||||||||||||||||||||||
Other interesting topics