SQL Server - How to use Index in stored procedure

Asked By bhanupratap singh on 25-Feb-13 07:27 AM
I know to create index on column.
But how to use to get result fast and reach to specific record quicly

thanks
Robbe Morris replied to bhanupratap singh on 25-Feb-13 08:39 AM
There is no such thing as indexing a stored procedure.  If you have created a proper index on the column and the query is still fairly slow for a single record query, then you've got other issues either with JOINs needing other indexes or poor SQL.  You may want to post your query.
bhanupratap singh replied to Robbe Morris on 25-Feb-13 11:42 PM
My procedure is below. I want an index on DELIVERY_STATION+DDRNO

ALTER procedure  [dbo].[sp_DeliveryChangeDDRNo]
(
@DELIVERY_STATION varchar(40)
,@DDRNO VARCHAR(7)
,@oldDELIVERY_STATION varchar(40)
,@oldDDRNO varchar(7)
)
AS 
  BEGIN 

UPDATE  DELIVERY       
SET DELIVERY_STATION=@DELIVERY_STATION,DDRNO=@DDRNO 
WHERE DELIVERY_STATION=@oldDELIVERY_STATION AND DDRNO=@oldDDRNO

 END  
I want to catch record by going quickly to where clause record by using index.
thanks
Robbe Morris replied to bhanupratap singh on 26-Feb-13 08:38 AM
You said you knew how to create an index on a column.  So, go create an index with either both columns or two separate indexes, one with each column.

If Delivery is unique by DELIVERY_STATION and DDRNO combined, then just create one index with both columns.
bhanupratap singh replied to Robbe Morris on 26-Feb-13 11:40 PM
How to specifey the index on column in where clause
thanks
If i write query like

Select *from Delivery where DELIVERY_STATION='DELHI' (IN THIS CLAUSE HOW TO USE INDEX WHICH I HAVE CREATED ON COLUMN "DELIVERY_STATION"

THANKS
Robbe Morris replied to bhanupratap singh on 27-Feb-13 08:45 AM
You do not need to specify the index or set Hints.  The optimizer will create an execution plan by evaluating your query and determining which indexes to use.

In SQL Server Management Studio, there is a tool called the Display Execution Plan.  If you create a new query window and manually execute your stored procedure by clicking Display Execution Plan (in 2012, it is in the top menu under Query), it will show you all the indexes and paths the optimizer took to run your query.
bhanupratap singh replied to Robbe Morris on 28-Feb-13 01:42 AM
Thanks for reply