T-SQL Documentation

Introduction to Transact-SQL (T-SQL)

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL language used by Microsoft SQL Server and Azure SQL Database. It adds procedural programming, local variables, string and date processing functions, and other constructs for procedural programming.

T-SQL is a powerful language that allows you to interact with and manage your relational databases effectively. This page provides a quick reference to common T-SQL concepts and syntax.

Basic Syntax

T-SQL statements are generally composed of keywords, identifiers (like table and column names), and operators. Statements are typically terminated by a semicolon (;), although it's not always strictly required for single statements.

Keywords

Common keywords include SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, WHERE, FROM, JOIN, GROUP BY, ORDER BY, BEGIN, END, IF, WHILE, etc.

Identifiers

Table names, column names, and other database objects can be identifiers. They can be standard or quoted (using double quotes " or square brackets []).

-- Standard identifier
SELECT FirstName, LastName FROM Employees;

-- Quoted identifier (if name contains spaces or is a reserved word)
SELECT [First Name], [Last Name] FROM "Employee Records";

Data Definition Language (DDL)

DDL statements are used to define, alter, and drop database objects.

`CREATE TABLE`

Creates a new table in the database.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2) CHECK (Price >= 0),
    CategoryID INT FOREIGN KEY REFERENCES Categories(CategoryID)
);

`ALTER TABLE`

Modifies an existing table structure.

-- Add a new column
ALTER TABLE Products
ADD StockQuantity INT DEFAULT 0;

-- Modify an existing column
ALTER TABLE Products
ALTER COLUMN ProductName VARCHAR(300) NOT NULL;

-- Drop a column
ALTER TABLE Products
DROP COLUMN CategoryID;

`DROP TABLE`

Deletes a table and all its data.

DROP TABLE Products;

Data Manipulation Language (DML)

DML statements are used to manage data within database objects.

`SELECT`

Retrieves data from one or more tables.

SELECT
    ProductID,
    ProductName,
    Price
FROM
    Products
WHERE
    Price > 50.00
ORDER BY
    ProductName ASC;

`INSERT`

Adds new rows of data into a table.

INSERT INTO Products (ProductName, Price, CategoryID)
VALUES ('Laptop', 1200.00, 1);

`UPDATE`

Modifies existing data in a table.

UPDATE Products
SET Price = Price * 1.05
WHERE CategoryID = 2;

`DELETE`

Removes rows from a table.

DELETE FROM Products
WHERE ProductName = 'Old Monitor';

Data Control Language (DCL)

DCL statements manage permissions and access to data.

`GRANT`

Gives users access rights to the database.

GRANT SELECT ON Products TO SalesUser;

`REVOKE`

Removes users' access rights.

REVOKE DELETE ON Orders TO GuestUser;

Transactions

Transactions allow you to group a sequence of operations into a single logical unit of work. They ensure data integrity by adhering to ACID properties (Atomicity, Consistency, Isolation, Durability).

`BEGIN TRANSACTION`, `COMMIT TRANSACTION`, `ROLLBACK TRANSACTION`

BEGIN TRANSACTION;
  UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
  UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT TRANSACTION; -- Or ROLLBACK TRANSACTION; if an error occurs

`SAVE TRANSACTION`

Allows you to roll back to a specific point within a transaction.

BEGIN TRANSACTION;
  -- Some operations
  SAVE TRANSACTION SavePoint1;
  -- More operations
  ROLLBACK TRANSACTION SavePoint1; -- Rolls back to SavePoint1
COMMIT TRANSACTION;

Variables

T-SQL supports local variables, which can be declared and used within batches or stored procedures.

Declaring and Assigning

DECLARE @MyVariable INT;
SET @MyVariable = 10;

DECLARE @ProductName VARCHAR(100) = 'New Gadget';
SELECT @ProductName AS CurrentProductName;

Control Flow

T-SQL provides constructs for controlling the execution flow of your scripts and procedures.

`IF...ELSE`

Executes statements based on a condition.

DECLARE @Quantity INT = 50;
IF @Quantity > 100
BEGIN
    PRINT 'High stock level.';
END
ELSE
BEGIN
    PRINT 'Stock level is moderate or low.';
END;

`WHILE`

Executes statements repeatedly as long as a condition is true.

DECLARE @Counter INT = 1;
WHILE @Counter <= 5
BEGIN
    PRINT 'Iteration: ' + CAST(@Counter AS VARCHAR);
    SET @Counter = @Counter + 1;
END;

`CASE` Expression

A conditional expression that returns a value from a list of possibilities.

SELECT
    ProductName,
    Price,
    CASE
        WHEN Price < 20.00 THEN 'Inexpensive'
        WHEN Price BETWEEN 20.00 AND 100.00 THEN 'Moderately Priced'
        ELSE 'Expensive'
    END AS PriceCategory
FROM
    Products;

Cursors

Cursors allow row-by-row processing of a result set. Use them judiciously, as they can be inefficient for large datasets. Set-based operations are generally preferred.

DECLARE @ProductName VARCHAR(100);
DECLARE ProductCursor CURSOR FOR
SELECT ProductName FROM Products WHERE Price < 30.00;

OPEN ProductCursor;
FETCH NEXT FROM ProductCursor INTO @ProductName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Found low-price item: ' + @ProductName;
    FETCH NEXT FROM ProductCursor INTO @ProductName;
END;

CLOSE ProductCursor;
DEALLOCATE ProductCursor;

Error Handling

T-SQL provides mechanisms to handle errors gracefully.

`TRY...CATCH`

Catches errors that occur within the TRY block and allows for error handling in the CATCH block.

BEGIN TRY
    -- Code that might raise an error
    SELECT 1/0; -- This will cause a divide-by-zero error
END TRY
BEGIN CATCH
    PRINT 'An error occurred:';
    PRINT ERROR_MESSAGE();
    -- You can also use ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE()
END CATCH;

`@@ERROR` (Deprecated but still seen)

Returns the error number of the last executed Transact-SQL statement. It's reset to 0 if the previous statement executed successfully or if the statement is within a TRY...CATCH block.

Common Functions

T-SQL offers a rich set of built-in functions for various purposes.

String Functions

LEN() / DATALENGTH(): Returns the length of a string.
SUBSTRING(): Extracts a portion of a string.
REPLACE(): Replaces all occurrences of a substring.
UPPER() / LOWER(): Converts string to uppercase/lowercase.
LEFT() / RIGHT(): Returns the specified number of characters from the left/right.

Date Functions

GETDATE(): Returns the current database system date and time.
DATEADD(): Adds or subtracts a specified time interval from a date.
DATEDIFF(): Returns the difference between two dates.
YEAR(), MONTH(), DAY(): Extracts parts of a date.
FORMAT(): Formats a date value.

Aggregate Functions

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

Conversion Functions

CAST() / CONVERT(): Converts an expression from one data type to another.

System Functions

@@ROWCOUNT: Returns the number of rows affected by the last statement.
@@IDENTITY: Returns the last identity value inserted into an identity column in the same scope.
COALESCE(): Returns the first non-NULL expression.

Example: Using `GETDATE()` and `DATEADD()`

SELECT
    ProductName,
    Price,
    GETDATE() AS CurrentDateTime,
    DATEADD(day, 7, GETDATE()) AS DateInOneWeek
FROM
    Products
WHERE
    Price > 100.00;

Introduction to Transact-SQL (T-SQL)

Basic Syntax

Keywords

Identifiers

Data Definition Language (DDL)

CREATE TABLE

ALTER TABLE

DROP TABLE

Data Manipulation Language (DML)

SELECT

INSERT

UPDATE

DELETE