SQL SERVER – Inside Temp table Object Creation – Part II

After my previous article on “Inside Temp table object creation”, I had a number of people ask me more about this topic. There seems to be a need for understanding these basic fundamentals that we have known for ages in a refreshingly new way. Though there were tons of mails that had come to me, there was one mail that caught my attention and I was pleasantly surprised to see the same. I thought it was worth the time to explain the same here over the blogs again. The Email reads like:

“Hi Pinal,

I really liked your article on Inside TempDB Table creation article. And the steps were very useful for me to understand how tempdb works. As a fresher and into my first year of career, I somehow found courage to send you this email. I am not sure if you will reply, still thought you will have some pointers to help me. When I did the step on my local laptop, I could see the exact steps shown by you. I was so excited and I thought let me know what our dev / test environments look like and I ran the DMV for user tables there.

I do get to see a number of objects with names like #B0D42C6C, #D5F46A7C and so on. They are not like the table names that you mention inside your previous article. And even after I run the same DMV after 5-10 mins, I see a lot of them still on my system. What are these tables? They also have negative ID value as you mentioned before. Are they generated by system? Are they used by my application? How do I get to know about them?”

I think my friend you have brought a different dimension to this article. I am glad you found time to write to me and I appreciate each and every one who takes time to write to me. There is no simple questions in this world. I think this is yet another example of complex SQL can be and how we can learn from SQL Server every single day.

Now let me explain you what these temp objects are which my friend is talking about. These are also temp tables created by user but inside a Stored Procedure. To illustrate the same I am going to show you inside a code block below:

Let us first create the stored procedure that creates a temp table inside the SP:

USE AdventureWorks2012
GO
DROP PROCEDURE TempTable_Objects
GO
CREATE PROCEDURE TempTable_Objects
AS
CREATE TABLE
#temptable_in_sp (
id INT NOT NULL IDENTITY (1,1)
,
name CHAR(30) NOT NULL
,
DOJ DATETIME NOT NULL
)
-- Do what ever you want with the temp table :)
-- WAIT, so that the SP doesn’t complete fast :)
WAITFOR DELAY '00:00:05'
GO

Once the stored procedure is created, let us open two sessions and execute the stored procedure in one window and then execute the objects created inside TempDB on the second window. The two session windows are:

Session 1:  Run the SP

-- Execute the SP in Session 1
EXEC TempTable_Objects
WAITFOR DELAY '00:00:05'
EXEC TempTable_Objects

Session 2: Track the Object creation inside TempDB

-- Results when SP started
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'
WAITFOR DELAY '00:00:05'
-- Results when the first SP call ended
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'
WAITFOR DELAY '00:00:05'
-- Results when the SP was called again
SELECT * FROM tempdb.sys.sysobjects WHERE TYPE = 'U'

The output for the same look like:

As you can see, the object name is similar to the previous article. But unlike the standard temp table creation, here SQL Server is caching and keeping this so that the subsequent users can reuse the same. Hence in this particular case, since my friends said he saw a number of such values in TempDB, it means there are lots of temp tables created via the stored procedure which are still there post the session ending. The temp table object ID confirms our assumption that it is getting reused. On further quizzing my friend, I found there were reporting queries which are generating temp tables every 15-20 mins for in a scheduled manner. These were a constant behavior in their environment.

If you check SQL Server Management Studio Object Explorer, we can see these objects there too. Here is a typical snapshot of the same.

As I said before, these are great learning experience for me too and hope you learnt something new today with me in this exploration.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Maximum Allowable Length of Characters for Temp Objects is 116 – Guest Post by Balmukund Lakhani

Balmukund Lakhani (Blog | Twitter | Site) is currently working as Technical Lead in SQL Support team with Microsoft India GTSC. In past 7+ years with Microsoft he was also a part of Premier Field Engineering Team for 18 month. During that time he was a part of rapid onsite support (ROSS) team. Prior to joining Microsoft in 2005, he worked as SQL developer, SQL DBA and also got chance to wear his other hat as an ERP Consultant.

Here is the guest post by Balmukund Lakhani

Recently Dhananjay (B | T) posted an interesting question on the SQLServerFAQ community page since the answer was new to almost all in the group, I have taken idea to write a blog post to explain this in detail. Here we go.

Question:
All objects can have minimum 1 and maximum of 128 characters in their names in SQL Server. Only exception is local temporary tables that can have maximum of 116 characters. What’s the technical reason behind this? Anyone, why that’s so?

Manas (B | T) posted this link from books online which tells this limitation but I always wanted to explore what happens under the hood.  Here is the simple repro of problem statement

DECLARE @i NVARCHAR(800)
SELECT @i = REPLICATE('A', 116)
SELECT @i = 'CREATE TABLE #'+@i+'(i int)'
PRINT @i
EXEC(@i)

In above script I am trying to create a temp table (starts with #) and you would see below output.

CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i int)

Msg 193, Level 15, State 1, Line 1
The object or column name starting with ‘#AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ is too long. The maximum length is 116 characters.

The URL which was pointed by Manas (B | T) clearly explained this
{
Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.
}

If you try to create a normal table with more than 128 characters then you would see

Msg 103, Level 15, State 4, Line 1
The identifier that starts with ‘#AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ is too long. Maximum length is 128.

If you read the message closely, there are two error numbers 193 and 103 which have different length.  Now the real question is why there is differentiation between regular table and temp table Let’s create two temp tables now. I am giving name as A and A [repeated 128 times]

DECLARE @i NVARCHAR(800)
SELECT @i = REPLICATE('A', 115)
SELECT @i = 'CREATE TABLE #'+@i+'(i int)'
PRINT @i
EXEC(@i)
GO
DECLARE @i NVARCHAR(800)
SELECT @i = REPLICATE('B', 1)
SELECT @i = 'CREATE TABLE #'+@i+'(i int)'
PRINT @i
EXEC(@i)
GO

Here is the output which you would see. Now execute the same output in the SSMS.

CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i INT)
CREATE TABLE #B(i INT)

If you run above piece of code in SSMS, you would see object getting created. Now, let’s find out the name of the object in tempdb database

SELECT LEN(name) 'Length', name
FROM tempdb.sys.objects
WHERE TYPE = 'u'
AND name LIKE '#AAAAAA%'
OR name LIKE '#B%'

Here is the output.

Click to expand

Length of both the object name is 128 even if we create object with length name as 116 and 1. That should give you little clue about the behavior.

Summary:
Whatever object name you give for temp table, SQL is going to pad the value with underscores and few “magic” bit as the end and would cover complete 128 characters. Have a closer look at length of magic bit “000000000058” and “000000000059” in above output [yeah, its 12]. Those are needed because you are allowed to create same name temp table by different sessions.  The length of magical number is 12 so 128 – 12 = 116 is the max length which is allowed for temp objects as SQL adds 12 chars at the end.

In case you are in Facebook, you may want to check the group SQLServerFAQ for other interesting questions.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Drop Temp Table – Check Existence of Temp Table

I have received following questions numerous times:

“How to check existence of Temp Table in SQL Server Database?”

“How to drop Temp Table from TempDB?”

“When I try to drop Temp Table I get following error. Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.
How can I fix it?”

“Can we have only one Temp Table or we can have multiple Temp Table?”

“I have SP using Temp Table, when it will run simultaneously, will it overwrite data of temp table?”

In fact I have already answer this question earlier in one of my blog post. I have even explained how Temp Table works in TempDB and how they are managed.

Visit my earlier written article for answers to questions listed above.

SQL SERVER – Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable – Part 2

Some questions never get old. One of them is temp table variable and temp table in SQL Server.

I have previously wrote about this indepth here : SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

Recently I received question:

Can temporary table have indexes? If yes, are they really useful and efficient? When nonclustered index are created a separate table is created, what happens in the case of when temporary table?

I really liked the question of user.

Yes, temporary table can have indexes. If you have to use temporary table more than one time in your operation, create index on it and it will be have performance improvement because of Index usage.

When indexes are created on temporary table (which are stored in TempDB), indexes are created on TempDB as well. When original table is dropped the index table may be or may not be dropped, but definitely become not useful. The same index can not be used again with new temp table.

I am interested in listening my blog readers comment about these questions.

Reference : Pinal Dave (http://blog.SQLAuthority.com)