We know that stored procedures are special database objects which are stored in database and can be invoked or executed as and when needed.

But, where these stored procedures are stored in database? And how are the stored procedures stored?

In this article we will discuss in detail about how and where stored procedures are saved.

When stored procedures are compiled, these stored procedures are saved in the following 3 system tables:

Sysdepends: This table will contain all the database objects which are referred or used in stored procedure like tables,columns,functions,Stored procedures, etc.,

Number of records inserted in this table for each stored procedure depends on number of dependent objects in that stored procedure.

Sysobjects : When ever any database object is created in the database an entry will be made in this table. Since Stored procedure is also a database object, this table will have an entry for this as well.

Syscomments : Text column in this table will hold the complete structure of stored procedure. That is text column in this table will contain the SQL statements which are used for creating stored procedure.

SQL Server will parse the queries that are present in stored procedure and checks for the following:

Check whether all the queries are syntactically correct or not.

Check whether all the tables listed in stored procedures exists in database or not. If some tables are internally created as part of stored procedure, then temporary tables will be created fro time being and these tables will be used while compiling the query. These tables are not permanent. They will not be present in the database after stored procedure compilation. This type of compilation is known as Deferred Name Resolution.

Check whether all the columns which are used in stored procedure exists in data base in appropriate tables.

If we are running set of queries as part of a process, then sending or transmitting all those queries through network will increase the network traffic and it uses more bandwidth. Stored procedures internally encapsulate the entire business logic in a unit. By invoking just stored procedure name will reduce the burden on network. Also, this uses less bandwidth. This is the reason Stored procedures are more preferred in cross applications than independent queries.

Stored procedures are parsed and optimized as soon as they are created or complied in the database. Compiled stored procedures are stored in the memory. Database will internally stores the execution plan of a stored procedure. When ever a stored procedure is invoked or executed same query plan will be re-used. Running independent queries means that we are sending the queries 1st to database optimizer which will create the query plan and will execute that query plan. By using the stored procedure this extra burden on the database can be reduced, which in turn will result in a better performance and faster query or process execution,

Stored procedures are special Sql server objects which will be stored in database.

Advantages of stored procedures:

Script re-usability: Script complied once can be reused as many times as needed.

Execution Plan Retention and Re-usability: Execution plan will be stored in the database for a stored procedure and same plan will be executed when ever a stored procedure runs.

Encapsulation of complete business logic in a unit: Stored procedure encapsulates the complete process business logic inside the stored procedure. Instead of executing all the queries, it’s easy and safe to execute the stored procedure.

Safety and Security:Stored procedure can be granted with access. This will allow only intended resources to utilize or run the stored procedure. Security feature in stored procedures shield the user from directly accessing the tables in a database.

Easier to call from external application: Stored procedures can be easily invoked from external applications like C#.net , Asp.Net and web services.

Client- Server traffic will be reduced by using stored procedures since this will use lesser bandwidth compared to the queries.

Temporary stored procedures are also supported in SQL Server.This feature will allow stored procedures to be created in temp db just like temporary tables. That is # and ## stored procedures can be created in SQL SERVER. They will automatically be dropped when you disconnect from the server.

Simple and Easy to use:When a process needs to be executed or invoked from some external application, we need not transfer all sql queries involved in the process. Instead we can just call the stored procedure.

Disadvantages of stored procedures:

Execution plan of the stored procedure is cashed. Same Execution plan will be used and executed whenever a stored procedure is executed.Some times old execution plan may not be using the latest indexes. In those cases execution plan will not be correctly optimized. This will hinder the performance of the stored procedure.

As the Name implies stored procedures are data base objects which store the business logic or procedure in a complied script. These are special data base objects. They are pre-compiled T-SQL statements/queries.

Entire business logic of a process can be encapsulated in a stored procedure and this can be used when ever needed. This will reduce the overhead of writing the same queries again and again. Executing stored procedures is comparatively faster than executing the queries independently. Also, stored procedures use less bandwidth. That is the reasons stored procedures are most preferred for the cross applications.

Following data types are not so frequently used in SQL Server. These are the special data types which are used only in specific cases.

Xml: XML data type is used to hold xml data in SQL Server.

Timestamp ( or ) Rowversion: Timestamp or Rowversion is the data type which is used for maintaining the version numbers for the rows in a table. This is not generally used for the variable. RowVersion or TimeStamp data type is used only if data needs to be monitors for each DML operation. When ever any DML operation occurs on the table in which rowversion or timestamp data type is used, then automatically rowversion column number will be incremented.

Cursor: Cursor data type is used to fetch data from a table row by row.

Cursor is a special data type. This cannot used to create columns in a table. This data type should be used only to declare cursor type variable.