Stored Procedures

Stored Procedure

     Stored Procedures, these are most important or common things in every Data Base these will play main role in lot of cases, now I am going to tell you some points

     Stored Procedures, as of known everyone it's a collection of SQL Statements, Yes its exactly right.

     Stored Procedure takes the multiple parameters and will returns the output as result set to the calling program.

Advantages of Stored Procedure

Reduce the Network Traffic, Yes it will reduce the network traffic you may get a doubt on this, how the network traffic would reduce using the Stored Procedure?, Yes it will reduce, for example if we are calling 10 statements and calculating these 10 statements in application level then passing to the server it takes much time so instead of calling 10 statements we can call just single statement that simply pass to the server and will fire at server level, these 10 statements will execute as batch, I hope you got the logic here.

Security, using stored procedures user can't find the database level objects, because in application level we are calling only stored procedure only, so if any third person (malicious users) in between application and server they cannot see the objects of Data Base

These everyone knows I hope

Re-usability of Code, Yes we can use multiple times which we created as Stored Procedure or collection of SQL statements, no need to write these statement multiple times, if any changes are required we need to Alter the Stored Procedure.

 which may you don't know if you know more information or if anything wrong in below just mail me.

Clustered Indexes

CLUSTERED INDEX

     Yes, Here I am going to explain about Indexes in SQL Server  as of my knowledge, I hope every one heard about Indexes but don't have a complete idea about it.

Ok, Let's start

     Basically Indexes are using for Query performance in SQL Server (actually not in SQL Server in all other Data Bases also using for performance only) , these are on-disk structure, and I read some where we can call as row store Index.

     Clustered Index will creates an Index Key and stored the data rows of the tables in order to clustered Index Key in sorting order (ascending or descending) , flow of the clustered index follows B-Tree structure.

    We can create using SQL Server Management Studio or T-SQL, a clustered Index can be rebuild or reorganize on demand to manage the Index Key order.

     When we create a table with the Primary Key that table will create along with Index Key, if table having the Clustered Index then those kind of tables called Clustered Tables, if that table doesn't have any Clustered Index then that we call as Heap Tables

    There can be only one Clustered Index per table, Table doesn't allow more than one Clustered Index

Syntax:

CREATE CLUSTERED INDEX <Index Name> ON <Table Name> (<Column Name>);   

Example:

CREATE CLUSTERED INDEX INDX_Emp_Tbl_Emp_Num ON dbo.Emp (Emp_Num);   

In Generally If table have more than 8 MB then only it goes to Index to get the data else it directly fetch the data from the table it self.

Soon I will update more Information on this

Please point if anything wrong, you can shoot a mail to : tthirmal@gmail.com