SQL SERVER – Restrictions of Views – T SQL View Limitations

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 http://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)

About these ads

27 thoughts on “SQL SERVER – Restrictions of Views – T SQL View Limitations

  1. Pingback: SQL SERVER - Fix : ERROR : Msg 1033, Level 15, State 1 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. Journey to SQL Authority with Pina

  2. 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.

  3. 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…

  4. 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..

  5. 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?

  6. 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?

  7. 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

  8. @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 :).

  9. I tried update in view but I don’t understand .

    Can you give me the query of update view with tables

    Regards
    Hari

  10. 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..

  11. 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.

  12. “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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s