March 8, 2016 21:12 by
Peter
Today let me explain you about how to make an 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.
-- creating temp table - #employees
CREATE TABLE #Employees
(
ID INT IDENTITY(1,1),
EmployeeID INT,
EmployeeName VARCHAR(50)
)
INSERT INTO #Employees
(
EmployeeID,
EmployeeName
)
SELECT
EmployeeID = e.EmployeeID
,EmployeeName = e.EmployeeName
FROM dbo.Employees e
-- creating clustered index
CREATE CLUSTERED INDEX IDX_C_Employees_EmployeeID ON #Employees(EmployeeID)
-- creating non-clustured index
CREATE INDEX IDX_Users_UserName ON #Employees(EmployeeName)
HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.