| Table Type | SQL Server | PostgreSQL | Scope / Lifetime | Usage Notes |
|---|---|---|---|---|
| Permanent Table | CREATE TABLE Employee (...) | CREATE TABLE Employee (...) | Permanent in DB until dropped | Used for regular data storage |
| Local Temporary Table | CREATE TABLE #Employee (...) | CREATE TEMP TABLE Employee (...) | Session-local; auto dropped on disconnect | Used for session-specific temporary data |
| Global Temp Table | CREATE TABLE ##Employee (...) | Not supported | Available to all sessions until last disconnect | Shared temp data between sessions |
| Table Variable | DECLARE @Employee TABLE (...) | Not supported | Local to batch or procedure | Lightweight table variable for procedural code |
| Common Table Expression (CTE) | N/A (but SQL Server supports it too) | WITH Employee AS (VALUES (...)) | Inline, read-only during a single query | Quick temporary inline data set for queries |
| Unlogged Table | N/A | CREATE UNLOGGED TABLE Employee (...) | Like permanent but faster, not WAL logged | For fast temp storage with risk of data loss |
Table Types Comparison: SQL Server vs PostgreSQL
Lesson 94 of 96