Stored Procedures using In and Out
Parameters in Sql server 2005
Benefits
of using the Stored Procedure
- One of the main benefit of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become more important benefit when the bandwidth of the network is less. Since if we send the sql query (statement)which is executing in a loop to the server through network and the network get disconnected then the execution of the sql statement don't returns the expected results, if the sql query is not used between Transaction statement and rollback statement is not used.
- Compilation step is required only once when the stored procedure is created. Then after it does not required recompilation before executing unless it is modified and re utilizes the same execution plan whereas the sql statements needs to be compiled every time whenever it is sent for execution even if we send the same sql statement every time.
- It helps in re usability of the sql code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same sql statement every time. It helps in reduces the development time.
- Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure.
- Sometime it is useful to use the database for storing the business logic in the form of stored procedure since it make it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.
create procedure
GetStudentBynameInOut
(
@studentid int ,
@studentname varchar(100),
@studentemail varchar(100)
)
as
begin
select @studentname = firstname +
' ' + lastname ,@studentemail=email
from tbl_students where @studentid=student_id
end
To Execute :
Declare @studentname as nvarchar(100)
Declare @studentemail as nvarchar(100)
Execute GetstudentBynameInOut 1,@studentname
output,@studentemail output
select @studentname aS StudentName,@studentemail EmailId
Clustered Index
Every table can have one and only Clustered Index because
index is built on unique key columns and the key values in data rows is
unique. It stores the data rows in table based on its key values. Table
having clustered index also called as clustered table.
Non-Clustered Index
It has structure different from the data rows. Key value of
non clustered index is used for pointing data rows containing key values. This
value is known as row locator. Type of storage of data pages determines the
structure of this row Locator. Row locator becomes pointer if these data pages
stored as a heap. As well as row locator becomes a clustered index key if data
page is stored in clustered table.
Both of these may be unique. Wherever we make changes to the data table, managing of indexes is done automatically.
SQL Server allows us to add non-key column at the leaf node of the non clustered index by passing current index key limit and to execute fully covered index query.
Automatic index is created wherever we create primary key, unique key constraints to table.
Both of these may be unique. Wherever we make changes to the data table, managing of indexes is done automatically.
SQL Server allows us to add non-key column at the leaf node of the non clustered index by passing current index key limit and to execute fully covered index query.
Automatic index is created wherever we create primary key, unique key constraints to table.
The Query Optimizer
Query Optimizer indexes
to reduce operations of disk input-output and using of system resources when we
fire query on data. Data manipulation Query statements (like SELECT, DELETE OR
UPDATE) need indexes for maximization of
the performance. When Query fires the most efficient method for retrieval of the
data is evaluated among available methods. It uses table scans or index scans.
Table scans uses many Input-output operations, it also uses
large number of resources as all rows from the table are scanned.
Index scan used for searching index key columns to find storage location.
The index containing fewer columns results in to faster query execution and vice-versa.
Index scan used for searching index key columns to find storage location.
The index containing fewer columns results in to faster query execution and vice-versa.
Creating Indexes:
Create index country
on Customers (country)
Creating unique index:
Create unique index contactname on customers (CompanyName,ContactName)
Sorting :
Select * from customers order by country desc
Grouping Records :
Select Count(*) from
products group by unitprice
How Index works
The columns specified in the CREATE INDEX COMMAND taken by
the database engine and sorts the values in Balanced Tree(B-Tree) data
structure. B-Tree structure supports faster search with minimum dist reads, and
allows the database engine to find quick start and end point for the stated
query.
The database takes the columns specified in a CREATE INDEX command and sorts the values into a special data structure known as a B-tree. A B-tree structure supports fast searches with a minimum amount of disk reads, allowing the database engine to quickly find the starting and stopping points for the query we are using.
Conceptually, every index entry has the index key. Each entry also includes a references to the table rows which share that particular value and from which we can retrieve the required information.
It is much similar to the back of a book helps us to find keywords quickly, so the database is able to quickly narrow the number of records it must examine to a minimum by using the sorted list of Key values stored in the index. Thus we avoid a table scan to fetch the query results. Following some of the scenarios where indexes offer a benefit. Advantages of Indexing
The database takes the columns specified in a CREATE INDEX command and sorts the values into a special data structure known as a B-tree. A B-tree structure supports fast searches with a minimum amount of disk reads, allowing the database engine to quickly find the starting and stopping points for the query we are using.
Conceptually, every index entry has the index key. Each entry also includes a references to the table rows which share that particular value and from which we can retrieve the required information.
It is much similar to the back of a book helps us to find keywords quickly, so the database is able to quickly narrow the number of records it must examine to a minimum by using the sorted list of Key values stored in the index. Thus we avoid a table scan to fetch the query results. Following some of the scenarios where indexes offer a benefit. Advantages of Indexing
