Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
SQL Server Views
2.1.
Uses of Views
3.
Types of Views
4.
Creating Views
4.1.
Creating a view with T-SQL
4.2.
Create View using SQL Server Management Studio
5.
Benefits of Views
6.
Frequently Asked Questions
6.1.
Are views preferable to tables?
6.2.
What kinds of views are there in SQL?
6.3.
When should a view not be used?
6.4.
What are the drawbacks of view?
7.
Conclusion
Last Updated: Mar 27, 2024

Views in SQL Server

Author Palak Mishra
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

The views in the SQL server are definitions built on top of other tables, which sets them apart from tables (or views). If there are any changes to the underlying table, the views will also reflect those changes.
This article will discuss the views in SQL Server, Uses, Types, and how to create views in SQL Server.

SQL Server Views

A database object with no values is called views in SQL Server. It is a virtual table that is created by a SQL query's result set. It does, however, resemble a real table with rows and columns.
Its contents are therefore based on the base table, as we can say. Although it doesn't have any data of its own, it functions like the base table. Like tables, its name is always distinct. 

                                                  SQL Views

                                                                                                                Image Source

Uses of Views

Implementing the security mechanism is the primary function of views in SQL Server. Specific columns and rows in tables are hidden to put it another way, from users by this. 
When a view in Sql Server is created, only the declared query is returned. The user is completely kept in the dark about the remaining information.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Types of Views

System-defined views and user-defined views are two different types of views that can be created in SQL Server. These views can be used to define the views in a way that gets all the fundamental information, including domain usage, checking constraints, getting columns, assemblies, objects, and columns along with the necessary user-defined views.

We have two different types of views in SQL Server.

  • System Defined Views: Predefined Views already present in the SQL Server Master database are known as system-defined Views. Additionally, all newly created databases use these as their default template views. Any user-defined database will automatically receive these system views attached.
     
  • The user-defined databases available in the SQL server instance are listed in the "sys.databases" view, which returns information about them. The system-defined views will expose the database's metadata and can be used to obtain all relevant information about the SQL Server instance or the objects, columns, and objects that the database contains.


The system-defined view categories that we have are as follows.

  1. Information Schema View: Twenty different schema views are available in SQL Server. These are employed to display data in databases as tables and columns. Information Schema is the first character in this view, followed by the view name.
     
  2. Catalog View: With SQL Server 2005, catalogue views were unveiled. These are used to display database self-descriptive data.
     
  3. Dynamic Management View: SQL Server 2005 introduced Dynamic Management Views. These views provide the SQL  Server machine's current state and database information to the administrator. The administrator can analyse issues and tune the server for peak performance with these values. There are two kinds of these.     
  • Server-Scoped Dynamic Management View:
    Only the Master database houses these.
  • Database-Scoped Dynamic Management View:
    Every database contains these.

 

User-Defined Views: Users establish these view categories. Users can define two different types of views.

  1. Simple View: A simple view is created on a single table.
    We can insert, update, and delete data in the simple view. A simple view can only be used to insert data if it has a primary key and all of its fields are not null.
     
  2. Complex View: A complex view contains data from multiple tables.
    Only a complex view's data can be updated. Data cannot be added to a complex view.

Creating Views

We can create a view in SQL Server primarily in two ways:

  • T-SQL Query Usage
  • SQL Server Management Studio usage

Let's go over each method in more detail.

Creating a view with T-SQL

The T-SQL syntax to create a new view is as follows.

Syntax: 
CREATE VIEW <schema_name>.<view_name> 
AS
    SELECT column1, column2,column3, ...
    FROM table1, table2,table3...
    [WHERE];


The following T-SQL script creates a new view named StudentAddress in the database.

Example: Create a View 
CREATE VIEW dbo.StudentAddress  
    AS 
    SELECT stu.FirstName, stu.LastName, stu.AdmissDate, addr.Address 
    FROM Student stu JOIN Address addr 
    on stu.StudentID = addr.StudentID;
   


The StudentAddress view is created in the example mentioned above with the query written after the AS keyword. It includes the records that its query produced.

Create View using SQL Server Management Studio

Step 1: To connect to the database instance, launch SSMS.


Step 2: Expand the database where you want to create a view in Object Explorer.


Step 3: As shown below, right-click on the Views folder and choose New View

                                                                                      Views Folder in Object Explorer 

                                                                                           Image source
 

Step 4: From one of the tabs, Tables, Views, Functions, and Synonyms, in the "Add Table" dialogue box, choose one or more tables, existing views, functions, or synonyms to be used to create a new view. We will now include the Employee and Address tables here. To end it, press the Close button.

                                                                       Add Table Dialogue Box

Image Sorce

Step 5: Pick the columns or other components you want to include in the new view in the diagram pane.

                                                   Picking Columns from different table

 Image Sorce

Step 6: Pick a sort or filter criteria in the Criteria pane.

                                                 Filter Criteria

                                                                                                Image Source


Step 7: Select Save view name under the File menu.

 

                                                                                   Saving View Name

 Image Source


Step 8: Type a name for the new view in the Choose name dialogue box. Hit "OK."

 

                                                                                 Choose name dialogue box

Image Source

 

Refresh the Views folder in the database instance of Object Explorer to see the new view.

                                                                                       Database Instance of Object Explorer

Image Source

 

Benefits of Views

  • Security: A user's access to stored data can be restricted by allowing them to access the database only through a limited number of views that contain the specific data they are permitted to see.
     
  • A Simple Query: Multi-table queries against a view become single-table queries because the view can combine data from multiple tables into a single table and display it.
     
  • Structured clarity: By presenting the database as a collection of virtual tables that make sense to the user, views can provide a "personalised" view of the database structure to the user.
     
  • Consistency: Even if the underlying source tables are split, restructured, or given new names, a view can still display a consistent, unaltered representation of the database's structure.
     
  • Data Reliability: The DBMS can automatically check the data to make sure it complies with the specified integrity constraints if it is accessed and entered through a view
     
  • Independent data in logic: The application and database tables can become somewhat independent thanks to views. The application must be based on a table if there is no view. With the help of the view, the program can be set up in the context of the previous view, allowing the programme and a database table to be separated.

 

Frequently Asked Questions

Are views preferable to tables?

A view is simply a SELECT statement that has been recorded in the database, whereas a table contains data (more or less, depending on your database). A view's ability to combine data from various tables and produce a new view of it is a benefit.

What kinds of views are there in SQL?

Information Schema, Catalog View, and Dynamic Management View are the three different types of system-defined views.

When should a view not be used?

Another drawback of this viewpoint is that it makes the master and slave slightly behind one another when using MySQL Replicator. Activate this post's status. Calculated columns should not be present in a database table, but they are allowed in a database view.

What are the drawbacks of view?

Although views have many benefits, the performance loss is the main drawback of using views rather than actual tables. The query processor must convert queries against views into queries against the underlying source tables because views only create the appearance of tables, not actual tables.

Conclusion

In this blog, we have extensively discussed views and how to manage views in a SQL server. A view in SQL Server is a virtual table whose values are determined by a query. A view's rows and columns are drawn from the tables that a query refers to.

We hope this blog has helped you enhance your knowledge regarding Views in SQL Server. If you want to learn more about the database, check out the excellent content on the Coding Ninjas:

  1. Top 100 SQL Problems
  2. SQL Databases
  3. Microsoft SQL
  4. Database Management System
     

You can refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and AlgorithmsCompetitive ProgrammingJavaScriptSystem Design, and many more! If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio! But if you have just started your learning process and are looking for questions asked by tech giants like Amazon, Microsoft, Uber, etc., you must look at the problemsinterview experiences, and interview bundle for placement preparations.

Nevertheless, you may consider our paid courses to give your career an edge over others!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!

Previous article
SQL Server Basics
Next article
SQL Server Indexes
Live masterclass