What is the difference between temp tables and table variables in SQL?

### 1. Syntax & DeclarationTemp Table```sql CREATE TABLE #TempTable (Id INT, Name NVARCHAR(50));```- Lives in tempdb database.- Behaves like a regular table (can have indexes, constraints).Table Variable```sql DECLARE @TableVar TABLE (Id INT, Name NVARCHAR(50)); ```- Declared inside a batch, stored procedure, or function.- Scope is limited to that block.### 2. Storage & Scope2. Storage & ScopeTemp Table- Stored physically in tempdb.- Supports indexes (clustered & non-clustered).- Scope: session & connection (exists until dropped or connection closed).Table Variable- Stored in memory (but spills to tempdb internally if large).- Limited indexing support (only primary key and unique constraints).- Scope: the batch/procedure/function it was declared in.### 3. Performance3. PerformanceTemp Table- Better for large datasets (10,000+ rows).- Query Optimizer can create statistics ? better execution plans.- Supports indexing, making complex queries faster.Table Variable- Better for small datasets (hundreds of rows).- No automatic statistics ? can lead to poor query plans.- Usually slower with large datasets.### 4. Transactions & Rollbacks4. Transactions & RollbacksTemp Table- Participates fully in transactions.- If a transaction is rolled back, data in the temp table is rolled back too.Table Variable- Not affected by rollbacks in the same way.- Once declared, exists until scope ends, regardless of rollbacks.### 5. When to Use5. When to UseUse Temp Tables when:- You’re working with large datasets.- You need indexes for performance.- You need query optimizer statistics.- You need to use it across multiple stored procedures.Use Table Variables when:- You’re working with small datasets.- You only need them inside a single procedure/function.- You want simpler syntax with less overhead.