GLOBAL

Example

CREATE DATABASE testDB;
USE testDB;

CREATE TABLE Employees (
    Id INT,
    Name VARCHAR(50),
    Salary DECIMAL(8, 2)
);

INSERT INTO Employees (Id, Name, Salary) Values (1, 'John Doe', 75000.00);

SET GLOBAL max_connections = 200;
SHOW GLOBAL VARIABLES LIKE 'max_connections';

Output

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+

Explanation

In the example code, a database called testDB is created and used. A table Employees is then created in testDB, followed by an insertion of a record into it.

Then the session variable max_connections is set to 200 at a global level with SET GLOBAL max_connections = 200;.

The SHOW GLOBAL VARIABLES LIKE 'max_connections'; command is used to display the globally set variable for ‘max_connections’, which displays the max_connections value as 200 in the output as proof of the successful execution.

Example

SELECT *
FROM ::fn_helpcollations()
WHERE [name] = 'Latin1_General_CI_AI';

Output

| name                 | description                                                  |
|----------------------|--------------------------------------------------------------|
| Latin1_General_CI_AI | Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data |

Explanation

In the example, the GLOBAL keyword is implied with the double colons (::) before fn_helpcollations(). This function is a global function and it fetches a list of all collations and their properties in SQL Server. The WHERE clause filters for a specific collation 'Latin1_General_CI_AI', and the output is a record of this collation and its properties.

Example

GLOBAL TEMPORARY TABLE session_addresses AS
SELECT user_id, address FROM addresses WHERE 1 = 0;

Output

No output produced for table creation. Executing SELECT statement on session_addresses would result in:

SELECT * FROM session_addresses;

Explanation

GLOBAL temporary tables are visible to all sessions, but the data is session-specific. The statement above creates a global temporary table called session_addresses, structured similarly to the addresses table but initially empty as denoted by WHERE 1 = 0. The SELECT * FROM session_addresses; statement retrieves data from the session_addresses table, but as the table is initially empty, no rows are returned.