Does LocalDB support temporary tables?
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