When working with temporary tables in SQL Server, one of the most common tasks is checking whether the table already exists before creating it. This prevents errors in the event the table already exists, and ensures your scripts run smoothly regardless of previous executions.
In this article, we’ll explore five different approaches to checking for temporary table existence in SQL Server.
1. DROP TABLE IF EXISTS (SQL Server 2016+)
This is a great option for SQL Server 2016 and later versions. It only tries to drop the table if it already exists. If it doesn’t exist, then it won’t try to drop it and we won’t get an error.
Here’s an example:
DROP TABLE IF EXISTS #OrderTemp;
CREATE TABLE #OrderTemp (
OrderID INT,
OrderDate DATETIME,
CustomerID INT,
TotalAmount DECIMAL(12,2)
);
-- Insert sample data to verify the table was created
INSERT INTO #OrderTemp VALUES
(1, GETDATE(), 101, 299.99),
(2, GETDATE(), 102, 159.50);
SELECT COUNT(*) AS RecordCount FROM #OrderTemp;Output:
RecordCount
-----------
2
The DROP TABLE IF EXISTS syntax is atomic, meaning there’s no chance of race conditions between checking and dropping. It’s also the most concise and readable approach of all the options presented here.
2. OBJECT_ID() – The Best Option for Older SQL Server Versions
The OBJECT_ID() function returns the object identification number for a schema-scoped object, or NULL if the object doesn’t exist. This makes it perfect for existence checks and is the fastest method available in pre-2016 SQL Server versions. It’s also more reliable than querying system views because it’s optimized specifically for this purpose.
IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable;
CREATE TABLE #MyTempTable (
ID INT IDENTITY(1,1),
Name VARCHAR(50),
CreatedDate DATETIME DEFAULT GETDATE()
);
-- Insert some test data
INSERT INTO #MyTempTable (Name) VALUES ('Test 1'), ('Test 2');
SELECT * FROM #MyTempTable;Output:
ID Name CreatedDate
----------- -------------------------------------------------- -----------------------
1 Test 1 2025-07-30 09:20:41.677
2 Test 2 2025-07-30 09:20:41.677
The important part is specifying tempdb.. since all temporary tables are stored in the tempdb database, regardless of which database context you’re working in.
One benefit that this method has over DROP TABLE IF EXISTS is that we don’t necessarily need to drop the table. We can check OBJECT_ID() in order to do other stuff if we want. For example, we could use it to create the table only if it doesn’t exist – therefore preserving any existing data.
3. sys.tables System View – When You Need Additional Metadata
This method queries the system catalog directly, giving you more insight into SQL Server’s internal workings. In particular, it queries the tempdb.sys.tables system view.
This option can be useful when you need additional metadata about the table beyond just existence checking. The important thing to remember is that SQL Server appends random suffixes to temporary table names to ensure uniqueness across sessions, which is why we use the LIKE operator with wildcards.
IF EXISTS (
SELECT 1
FROM tempdb.sys.tables
WHERE name LIKE '#CustomerTemp%'
)
DROP TABLE #CustomerTemp;
CREATE TABLE #CustomerTemp (
CustomerID INT,
CustomerName VARCHAR(100),
City VARCHAR(50)
);
-- Demonstrate the naming convention - temp tables get unique suffixes
SELECT name, object_id, create_date
FROM tempdb.sys.tables
WHERE name LIKE '#CustomerTemp%';Output:
name object_id create_date
-------------------------------------------------------------------------------------------------------------------------------- ----------- -----------------------
#CustomerTemp_______________________________________________________________________________________________________00000000000C -1114606142 2025-07-30 09:23:20.177
This approach is more verbose than OBJECT_ID() but provides additional benefits when you need to retrieve metadata about the temporary table, such as creation date, object ID, or other system information. Use this method when you need more than just existence checking.
4. INFORMATION_SCHEMA Views – Standards-Based Approach
This method uses the ANSI standard INFORMATION_SCHEMA views, making it more portable across different database systems:
IF EXISTS (
SELECT 1
FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '#ProductTemp%'
AND TABLE_TYPE = 'BASE TABLE'
)
DROP TABLE #ProductTemp;
CREATE TABLE #ProductTemp (
ProductID INT,
ProductName VARCHAR(100),
Price DECIMAL(10,2),
CategoryID INT
);
-- Show table information using INFORMATION_SCHEMA
SELECT TABLE_NAME, TABLE_TYPE, TABLE_SCHEMA
FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '#ProductTemp%';Output:
TABLE_NAME TABLE_TYPE TABLE_SCHEMA
-------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
#ProductTemp________________________________________________________________________________________________________000000000003 BASE TABLE dbo
While this approach follows ANSI SQL standards and offers better portability, it’s generally slower than SQL Server-specific methods and still requires wildcard matching for temporary tables. Consider this method only if cross-database portability is a primary concern.
5. Exception Handling Approach – Generally Not Recommended
In some cases, a SQL developer might use exception handling to deal with table existence. While functional, it’s generally not recommended as it’s less efficient and harder to read than explicit existence checks. Exception handling should be reserved for actual error conditions, not normal control flow.
BEGIN TRY
DROP TABLE #ErrorHandlingTemp;
END TRY
BEGIN CATCH
-- Table didn't exist, which is fine
PRINT 'Table did not exist, continuing...';
END CATCH
CREATE TABLE #ErrorHandlingTemp (
ID INT,
Message VARCHAR(100),
Timestamp DATETIME DEFAULT GETDATE()
);The following message is output the first time this is run:
Started executing query at Line 1
Table did not exist, continuing...
Total execution time: 00:00:00.011
Running it again produces the usual “completed successfully” message:
Started executing query at Line 1
Commands completed successfully.
Total execution time: 00:00:00.016
Using exceptions for control flow is not normally a good idea because it’s less efficient than explicit checks, makes code harder to maintain and debug, and can mask real errors. You should only consider this approach in very specific scenarios where other methods aren’t suitable.
Summary
Here’s a quick recap:
- For SQL Server 2016 and later: Use
DROP TABLE IF EXISTS– it’s the most modern, atomic, and readable approach. - For SQL Server 2014 and earlier: Use
OBJECT_ID('tempdb..#TableName') IS NOT NULLfor maximum performance and reliability. - When you need metadata: Use
sys.tablesqueries only when you need additional information about the temporary table beyond just existence checking. - For portability concerns: Use
INFORMATION_SCHEMAviews only if cross-database portability is a primary requirement, accepting the performance trade-off. - Avoid exception handling: Never use try/catch blocks for routine existence checks – reserve them for actual error conditions.