How to create temporary non cluster index on my searching table Columns and remove it after select operation

Question

I have 1 stored procedure which can return more than 1000 of records.

I want to Create temporary Non cluster index on my searching table columns as because i have heard that non cluster index will speed up data retrieval (SELECT) operations and slow down data updates(UPDATE and DELETE) operations and remove that non cluster index after my Operation have been completed.

Like I am having 2 Tables UserDetails and CategoryMaster and my searching fieds:

• UserDetails(ServiceDescription,Skills)
• CategoryMaster(Name)

This is my stored procedure:

ALTER PROCEDURE [dbo].[SearchworkerProcedure1]
    @SearchKeyword nvarchar(70)

AS
 DECLARE @Keywords TABLE
   (    
      sno INT IDENTITY(1,1) PRIMARY KEY,
      keyname VARCHAR(100),
      Shortkeyname as  substring(keyname,0,5)
    )

DECLARE @SearchKeywordTable TABLE
(
        [VendorId] [int]  NULL,
        [ServiceDescription] [nvarchar](max) NULL,
        [Skills] [nvarchar](max) NULL
)  
INSERT INTO @Keywords SELECT * FROM [splitstring_to_table](@SearchKeyword,',')
BEGIN

                    --My Query
END

My UserDetails Create Query:

CREATE TABLE [dbo].[UserDetails](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Fullname] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

So is that Possible to create temporary non cluster index in stored procedure and remove that non cluster index after Select Operation????

Answer 1

The temporary index is a bad idea. To index a table, a table needs to be scanned - just as it would if you were doing a SELECT on it with the current setup.

Permanent (and temporary) indexes on the fields that you have mentioned would have absolutely no effect whatsoever because your search criteria has leading wildcards. This will result in a table scan anyway.

The only place where indexes may help are on your foreign key columns used in joins. However without having any meaningful sizing stats in regards to yoiur tables, it's a guess.