What are the differences between Stored Procedure and a Trigger

1. We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete, and update) is fired on the table on which the trigger is defined.
2. We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which the action (insert, delete, and update) defined within a trigger can initiate execution of another trigger defined on the same table or a different table.
3. Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.
4. Stored procedure can take input parameters, but we can't pass parameters as input to a trigger.
5. Stored procedures can return values but a trigger cannot return a value.
6. We can use Print commands inside a stored procedure for debugging purposes but we can't use print commands inside a trigger.
7. We can use transaction statements like begin transaction, commit transaction, and rollback inside a stored procedure but we can't use transaction statements inside a trigger.
8. We can call a stored procedure from the front end (.asp files, .aspx files, .ascx files, etc.) but we can't call a trigger from these files.
9. Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks. They can have parameters and return multiple results sets.
10. The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.

How to find the tables used in stored procedures ?

;WITH stored_procedures AS (
SELECT
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

What are the benefits of using Exists ?

Many times you're required to write query to determine if a record exists. Typically you use this to determine whether to insert or update a records. Using the EXISTS keyword is a great way to accomplish this.
Here's a simple example from the pubs database using EXISTS:

if EXISTS (select *
 from authors
 where au_id = '172-32-1176')
  Print 'Record exits - Update'
ELSE
  Print 'Record doesn''t exist - Insert'

The EXISTS function takes one parameter which is a SQL statement. If any records exist that match the criteria it returns true, otherwise it returns false. This gives you a clean, efficient way to write a stored procedure that does either an insert or update.
The other benefit of EXISTS is that once it finds a single record that matches it stops processing. This doesn't have a huge impact if you're checking on a primary key. It does have a big impact if you're checking for existance based on another field. Consider the following two queries:

if exists (select *
 from authors
 where state = 'ca')
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

if (select count(*)
 from authors
 where state = '172-32-1176') > 0
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

In the pubs database there are only 23 records in the authors table.  
Even with that small number of records, the IF EXISTS version runs 4 
times faster than selecting a count.  This is because it stops as soon 
as it finds a single record that matches the criteria.  The second 
statement must process all the rows that match.

 

What are the differenct types of cursors in SQL Server ?

Following are the different types of cursors in Sql Server:

• Base Table : These are the lowest level cursor available. These can scroll forward or backward with minimal cost, and can be updated.
• Static : Cursor can move to any record, but the changes on data can't be seen.
• Forward-Only : Cursor moves one step forward. Can't move backward
• Dynamic : Most resource extensive. Cursor can move anywhere and all the changes on the data can be seen
• Keyset-driven : Only updated data can be viewed, inserted and deleted data cannot be viewed.

What are the types of temporary tables and where are they stored in SQL Server ?

There are two types of temporary tables:

• Local temporary tables:
  • Only available to the current connection to the database for the current login
  • They are dropped when the connection is closed
• Global temporary tables:
  • Available to any connection upon their creation
  • They are dropped when the last connection using them is closed  
   

Local Temporary Table CREATE TABLE #table_name (
    column_name [DATATYPE] )  

Global Temporary Table CREATE TABLE ##table_name (
column_name [DATATYPE] )

What are the advantages of using Stored Procedures ?

Stored procedures have many advantages. They help you to separate the client application from the underlying structure of the database which allows you to simplify client coding, and improve the stability of the application. Furthermore, by using stored procedures, you can very easily create reusable code that can be executed from all of the applications that you and other developers write.

Stored procedures are very effective in terms of performance because they are pre-compiled and execute quickly. When you build a stored procedure, a query plan is created-which contains the most efficient method of executing the stored procedure given available indexes and other environmental factors. Another benefit of stored procedures is that they tend to lock data for shorter periods of time than the equivalent application code.

Stored procedures facilitate the security of data. This is because when you give users or groups rights to stored procedures, it is not necessary for you to give them rights to the underlying tables. A common scenario is to give users or groups view-only rights to tables. This way, they can build their own queries and reports. You then use stored procedures to add, edit, and delete data. Once you give users or groups the rights to the stored procedures, it is not necessary to give them add, edit, and delete rights to the underlying tables.

You can use stored procedures in either a two-tier or a three-tier application development model. In the two-tier model, the data and the stored procedures reside on a SQL Server. The application tier contains the GUI (graphical user interface) as well as the code that interacts with the tables, views, and stored procedures that reside on the SQL Server. In the three-tier model, the data and the stored procedures once again reside on the SQL Server. The difference is that the application tier contains only the GUI. The code that interacts with the data tier is located in a separate library, generally a Visual Basic DLL. We refer to this as the business logic tier. You can then use the business object, or DLL, with multiple applications. The three-tier model promotes reuse, and further separates the application from the data tier.

What is the difference between Stored Procedures and Functions

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won’t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

What is the difference between DELETE and TRUNCATE ?

1. TRUNCATE is a DDL command whereas DELETE is a DML command.
2. TRUNCATE is much faster than DELETE. Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3. You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
4. In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
5. You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause
6. TRUNCATE command resets the High Water Mark for the table but DELETE does not. So after TRUNCATE the operations on table are much faster.