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 (http://blog.SQLAuthority.com)
[...] Aug 12th, 2007 by pinaldave Following error is encountered when view is attempted to created with ORDER BY clause in it. ORDER BY clause is not allowed in views in SQL Server 2005. This solution also displays the workaround to use ORDER BY in VIEW. I really do not prefer to use views. My views on SQL Views read it SQL SERVER – Restrictions of Views – T SQL View Limitations. [...]
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..
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?
“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?