UPDATE: (5/15/2007) Thank you Ben Taylor for correcting errors and incorrect information from this post. He is Database Architect and writes Database Articles at www.sswug.org.
I have been coding as T-SQL for many years. I never have to use view ever in my career. I do not see in my near future I am using Views. I am able to achieve same database architecture goal using either using Third Normal tables, Replications or other database design work around.SQL Views have many many restrictions. There are few listed below. I love T-SQL but I do not like using Views.
The ANSI_NULLS and QUOTED_IDENTIFIER options must be turned on when the view is created. Following SP will turn those options on.
sp_dboption 'ANSI_NULLS', TRUE sp_dboption 'QUOTED_IDENTIFIER', TRUE
The ANSI_NULLS option must have been turned on during the creation of all the tables that are referenced by the view.
All the tables referenced by the view must be in the same database as the view.
All the tables referenced by the view must have the same owner as the view.
Indexed view must be created with the SCHEMABINDING option. This option prohibits the schema of the base tables from being changed (adding or dropping a column, for instance). To change the tables, Indexed view must be dropped.
Any user-defined functions referenced in the view must have been created with the SCHEMABINDING option as well.
Any functions referenced in an indexed view must be deterministic; deterministic functions return the same value each time they’re invoked with the same arguments.
A column can not be referenced twice in the SELECT statement unless all references, or all but one reference, to the column are made in a complex expression.
Illegal:
SELECT qty, orderid, qty
Legal:
SELECT qty, orderid, SUM(qty)
You can’t use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, or COMPUTE BY.
The AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP aggregate functions aren’t allowed in the SELECT statement.
A SUM() that references a nullable expression isn’t allowed.
CONTAINS and FREETEXT aren’t allowed in the SELECT statement.
If you use GROUP BY, you can’t use HAVING, ROLLUP, or CUBE, and you must use COUNT_BIG() in the select list.
You can’t modify more than one table at a time through a view.
If your view is based on aggregate functions, you can’t use it to modify data.
If your view is based on a table that contains fields that don’t allow null values yet your view doesn’t display those fields, then you won’t be able to insert new data.
Reference : Pinal Dave (https://blog.sqlauthority.com)
26 Comments. Leave new
i have a question .
is the view more fast than the SELECT stat in the SP. ???
or both have the same performance. ???
Hi Dave,
do you have any idea why this error would happen?
Cannot create index on view “viewName” because function “functionName” referenced by the view performs user or system data access.
Great article.
I think you however miss the simplistic usage for Views.
They are very good for ‘canned’ reporting.
Anyone trying to use a View to update data is missing the entire point of relational databases.
Pulling that ‘canned’ data into a DataGrid without having to code extra lines for a StoredProc…
There seem to be many errors in this article, for example:
“You can’t use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, or COMPUTE BY.”
perhaps it should be rewritten so as not to confuse..
Many, many errors, not a good article I think
I wanted to know the Limitation of the view in sql server 2005 please
Hi,
I’m having kind of a dilema here designing my database and i would to know your opinion…
I have a table “People” where i store general info about employees, pacients, suppliers, etc… and for each entity i have a specific table that references the table “People”. And my dilema is since i will have about 2 millions of rows about pacients and every time i need they general info i will have to join with the people table that haves more or less 5 millions of rows, doest it make sense denormalize my database in this case? and have a people general info into every table that represents an entity like employees, suppliers, pacientes, … ?
the problem in that case is the data anomalies i could have an employee can in future or in present be a pacient too, and because of this i will have to validate inserts and updates…
can you advise me a better solution?
can a view in this case solve my problem?
Hello JP,
Creating an index on the ID column of Person table would be enough to solve any performance issue that you are suspecting. Otherwise partitioning is the way of least overhead to horizontly split this table.
Regards,
Pinal Dave
Hi Dave,
Thanks for you reply. I don’t know if i understood your last sentence, but, does it make sense to put all the fields of the People table into Pacient table so that i dont have to do an inner join?
Hi JP,
Because “Person” is a common entity that represents all other entities like employees, suppliers, patientes.
So either create a single table Person or create saperate table for each entity like employees, suppliers, patientes. If you are create saperate table for employees, suppliers, patientes, etc then you should not create a common table “Person” because it would cause duplicacy and redundancy.
Regards,
Pinal Dave
Hi Dave,
Yes it makes sense.
Thanks for your help,
Regards,
JP
what is the use of views?
@sarala – Basically, views allow us to create like a “virtual” table representing the data in one or more tables in an alternative way.
Note that this is NOT my definition but that based on the T-SQL command CREATE VIEW in msdn.com.
But it’s a good simple description.
They can be quite handy when say we don’t want to create further tables but would like to see a combination of selected data/fields in the same place :).
I tried update in view but I don’t understand .
Can you give me the query of update view with tables
Regards
Hari
Is it possible to change values in a table through a view?
Yes it possible. You can insert/update/delete rows using a view
“All the tables referenced by the view must be in the same database as the view.”
Can we use SYNONYM s to work with tables which are part of other database?
BEGIN TRY
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
— write view logic
END TRY
BEGIN CATCH
SELECT [Error_Line] = ERROR_LINE(),
[Error_Number] = ERROR_NUMBER(),
[Error_Severity] = ERROR_SEVERITY(),
[Error_State] = ERROR_STATE()
SELECT [Error_Message] = ERROR_MESSAGE()
END CATCH
But It give error ” Incorrcet Syntax near keyword ‘BEGIN’
i am getting error “Error near identifier BEGIN. Expecting TRY. ” in following query .
BEGIN TRY
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
END TRY
BEGIN CATCH
SELECT [Error_Line] = ERROR_LINE(),
[Error_Number] = ERROR_NUMBER(),
[Error_Severity] = ERROR_SEVERITY(),
[Error_State] = ERROR_STATE()
SELECT [Error_Message] = ERROR_MESSAGE()
END CATCH
anyone knows solution….plz let me know..
Hi Surekha,
In which version of DB, you had executed.
I had tested in the sqlserver 2005, It’s working fine.
View is excuted properly but It is unble to save. and show “Incorrect syntax near keyword ‘Begin’ “
can u tell me the main disadvantages of views in sql server
@Gopi,
Disavange of View:-
1.When table is dropped or modified, view becomes inactive, it depends on the table objects.
2.Can not use DML operations on this.
thank u dinesh
I make use of views for three main purposes.
1. To “hold” frequently derived or calculated fields. There is no sound reason to create a column in a table to hold data that can always be derived from other data. If a calculation is going to be used routinely and is part of the core business intelligence then placing it in a view can ensure the calculation is done the same way each time and that when the business intelligence changes, the calculation is changed once and across the board.
2. To create a subset of a table. Think of an address table in a large database. One business area only uses addresses in Texas. For confidentially reasons, these people should only be able to see and update addresses they have the authority to modify. By giving them access to a view of only Texas addresses is one way to accomplish this. This is a very simplistic example, but there are times when slicing out a subset of records is needed.
3. To denormalize data. Normalizing your data to the 4th level is recommended. At times for reporting, this requires 6-7 joins to get to the fields needed. If this is a frequent issue, a view can present a denormalized look at these tables joined together.
Most of the time it is not just one of these issues that causes a view to be created. It is a combination of the three.
“All the tables referenced by the view must be in the same database as the view.”
this sentence make me confused!
we can create view in one database base on tables from other database even from a linked server.
I have some views which are working well.
select table1.f1, table2.f2
from db1.dbo.table1
inner join db2.dbo.table2