Kdjykuj

Are SQL Server temporary tables (prefixed with #) supported in LocalDB instances?

I can't answer for all versions, but for SQL Server 2012 up until SQL Server 2017 I am certain that they are supported

LocalDB has the same programmability features as SQL Server Express.

SQL Server Express LocalDB, a lightweight version of Express that has
all of its programmability features, yet runs in user mode and has a
fast, zero-configuration installation and a short list of
prerequisites.

Source

And then, building on the previous point, for the SQL Server express 2012 T-SQL Syntax

Express supports the same T-SQL language elements you find in any
edition of SQL Server. Not only can you issue data manipulation
language queries against the database, but you can also run data
definition language statements to create such objects as views,
triggers, cursors and stored procedures

Source

Testing (SQL Server 2017)

USE testdb

GO

CREATE TABLE #temp (id int , value nvarchar(255));



INSERT INTO #temp( id ,value)

SELECT 5, 'bla';



SELECT * FROM #temp;

result

id  value

5   bla

The temporary table also works when changing the db's compatibility mode to 100 (2008).

Yes, all forms of temporary objects (local temp tables, global temp tables, table variables, local temp stored procedures, and global temp stored procedures) are available in all versions of SQL Server Express LocalDB.

I have executed the following simple test of each of those 5 objects types across versions 2012, 2014, 2016, and 2017 and received no errors.

CREATE TABLE #LocalTempTable (Col1 INT);

SELECT * FROM #LocalTempTable;





CREATE TABLE ##GlobalTempTable (Col1 INT);

SELECT * FROM ##GlobalTempTable;





DECLARE @TableVariable TABLE (Col1 INT);

SELECT * FROM @TableVariable;





GO

CREATE PROCEDURE #LocalTempProc

AS

SELECT 1;

GO

EXEC #LocalTempProc;



GO

CREATE PROCEDURE ##GlobalTempProc

AS

SELECT 2;

GO

EXEC ##GlobalTempProc;

Also, SQL Server would likely not even start up if these were not available as they are used in system stored procedures, features found in msdb, etc.

Yes, Localdb supports temporay tables.

USE [test];

GO



CREATE TABLE #mytemp

(

    id int,

    foo int,

    bar int

);

GO



SELECT 

    *

FROM   

    tempdb.INFORMATION_SCHEMA.TABLES;

GO



DROP TABLE #mytemp;

GO

Returns:

TABLE_CATALOG    TABLE_SCHEMA     TABLE_NAME                            TABLE_TYPE

---------------- ---------------- ------------------------------------  ----------

tempdb           dbo              #mytemp________________00000000000A   BASE TABLE

And querying sys.databases:

SELECT 

    name,

    database_id

FROM

    sys.databases;

returns same system databases structure.

name      database_id

--------- -----------

master    1

tempdb    2

model     3

msdb      4

test      5

Agree with @RandiVertongen.

LocalDB installation copies a minimal set of files necessary to start the SQL Server Database Engine. Once LocalDB is installed, you can initiate a connection using a special connection string. When connecting, the necessary SQL Server infrastructure is automatically created and started, enabling the application to use the database without complex configuration tasks. Developer Tools can provide developers with a SQL Server Database Engine that lets them write and test Transact-SQL code without having to manage a full server instance of SQL Server.

For more information on LocalDB refer official link source

Note it has restrictions:

1) LocalDB cannot be a merge replication subscriber.

2) LocalDB does not support FILESTREAM.

3) LocalDB only allows local queues for Service Broker.

4) An instance of LocalDB owned by the built-in accounts such as NT AUTHORITYSYSTEM can have manageability issues due to windows file system redirection; Instead use a normal windows account as the owner.

I hope this helps!