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.
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.
CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
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 @.
Local Temporary Table
CREATE TABLE #person ( PersonID INT, Personname VARCHAR (32) ) Global Temporary Table CREATE TABLE ##person ( PersonID INT, Personname VARCHAR (32) )
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) )