We recommend using Visual Studio 2017

Comparison of Queries and Views

Because queries and views have so many similarities, it is easy to overlook their differences. This section briefly compares queries and views.

Storage   Views are stored as part of a database design, but queries are not. As you design a database, you can include views in the design for the following reasons:

  • Some subsets of data are of interest to many users.   Because each view is stored in the database, it establishes a particular subset of data that can be used by any database user.

  • Views can conceal base tables.   You can disallow all user access to database tables, requiring users to manipulate data through views only. Such an approach can protect users and application programs from certain database modifications. For example, you can create a view called "Current Month Sales." On the first of each month, you can modify the view definition accordingly. Without such a view, users would each month need to rewrite their queries to select Sales rows from the appropriate month.

Updating results   The restrictions on updating result sets are different for views and queries. For more information, see Rules for Updating Results.

Sorting results   (Microsoft SQL Server only) You can sort any query result, but you can sort a view result only if the view includes the TOP clause. For more information about the TOP clause, see the SQL Server documentation. For more information about sorting, see Sorting and Grouping Query Results.

Query plan generation   A query plan is an internal strategy by which a database server tries to create result sets quickly. A database server can establish a query plan for a view as soon as the view is saved. For a query, however, a database server cannot establish a query plan until the query is actually run — that is, until the user explicitly demands the result set.

Parameterization   You can create parameters for a query, but not for a view. For more information about parameterized queries, see Querying with Parameters.

Show: