Site Server - How to create and drop non clustered index in sql server 2008 with Example

Hello Mr. Bobe.
I hope u will be doing well.
I need  non  clustred index  on datetime column. So how to create and drop the index  after use
Acually I have data in more than 9900000. Therefore it take time too mutch. I thing if I search data by providing index to search coulumn , it will give response fast . Pls help me out.

And  one thing more. How many rows can we bind in gridview. If rows are more than one laks (100000) than will gridview bind it ?
thanks

"So how to create and drop the index  after use"

Why would you want to drop the index?

Because I have all ready clustred index. .But clustered index is on (ID)primery key. I want to search data  on datetime base column.
One thing more is important how many rows can we bind with gridview in asp.net c#.
with this procedure it returns 1.5 lakhs record. So I am unable to bind these record to gridvierw.
Due to large number of records I want to created non clustered index so that we could search record without reading whole table, and after end of search that created index should be droped. So that it could not take any space in memory of slq.
thanks

Procedure is below.
*********************************/        
        
CREATE PROCEDURE usp_CRM_AwbStatusSummaryOnDateWise --'','','','02/08/2026','02/08/2026'
@OfficeCode VARCHAR(10) = NULL,
@CustomerGroup VARCHAR(50) = NULL,
@CRMCustomerCode VARCHAR(20) = NULL,
@FromDate DATETIME,
@ToDate DATETIME
AS
BEGIN
--DECLARE @OfficeCode       VARCHAR(10) = '01010'
--DECLARE @CustomerGroup    VARCHAR(50) = ''
--DECLARE @CRMCustomerCode  VARCHAR(20) = '01010DPLIN'
--DECLARE @FromDate         DATETIME = '01/01/2026'
--DECLARE @ToDate           DATETIME = '01/20/2014'        
SELECT cd.CustomerName,
      cd.CRMCustomerID,
      cd.CRMCustomerCode,
      cd.OfficeID,
      ca.AWBNumber,
      cgm.GroupName,
      ro.OfficeCode + ' - ' + ro.OfficeName [RO Name] 
      INTO #TempAWBStatus
FROM   CRMCustomerDetails cd WITH(NOLOCK)
      INNER JOIN OfficeMaster om WITH(NOLOCK)
           ON  om.OfficeID = cd.OfficeID
      INNER JOIN CRMCustomerAWBDetails ca WITH(NOLOCK)
           ON  ca.CRMCustomerID = cd.CRMCustomerID
      LEFT JOIN CRMCustomerGroupMaster cgm(NOLOCK)
           ON  cgm.CRMCustomerGroupID = cd.CRMCustomerGroupID
      LEFT JOIN OfficeMaster ro(NOLOCK)
           ON  ro.OfficeID = om.ParentOfficeID
WHERE  (
          ISNULL(@OfficeCode, '') = ''
          OR om.OfficeCode = @OfficeCode
      )
      AND (
              ISNULL(@CustomerGroup, '') = ''
              OR cgm.GroupName = @CustomerGroup
          )
      AND (
              ISNULL(@CRMCustomerCode, '') = ''
              OR cd.CRMCustomerCode = @CRMCustomerCode
          )
      AND CAST(ca.AWBEntryDate AS date) BETWEEN CAST(@FromDate AS DATE) AND 
          CAST(@ToDate AS DATE) 

------summary                 
SELECT DISTINCT ISNULL(tt.[RO Name], '')[RO Name],
      ISNULL(tt.GroupName, '') [Customer Group],
      ISNULL(tt.CRMCustomerCode + ' - ' + tt.CustomerName, '') 
      [CRM Customer],
      ISNULL(REnquiry.Origin, '') [Origin],
      ISNULL(REnquiry.Location, '') [DESTINATION],
      COUNT(DISTINCT tt.AWBNumber)[Total AWB]
FROM   #TempAWBStatus tt
      OUTER APPLY(
   SELECT TOP 1 
          od.Location,
          od.Origin
   FROM   OPSAWBEnquiryDetail od(NOLOCK)
   WHERE  od.[AWBNO] = tt.AWBNumber
   ORDER BY
          od.ActivityDateTime DESC
) AS REnquiry
GROUP BY
      ISNULL(tt.[RO Name], ''),
      ISNULL(tt.GroupName, ''),
      ISNULL(tt.CRMCustomerCode + ' - ' + tt.CustomerName, ''),
      ISNULL(REnquiry.Origin, ''),
      ISNULL(REnquiry.Location, '') 

------Detail
--             
SELECT DISTINCT 
      ISNULL(tt.[RO Name], '')[RO Name],
      ISNULL(tt.GroupName, '') [Customer Group],
      ISNULL(tt.CRMCustomerCode + ' - ' + tt.CustomerName, '') 
      [CRM Customer],
      ISNULL(REnquiry.Origin, '') [Origin],
      ISNULL(REnquiry.Location, '')[Destination],
      tt.AWBNumber,
      ca.RefNumber,
      CONVERT(VARCHAR(10), ca.AWBEntryDate, 103)[AWB Entry Date],
      REnquiry.Activity,
      CONVERT(VARCHAR(10), REnquiry.ActivityDateTime, 103)
      [Activity DateTime],
      REnquiry.[Status],
      REnquiry.ROName[Destination RO Name],
      cad.PrimaryConsigneeFirstName + ' ' + cad.PrimaryConsigneeLastName 
      [Consignee Name],
      cad.PrimaryAdd1 [Consignee Add1],
      cad.PrimaryAdd2 [Consignee Add2],
      cad.PrimaryCity [Consignee City],
      cad.PrimaryState [Consignee State],
      cad.PrimaryPincode [Consignee Pin],
      cad.PrimaryMobileNo [Consignee Phone],
      CASE 
           WHEN REnquiry.Activity = 'POD' THEN CONVERT(VARCHAR(10), REnquiry.ActivityDateTime, 103)
           ELSE ''
      END 
      [Delivery Date],
      CASE 
           WHEN REnquiry.Activity = 'POD' THEN CONVERT(VARCHAR(8), REnquiry.ActivityDateTime, 108)
           ELSE ''
      END[Delivery Time],
      CASE 
           WHEN REnquiry.Activity = 'POD' THEN REnquiry.Detail
           ELSE ''
      END [Delivery Remarks]
FROM   #TempAWBStatus tt
      INNER JOIN CRMCustomerAWBDetails ca WITH(NOLOCK)
           ON  ca.CRMCustomerID = tt.CRMCustomerID
           AND ca.AWBNumber = tt.AWBNumber
      LEFT JOIN CRMCustomerAWBAddressDetails cad(NOLOCK)
           ON  cad.CustomerAWBDetailsID = ca.CustomerAWBDetailsID
      OUTER APPLY(
   SELECT TOP 1 
          od.Activity,
          od.ActivityDateTime,
          od.[Status],
          od.ROName,
          od.Detail,
          od.Origin,
          od.Location
   FROM   OPSAWBEnquiryDetail od(NOLOCK)
   WHERE  od.[AWBNO] = tt.AWBNumber
   ORDER BY
          od.ActivityDateTime DESC
) AS REnquiry 


DROP TABLE #TempAWBStatus
END

You already having a clustered index has nothing to do with creating a dropping a nonclustered index on each use.  This will kill your performance (especially on a large table) for no reason at all.  SQL Server is perfectly capable of having numerous indexes on a table at the same time.

You should also consider the use of a TABLE VARIABLE instead of a TEMP TABLE for queries like this.  TEMP TABLES have different kinds of overhead that a TABLE VARIABLE doesn't have.

Also, I think you'll find that this line:

AND CAST(ca.AWBEntryDate AS date) BETWEEN CAST(@FromDate AS DATE) AND 
          CAST(@ToDate AS DATE) 

Is creating performance problems for you.  SQL Server doesn't always optimize use of indexes when you alter the state of the column value in your WHERE clause.  In this case, the CAST,  you'd want to force your @ToDate to get to be the absolutely end time for that date for use later on in your queries.

declare @Time varchar(20)

declare @Day smallint

declare @Month smallint

declare @Year smallint

set @Time = '23:59:59.998'

set @Month = datepart(month,@ToDate)

set @Year = datepart(yy,@ToDate)

set @Day = datepart(dd,@ToDate)

set @ToDate = cast(cast(@Month as varchar(2)) + '/' + cast(@Day as varchar(2)) + '/' + cast(@Year as varchar(4)) + ' ' + @Time as Datetime)

 
and then use this instead:

AND ca.AWBEntryDate BETWEEN @FromDate AND @ToDate