Views and temporary tables

I had nothing to do this morning. I suddenly wanted to write views and temporary tables as follows:

View:

View definition: A table exported from one or more tables (or views.

The view and table are different: the view is a virtual table, that is, the data corresponding to the view is not actually stored, and the database only stores the view definition. When operating on the View data, the system operates the basic tables associated with the view according to the view definition.

In fact, the preceding section describes the essential features of a view.

A view is mainly used for system security, query, and efficiency. For example, you only want users to see certain fields in a table, some fields cannot be seen by the user. This is a good solution to the view, and can also be implemented during select. In terms of queries, complicated queries can greatly reduce the trouble of frequently writing SQL statements. At the same time, in terms of efficiency, data is distributed across multiple servers, and views will certainly bring efficiency benefits.

 

Temporary table:

Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb. They are automatically deleted when they are no longer used.
Temporary tables can be local or global. They differ in terms of name, visibility, and availability. The name of the local temporary table starts with a single digit (#). They are only visible to the current user connection and are deleted when the user is disconnected from the SQL Server instance. The name of the global temporary table starts with two numeric symbols (#). After being created, the table is visible to all users. When all users that reference the table are disconnected from SQL Server, the table is deleted.

For example, if you have created an employees table, you can use the table unless it has been deleted. If a local temporary table # employees is created for a database session, only the table can be used by the session. After the session is disconnected, the table is deleted. If the # employees global temporary table is created, any user in the database can use the table. If the table is not used by other users after you create it, delete it when you disconnect it. If another user is using the table after you create the table, SQL Server will delete the table when you disconnect it and all other sessions no longer use it.
Temporary tables are mainly used to improve efficiency.

 

Differences:
Space allocation: physical space allocation is different. Try not to allocate space, Space is allocated to the temporary table.
Real-world: a view is a pre-compiled SQL statement that does not save actual data. A temporary table is the actual table saved in tempdb. That is, a view is a snapshot, a virtual table, and a temporary table is an objective object of the table type. They have a table and a snapshot structure. You can think of a view as a shortcut to join a table.