SQL Server review-views and stored procedures

View

A view is actually a query result of one or more tables. The view displays data in a centralized manner, facilitating your query.

Advantages of a view:

1. Data Confidentiality

2. Simplified Query

3. Ensure logical independence of data

Disadvantages of the View:

Updating views is to update the data of basic tables. Some views cannot update data.

1. View with union and other set operations

2. View with group by clause

3. view using functions such as AVG, sum, and Max

4. View with distinct keyword

5. Connecting table View

Common Operations:

Add create view v_name

Modify clter view v_name

Delete drop view v_name

View the view definition exec sp_helptext 'v _ name'

To view the view, see exec sp_depends 'v _ name'

-------------------------------------------------------------------------------

Stored Procedure

Compile and save it in the database

Stored Procedure category:

1. system stored procedures

2. Local stored procedures

3. Temporary stored procedures

A. Local temporary stored procedure (# Name of the first character) can be called only by the currently connected user

B. Global temporary stored procedure (# Name of the START) can be called by all connected users

4. Remote Stored Procedure

5. Extended Stored Procedures (using external language extensions, such as DLL)

Advantages of stored procedures:

1. LocalCodePre-compilation and caching for high-performance data operations

2. The Stored Procedure contains the business logic. When the business changes, you do not need to modify the client.

3. database security can be improved through isolation and Encryption

-------------------------------------------------------------------------------

Comparison of stored procedures and views

1. You can execute a series of SQL statements in a single stored procedure. The view can only be called in select.

2. The view cannot receive parameters and can only return result sets. The stored procedure can accept parameters and return the rest or multiple sets.

3. Views can be called during stored procedures