DbSchema | SQL Server - How to Create a View?

SQL Server • 14-Jun-2023

by Malik Umair Sajjad

Introduction
Prerequisites
What is a View?
Usage of Views
Advantages and Limitations of Using a View
Restrictions on Using a View
Permissions Required for Creating a View
View Attributes
Creating a View in sqlcmd
Creating a View in DbSchema
Conclusion
References

Introduction

In __SQL Server, a view is a __virtual table that is based on the result set of a __SELECT statement. It contains rows and columns, similar to a real table. The __fields in a view are fields from one or more real tables in the database.

Prerequisites

To follow this tutorial, you should have:

__SQL` Server installed in your system.
Basic knowledge of __SQL syntax` and commands.
Access to an existing SQL Server database or permissions to create a new one.

For installation and establishing connection you can read our article SQL Server-How to create a database?

What is a View?

A view can be considered a virtual table, representing the result of a database query. While a table contains data and is stored physically, a view does not. It’s a set of instructions for the SQL Server engine on how to retrieve data when queried.

Usage of Views

Simplification of Complex Queries: Views can encapsulate complex SQL queries. Instead of writing a complicated query every time, you can create a view and select data from it.
Enhanced Security: You can provide users access to the view without granting them access to the entire database, thereby ensuring data security.
Data Abstraction: Views can present a different representation of the data than exists in the database. It allows users to focus on specific parts of the data.

Advantages and Limitations of Using a View

Advantages:

Data Protection: Views restrict data access. Users can only see the data they are authorized to see.
Data Abstraction: Views do not display how data is stored in the database.
Complexity Hiding: Views encapsulate the complexity of data. Users don’t need to understand complex queries.

Limitations:

Performance: Since views are virtual tables, every time a view is queried, its data is generated using the underlying tables, which can impact performance.
Update Restrictions: Not all views are updatable. Only simple views (those based on a single table) can be updated.

Restrictions on Using a View

You cannot create a view on temporary tables or tables with schema binding.
Certain functions, like SUM(), MAX(), etc., cannot be used with views.
A view cannot include a ORDER BY clause, unless there is also a TOP clause.

Permissions Required for Creating a View

To create a view in SQL Server, you must have the CREATE VIEW permission in the database and the ALTER SCHEMA permission on the schema in which the view is being created.

View Attributes

A CREATE VIEW statement can include the following attributes:

*Attribute*	*Description*
`ENCRYPTION`	Encrypts the entries in sys.syscomments that contain the text of the `CREATE VIEW` statement.
`SCHEMABINDING`	Binds the view to the schema of the underlying table or tables. If SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view.
`VIEW_METADATA`	Allows a user to see the metadata of the view, as long as the user has permissions on the view.

Creating a View in sqlcmd

Here’s how to create a view using sqlcmd:

Open SQL Server command prompt - sqlcmd.
Connect to your SQL Server instance using the following command:

sqlcmd -S servername -d dbname -U username -P password

Replace dbname with the name of your database, username with your username and password with your password.

Once connected, you can create a view using the CREATE VIEW statement. Here’s the general syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

For example, if you have a Customers table, and you want to create a view that shows all customers from the USA, you would write:

CREATE VIEW USA_Customers AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'USA';

To know more about creating a table you can read our article SQL Server-How to create a table?

Sample Database:

Customers Table:

*CustomerID*	*CustomerName*	*ContactName*	*Country*
`1`	Alfreds	Maria	Germany
`2`	Ana Trujillo	Ana	Mexico
`3`	Antonio	Antonio	Mexico
`4`	Around the Horn	Thomas	UK
`5`	Berglunds	Christina	Sweden
`6`	Bottom-Dollar	Elizabeth	Canada
`7`	B’s Beverages	Victoria	UK
`8`	Chop-suey	Yang	USA
`9`	Great Lakes	Howard	USA
`10`	Island Trading	Helen	UK

To confirm the view was created successfully, you can query it as follows:

SELECT * FROM USA_Customers;

Result from Query:

Following result will be obtained when we execute the above query on the sample database:

*CustomerName*	*ContactName*
`Chop-suey`	Yang
`Great Lakes`	Howard

The result is a table that only includes the customers from the USA, as specified in the view’s definition.

Creating a View in DbSchema

Here’s how to create a view using DbSchema:

Open DbSchema and connect to your database.
In the Schema panel, right-click on Views and select Create view.
In the new view panel, you can write your SELECT statement. Here’s an example:

SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'USA';

Click OK. Your view will appear under Views in the Schema panel.
You can right-click on the view and select Open in Editor to see its contents.

Visually Manage SQL Server using DbSchema

DbSchema is a SQL Server client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.

Conclusion

This article covered SQL Server views, their usage, advantages, limitations, and restrictions. We also discussed how to create a view in sqlcmd and DbSchema. It’s important to remember that views are not physically present and act as a layer of abstraction over the data stored in your database tables.