Store Procedure SQL SharePoint 2010

SQL Database: To improve the retrieval speed(by reducing disk I/O operations and cosume fewer system resources) of rows from table or view, we have indexes. There are 2 type of indexes.
1. Clustered Indexes sort and store the data rows in a table or view based on their key values(values are columns). Their can be only one clustered index per table beacuse data rows can be sorted only in one order. 1 clustered index per table only. Primary key automatically creates clustered index for the table.
2. Non-clustered Indexes are pointers to key values of the data rows called as row locators. 999 non-clustered index per table only.

Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. Query optimizer evaluates and decides whether to go with indexes or with table scan. If there is no indexes, query optimizer has to go with table scan. Its upto us to design and create indexes that are best suited to your environment

use Rahul
go

create table Accenture
(
EmployeeID int constraint pk_empID primary key,
Employeename varchar(50) not null,
Employeesal int not null,
);

create table PFForm
(
PFNumber int constraint pk_pfnumber primary key,
EmployeeID int constraint fk_EmpID foreign key references Accenture(EmployeeID),
);

alter table pfform
add pfMoney int

alter table pfform
drop column pfMoney

insert into [Rahul].[dbo].[Accenture] values(10618060,'Rahul',50000)
insert into [Rahul].[dbo].[Accenture] (Employeesal,Employeename,EmployeeID) values(10618060,'Rahul',50000)

select * from Rahul.dbo.accenture

To work with store procedure.
 SqlConnection connection = new SqlConnection(@"Data Source=WAYTOUS\SHAREPOINT;Initial Catalog=Rahul;Integrated Security=SSPI;");
            SqlCommand cmd = new SqlCommand("dbo.SP_GetEmployee",connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            int value = 500001;
            cmd.Parameters.Add("@EmployeeID", value);
            cmd.Connection.Open();
             SqlDataReader reader = cmd.ExecuteReader();
             while (reader.Read())
             {
                 string empid = reader["EmployeeID"].ToString();
             }



use Rahul
Go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_GetEmployee]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_GetEmployee]
GO

use Rahul
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_GetEmployee]
-- Add the parameters for the stored procedure here
@EmployeeID int = null

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
select * from Accenture where EmployeeId<@EmployeeID
END
GO


We can also use third party tools like Microsoft.Practice.EnterpriseLibrary.