Post
Ask Question

Creating Index on #temp tables

#Temp tables are much like SQL tables that are defined and stored in TempDB. Difference between them and a permanent table is they are not allowed to have foreign keys.
 
One of the feature of temp table (#temp) is that we can add a clustered or non clustered index. Also, #temp tables allow for the auto-generated columns (fields) to be created. This can help the optimizer when determining the number of records. Below is an example of creating both a clustered and non-clustered index on a temp table.
  1. -- creating temp table - #employees
  2. CREATE TABLE #Employees  
  3. (  
  4. ID INT IDENTITY(1,1),  
  5. EmployeeID INT,  
  6. EmployeeName VARCHAR(50)  
  7. )  

  9. INSERT INTO #Employees  
  10. (  
  11. EmployeeID,  
  12. EmployeeName  
  13. )  
  14. SELECT  
  15. EmployeeID = e.EmployeeID  
  16. ,EmployeeName = e.EmployeeName  
  17. FROM dbo.Employees e  
  18.   
  19. -- creating clustered index  
  20. CREATE CLUSTERED INDEX IDX_C_Employees_EmployeeID ON #Employees(EmployeeID) 
  21.  
  22. -- creating non-clustured index  
  23. CREATE INDEX IDX_Users_UserName ON #Employees(EmployeeName)