In SQL Server, a view is a virtual table with contents that you define by means of a query. The structure of a view is similar to a table. The contents of a view comprise a set of named columns and rows of data, that are generated dynamically whenever the view is referenced.

Its typical purpose is to act as a filter on the underlying table(s). It can be used to focus, simplify, and customize the glimpse each user has of the database. A view can also enforce security mechanisms - by enabling users to access data only through the view and not granting them permissions to directly access underlying base tables of the view. It can also include columns from other views created in the same or a different database. A view can have a maximum of 1,024 columns. When created as an indexed view, a view will exist as a stored set of data values in a database.

Types of Views

SQL Server supports following three types of views: indexed views, partitioned views, and system views. Each of these is described below.

Indexed Views

An indexed view is a view that has a unique clustered index. The unique clustered index is stored in SQL Server. Thus, the resulting data of the view is stored just like a table. To index a view, you create a unique clustered index on it. One of the main benefits offered by indexed views is that they dramatically improve performance of queries that aggregate large number of rows.

Partitioned Views

A partitioned view combines horizontally partitioned data from multiple tables across one or more servers. The outcome is that the end user sees  the data as though it were gathered from one table.

System Views

System views are views that expose catalog metadata. They can be used to retrieve various kinds of information. For instance, if you want to know the list of languages supported for full text search in the SQL Server instance, you would use the sys.fulltext_languages view. Or if you want to know the list of constraints created across the database, you can use the sys.objects view which contains a row for each user-defined, schema-scoped object created within a database, including constraints.

System views are further categorized as catalog views, compatibility views, and dynamic management views (DMV).

Catalog views

Catalog views return information that the SQL Server Database Engine will make use of. Some of the subcategories into which catalog views in SQL Server have been organized include:

• Azure SQL Database Catalog Views
• Object Catalog Views
• Databases and Files Catalog Views
• Endpoints Catalog Views

There are other categories that are available for querying.

Compatibility views

Several of the system tables from older releases of SQL Server are now implemented as a set of views. These views are known as compatibility views. They are only retained for backward compatibility.

These views should be avoided in new code.

Dynamic management views

You can use dynamic management views (DMV) and functions to get server state information that can help you observe health of a server instance, diagnose issues if any, and enhance performance. Some of the sub- categories of dynamic management views and functions are:

• Always On Availability Groups Dynamic Management Views and Functions
• Common Language Runtime Related Dynamic Management Views
• Database Related Dynamic Management Views
• Execution Related Dynamic Management Views and Functions

There are many more categories that you can see on the main DMV page.

Approaches and Guidelines

You can create a view using the CREATE VIEW command by manually typing it in the Query Editor or by using SQL Server Management Studio (SSMS). Note the following guidelines and restrictions:

• View names must be unique and cannot be the same as the table names in the schema.
• You cannot create a view on temporary tables.
• A view cannot have a full-text index.
• A view cannot contain the DEFAULT definition.
• The CREATE VIEW statement can include the ORDER BY clause only if you use the TOP keyword.
• Views cannot reference more than 1,024 columns.

To improve productivity, database developers can make more use of SSMS for their database related tasks. Let's see how we can browse through system views and create user-defined views with SSMS.

For the purpose of this article, we will use the AdventureWorks2019 sample database provided by Microsoft. If you do not already have this database attached to your server instance, you can get it from here.

Once you've installed the database, you can browse through system views as follows. First, launch SSMS and open Object Explorer. Expand AdventureWorks2019. Observe the Views node below Tables as shown in Figure 1.

Figure 1

Expand the Views You can see system views and built-in views available with the AdventureWorks2019 database. Refer to Figure 2.

Figure 2 

Select any one system catalog view, say all_objects. Right-click the view name to display the shortcut menu as shown in Figure 3.

Figure 3 

Click the first option in the shortcut menu, Select Top 1000 Rows. A query script is auto-generated in the Query Editor. Click Execute to render the outcome as shown in Figure 4. Observe the output especially the type_desc column. You can see that stored procedures, views, functions, and so on are displayed as part of the results of this view.

Figure 4 

Editing Auto-Generated Queries of Views

You can also make your own edits to the auto-generated query of a view. For example, after getting the query for the view sys.objects in Figure 4, if you edit the query to add ORDER BY type, SQL Server sorts the results according to type. Refer to Figure 5. The sys.objects view shows rows for each user-defined, schema-scoped object created within a database, including constraints. Since the results are sorted, you will see CHECK_CONSTRAINTS at the top.

Figure 5 

View Definitions

So far so good. You displayed outcomes of system views and saw what they displayed. But what is the view definition of that view? Like, what are the statements that generated the view?

To see this using SSMS, right-click the corresponding view name, click Script View as -> CREATE TO -> New Query Editor Window. This will auto-generate the view definition in a new window. See Figures 6 and 7. They show the view creation statements for vEmployeeDepartment.

Figure 6

Figure 7 

Creating Your Own View

Now that you explored system catalog views and built-in database views, you'll want to experiment with creating your own view.

To create a new view of your own based on tables in the AdventureWorks2019 database, right-click any view and then, click New View. See Figure 8.

Figure 8

Now, the beauty of using SSMS here is that you can perform the entire process of creating and executing the view through menus and tool buttons. After you click New View, you'll see a Add Table dialog box where you can choose the tables for your view.  See Figure 9.

Figure 9 

Add the desired tables and select the appropriate columns as per your requirements and also generate relationships based on respective keys in the tables. See Figure 10 for example. For example, add and link the tables, Person.Person, Sales.SalesPerson, and Sales.SalesTerritory based on their respective primary keys.

Figure 10 

Observe the SQL code for this view in the lower section of the Query Designer in the SQL pane. See Figure 11.

Figure 11

See the outcome of the view by executing the query using Query Designer -> Execute SQL.

Figure 12

However, remember that you did not save the view yet, and hence, it exists only as a SQL statement right now.

To save it as a view, click Save button on the toolbar. The Choose Name dialog box prompts you for the view name. Give an appropriate name, such as vwSalesViewYTD. The vw prefix helps to indicate that this is a view.

Figure 13 

After you save the view, it can be seen in the Object Explorer, under the Views node.

Through this example, you created a view from multiple tables using only SSMS features.

Note that despite its many advantages, the View Designer may not always be the best choice to work with. It is ideal for small tables and views. If you intend to work with multiple joins with complex relationships or you find it difficult to decipher and process the auto-generated SQL scripts rendered by the View Designer, it is best to use the Query Editor and write your own code.