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