Friday, 25 December 2015

Difference between stored procedure and view in SQL Server

Stored Procedure
Stored procedures are precompiled database queries that improve the security, efficiency and usability of database client/server applications. Developers specify a stored procedure in terms of input and output variables. They then compile the code on the database platform and make it available to application developers for use in other environments, such as web applications. All of the major database platforms, including Oracle, SQL Server and MySQL support stored procedures. The major benefits of this technology are the substantial performance gains from precompiled execution, the reduction of client/server traffic, development efficiency gains from code reuse and abstraction and the security controls inherent in granting users permissions on specific stored procedures instead of the underlying database tables.

Database views allow you to create "virtual tables" that are generated on the fly when they are accessed. A view is stored on the database server as an SQL statement that pulls data from one or more tables and (optionally) performs transformations on that data. Users may then query the view just as they would any real database table. Views are often used to alleviate security concerns by providing users with access to a certain view of a database table without providing access to the underlying table itself.

Stored Procedure
Does not accepts parameters
Accept parameters
Can be used as a building block in large query.
Cannot be used as a building block in large query.
Can contain only one single Select query.
Can contain several statement like if, else, loop etc.
Cannot perform modification to any table.
Can perform modification to one or several tables.
Can be used (sometimes) as the target for Insert, update, delete queries.
Cannot be used as the target for Insert, update, and delete queries.

No comments:

Post a Comment