why we need cursor in sql server

Question

why we need cursor why we need view why we need procedure

Dharmesh Singh · Answer

One of the main benefits of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less. Since if we send the SQL query (statement) which is executing in a loop to the server through network and the network gets disconnected then the execution of the SQL statement doesn't return the expected results if the SQL query is not used between Transaction statement and rollback statement is not used. Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time. It helps in re usability of the SQL code because it can be used by multiple users and by multiple clients since we need to just call the stored procedure instead of writing the same SQL statement every time. It helps in reducing the development time. Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure. Sometimes it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.

Dharmesh Singh · Answer

down vote accepted A view provides several benefits. 1. Views can hide complexity If you have a query that requires joining several tables or has complex logic or calculations you can code all that logic into a view then select from the view just like you would a table. 2. Views can be used as a security mechanism A view can select certain columns and/or rows from a table and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see. 3. Views can simplify supporting legacy code If you need to refactor a table that would break a lot of code you can replace the table with a view of the same name. The view provides the exact same schema as the original table while the actual schema has changed. This keeps the legacy code that references the table from breaking allowing you to change the legacy code at your leisure. These are just some of the many examples of how views can be useful

Dharmesh Singh · Answer

Cursors are a mechanism to explicitly enumerate through the rows of a result set rather than retrieving it as such. However while they may be more comfortable to use for programmers accustomed to writing While Not RS.EOF Do ...  they are typically a thing to be avoided within SQL Server stored procedures if at all possible -- if you can write a query without the use of cursors you give the optimizer a much better chance to find a fast way to implement it. In all honesty I've never found a realistic use case for a cursor that couldn't be avoided with the exception of a few administrative tasks such as looping over all indexes in the catalog and rebuilding them. I suppose they might have some uses in report generation or mail merges but it's probably more efficient to do the cursor-like work in an application that talks to the database letting the database engine do what it does best -- set manipulation.