What are the Different Types of Triggers | What is a View | What is the Difference between a Function and a Stored Procedure | What are the Advantages of Using Stored Procedures in SQL Server

What are the Different Types of Triggers?

After triggers and Instead of triggers 

After triggers are the triggers which are raised before the transaction committed or postback.

Instead of triggers are used to update the database tables which are associated with view instead of updating the view directly.

 

What is a View?

A view is virtual table based on result set of sql statement which contains rows and columns just like real table. The fields in view are fields from real tables in the databases.

Ex: Create VIEW [view_name] as select Column_Name from table_name where condtion

A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in underlying tables.

A view also serves as mechanism to simply query execution. Complex queries can be stored in the form as a view and data from the view can be extracted using simple queries 

What is the Difference between a Function and a Stored Procedure? 

Stored procedure returns one or more values where as function can return one value that is mandatory

Stored procedure can have input and output parameters where as function is having only input parameters.

Functions can be called from procedures but procedures cannot be called from functions

Exceptions can be handled by try-catch block in procedures whereas try-catch block cannot be used in function. (Read more Here)

What are the Advantages of Using Stored Procedures?

1) Stored Procedures reduce the network traffic and increase the performance 

2) Stored Procedures allows faster execution

3) Stored Procedures allows modular programming

4) Stored Procedures provides better security to our data

 

http://pramadha.com/

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s