Technology articles, eBooks and courses - SQL Server - Databases - Azure - Python - Programming - TechHowTos

How to Create Database Views in SQL Server and what are their Benefits

by
Share this article!

Hi friends! In previous posts, you’ve learned how to create databases in SQL Server, as well as, how to create and alter tables. In this post, you will learn how to easily create database views in SQL Server and see relevant examples.

What is a Database View?

A database view, is an entity, which in plain words, is the result of a query against the database. Therefore, this entity consists of  a name (i.e. the view name), and a query. Whenever you call the database view by its name, it returns the results of its associated query.

 

How to Create a View in SQL Server?

The easiest way to create a database view in SQL Server, is to use the below syntax:

CREATE VIEW [view_name] AS
SELECT_Statement_Goes_Here;

 

So, if for example, you want to create a database view named “vProductInfo” that queries two tables named “tblProducts” and “tblProductPrices” in SQL Server and selects the columns “code” and “descr” from the first table, and “price” from the second table, this is how you could define it in SQL Server:

CREATE VIEW vProductInfo AS
SELECT p.code, p.descr, r.price
FROM tblProducts p INNER JOIN tblProductPrices r on p.ID=r.ProductID;

 

Tip: Note that the definition of a database view in SQL Server, always ends with a semicolon (;)

 

How to Call a Database View in SQL Server?

Now that we have created our database view named “vProductInfo“, it is time to call it.

It is very easy to call a database view, since you just need to use it in your query like a table.

Therefore, using a SELECT statement, you can call the database view.

Let’s see the relevant example below, based on this post’s scenario:

SELECT * FROM vProductInfo;
GO

 

In addition to the above example, you can also use the view’s columns in a WHERE clause in your SQL query. Here’s a relevant example:

SELECT * 
FROM vProductInfo
WHERE code='ProductCode1';
GO

 

The Benefits of Database Views

The benefits of using database views are many. Below, we list the main benefits:

  • You can simplify the complexity of data (i.e. hide complex queries and joins using a view)
  • You can apply more granular access controls in SQL Server (i.e. grant access only to specific views)
  • You write more module SQL scripts and you apply the programming principle of code reuse

 

 

Learn More about SQL Server – Enroll to the Course!

Enroll to our online course on Udemy, titled “SQL Server Fundamentals – SQL Database for Beginners” and get the guidance you need, in order to get started with SQL Server on both Windows and Linux in no time!

SQL Server Fundamentals - SQL Database for Beginners (Online Course)
(Lifetime Access, Downloadable Resources, Certificate of Completion and more!)

Enroll Now!

 

 

Featured Online Courses:

 

Read Also:

 

Featured Database Software Tool

SQLNetHub's Snippets Generator

SQLNetHub’s Snippets Generator 

Create and modify T-SQL code snippets for use in SQL Server Management Studio, fast, easy and efficiently.

Learn More

 

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: TechHowTos.com (https://www.techhowtos.com)

© TechHowTos.com

 



Views: 685