What does # indicate in SQL Server SELECT FROM?

Question

Can someone quickly explain to me (as in without directing me to an illegible mountain of documentation) what a construct such as this (below) means in a SQL Server stored procedure?

Note, this is in a stored procedure on a database which resides on a server with many other databases and this is a generalization not the exact query

INSERT INTO [schema].TableName (Field1, Field2)
    SELECT
        OtherField1,
        OtherField2
    FROM 
        #Something TMP
    INNER JOIN 
        [schema].[Field] A ON A.Whatever = TMP.Whatever;

Note: I've been using MySQL and Postgres for years so understand the basics, but this SQL Server has a lot of features that are new to me. What I can tell here is that we are filling a table with the results from another table (or something) and that is what I want to know. What is the or something? In this example, what does #Something actually reference?

I am aware of linked servers, but I don't see one in this system with the name referenced. And I also don't see any definition in this stored procedure of what #Something references.

One thought I had was that perhaps a stored procedure can use another stored procedure and in that way perhaps this #Something is defined in the calling procedure? Is this even possible?

Does this answer your question? [How to create temp table using Create statement in SQL Server?](https://stackoverflow.com/questions/43026755/how-to-create-temp-table-using-create-statement-in-sql-server) — Charlieface, Jan 31 '22 at 23:46

score 3 · Accepted Answer · edited Jan 31 '22 at 18:28

3

This indicates that the table in question is a temp table.

You can find the table by checking the system database.

SELECT * 
FROM tempdb.sys.table 
WHERE name LIKE '#something%'

You can find other procedures that reference the same table by using the following query.

DECLARE @s VARCHAR(255) = '#Something';

SELECT DISTINCT
       O.name sproc_name,
       O.type_desc,
       (
         SELECT TOP (1)
                SUBSTRING(definition,CHARINDEX(@s,definition),100)
         FROM sys.sql_modules
         WHERE object_id=M.object_id
               AND CHARINDEX(@s,definition)>0
       ) DEF
FROM sys.sql_modules M
  JOIN sys.objects O
    ON M.object_id=O.object_id
WHERE M.definition LIKE '%' + @s + '%'
ORDER BY 2,1;

edited Jan 31 '22 at 18:28

marc_s

732,580
175
1,330
1,459

answered Jan 31 '22 at 17:47

fourwhey

490
4
19

ok, that makes sense. but it's not defined in the stored procedure so where does it come from? – Dallas Caley Jan 31 '22 at 17:48
3

@DallasCaley It was created in a higher scope - i.e., the caller of the stored procedure. – SMor Jan 31 '22 at 17:50
2

Temp tables are scoped to the connection of the caller. So, tables that are created further up in the call stack are visible to things that come after. There's not an easy way to pass around a shared temp table in T-SQL so often times you may see where temp tables are referenced but are not created in the same procedure. I generally check that they exist just in case. There's also a global scoped temp table, `##something` would be globally scoped. – fourwhey Jan 31 '22 at 17:54
Actually temporary tables are scoped to the batch that created them and are visible to all subordinate batches, *unless* they are created in the top-level batch, in which case they are scoped to the session and are then visible to all batches in the session. – RBarryYoung Jan 31 '22 at 18:04
Dumb question, but is there any easy way to determine which stored procedure is the parent scope? – Dallas Caley Jan 31 '22 at 18:05
1

Not that I know of, but I'd start by searching other procedures that are referencing the temp table. They'll be using the same name. – fourwhey Jan 31 '22 at 18:08
Check my answer I've added a query I use to find other sprocs that have a matching string in their body. – fourwhey Jan 31 '22 at 18:17
Brilliant, i was not aware you could query the contents of a stored procedure – Dallas Caley Jan 31 '22 at 18:23
@RBarryYoung It's always been my understanding that the scope was the [connection](https://www.mssqltips.com/sqlservertip/6021/differences-between-sql-server-temp-tables-table-variables-subqueries-derived-tables-ctes-and-physical-tables/#:~:text=The%20scope%20for%20a%20local%20temp%20table%20is,the%20tempdb%20database%20with%20a%20drop%20table%20statement.) itself. I did some searching and found some contradicting info so perhaps batch is correct. Thank you. – fourwhey Jan 31 '22 at 18:39
1

This is explained in the [doc](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#temporary-tables), check out the first two bullets of the list in this section. Temp tables are scoped to the sesion, not the connection. And stored procedures, which execute in subordinate batches, always delete any local temp tables that they created when they go out of scope. – RBarryYoung Jan 31 '22 at 21:52
@RBarryYoung "You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned." Hmm connection still seems right, since to me session and connection are synonymous. Either way, thanks for the link. – fourwhey Feb 04 '22 at 17:26

score 3 · Answer 2 · answered Jan 31 '22 at 17:49

Object names that start with a # are temporary objects. A single # means that the object is limited to the local scope (so therefore must have been created in the same scope of a parent of it), and 2 (or more) #s at the start denote "global" temporary objects, where they can be referenced in any scope.

Either way, a temporary object is dropped once the scope that created it ends or the object is no longer in use (which ever is last).

What does # indicate in SQL Server SELECT FROM?

2 Answers2