ometimes, how you present data is just as important as the data itself. One of the simplest presentation techniques is to sort the data in some meaningful way, but SQL Server Views don't support the ORDER BY clause. In this article, you'll learn how to sort the results of a View, despite this small limitation.
About ViewsThey allow users to see limited data while also restricting access to the actual tables, providing a pseudo-layer of security.
They enable you to present a simplified version of your data, which means your user really doesn't need to be familiar with your database structure to use your application.
They can increase performance when transferring data across a network.
The SQL Server View is one of SQL Server's simpler structures for displaying data. A View basically retrieves a limited recordset. You can retrieve all the data, but you'll rarely need to do so. Views also are one of SQL Server's more flexible tools because:
The View does have a few restrictions, however:
You can't use the ORDER BY clause.
You can't use parameters to pass criteria.
You can't use a View with temporary tables.
To create a View, use the CREATE VIEW statement in the form (Arguments enclosed in brackets, [ ], are optional.):
CREATE VIEW [databasename.][owner.]viewname[(column[,n])]
[WITH CHECK OPTION]
The WITH CHECK OPTION clause tracks changes to the data via the View to ensure that the View is still valid once the statement is executed. If a change invalidates the View, this option prohibits the change. Table 1 describes the CREATE VIEW arguments.
Table 1: CREATE VIEW Arguments
||Identifies the database that contains the data you're viewing
||Identifies the database owner, usually a three-letter prefix such as dbo
||Identifies the columns from which you're retrieving data (in selectstatement; generally used only if column is the result of an expression, function, or if two columns have the same name
||One of the following:
ENCRYPTION: Encrypts the view definition with the SQL Server system tables
SCHEMABINDING: Restricts modifications to the underlying table
VIEW_METADATA: Makes the View updateable
||A valid SQL statement that fully defines the View