| Author: Harender 16 Oct 2008 | Member Level: Bronze | Rating:  Points: 2 |
Stored procedure are bunch of instructions. Execution of stored procedure is faster than query as they are precompiled. You can use SPs as per your requirement. You can call SPs from your program in almost similar way to queries.
Trigger are also set of instructions that are event-driven. Unlike stored procedures triggers cannot be called explicitly (or programmatically).
|
| Author: Kavitha 16 Oct 2008 | Member Level: Silver | Rating: Points: 3 |
Stored Procedures:
If we create stored procedure once, we can call and use it anywhere.
Stored procedures simplify Analysis Services database development and implementation by allowing common code to be developed once and stored in a single location.
Index:
Idex is like a index in a book.It is quickly find specific information in a table. It improves the performance of database.it can reduce the amount of data that must be return the query result.They are two types of index . one is clustered and another one is non-clustered.
For more details:
http://msdn.microsoft.com/en-us/library/ms188783.aspx
|
| Author: Bunty 16 Oct 2008 | Member Level: Diamond | Rating: Points: 2 |
Hi,
The code sample is a SQL Stored Procedure named prcDeductions that calculates the PF and PT of an employee. The employee is identified by his Employee Number.
Both PF and PT are OUTPUT variables here.
CREATE PROCEDURE prcDeductions(@Empno int,@PF money OUTPUT,@PT money OUTPUT)
As
Begin
Declare @Sal money
SELECT @Sal=sal from Emp
Where empno=@Empno
SET @PF=@Sal*0.12
SET @PT=@Sal*0.05
END
The below code is an example on how to execute the above procedure. Here, the output variables are passed along with the empno.
The SQL Procedure is executed with 'EXEC' clause.
Declare @VPF money,@VPT money
EXEC prcDeductions 1005,@VPF OUTPUT,@VPT OUTPUT
PRINT @VPF
PRINT @VPT
Regards
S.S.Bajoria
|
| Author: Bunty 16 Oct 2008 | Member Level: Diamond | Rating: Points: -20 |
Hi,
A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a sproc or SP) are actually stored in the database data dictionary
The stored procedures can be classified as:
User-Defined Procedures:
The user-defined stored procedure are created by users and stored in the current database.
System Stored Procedures:
The system stored procedures have names prefixed with sp_ .These primarily support various administrative tasks that help to manage SQL Server.The system databases,which store system stored procedures,are master and msdb databases.
Temporary Stored Procedures:
The temporary stored procedures have names prefixed with the # symbol.They are stored in the tempdb database and are automatically dropped when the client connection to server terminates.
Remote Stored Procedures:
The remote stored procedures are procedures that are created and stored in databases on remote servers.These remote stored procedures can be accessed from various servers,provided the users have the appropriate permissions.
Extended Stored Procedures:
These are Dynamic-Link Libraries (DLL's)that are executed outside the SQL Server environment.They are identified by the prefix xp_ .
Regards
S.S.Bajoria
|
| Author: Venkatesh Murali 17 Oct 2008 | Member Level: Gold | Rating: Points: -20 |
Triggers are powerful tools that can be used to enforce business rules automatically when data is modified. Triggers can extend the integrity checking logic of SQL Server constraints, defaults, and rules.Tables can have multiple triggers.Triggers contain Transact-SQL statements, much the same as stored procedures.
Triggers, like stored procedures, return the result set generated by any SELECT statements in the trigger. Including SELECT statements in triggers, except statements that only fill parameters, is not recommended
INDEX
An index in a database is a list of values in a table with the storage locations of rows in the table that contain each value.
In a database, an index allows the database program to find data in a table without scanning the entire table.
Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees.
An index contains an entry with one or more columns (the search key) from each row in a table.
When you design and create indexes, you should ensure that the performance benefits outweigh the extra cost in storage space and processing resources.
|