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
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;
SqlDataReader reader = cmd.ExecuteReader();
string empid = reader["EmployeeID"].ToString();
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]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[SP_GetEmployee]
-- Add the parameters for the stored procedure here
@EmployeeID int = null
-- 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
We can also use third party tools like Microsoft.Practice.EnterpriseLibrary.