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.
-
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.
-
Catalog View: With SQL Server 2005, catalogue views were unveiled. These are used to display database self-descriptive data.
- 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.
-
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.
-
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
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.
Image Sorce
Step 5: Pick the columns or other components you want to include in the new view in the diagram pane.
Image Sorce
Step 6: Pick a sort or filter criteria in the Criteria pane.
Image Source
Step 7: Select Save view name under the File menu.
Image Source
Step 8: Type a name for the new view in the Choose name dialogue box. Hit "OK."
Image Source
Refresh the Views folder in the database instance of Object Explorer to see the new view.
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:
- Top 100 SQL Problems
- SQL Databases
- Microsoft SQL
-
Database Management System
You can refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and Algorithms, Competitive Programming, JavaScript, System 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 problems, interview 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!
8+ registered 