How to create index on temp table in SQL Server

Performance is the primary key for one of our clients, so we are dealing with many temp tables. We then considered creating an index for those temp tables to significantly boost query performance. We have identified a few approaches to this. In this article, we will walk you through all those approaches to creating the index on the temp table.

For example, We will create a primary index for a temp table for USAShoppingMart’s daily transactions.

Create index on temp table in SQL Server

Let us create the #DailyProductSales temp table using the below query.

CREATE TABLE #DailyProductSales (
    SalesID INT,
    ProductID INT,
    DateOfSale DATE,
    TotalAmount DECIMAL(10,2)
)

Now, let us insert some sample data using the query below in the screenshot below.

Check out How To Create A Temp Table In SQL Server

Case-1: Create a simple index

Next, let us create a simple index on the ProductID column using the query below.

CREATE INDEX IX_DailyProductSales_ProductID ON #DailyProductSales (ProductID)

The above query was executed successfully, and the index was created successfully on the temp table.

Case-2: Create a clustered index

We can also use the query below to create a clustered index on the DateOfSale column.

CREATE CLUSTERED INDEX DailyProductSales_DateOfSale ON #DailyProductSales (DateOfSale)

After executing the above query, the clustered index was created successfully, as shown below.

Case-3: Create a nonclustered index

We can execute the below query to create a non-clustered index for the DateOfSale and ProductID columns.

CREATE NONCLUSTERED INDEX NIX_DailySales_ProductID_DateOfSale 
ON #DailyProductSales (ProductID, DateOfSale)

Check out How To Insert Into Temp Table In SQL Server

Now, the non-clustered index was created successfully after executing the below query. Check out the below screenshot for your reference.

Case-4: Create a unique nonclustered index

You can execute the below query to create a unique nonclustered index.

CREATE UNIQUE NONCLUSTERED INDEX UINX_DailySales_SalesID 
ON #DailyProductSales (DateOfSale)

Conclusion

Suppose you need to optimize the query performance, especially when working with complex operations. In that case, you can index your temp tables using the instructions in this article.

You may also like following the articles below.

• How To Drop Temp Table If Exists In SQL Server
• How To Enable CDC On A Table In SQL Server
• How to get table size in SQL Server