Temporary tables

• Temp tables are used for the large temporary storage of data.
• Only use local temp tables. 
• Use temporary tables cautiously / only when necessary e.g. early filtering in reports / complex queries.
• When a temporary table is used in a stored procedure, evaluate if it is absolutely necessary.
• Ensure that temporary table are always explicitly dropped at the end of the stored procedure.
• When the possibility exist that the temp table does not exist test its existence in the temp..DB before dropping e.g. conditionally created temp table 

IF OBJECT_ID('tempdb..#TheTable') IS NOT NULL
BEGIN
/*Do Stuff*/
END

• Create the table before addition when a fixed definition is required or multiple data sources are used to populate it

      CREATE TABLE #LeaveCycles(
           StartDate   DATETIME,
           EndDate     DATETIME,
           LeaveTypeId INT,
           Name        VARCHAR(200) COLLATE DATABASE_DEFAULT,
           CycleId     INT,
           CreatedDate DATETIME,
           EmployeeId  INT
                          )

      INSERT INTO #LeaveCycles(
             StartDate
           , EndDate
           , LeaveTypeId
           , Name
           , CycleId
           , CreatedDate
           , EmployeeId
                       )
      EXEC LeaveCalculateActiveCyclesByLeaveType
           @EmployeeId = @EmployeeId,
           @SchemaId = @SchemaId,
           @LeaveTypeId = @LeaveTypeId,
           @IsHistoric = @IsHistoric

• When a single table is used a select into can be done to create the temp table