DbSchema | SQL Server - How to Create a View?

Table of Contents

1. Introduction
2. Prerequisites
3. What is a View?
4. Usage of Views
5. Advantages and Limitations of Using a View
6. Restrictions on Using a View
7. Permissions Required for Creating a View
8. View Attributes
9. Creating a View in sqlcmd
10. Creating a View in DbSchema
11. Conclusion
12. 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:

1. SQL Server installed in your system.
2. Basic knowledge of SQL syntax and commands.
3. 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

1. 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.
2. Enhanced Security: You can provide users access to the view without granting them access to the entire database, thereby ensuring data security.
3. 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:

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

Limitations:

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

Restrictions on Using a View

1. You cannot create a view on temporary tables or tables with schema binding.
2. Certain functions, like SUM(), MAX(), etc., cannot be used with views.
3. 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:

Creating a View in sqlcmd

Here's how to create a view using sqlcmd:

1. Open SQL Server command prompt - sqlcmd.
2. 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.

3. 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:

4. 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:

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:

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

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

4. Click OK. Your view will appear under Views in the Schema panel.
5. 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.

References

1. SQL Server Views - Microsoft Docs
2. Create a View - Microsoft Docs
3. DbSchema Documentation