In SQL Server, a view is a virtual table with contents that you define by means of a query. It acts as a filter on the underlying table(s) and can be used to focus, simplify, and customize the glimpse each user has of the database. Additionally, views can enforce security mechanisms by enabling users to access data only through the view and not granting them permissions to directly access underlying base tables.

SQL Server supports three types of views: indexed views, partitioned views, and system views.

Indexed Views

An indexed view is a view that has a unique clustered index. The unique clustered index is stored in SQL Server, making the resulting data of the view stored just like a table. Indexed views dramatically improve the performance of queries that aggregate a large number of rows.

Partitioned Views

A partitioned view combines horizontally partitioned data from multiple tables across one or more servers. This allows the end user to see 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 example, the sys.fulltext_languages view provides a list of languages supported for full-text search in the SQL Server instance. 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. They are organized into subcategories such as Azure SQL Database Catalog Views, Object Catalog Views, Databases and Files Catalog Views, and Endpoints Catalog Views.

Compatibility Views

Compatibility views are a set of views that implement several of the system tables from older releases of SQL Server. They are retained for backward compatibility and should be avoided in new code.

Dynamic Management Views

Dynamic management views (DMV) and functions provide server state information that can help observe the health of a server instance, diagnose issues, and enhance performance. They are categorized into subcategories such as Always On Availability Groups, Dynamic Management Views and Functions, Common Language Runtime Related Dynamic Management Views, Database Related Dynamic Management Views, and Execution Related Dynamic Management Views and Functions.

Creating Views

To create a view, you can use the CREATE VIEW command by manually typing it in the Query Editor or by using SQL Server Management Studio (SSMS). There are some guidelines and restrictions to keep in mind:

• View names must be unique and cannot be the same as table names in the schema.
• Views cannot be created on temporary tables.
• Views cannot have a full-text index or contain the DEFAULT definition.
• Views cannot reference more than 1,024 columns.

SSMS can be used to browse system views and create user-defined views. Let’s take a look at an example using the AdventureWorks2019 sample database provided by Microsoft.

First, launch SSMS and open Object Explorer. Expand AdventureWorks2019 and observe the Views node below Tables. You can see system views and built-in views available with the AdventureWorks2019 database. Select any system catalog view, right-click the view name, and click “Select Top 1000 Rows” to see the results. You can also edit the auto-generated query to add an ORDER BY clause or view the view definition by right-clicking the view name and selecting “Script View as -> CREATE TO -> New Query Editor Window”.

To create your own view, right-click any view and click “New View”. SSMS provides a user-friendly interface where you can choose tables, select columns, and generate relationships based on respective keys in the tables. Once you have defined the view, you can execute the query to see the outcome. To save the view, click the Save button on the toolbar and provide an appropriate name.

While SSMS provides a convenient way to work with views, it may not always be the best choice for complex relationships or when working with multiple joins. In such cases, it is recommended to use the Query Editor and write your own code.

By understanding and utilizing views in SQL Server, you can effectively manage and customize your database to meet your specific needs.