SQL Advance Concept

View in SQL

In SQL (Structured Query Language), a View is a virtual table based on the SQL query. It contain rows and columns, just like real tables in database. But the Fields in Views are taken from the real tables of the database. In view we can add different functions of SQL like, WHERE, JOIN etc. to view the selective data from tables.

Why VIEW is used?

SQL View makes user work simple and flexible. By the use of view the security of the query is increased.

For an example, Database named PersonDetail in a sql there is two table in it i.e. active_person and inactive_person. Active_person table consists of active person list and inactive_person is consists of inactive persons list.

So if we want to select the active person list inactive person from both the tables, so if we write a simple select query then it will take long time and we have write long query each time whenever we want to execute the query. But if we create a view then it will store the select statement inside it and just by the name of the view we can execute the query.

View Syntax:

{`
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
`}

• Where view_name is the name of the view which we want to create.
• The SELECT statement is used for selecting the data from the table.
• For Example: to create a view on the person table the sql query would be like
• Person table is consists of PersonID, PersonName, Address, Phone_No.
• SQL Statement to create View:
• CREATE VIEW view_person AS
• SELECT of PersonID, PersonName FROM person;

Benefits of using SQL View:

• By the use of View the database queries become simple.
• The complexity of the database are hidden.
• The queries of the views may not change by the change in the tables, so its flexibility increased.
• Security is increased, the sensitive information can be executed from a view.

TEMP Tables in SQL:

TEMP Table is nothing but the Temporary table created in SQL Temporary Tables are great features that lets us to process the intermediate result by using selection, update and join compatibility that we can use with typical SQL Server tables. In some cases it is very important to store the temporary data. The most important thing about the temporary table is that it will be deleted when the current user session is terminated.

The Local Temporary Table i.e. starting with #, Global Temporary table I.e. starting with ##, Persistence Temporary Table which is prefixed by TempDB and table variables stating with @.

TEMP Tables Example:

Local Temporary Table

{`
CREATE TABLE #person
(
PersonID INT,
Personname VARCHAR (32)
)
Global Temporary Table
CREATE TABLE ##person
(
PersonID INT,
Personname VARCHAR (32)
)
`}

Table Variables

Table Variable is created in memory and so performed better Local temp table. It is the only way that we can use DML statement i.e. INSERT, Delete etc. on temporary data within user-defined function. It is used within the scope or batch within which they are defined.

{`
DECLARE @people TABLE
(
id INT,
name VARCHAR(32)
)
`}