Transact-SQL (T-SQL) Language Reference

This section provides a comprehensive reference for the Transact-SQL (T-SQL) language used in Azure SQL Database. T-SQL is an extension of SQL that adds procedural programming, local variables, character string processing, date processing, and other capabilities.

Introduction to T-SQL

Azure SQL Database supports most of the T-SQL syntax and features that are implemented in Microsoft SQL Server. T-SQL allows you to interact with your database by querying data, modifying data, and managing database objects. Understanding the syntax and semantics of T-SQL is crucial for effective database development and administration.

Core T-SQL Statements

Below are some of the most frequently used T-SQL statements:

SELECT

Retrieves data from one or more tables.

SELECT column1, column2
FROM table_name
WHERE condition;

INSERT

Adds new rows of data to a table.

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

UPDATE

Modifies existing data in a table.

UPDATE table_name
SET column1 = new_value1, column2 = new_value2
WHERE condition;

DELETE

Removes rows from a table.

DELETE FROM table_name
WHERE condition;

CREATE

Used to create new database objects such as tables, views, and stored procedures.

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

ALTER

Modifies existing database objects.

ALTER TABLE table_name
ADD column_name datatype;

DROP

Deletes database objects.

DROP TABLE table_name;

USE

Specifies the database context for subsequent commands.

USE database_name;

MERGE

Performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.

MERGE target_table AS T
USING source_table AS S
ON T.key = S.key
WHEN MATCHED THEN
    UPDATE SET T.column = S.column
WHEN NOT MATCHED BY TARGET THEN
    INSERT (key, column) VALUES (S.key, S.column)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

EXECUTE (or EXEC)

Executes a stored procedure or a batch of T-SQL statements.

EXECUTE stored_procedure_name @parameter1 = value1;

GRANT

Grants permissions on database objects.

GRANT SELECT ON schema_name.table_name TO user_name;

REVOKE

Removes permissions from database objects.

REVOKE DELETE ON schema_name.table_name TO user_name;

BACKUP

Creates a backup of a database.

BACKUP DATABASE database_name
TO DISK = 'path_to_backup.bak'
WITH FORMAT;

T-SQL Functions

T-SQL provides a rich set of built-in functions to perform calculations, manipulate strings, work with dates, and more.

Aggregate Functions

Perform calculations on a set of rows and return a single value.

AVG: Computes the average value.
COUNT: Counts the number of rows.
MAX: Finds the maximum value.
MIN: Finds the minimum value.
SUM: Computes the sum of values.

SELECT AVG(salary) AS AverageSalary
FROM employees;

Scalar Functions

Return a single value based on the input values.

GETDATE(): Returns the current database system timestamp.
LEN(): Returns the length of a string.
UPPER(): Converts a string to uppercase.
LOWER(): Converts a string to lowercase.
CONVERT(): Converts an expression from one data type to another.

SELECT UPPER(customer_name), GETDATE() AS CurrentTime
FROM customers;

Table-Valued Functions

Return a table result set.

Refer to specific documentation for creating and using table-valued functions.

Data Types

Azure SQL Database supports a wide range of data types for storing different kinds of information.

Numeric: INT, BIGINT, DECIMAL, FLOAT
Date and Time: DATETIME2, DATE, TIME
String: VARCHAR, NVARCHAR, CHAR, NCHAR
Binary: VARBINARY, BINARY
Other: BIT, UNIQUEIDENTIFIER, XML, GEOMETRY, GEOGRAPHY

Note: Always choose the most appropriate data type to optimize storage and performance.

Operators

Operators are used to perform operations on values.

Arithmetic: +, -, *, /, %
Comparison: =, >, <, >=, <=, <>, !=
Logical: AND, OR, NOT, ALL, ANY, BETWEEN, EXISTS, IN, LIKE, NULLIF, SOME
Bitwise: &, |, ^, ~

System Stored Procedures

Predefined stored procedures that perform system-level tasks, such as managing security, replication, and database maintenance.

sp_helpdb: Displays information about a database.
sp_who: Provides information about current users and processes.
sp_configure: Shows or changes server configuration options.

Tip: Explore system stored procedures to understand and manage your Azure SQL Database effectively.

This reference covers the fundamental aspects of T-SQL in Azure SQL Database. For detailed syntax, advanced features, and specific function descriptions, please refer to the official Microsoft documentation.