What is the relationship between SQL stored procedures and views?

Both are SQL data objects, and are similar in writing. The most important thing is that the storage and running processes on the server are almost the same, both of which are stored in the SQL statement set and are compiled before running, that is, you do not need to re-compile each time, which can greatly improve the execution efficiency.

As the name suggests, the difference lies in "process" and "Diagram ".
That is, the stored procedure involves a lot of data processing, and the whole process is a complicated process. It can receive parameters, which is equivalent to a function. The main purpose is to process data.

A view combines existing data into a new form, which is equivalent to a virtual table. It can be used as a table for query at run time (adding, deleting, modifying, and deleting data is not acceptable ). It is used to present data.

In fact, the internal implementation of the two is basically the same. When developing these objects, the provider will apply them for different purposes.
When we use the same thing, we can implement a variety of methods, not to say that there is only one line, but other methods won't work. What is used for implementation? This requires a detailed analysis of the actual situation to see how to achieve convenience and how to achieve efficiency.

The following are some materials:
Stored Procedure

Stored Procedure is a set of SQL statements for specific functions. It is compiled and stored in the database. You can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. Stored procedures are an important object in databases. Any well-designed database application should use stored procedures. In general, stored procedures have the following advantages:

◆ Standard component programming is allowed for Stored Procedures

◆ Fast execution of Stored Procedures

◆ Stored procedures can reduce network traffic

◆ Stored procedures can be fully utilized as a security mechanism

The basic syntax is as follows:
The complete syntax rules are as follows:

Create proc [edure] procedure_name [; number]
[{@ Parameter data_type}
[Varying] [= default] [Output]
] [,... N]
[
{Recompile | encryption | recompile, encryption}]
[For replication]
As SQL _statement [... n]

SQL View

A view can be viewed as a virtual table or storage query. Data that can be accessed through views is not stored in the database as unique objects. The SELECT statement is stored in the database. The result set of the SELECT statement constitutes the virtual table returned by the view. You can use the method used to reference a table to use a virtual table in a Transact-SQL statement by referencing the view name. You can use a view to implement any or all of the following functions:

Limit the user to a specific row in the table.
For example, only employees are allowed to see the rows that record their work in the work tracking table.

Restrict users to specific columns.
For example, employees who are not responsible for payroll processing can only see the name column, office column, work phone column, and department column in the employee table, you cannot see any columns that contain salary information or personal information.

Join the columns in multiple tables to make them look like a table.

Aggregate information instead of providing detailed information.
For example, display the sum of a column, or the maximum and minimum values of a column.

Create a view by defining a SELECT statement to retrieve the data displayed in the view. The data table referenced by the SELECT statement is called the base table of the view. In the following example, the titleview in the pubs database is a view that selects data from three base tables to display the virtual tables that contain common data:

Create view titleview
As
Select title, au_ord, au_lname, price, ytd_sales, pub_id
From authors as
Join titleauthor as Ta on (A. au_id = TA. au_id)
Join titles as t on (T. title_id = TA. title_id )???

Then, you can reference titleview in the statement using the method used when referencing the table.

Select *
From titleview

One view can reference another view. For example, the information displayed by titleview is useful to managers, but the company usually only publishes financial figures for the current year in the quarterly or annual financial statements. You can create a view that contains all the titleview columns except au_ord and ytd_sales. With this new view, the customer can obtain a list of listed books without seeing financial information:

Create view cust_titleview
As
Select title, au_lname, price, pub_id
From titleview

[Do not understand the following. Please take a look at it later]

View can be used in multiple databases or Microsoft? SQL Server? 2000. Data is partitioned between instances. The partition view can be used to distribute database processing in the entire server group. Server groups have the same performance advantages as server aggregation and can be used to support the processing requirements of the largest web site or company data center. The original table is subdivided into multiple Member tables. Each member table contains the row subsets of the original table. Each member table can be placed in databases of different servers. Each server can also obtain a partition view. The partition view uses the transact-SQL Union operator to merge the selected results from all Member tables into a single result set. The behavior of the result set is exactly the same as that of the original table. For example, table partitioning is performed between three servers. Defined on the first Server
The following Partition View:

Create view partitionedview
Select *
From mydatabase. DBO. partitiontable1
Union all
Select *
From server2.mydatabase. DBO. partitiontable2
Union all
Select *
From server3.mydatabase. DBO. partitiontable3

Define a similar partition view on the other two servers. With these three views, any Transact-SQL statement that references partitionedview on the three servers will see the same behavior as the original table. It seems that each server has the same copy of the original table, but in fact each table has only one member table and partition view. For more information, see view User cases.

As long as the modifications only affect one of the base tables referenced by the view, you can update the views in all SQL Server versions (you can execute update, delete, or insert statements on them ).

-- Increase the prices for publisher '000000' by 0736.
Update titleview
Set price = price * 1.10
Where pub_id = '20140901'
Go

SQL Server 2000 supports more complex insert, update, and delete statements that can reference views. You can define the instead trigger on the view, specifying the individual updates that must be executed on the base table to support the insert, update, or delete statements. In addition, the partition view supports insert, udpate, and delete statements to modify multiple Member tables referenced by the view.

The index view is a feature of SQL Server 2000 that significantly improves the performance of complex view types, which are usually used in data warehouses or other decision support systems.

A view result set is usually not stored in a database. Therefore, a view is also called a virtual table. The view result set is dynamically included in the statement logic and generated at runtime. For more information, see view resolution.

Complex queries (such as queries in decision-making support systems) can reference a large number of rows in the base table and accumulate a large amount of information in relatively concise aggregation, such as sum or average. SQL Server 2000 supports creating clustered indexes on the view that executes such complex queries. When the create index statement is executed, the result set of the select view is permanently stored in the database. If the SQL statement references this view, the response time will be significantly shortened. Changes to basic data are automatically reflected in the view.

The create view statement of SQL Server 2000 supports the schemabinding option to prevent the table referenced by the view from being changed when the view is not adjusted. Schemabinding must be specified for any view that creates an index.

Example: Create a view
Create view titleview
As
Select ID, title, posttime, hits from [LIST]
Then reference select Top 100 * From titleview
Loop output data is faster than direct select Top 100 * from [LIST] by nearly 30 milliseconds!
Of course, here is just an example. Generally, you do not need to use a view for a single table.