SQL Server: Global Temporary tables

This SQL Server tutorial explains how to use the GLOBAL TEMPORARY TABLES in SQL Server (Transact-SQL) with syntax and examples.

Description

GLOBAL TEMPORARY TABLES in SQL Server (Transact-SQL) are tables that are created distinct within the SQL Server sessions.

Syntax

The syntax for CREATE GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL) is:

CREATE TABLE ##table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

);

Parameters or Arguments

table_name

The name of the global temporary table that you wish to create. The name of the global temporary table starts with ## characters.

column1, column2

The columns that you wish to create in the global temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Note

• The name of GLOBAL TEMPORARY TABLES are prefixed with ## characters (ie: ##employees).

Example

Let's look at an example of how to create a GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL).

For example:

CREATE TABLE #EmpDetails

( employee_id INT PRIMARY KEY,

  last_name VARCHAR(200) NOT NULL,

  first_name VARCHAR(200),

);

This example would create a GLOBAL TEMPORARY TABLE called ## EmpDetails in SQL Server which has 4 columns.

• The first column is called employee_id which is created as an INT datatype and cannot contain NULL values.
• The second column is called last_name which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
• The third column is called first_name which is a VARCHAR datatype but can contain NULL values.
• The primary key for the #employees table is the employee_id column.

This ## EmpDetails table is stored in tempdb and SQL Server will automatically delete this table when all users referencing the table have disconnected from the SQL Server session.

SQL Server: LOCAL TEMPORARY TABLES

This SQL Server tutorial explains how to use the LOCAL TEMPORARY TABLES in SQL Server (Transact-SQL) with syntax and examples.

Description

LOCAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within SQL Server sessions. LOCAL TEMPORARY TABLES are stored in tempdb and SQL Server automatically deletes these tables when they are no longer used.

Syntax

The syntax to create a LOCAL TEMPORARY TABLE in SQL Server (Transact-SQL) is:

CREATE TABLE #table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

);

Parameters or Arguments

table_name

The name of the local temporary table that you wish to create. The name of the local temporary table starts with the # character.

column1, column2

The columns that you wish to create in the local temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Note

• The name of LOCAL TEMPORARY TABLES are prefixed with the # character (ie: #employees).

Example

Let's look at an example of how to create a LOCAL TEMPORARY TABLE in SQL Server (Transact-SQL).

For example:

CREATE TABLE #EmpDetails

( employee_id INT PRIMARY KEY,

  last_name VARCHAR(200) NOT NULL,

  first_name VARCHAR(200),

);

This example would create a LOCAL TEMPORARY TABLE called #EmpDetails in SQL Server which has 4 columns.

• The first column is called employee_id which is created as an INT datatype and can not contain NULL values.
• The second column is called last_name which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
• The third column is called first_name which is a VARCHAR datatype but can contain NULL values.
• The primary key for the #employees table is the employee_id column.

This #EmpDetails table is stored in tempdb and SQL Server will automatically delete this table when the SQL Server session no longer requires it

SQL GROUP Functions

SQL GROUP Functions

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.

For Example: If you want the number of employees in a particular department, the query would be:

SELECT COUNT (*) FROM EmpDetails WHERE dept = 'HR';

If you want the total number of employees in all the department, the query would take the form:

SELECT COUNT (*) FROM EmpDetails

SQL DISTINCT(): This function is used to select the distinct rows.

For Example: If you want to select all distinct department names from EmpDetails table, the query would be:

SELECT DISTINCT dept FROM EmpDetails

To get the count of employees with unique name, the query would be:

SELECT COUNT (DISTINCT name) FROM EmpDetails;

SQL MAX(): This function is used to get the maximum value from a column.

To get the maximum salary drawn by an EmpDetails, the query would be:

SELECT MAX (salary) FROM EmpDetails;

SQL MIN(): This function is used to get the minimum value from a column.

To get the minimum salary drawn by an EmpDetails, he query would be:

SELECT MIN (salary) FROM EmpDetails;

SQL AVG(): This function is used to get the average value of a numeric column.

To get the average salary, the query would be

SELECT AVG (salary) FROM EmpDetails;

SQL SUM(): This function is used to get the sum of a numeric column

To get the total salary given out to the EmpDetails,

SELECT SUM (salary) FROM EmpDetails;

SQL - ALTER TABLE Command

 The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table.

You would also use ALTER TABLE command to add and drop various constraints on an existing table.

The basic syntax of ALTER TABLE to add a new column in an existing table is as follows:

ALTER TABLE table_name ADD column_name datatype;

The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

The basic syntax of ALTER TABLE to add a NOT NULL constraint to a column in a table is as follows:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows:

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

The basic syntax of ALTER TABLE to ADD CHECK CONSTRAINT to a table is as follows:

ALTER TABLE table_name

ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as follows:

ALTER TABLE table_name

ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

The basic syntax of ALTER TABLE to DROP CONSTRAINT from a table is as follows:

ALTER TABLE table_name

DROP CONSTRAINT MyUniqueConstraint;

The basic syntax of ALTER TABLE to DROP PRIMARY KEY constraint from a table is as follows:

ALTER TABLE table_name

DROP CONSTRAINT MyPrimaryKey;

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

• SELECT - extracts data from a database

• UPDATE - updates data in a database

• DELETE - deletes data from a database

• INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:

• CREATE DATABASE - creates a new database

• ALTER DATABASE - modifies a database

• CREATE TABLE - creates a new table

• ALTER TABLE - modifies a table

• DROP TABLE - deletes a table

• CREATE INDEX - creates an index (search key)

• DROP INDEX - deletes an index