Problem
What is the purpose of using a view instead of directly accessing a SQL Server table? In this tip, we will discuss various aspects of views in SQL Server and how they can make things much simpler and allow you to reuse code.
Solution
A view in SQL is a virtual table based on the result of a SELECT statement from one or more tables. It provides a way to simplify complex queries, hide data complexity, and provide security by restricting access to specific columns of a table.
Reasons for Using Views in SQL Server
There are a few reasons why you might choose to use views in SQL:
- Simplifying complex queries: Views allow you to encapsulate complex SELECT statements into a single, reusable object. It makes reading and writing queries easier and reduces the risk of making mistakes when writing complex queries.
- Hiding data complexity: Views allow you to abstract away the underlying data structure and present a simplified version of the data to the users. It can make it easier for non-technical users to work with the data and reduces the risk of them making mistakes due to a lack of understanding of the underlying data structure.
- Providing security: Views can be used to restrict access to specific table columns, which can help protect sensitive data. For example, you can create a view with only the columns a particular user can access.
- Improving performance: Using views can avoid repeating complex SELECT statements in multiple queries. It can help to improve query performance, as the view can be indexed, and the result can be cached and reused by subsequent queries.
- Enforcing data consistency: Views can enforce data consistency by providing a single, consistent view of the data. For example, you create a view that only includes data that a specific authority has approved or data within a particular date range.
Create View Statement
Here’s the basic syntax for creating a view in SQL:
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name -- Single Table WHERE [condition];
- CREATE VIEW is the keyword used to create a view in SQL.
- view_name is the name of the view that should be something logical based on the result set.
- AS separates the view name from the SELECT statement that defines the view.
- SELECT column1, column2, … specify the columns you want to include in the view. You can have one or multiple columns from one or multiple tables.
- FROM schema_name.table_name specifies the name of the schema and base table that you want to use as the source of data for the view.
- WHERE [condition] is an optional clause that allows you to specify a condition to filter the data that will be included in the view.
Once you have created a view, you can use it like any other table in SQL. You can query the view using SELECT statements, join it with other tables, or use it as the source of data for other views.
For example, suppose you have a table named employees containing information about company employees. In that case, you could create a view named employee_names that includes only the first_name and last_name from the employees table. Here is the SQL statement:
CREATE VIEW employee_names AS
SELECT first_name, last_name
FROM employees;
Now, you can use the employee_names view in your SQL queries like this. This query will return all the rows from the employee_names view, which contains only the first_name, last_name columns from the employees table.
SELECT * FROM employee_names;
It’s essential to remember that a view in SQL does not store data. Instead, it is just a SELECT statement executed each time you query the view. It means that the data in the view is always up-to-date with the data in the underlying table or tables. It is possible to update data via the view in certain circumstances, but not recommended. You should modify the data in the underlying table if you need to modify the data.
By using a SELECT statement with the AS keyword, you can create a view in SQL that replaces column names with new names. The syntax for creating a view with alias columns is shown in the following example, which can be considered the view definition:
CREATE VIEW orders_vw AS
SELECT order_id AS id, customer_name AS name, order_date AS date
FROM orders;
You can query the view like you would query a table but with the new column names. Note: By renaming columns in the view, this has no impact to the original column names in the table.
SQL View to Join Multiple Tables
Use the following syntax to create a SQL view that joins multiple tables:
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table1 JOIN table2 ON table1.column_name = table2.column_name JOIN table3 ON table2.column_name = table3.column_name WHERE condition;
In this syntax, replace view_name with the name you want to give your view, column1, column2, and so on with the names of the columns you want to SELECT FROM the tables, table1, table2, and table3 with the names of the tables you want to join, and column_name with the names of the columns that are used to join the tables. You can also add a WHERE clause to filter the results if necessary.
For example, we can create view [HumanResources].[dbo].[vEmployee] in the [AdventureWorks2019] database as follows:
CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[BusinessEntityID], p.[Title], p.[FirstName], p.[MiddleName], p.[LastName]
,p.[Suffix],e.[JobTitle], pp.[PhoneNumber], pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress], p.[EmailPromotion], a.[AddressLine1], a.[AddressLine2]
,a.[City],sp.[Name] AS [StateProvinceName], a.[PostalCode], cr.[Name] AS [CountryRegionName]
,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
LEFT OUTER JOIN [Person].[PersonPhone] pp ON pp.BusinessEntityID = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
LEFT OUTER JOIN [Person].[EmailAddress] ea ON p.[BusinessEntityID] = ea.[BusinessEntityID];
When you SELECT from the view it runs the SELECT query and returns the result set from the multiple tables defined in the view.
Create a View Using SQL Server Management Studio
You can create a view with the GUI in SQL Server Management Studio (SSMS) using the steps below.
Step 1
Open SSMS and connect to a database where you want to create the view.
Step 2
In Object Explorer, expand the database, right-click on the Views folder, and select “New View.”
Step 3
In the “Add Table” dialog box, select the tables you want to include in the view and click “Add.” For example, we highlighted two tables for the view as seen below.
Step 4
Use the drag-and-drop interface in SSMS to create the join between the tables, here we are joining on BusinessEntityID.
Step 5
Select the columns you want to include in the view from the tables and any additional calculated columns or expressions. You can view the SELECT statement query as shown below.
Step 6
To save the view, click Ctrl+S and give the view a name in the “Enter a name for the view” field and click “OK”.
Step 7
Right click on Views and select Refresh and you should now see the view under the Views folder in Object Explorer. This can now be used to run a SELECT statement like any other table.
Conclusion
In conclusion, views provide a convenient and powerful way to simplify complex queries, hide data complexity, and provide security by restricting access to specific table columns. With this knowledge, you can start using views in your SQL projects to improve your database’s readability, maintainability, and security.
Next Steps
- Explore Views in SQL Server and be familiar with the syntax, usage.
- Related articles about Views
- Read the Microsoft documentation on views.
- Check out these related tips:
