{"id":687,"date":"2010-10-12T16:00:48","date_gmt":"2010-10-12T14:00:48","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=687"},"modified":"2010-10-12T18:34:47","modified_gmt":"2010-10-12T16:34:47","slug":"a-trio-of-table-variables","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2010\/10\/12\/a-trio-of-table-variables\/","title":{"rendered":"A Trio of Table Variables"},"content":{"rendered":"

So, it’s the second Tuesday of the month again, and it’s time for T-SQL Tuesday again. \"TSQL2sDay150x150\"<\/a>This month it’s hosted by Sankar Reddy<\/a> and the topic is “Misconceptions in SQL Server<\/a><\/em>”<\/p>\n

I thought I’d tackle a trio of table variable myths and partial truths.<\/p>\n

Table Variables are memory-only<\/h3>\n

This one is pervasive and irritating. It typically goes like this:<\/p>\n

You should use table variables rather than temp tables because table variables are memory only.<\/p><\/blockquote>\n

This myth can be broken down into two parts:<\/p>\n

    \n
  1. That table variables are not part of TempDB<\/li>\n
  2. That table variables are not written to disk<\/li>\n<\/ol>\n

    The first is easy to prove and has been<\/a> done<\/a> repeatedly<\/a>. I’m not doing it again. I’m going to tackle the second portion only.<\/p>\n

    See, one could argue that, even though the table variable is created in the TempDB system tables and allocated pages within the TempDB data file, it is still kept entirely and only in memory. Let’s see if that’s true\u2026<\/p>\n

    DECLARE @LargeTable TABLE (\r\nid INT IDENTITY PRIMARY KEY,\r\nLargeStringColumn1 CHAR(100),\r\nLargeStringColumn2 CHAR(100)\r\n)\r\n\r\nINSERT INTO @LargeTable (LargeStringColumn1, LargeStringColumn2)\r\nSELECT TOP (100000) 'Table Variable Test','T-SQL Tuesday!'\r\nFROM master.sys.columns a CROSS JOIN master.sys.columns b\r\n\r\nWAITFOR DELAY '00:01:00' -- so that the table var doesn't go out of scope and get deallocated too quickly.<\/pre>\n
    This is not a massively large table. 100000 rows at 204 bytes per row (excluding header). A query of sys.dm_db_index_physical_stats (which does work on temp tables and table variables) reveals a total page count of 2632. That’s a grand total of 20.6 MB. 20 Megabytes. The SQL instance I’m running this on is allowed to use up to 2 GB of memory. No way on earth is this table variable going to cause any form of memory pressure (and I promise there is nothing else running)<\/p>\n
    So, run that code and, while that waitfor is running, do something that should never be done to a SQL server that you care anything about.<\/p>\n
    \"Dont<\/a><\/p>\n
    That’s going to kill SQL so fast that it’s not going to have a chance to clean up or deallocate anything on the way out. Just how I want it.<\/p>\n
    Now load up my favourite hex editor and open the TempDB data file and see if any rows from the table variable are there.<\/p>\n
    \"TableVariableOnDisk\"<\/a><\/p>\n
    That pretty much speaks for itself. This myth, clearly false.<\/p>\n
    Table Variables cannot be indexed<\/h3>\n
    Not too common, but I have seen this one floating around. It typically goes something like this:<\/p>\n
    Table variables cannot have indexes created on them. The only exception is a clustered index defined as part of the primary key.<\/p><\/blockquote>\n
    Now there’s a small grain of truth in this. Both of the following return an error<\/p>\n
    DECLARE @SomeTable TABLE (\r\nID int,\r\nSomeColumn VARCHAR(20)\r\n)\r\nALTER TABLE @SomeTable ADD CONSTRAINT pk_SomeTable PRIMARY KEY CLUSTERED (id)<\/pre>\n
    DECLARE @SomeTable TABLE (\r\nID int,\r\nSomeColumn VARCHAR(20)\r\n)\r\nCREATE INDEX idx_Testing ON @SomeTable (SomeColumn)<\/pre>\n
    Msg 102, Level 15, State 1, Line 5
    \nIncorrect syntax near ‘@SomeTable’.<\/span><\/p><\/blockquote>\n
    Ok, so it’s not possible to run a CREATE INDEX or ALTER TABLE statement against a table variable, but does that mean that it’s limited to a single clustered index (defined as part of the primary key?)<\/p>\n
    It does not.<\/p>\n
    Firstly, there’s no requirement that the primary key be enforced by a clustered index. The following is perfectly valid.<\/p>\n
    DECLARE @Test TABLE (\r\nID INT NOT NULL PRIMARY KEY NONCLUSTERED,\r\nSomeCol VARCHAR(20)\r\n)<\/pre>\n
    A query against TempDB’s system tables with that table declared clearly shows two entries in sys.indexes for that table variable, index id 0 (the heap) and a single non-clustered index with an auto-generated name indicating that it is enforcing the primary key<\/p>\n
    \"TableVarPK\"<\/a><\/p>\n
    So does that mean that we can have one and only one index on a table variable?<\/p>\n
    Again, no.<\/p>\n
    We’re limited to creating any desired indexes as part of the table’s definition, but there are two constructs that can be defined that way. Primary key and unique constraints. We can define as many unique constraints as desired on a table variable (up to the limit of number of indexes on tables). If the columns that need to be indexed aren’t unique themselves, we can always add the primary key column(s) to the unique constraint so that the combination is always unique.<\/p>\n
    DECLARE @Test TABLE (\r\nID INT NOT NULL PRIMARY KEY,\r\nIndexableColumn1 INT,\r\nIndexableColumn2 DATETIME,\r\nIndexableColumn3 VARCHAR(10),\r\nUNIQUE (IndexableColumn1,ID),\r\nUNIQUE (IndexableColumn2,ID),\r\nUNIQUE (IndexableColumn3, IndexableColumn2, ID)\r\n)\r\n\r\nINSERT INTO @Test (ID, IndexableColumn1, IndexableColumn2, IndexableColumn3)\r\nVALUES\r\n(1,0,GETDATE(),'abc'),\r\n(2,0,'2010\/05\/25','zzz'),\r\n(3,1,GETDATE(),'zzz')\r\n\r\nSELECT t.name, i.name, i.index_id\r\nFROM tempdb.sys.tables t\r\nINNER JOIN tempdb.sys.indexes i ON t.object_id = i.object_id<\/pre>\n
    \"TableVarIndexes\"<\/a><\/p>\n
    If the primary key is enforced by the clustered index, this does not make the index any wider than it would be were it defined as a non-unique index with Create Index, as a non-unique non-clustered index always gets the clustering key added to the key columns.<\/p>\n
    I think that’s this myth suitably busted.<\/p>\n
    Changes to Table Variables are not logged<\/h3>\n
    A fairly uncommon myth, but I have seen this a time or two, so I thought I’d tackle it as my third.<\/p>\n
    Table variables don’t participate in transactions, hence nothing is written to the transaction log when changes are made to them.<\/p><\/blockquote>\n
    This again has two parts to it<\/p>\n
      \n
    1. Table variables don’t participate in transactions<\/li>\n
    2. Operations on table variables are not logged<\/li>\n<\/ol>\n
      The first part is completely true. Table variables do not participate in user transactions and they are not affected by an explicit rollback. Easily demonstrated.<\/p>\n
      DECLARE @TransactionTest TABLE (\r\nID INT IDENTITY PRIMARY KEY,\r\nSomeCol VARCHAR(20)\r\n)\r\n\r\nINSERT INTO @TransactionTest (SomeCol) VALUES ('Row1')\r\nINSERT INTO @TransactionTest (SomeCol) VALUES ('Row2')\r\n\r\nBEGIN TRANSACTION\r\nINSERT INTO @TransactionTest (SomeCol) VALUES ('Row3')\r\nROLLBACK TRANSACTION\r\n\r\nSELECT * FROM @TransactionTest<\/pre>\n
      That final select returns 3 rows, not the two that might be expected. The rollback did not affect the table variable.<\/p>\n
      So does that lack of participation imply that there is no logging? Well, no. My university logic text would call this a Non sequitur fallacy (conclusion does not follow from its premises). The fact that explicit rollbacks don’t affect table variables in no way implies that there’s no logging happening. Let’s have a look into the transaction log to prove it.<\/p>\n
      USE tempdb -- make sure that the correct database is in use\r\nGO\r\nCHECKPOINT \u2013- To truncate the log and indicate the start of the test\r\n\r\nDECLARE @TransactionTest TABLE (\r\nID INT,\r\nSomeCol VARCHAR(20)\r\n)\r\n\r\nSELECT name AS TableVariableActualName FROM tempdb.sys.tables\r\n\r\nINSERT INTO @TransactionTest (ID, SomeCol)\r\nVALUES\r\n(0,'Row1'),\r\n(1,'Row2'),\r\n(2,'Row3')\r\n\r\nSELECT Operation, AllocUnitName, [Begin Time], [End Time] FROM fn_dblog(NULL, NULL)\r\nGO<\/pre>\n
      \"TableVariableLogging\"<\/a><\/p>\n
      The alloc unit name matches the table variable’s name as defined in the system tables, the times for the begin and end transaction matched. I don’t think there’s any arguing that the changes to the table variable are logged.<\/p>\n
      The next interesting question is whether there’s more or less logging than for a temp table, more or less logging than for a permanent table. Only one way to find out.<\/p>\n
      I’m going to run exactly the same code with the table variable replaced by a temp table (same structure) and then I’m going to create a new user database and run exactly the same code just using a permanent table.<\/p>\n
      First the temp table.<\/p>\n
      \"TempTableLogging\"<\/a><\/p>\n
      And now the permanent table in a user database<\/p>\n
      \"TableLogging\"<\/a><\/p>\n
      From that it appears that the table variable logs less than the temp table which logs less than the user table, however the table variable does still has some logging done.<\/p>\n
      ‘But why?’ I hear people asking. After all, TempDB doesn’t log redo information<\/a> and, since table variables don’t participate in transactions there’s no need to log undo information. So why log at all?<\/p>\n
      Because an explicit rollback (ROLLBACK TRANSACTION) is not the only time that changes to a table will have to be undone. Consider this one.<\/p>\n
      DECLARE @TransactionTest TABLE (\r\nID INT PRIMARY KEY,\r\nSomeCol VARCHAR(20)\r\n)\r\n\r\nINSERT INTO @TransactionTest (ID, SomeCol)\r\nVALUES\r\n(0,'Row1'),\r\n(1,'Row2'),\r\n(1,'Row3')<\/pre>\n
      That third row will fail with a primary key violation. If the table variable didn’t log at all, SQL would have no way of undoing the inserts of the first two rows when the third one fails. That’s not permitted, an insert is an atomic operation, it cannot partially succeed. Hence changes to a table variable must be logged sufficiently to allow SQL to generate the undo operations in cases like this. A glance over the transaction log shows in detail what happened<\/p>\n
      \"TableVariableRollback\"<\/a><\/p>\n
      Two rows inserted, followed by two rows deleted, as SQL generated operations to undo the insert statement, followed by an abort transaction.<\/p>\n
      I think that’s enough on this. As for the myth that changes to table variables aren’t logged, I believe that’s sufficiently disproven by this point.<\/p>\n
      In Conclusion<\/h3>\n
      Table Variables are memory-only: False<\/p>\n
      Table Variables cannot be indexed: False<\/p>\n
      Changes to Table Variables are not logged: False<\/p>\n","protected":false},"excerpt":{"rendered":"
      So, it’s the second Tuesday of the month again, and it’s time for T-SQL Tuesday again. This month it’s hosted by Sankar Reddy and the topic is “Misconceptions in SQL Server” I thought I’d tackle a trio of table variable… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,16,17],"tags":[],"class_list":["post-687","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-syndication","category-t-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-b5","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/687","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=687"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/687\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=687"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=687"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=687"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}