|
MAKE FULL WEB APPLICATION WITH JUST SQL KNOWLEDGE? CLICK HERE
SQL Server implements client/server technology. A
number of clients send queries to the central server. The server,
after receiving the query request, parses it for syntax errors and
process the request. Since the query passes from the client to the
server through the network, it adds to the network traffic. Hence
an increase in the number of queries from the clients leads to network
congestion and load on the server. A Stored Procedures is
a solution to all the above stated problems.
A stored procedures is a collection or batch of T-SQL
statements and control-of-flow language that is stored
under one name, and executed as a single unit. It helps in improving
the perfomance of a query.
BENEFITS OF STORED PROCEDURES
A stored procedure is a precompiled object. This means that a
procedures is compiled beforehand and is readily available for the
various applications to execute. Therefore, no time is spent at
all in parsing and compiling the procedures again.
Stored procedures offer the following benefits:-
| (1) Improved Performance - Applications
do not have to collect information procedure over and over again. |
| |
| (2) Reduction In Network Congestion - Applications
need not submit multiple T-SQL statements to the server for
the purpose of processing. |
| |
| (3) Enhanced Accuracy - T-SQL, statements
included in a procedures are designed by experienced programmers
and are, therefore, more efficient, error-free and tested. |
| |
| (4) Better Consistency - The coding logic
and T-SQL statements defined in the procedures are uniformly
implemented across all the applications as the procedures serves
as the single point of control. |
| |
| (5) Better Security Mechanism - Users can
be granted permission to execute a stored procedures even if
they do not own the procedures. |
TYPES OF PROCEDURES
The stored procedures can be classified as :-
(1) User-Defined Stored Procedures.
(2) System Stored Procedures.
(3) Temporary Stored Procedures.
(4) Remote Stored Procedures.
(5) Extended Stored Procedures
| (1) User-Defined Stored Procedures |
| User-Defined Stored Procedures
are created and stored in the current database. |
| (2) System Stored Procedures |
System Stored Procedures have
name prefixed with sp_ . These primarily support various administrative
tasks that help manage
the SQL server. System stored procedures are stored in
the system database and are accessible to the users of all the
databases. |
| (3) Temporary Stored Procedures |
| Temporary Stored Procedures have
names prefixed with the # symbol. They are stored in the TEMPDB
database and are automatically droped when the connection
terminates. |
| (4) Remote Stored Procedures. |
Remote Stored Procedures are procedures
that are created and stored in databases on remote servers,provided
the users have
the appropriate permissions. |
|
(5) Extended Stored Procedures
|
|
These are dIIs that are executed outside the SQL server environment.
They are identified by prefix xp..........
Identify the object that can be used to solve the problems.
|
If you don't find what you are looking for. Please click
here to submit your query, our experts will reply soon.
|
