SQL T-SQL Queries - MSDN Documentation

Introduction to T-SQL Queries

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL standard. It is the primary means of interacting with Microsoft SQL Server, allowing you to retrieve, manipulate, and manage data.

This section delves into the fundamental aspects of constructing T-SQL queries, covering essential clauses, operators, and best practices for efficient data retrieval.

Core Query Concepts

SELECT Statement: The foundation for retrieving data from one or more tables.
FROM Clause: Specifies the table(s) from which to retrieve rows.
WHERE Clause: Filters records based on specified conditions.
ORDER BY Clause: Sorts the result set in ascending or descending order.
GROUP BY Clause: Groups rows that have the same values in specified columns into summary rows.
HAVING Clause: Filters groups based on specified conditions.

Basic SELECT Statement

The most basic query retrieves all columns and all rows from a table. The asterisk (*) is a wildcard that represents all columns.

SELECT *
FROM Customers;

Selecting Specific Columns

You can specify the exact columns you want to retrieve by listing them after the SELECT keyword.

SELECT CustomerID, CompanyName, ContactName
FROM Customers;

Filtering Data with WHERE

The WHERE clause is used to extract only those records that fulfill specified criteria.

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > 50;

Common WHERE Operators:

Comparison Operators: =, <>, !=, >, <, >=, <=
Logical Operators: AND, OR, NOT
Set Operators: IN, BETWEEN
Pattern Matching: LIKE
Null Check: IS NULL, IS NOT NULL

Sorting Results with ORDER BY

The ORDER BY clause is used to sort the result set in ascending or descending order by one or more columns.

SELECT EmployeeID, LastName, FirstName, HireDate
FROM Employees
ORDER BY LastName ASC, FirstName ASC;

ASC (ascending) is the default if not specified. DESC specifies descending order.

Aggregating Data with GROUP BY

When you need to perform calculations on groups of rows, use the GROUP BY clause. Aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() are typically used with GROUP BY.

SELECT Country, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY Country
ORDER BY NumberOfCustomers DESC;

Filtering Groups with HAVING

The HAVING clause is used to filter groups based on a specified condition. It is similar to the WHERE clause, but it operates on aggregated results.

SELECT City, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY City
HAVING COUNT(CustomerID) > 5
ORDER BY NumberOfCustomers DESC;

Advanced Query Techniques

T-SQL offers powerful features for complex data manipulation and analysis.

Joins

Joins are used to combine rows from two or more tables based on a related column between them.

INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)

SELECT o.OrderID, c.CompanyName
FROM Orders AS o
INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID;

Subqueries

A subquery is a query nested inside another SQL query. It can be used in the WHERE, FROM, or SELECT clause.

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice = (SELECT MAX(UnitPrice) FROM Products);

Common Table Expressions (CTEs)

CTEs provide a temporary named result set that you can reference within a single SQL statement.

WITH HighValueOrders AS (
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE TotalAmount > 1000
)
SELECT COUNT(*) AS NumberOfHighValueOrders
FROM HighValueOrders;

Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not cause rows to be collapsed into a single output row.

SELECT
    ProductName,
    UnitPrice,
    ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS RowNum
FROM Products;

Query Optimization and Performance

Writing efficient queries is crucial for database performance. Consider the following:

Indexing: Proper indexing can dramatically speed up query execution.
Execution Plans: Analyze query execution plans to identify bottlenecks.
Avoiding Cursors: Whenever possible, use set-based operations instead of cursors.
SELECT Specific Columns: Avoid SELECT * in production code; select only the columns you need.
Efficient WHERE Clauses: Use indexed columns in WHERE clauses and avoid functions on indexed columns if possible.

Transact-SQL (T-SQL) Queries