Stored Procedures Against. Views

Question

I used both, but what I do not understand is when I should prefer one by one. I mean, I know that a stored procedure can take parameters ... but can we really still use the same thing, using Plums too, right?

Do you consider performance and other aspects when and why should I give preference to another?

Answer 1

Well, I would use a stored procedure to encapsulate code and control rights.

The view is not really encapsulation: it is a macro that expands. If you start joining presentations pretty soon, you will have terrible requests. Yes, they can be JOINED, but they should not ...

Considering that views are a tool that has its place (for example, indexed views), for example, saved procs.

Answer 2

Stored procedure: Stored procedures are precompiled database queries that increase the security, efficiency, and usability of the client / server application database. Developers define stored procedures in terms of input and output variables. They then compile the code on the database platform and make it available for use by developers for use in other environments such as web applications. All major database platforms including Oracle, SQL Server and MySQL support stored procedures. The main advantages of this technology are significant performance gains from precompiled execution, reduced client / server traffic, increased development efficiency with code reuse, and the abstraction and security controls inherent in granting users rights to certain stored procedures instead of base database tables.

Views: Database views allow you to create "virtual tables" that are generated "on the fly" when they are accessed. The view is saved to the database server as an SQL statement that retrieves data from one or more tables and (optionally) performs conversions on that data. Users can then request a view just like any real table database. Views are often used to facilitate giving users access to a specific type of database table without providing access to the main table itself.

Answer 3

The advantage of views is that they can be processed in exactly the same way as tables. You can use WHERE to get filtered data from them, JOIN in them, etc. You can even insert data into them if they are simple enough. Views also allow you to index your results, as opposed to stored procedures.

Answer 4

The main advantage of stored procedures is that they allow you to enable logic (scripting). This logic can be as simple as IF / ELSE or more complex, such as DO WHILE, SWITCH / CASE.

Answer 5

A View is like one saved query request, it cannot contain complex logic or several operators (except for using union, etc.). For any complex or custom parameter, you choose stored procedures that provide much more flexibility.

It is common practice to use a combination of views and stored procedures in a database architecture, and possibly for various reasons. Sometimes this allows for backward compatibility in sprocs when re-designing a scheme, sometimes in order to make data more manipulative compared to how it is initially stored in tables (de-noramlized views).

Heavy use of views can degrade performance because SQL Server makes it difficult to optimize these queries. However, you can use indexed views, which can actually improve performance when working with joins in the same way as indexed tables. There are much more stringent restrictions on the allowed syntax when implementing indexed views and a lot of subtleties in the process of their work depending on the release of SQL Server.

Think that views are more like tables than stored procedures.

Answer 6

I correlate the use of stored procedures with the need to send / receive transactions to and from the database. That is, whenever I need to send data to my database, I use a stored procedure. The same is true when I want to update data or query a database for information that will be used in my application.

Database views are great for use when you want to provide a subset of the fields from this table, allow MS Access users to view the data without the risk of changing them, and ensure that your reports collect the generated results.

Answer 7

Views are useful if there is a specific combination of tables or a subset of the data that you want to query sequentially, for example, the user associated with his permissions. In fact, views should be considered tables.

Stored procedures are fragments of sql code that are “compiled” as they are for execution more optimally than a random other query. The sql code execution plan in the stored procedure is already built, so the execution is slightly more smooth than a regular SQL query.

Answer 8

Saved procedure. Stored procedures are precompiled database queries that increase the security, efficiency, and usability of client / server database applications. Developers define a stored procedure in terms of input and output variables. They then compile the code on a database platform and make it available to application developers for use in other environments such as web applications. All major database platforms, including Oracle, SQL Server, and MySQL, support stored procedures. The main advantages of this technology are significant performance gains from precompiled execution, reduced client / server traffic, improved development efficiency from code reuse and abstraction, and security controls that are inherent to granting users rights to certain stored procedures rather than to base database tables.

Views: Database views allow you to create “virtual tables” that are generated on the fly when they are accessed. The view is stored on the database server as an SQL statement that retrieves data from one or more tables and (optionally) performs conversions on that data. Users can then request a view just like any real database table. Views are often used to alleviate security issues by giving users access to a specific view of a database table without providing access to the main table itself.

Answer 9

Two justifications.

Use a stored procedure instead of a view if you do not want insertion to be possible. Inserting into the view may not give what it seems to be doing. It will be inserted into the table, a row that may not correspond to the request from the view, a row that will not be displayed in the view; inserted somewhere, but not where it looks.

Use a view if you cannot use the result of a stored procedure from another stored procedure (I could never get the latter to work, at least with MySQL).

Answer 10

Relvars views containing tuples.

Stored procedures are scripts.