{"id":1221,"date":"2011-11-15T20:58:49","date_gmt":"2011-11-15T20:58:49","guid":{"rendered":"http:\/\/craftydba.com\/?p=1221"},"modified":"2011-11-17T16:33:58","modified_gmt":"2011-11-17T16:33:58","slug":"temporary-tables-part-4","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1221","title":{"rendered":"Temporary Tables – Part 4"},"content":{"rendered":"

Today, I am going to talk about regular tables in tempdb. Please check out the Microsoft white paper<\/a> on working with tempdb for a detailed discussion.<\/p>\n

The advantage of creating a table in tempdb is that it is persistent until the server is rebooted. Upon startup of SQL Server, tempdb is created again from scratch. One use of this technique is to have startup procedures create tables in tempdb that are used by an application program. One can leverage the fact that operations within tempdb are minimally logged.<\/p>\n

The major downside of using tempdb <\/a>is that everything else including the database engine does. This includes work tables used by queries, the version store for optimistic concurrency, and any other temporary objects. I want to cover this topic since there are many programs out using regular tables in tempdb. <\/p>\n

Some of the issues that you might encounter with using tempdb are the following:<\/p>\n

    \n
  1. Space issues when large queries execute<\/li>\n
  2. Performance issues when large queries execute<\/li>\n
  3. System tables locked by queries<\/li>\n<\/ol>\n

    Ways to mitigate these issues <\/a>is to make sure your tempdb is size appropriately with the number of data file groups equal to the number of processors. Trace flag 1118 can be used to grab segments of memory instead of pages which will further reduce issues.<\/p>\n

    With the about cautions listed out, I am going to use the car ownership table for person A to demonstration how to work with regular tables in tempdb. Please see the tiny AUTOS database I created in earlier article<\/a>. <\/p>\n

    The code snipet below creates regular tables in tempdb that persist until SQL Server is rebooted.<\/p>\n

    -- Database selection does not matter\r\nUSE tempdb;\r\nGO\r\n\r\n-- Create regular table in tempdb\r\nCREATE TABLE A\r\n(\r\n  MyId INT PRIMARY KEY CLUSTERED,\r\n  MyValue VARCHAR(20)\r\n)\r\nGO<\/span><\/pre>\n<\/p>\n
    I am going to use an INSERT <\/a>statment to populate the table with sample data.<\/p>\n
    -- Add data to regular table in tempdb\r\nINSERT INTO A (MyId, MyValue) VALUES\r\n  (1, 'Continental'),\r\n  (2, 'Edsel'),\r\n  (3, 'Lincoln'),\r\n  (4, 'Mercury'),\r\n  (5, 'Ram')\r\nGO<\/span><\/pre>\n<\/p>\n
    To really make sure the data is there, I am going to use a SELECT <\/a>statement to view the contents of the table.<\/p>\n
    -- Show data from table A\r\nSELECT MyId, MyValue\r\nFROM A\r\nGO<\/span><\/pre>\n<\/p>\n
    Regular tables in tempdb would not be very useful if we can not perform UPDATE <\/a>and DELETE <\/a>operations on the records.  The code below deletes the third record from the table, updates the fifth record with new data, and shows the results.<\/p>\n
    -- Remove record 3\r\nDELETE FROM A WHERE MyId = 3;\r\nGO\r\n\r\n-- Update record 5\r\nUPDATE A\r\nSET MyValue = 'Dodge Ram'\r\nWHERE MyId = 5;\r\nGO\r\n\r\n-- Show up dated data from table A\r\nSELECT MyId, MyValue\r\nFROM A\r\nGO<\/span><\/pre>\n<\/p>\n
    Last but not least, it is just good practice to clean up after yourself.  The code below looks for the
    \ntable in tempdb.  If it exists, it drops the table.  <\/p>\n
    -- Just look for table since we are in tempdb\r\nIF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE 'A')\r\n  DROP TABLE A\r\nGO<\/span><\/pre>\n<\/p>\n
    I will be using the Adventure Works<\/a> Sample database supplied by Microsoft during the rest of this talk.<\/p>\n
    A common query pattern is to get the most recent id by date.  Joining these results to the original table will bring back the most recent record by date.<\/p>\n
    The SELECT query below stores the last sales order by customer id as a regular table in tempdb called MC.  The main query joins to this table to return customer name, sales id, sub-total and order date.  This might be a query for the  SALES team to cold call customers that have not ordered in the past x months.<\/p>\n
    -- Use the A.W. database\r\nUSE AdventureWorks2008R2\r\nGO\r\n\r\n-- Regular table in tempdb (MC) - last sales order by customer in 2008 \r\nSELECT \r\n  CustomerID AS MyCustomer,\r\n  MAX(OrderDate) AS MyDate\r\nINTO\r\n  tempdb.dbo.MC\r\nFROM \r\n  Sales.SalesOrderHeader\r\nWHERE\r\n  YEAR(OrderDate) = 2008 \r\nGROUP BY \r\n  CustomerID\r\n\r\n-- Get customer name, sales id, sub-total and order date\r\nSELECT \r\n  P.FirstName, \r\n  P.LastName,\r\n  SH.SalesOrderID, \r\n  SH.SubTotal,\r\n  SH.OrderDate\r\nFROM\r\n  Sales.SalesOrderHeader AS SH   \r\n  INNER JOIN tempdb.dbo.MC ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate\r\n  INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID\r\n  INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID\r\nORDER BY \r\n  SH.OrderDate ASC  \r\n\r\n\r\n-- Switch to tempdb\r\nUSE tempdb;\r\nGO\r\n\r\n-- Remove regular static table\r\nIF EXISTS (SELECT * FROM tempdb.sys.objects T WHERE T.TYPE = 'U' AND T.name LIKE 'MC')\r\n  DROP TABLE MC\r\nGO<\/span><\/pre>\n<\/p>\n
    Creating regular tables in tempdb allows them to persist between sessions.  This is a benefit if several programs access the common table.  The minimal logging feature makes them attractive.  However, a word of caution since tempdb is the workspace of the database engine.<\/p>\n
    Unless performance is extremely critical, I suggest using a staging database that uses SIMPLE recovery.  This allows the log file to be truncated at check points while the impact on tempdb, and the overall database system is minimized.  Load data into heaps, tables without indexes, and then add indexes for fastest execution times.<\/p>\n
    Next time, I will be talking about Common Table Expressions (CTE).<\/p>\n","protected":false},"excerpt":{"rendered":"
    Today, I am going to talk about regular tables in tempdb. Please check out the Microsoft white paper on working with tempdb for a detailed discussion. The advantage of creating a table in tempdb is that it is persistent until the server is rebooted. Upon startup of SQL Server, tempdb is created again from scratch. One use of this technique is to have startup procedures create tables in tempdb that are used by an application program. One can leverage the fact that operations within tempdb are minimally logged. The major…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[33,12,30,15,241,37,28,242,29,32],"class_list":["post-1221","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-delete","tag-free-code","tag-insert","tag-john-f-miner-iii","tag-regular-tables","tag-select","tag-sql-server","tag-tempdb","tag-tsql","tag-update"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1221"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1221\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}