SQL GLOBAL

What is the SQL GLOBAL keyword?

Marks a temporary table or system variable as having global (server-wide) scope rather than local or session scope.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL GLOBAL: Oracle, MySQL, MariaDB, DB2, SAP HANA, Snowflake (as CREATE TEMPORARY TABLE with GLOBAL semantics), SQL Standard. Limited or no support in PostgreSQL, SQL Server, SQLite.

SQL GLOBAL Full Explanation

GLOBAL is a reserved SQL keyword that serves as a scope qualifier. In the SQL standard it appears in the construct CREATE GLOBAL TEMPORARY TABLE, which defines a temporary table whose definition is permanent in the catalog but whose data is private to each session. Many commercial databases adopt the same syntax. In MySQL and MariaDB, GLOBAL is also used with SET and SHOW to reference server-level system variables and status counters. Although GLOBAL never stands alone, understanding its meaning is critical for correctly handling temporary objects and configuration changes across sessions.Behavior by context:1. CREATE GLOBAL TEMPORARY TABLE - The table definition is stored in the schema. - Each session sees an empty copy of the table; rows disappear at session end (or optionally at transaction end in Oracle).2. SET GLOBAL system_variable = value - Changes the variable for all future sessions until the server restarts or another SET GLOBAL overrides it.3. SHOW GLOBAL STATUS / SHOW GLOBAL VARIABLES - Returns server-wide counters or configuration values.Caveats- GLOBAL does not make a temporary table share data across sessions; only the definition is global.- Setting GLOBAL variables may require SUPER or SYSTEM_VARIABLES_ADMIN privileges.- Some dialects (PostgreSQL, SQLite) reserve the word but do not implement GLOBAL-scoped temporary tables.- GLOBAL temporary tables cannot be indexed with non-temporary indexes in several systems.

SQL GLOBAL Syntax

-- Standard and Oracle syntax
a. CREATE GLOBAL TEMPORARY TABLE table_name (
       column_definition [, ...]
   );

-- MySQL / MariaDB variable syntax
b. SET GLOBAL system_variable = value;

c. SHOW GLOBAL STATUS;
d. SHOW GLOBAL VARIABLES;

SQL GLOBAL Parameters

table_name (identifier) - Name of the temporary table to create.
column_definition (list) - Column names, types, and constraints.
system_variable (identifier) - Name of a server variable (e.g., max_connections).
value (expression) - New value assigned to the variable.

Example Queries Using SQL GLOBAL

-- 1. Create a global temporary table (Oracle, Standard)
CREATE GLOBAL TEMPORARY TABLE temp_sales (
    sale_id      INTEGER,
    sale_amount  NUMERIC(12,2)
);

-- 2. Insert session-specific rows
INSERT INTO temp_sales VALUES (1, 125.00);

-- 3. Query remains visible only in current session
SELECT * FROM temp_sales;

-- 4. Change a MySQL server variable globally
SET GLOBAL max_connections = 300;

-- 5. Inspect server-wide status
SHOW GLOBAL STATUS LIKE 'Threads_connected';

Expected Output Using SQL GLOBAL

The table definition is stored permanently, but the INSERT affects only the current session.
SELECT returns the rows just inserted for this session.
SET GLOBAL modifies max_connections for all future connections.
SHOW GLOBAL STATUS returns the current server counter value.

Use Cases with SQL GLOBAL

Creating temporary staging tables that every session can reference without recreating the DDL.
Ensuring a server variable change applies to all new sessions (e.g., raising open_file_limit during maintenance).
Auditing server health by inspecting SHOW GLOBAL STATUS values.
Writing migration scripts that must run on different databases supporting GLOBAL TEMPORARY TABLE syntax.

Common Mistakes with SQL GLOBAL

Assuming rows in a GLOBAL TEMPORARY TABLE are shared across sessions.
Forgetting that GLOBAL variable changes in MySQL require elevated privileges.
Omitting TEMPORARY after GLOBAL, which results in a syntax error.
Using GLOBAL in PostgreSQL expecting temporary-table behavior that PostgreSQL does not support.

First Introduced In

SQL:1999

Frequently Asked Questions

What is the difference between GLOBAL and LOCAL temporary tables?

GLOBAL temporary tables have a permanent definition and session-private data, while LOCAL temporary tables (supported in some systems) may have both definition and data tied to the current session only.

Does SET GLOBAL require a server restart?

No. The change is effective immediately for new sessions and lasts until the server shuts down or another SET GLOBAL overrides it.

Can I use GLOBAL in PostgreSQL?

PostgreSQL reserves the word GLOBAL but does not implement GLOBAL TEMPORARY TABLE or GLOBAL variable scope. You must use CREATE TEMP TABLE instead.

Are privileges required for SET GLOBAL?

Yes. In MySQL you need the SUPER or SYSTEM_VARIABLES_ADMIN privilege to modify most GLOBAL variables.