title: How to create temporary non cluster index on my searching table Columns and remove it after select operation
body: <p>I have 1 stored procedure which can return more than 1000 of records.</p>
  
  <p>I want to Create temporary <strong>Non cluster index</strong> on my <strong>searching table columns</strong> 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.</p>
  
  <p>Like I am having 2 Tables UserDetails and CategoryMaster and my searching fieds:</p>
  
  <ul>
  <li><strong>UserDetails</strong>(ServiceDescription,Skills)</li>
  <li><strong>CategoryMaster</strong>(Name)</li>
  </ul>
  
  <p>This is my stored procedure:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My <strong>UserDetails</strong> Create Query:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>So is that Possible to create temporary <strong>non cluster index</strong> in <strong>stored procedure</strong> and remove that <strong>non cluster index after Select Operation</strong>????</p>