Skip to main content

Posts

Showing posts from May, 2017

Parallax effect

Caching Temporary Table DDL

When creating temporary tables within stored procedures with indexes, there is an opportunity for performance gains.  SQL Server will cache the DDL of temporary tables as long as there are no DDL changes after the creation.  As confusing as that may sound, it will make more sense by looking at the code.  The following are two procedures, one to create a non-cached table and the other cached.
CREATEPROCEDURE dbo.NoncachedTempTable AS /****************************************************************************************   Object Description:   Create a noncached temp table and enter 10000 random numbers then drop
  Revision History:   Date         Name             Label/PTS    Description   -----------  ---------------  ----------  ----------------------------------------   05/21/2017   Dan Andrews      PTS: 00000    Initial Release ****************************************************************************************/ BEGIN CREATETABLE #TempTable(ID        INTIDENTITY , randomVal INT); CREATE…