T-SQL Learning Notes

Each unique combination of values in the constraint attributes can appear only once in the table—in other words, only in one row.

An attempt to define a primary key constraint on a column that allows NULLs will be rejected by the RDBMS.

Each table can have only one primary key.

A unique index is a physical object used by SQL Server to enforce uniqueness.

Indexes (not necessarily unique ones) are also used to speed up queries by avoiding sorting and unnecessary full table scans (similar to indexes in books).

Consequently, for example, a single-column unique constraint allows only one NULL in the constrained column.

In other words, only the non-NULL values are compared to determine whether duplicates exist.

Consequently, a standard single-column unique constraint would allow multiple NULLs in the constrained column.

It is defined on one or more attributes in what’s called the referencing table and points to candidate key (primary key or unique constraint) attributes in what’s called the referenced table.

Note that the referencing and referenced tables can be one and the same.

The foreign key’s purpose is to restrict the values allowed in the foreign key columns to those that exist in the referenced columns.

It’s an expression that is used as the default value when an explicit value is not specified for the attribute when inserting a row.

If the query is a grouped query, all phases subsequent to the GROUP BY phase— including HAVING, SELECT, and ORDER BY—operate on groups as opposed to operating on individual rows.

Each group is ultimately represented by a single row in the final result of the query.

All expressions specified in clauses that are processed in phases subsequent to the GROUP BY phase are required to guarantee returning a scalar (single value) per group.

SELECT empid, YEAR(orderdate) AS orderyear, freight -- sum(freight) AS totalfreight
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate);

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.Orders.freight' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Total execution time: 00:00:00.016

Only groups for which the HAVING predicate evaluates to TRUE are returned by the HAVING phase to the next logical query processing phase.

Groups for which the predicate evaluates to FALSE or UNKNOWN are discarded.

The HAVING clause is processed after the rows have been grouped, so aggregate functions can be referred to in the HAVING filter predicate.

SELECT empid, YEAR(orderdate) AS orderyear, SUM(freight) AS totalfreight
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
-- filters only groups (employee and order year) with more than one row, and total freight with more than 500.0
HAVING COUNT(*) > 1 AND SUM(freight) > 500.0
ORDER BY empid, YEAR(orderdate)

1	2021	711.13
2	2022	672.16
4	2022	651.83
6	2021	628.31
7	2022	1231.56

The SELECT clause is processed after the FROM, WHERE, GROUP BY, and HAVING clauses, which means that aliases assigned to expressions in the SELECT clause do not exist as far as clauses that are processed before the SELECT clause are concerned.

SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE orderyear > 2021;

Msg 207, Level 16, State 1, Line 3
Invalid column name 'orderyear'.

SELECT orderid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE YEAR(orderdate) > 2021;

With T-SQL, elements can also be specified in the ORDER BY clause that do not appear in the SELECT clause, meaning to sort by something that don’t necessarily want to be returned.

SELECT empid, firstname, lastname, country
FROM HR.Employees
ORDER BY hiredate;

However, when the DISTINCT clause is specified, the ORDER BY are restricted to list only elements that appear in the SELECT list.

SELECT DISTINCT empid, firstname, lastname, country
FROM HR.Employees
ORDER BY hiredate;

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

In other words, the ordering is not strict total ordering. Multiple rows can have the same order date.

In such a case, the ordering among rows with the same order date is undefined, which makes the query nondeterministic—more than one result can be considered correct.

In case of ties, SQL Server filters rows based on optimization choices and physical access order.

Note that when using the TOP filter in a query without an ORDER BY clause, the ordering is completely undefined—SQL Server returns whichever n rows it happens to physically access first, where n is the requested number of rows.

To make the query be deterministic, a strict total ordering is needed; in other words, add a tiebreaker.

SELECT TOP (5)
  orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC; -- the row with the greater order ID value will be preferred.

11077	2022-05-06	65	1
11076	2022-05-06	9	4
11075	2022-05-06	68	8
11074	2022-05-06	73	7
11073	2022-05-05	58	2

Instead of adding a tiebreaker to the ORDER BY list, a request can be made to return all ties by adding the WITH TIES option.

SELECT TOP (5) WITH TIES
  orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

For each row in the underlying query, the OVER clause exposes to the function a subset of the rows from the underlying query’s result set.

The OVER clause can restrict the rows in the window by using an optional window partition clause (PARTITION BY).

It can define ordering for the calculation (if relevant) using a window order clause (ORDER BY)—not to be confused with the query’s presentation ORDER BY clause.

The IN predicate is used to check whether a value, or scalar expression, is equal to at least one of the elements in a set.

SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid IN(10248, 10249, 10250);

The BETWEEN predicate is used to to check whether a value falls within a specified range, INCLUSIVE of the two delimiters of the range.

SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderid BETWEEN 10300 AND 10310;

The LIKE predicate is used to check whether a character string value meets a specified pattern.

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE N'D%';

T-SQL supports the following comparison operators: =, >, <, >=, <=, <>, !=, !>, and !<, of which the last three are not standard and should be avoided using.

SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20220101';

The logical operators OR, AND, and NOT are used to combine logical expressions.

SELECT orderid, empid, orderdate
FROM Sales.Orders
WHERE orderdate >= '20220101'
  AND empid NOT IN(1, 3, 5);

T-SQL supports the four obvious arithmetic operators: +, -, *, and /, and also supports the % operator (modulo), which returns the remainder of integer division.

SELECT orderid, productid, qty, unitprice, discount,
  qty * unitprice * (1 - discount) AS val
FROM Sales.OrderDetails;

It operates on the two tables provided as inputs and produces a Cartesian product of the two, that is, each row from one input is matched with all rows from the other.

-- SQL-92 syntax
SELECT C.custid, E.empid
FROM Sales.Customers AS C
   CROSS JOIN HR.Employees AS E;

-- SQL-89 syntax (not recommended)
SELECT C.custid, E.empid
FROM Sales.Customers AS C, HR.Employees AS E;

-- Self cross joins
SELECT
  E1.empid, E1.firstname, E1.lastname,
  E2.empid, E2.firstname, E2.lastname
FROM HR.Employees AS E1
   CROSS JOIN HR.Employees AS E2;

DROP TABLE IF EXISTS dbo.Digits;
CREATE TABLE dbo.Digits (digit INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.Digits(digit)
  VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
-- Producing tables of numbers
SELECT D3.digit * 100 + D2.digit * 10 + D1.digit + 1 AS n
FROM dbo.Digits AS D1
   CROSS JOIN dbo.Digits AS D2
   CROSS JOIN dbo.Digits AS D3
ORDER BY n;

1
2
3
. . .
998
999
1000

When a join condition involves only an equality operator, the join is said to be an equi join.

When a join condition involves any operator besides equality, the join is said to be a non-equi join.

SELECT
  E1.empid, E1.firstname, E1.lastname,
  E2.empid, E2.firstname, E2.lastname
FROM HR.Employees AS E1
  INNER JOIN HR.Employees AS E2
  ON E1.empid < E2.empid;

The OUTER keyword is optional.

The LEFT keyword means that the rows of the left table (the one to the left of the JOIN keyword) are preserved; the RIGHT keyword means that the rows in the right table are preserved; and the FULL keyword means that the rows in both the left and right tables are preserved.

The third logical query processing phase of an outer join identifies the rows from the preserved table that did not find matches in the other table based on the ON predicate, which adds those rows to the result table produced by the first two phases of the join, and it uses NULLs as placeholders for the attributes from the nonpreserved side of the join in those outer rows.

SELECT C.custid, C.companyname, O.orderid
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
  ON C.custid = O.custid;

CTEs also support two forms of column aliasing: inline and external. For the inline form, specify <expression> AS <column_alias>; for the external form, specify the target column list in parentheses immediately after the CTE name.

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;

WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;

Each CTE can refer to all previously defined CTEs, and the outer query can refer to all CTEs.

WITH C1 AS
(
  SELECT YEAR(orderdate) AS orderyear,
    custid
  FROM Sales.Orders
),
C2 AS
(
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C1
  GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;

Multiple references in CTEs in table operators like joins

WITH YearlyCount AS
(
  SELECT YEAR(orderdate) AS orderyear,
    COUNT(DISTINCT custid) AS numcusts
  FROM Sales.Orders
  GROUP BY YEAR(orderdate)
)
SELECT Cur.orderyear,
  Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
  Cur.numcusts - Prv.numcusts AS growth
FROM YearlyCount AS Cur
  LEFT OUTER JOIN YearlyCount AS Prv
  ON Cur.orderyear = Prv.orderyear + 1;

CTEs are unique among table expressions in the sense that they support recursion.

WITH <CTE_Name>[(<target_column_list>)]
AS
(
  <anchor_member>
  UNION ALL
  <recursive_member>
)
<outer_query_against_CTE>;

Remember that a presentation ORDER BY clause is not allowed in the query defining a table expression because a relation isn’t ordered.

CREATE OR ALTER VIEW Sales.USACusts
AS
  SELECT
    custid, companyname, contactname, contacttitle, address,
    city, region, postalcode, country, phone, fax
  FROM Sales.Customers
  WHERE country = N'USA'
  ORDER BY region;
GO

Msg 1033, Level 15, State 1, Procedure USACusts, Line 8
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Inline TVFs are reusable table expressions that support input parameters.

A CROSS APPLY operator is equavelent to a CROSS JOIN.

SELECT S.shipperid, E.empid
FROM Sales.Shippers AS S
  CROSS JOIN HR.Employees AS E;

SELECT S.shipperid, E.empid
FROM Sales.Shippers AS S
  CROSS APPLY HR.Employees AS E;

With APPLY, the left side is evaluated first, and the right side is evaluated per row from the left, and can have references to elements from the left.

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
  CROSS APPLY
    (SELECT TOP (3) orderid, empid, orderdate, requireddate
     FROM Sales.Orders AS O
     WHERE O.custid = C.custid
     ORDER BY orderdate DESC, orderid DESC) AS A; -- A is a correlated derived table

1	11011	2022-04-09
1	10952	2022-03-16
1	10835	2022-01-15
2	10926	2022-03-04
2	10759	2021-11-28
2	10625	2021-08-08
3	10856	2022-01-28
3	10682	2021-09-25
3	10677	2021-09-22
. . .

If the right table expression returns an empty set, the CROSS APPLY operator does not return the corresponding left row. To return rows from the left side even if there are no matches on the right side, use OUTER APPLY.

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
  OUTER APPLY
    (SELECT orderid, empid, orderdate, requireddate
     FROM Sales.Orders AS O
     WHERE O.custid = C.custid AND O.custid in (22, 57)
     ORDER BY orderdate DESC, orderid DESC
     OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY) AS A;

1	NULL	NULL
2	NULL	NULL
3	NULL	NULL

It’s more conventional to work with inline TVFs instead of derived tables.

CREATE OR ALTER FUNCTION dbo.TopOrders
  (@custid AS INT, @n AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT orderid, empid, orderdate, requireddate
  FROM Sales.Orders
  WHERE custid = @custid
  ORDER BY orderdate DESC, requireddate DESC
  OFFSET 0 ROWS FETCH NEXT @N ROWS ONLY;
GO

SELECT
  C.custid, C.companyname,
  A.orderid, A.empid, A.orderdate, A.requireddate
FROM Sales.Customers AS C
  CROSS APPLY dbo.TopOrders(C.custid, 3) AS A;
GO

-- cleanup
DROP FUNCTION if EXISTS dbo.TopOrders

Window functions perform calculations on a per-row basis within a defined window of rows, preserving detail, whereas grouped queries lose detail by aggregation.

Window functions operate directly on the underlying query result set, while subqueries often start with a fresh view of the data, potentially requiring duplication of query logic.

Window functions can define the order of rows for calculations separately from the presentation order of the result set.

Window functions are allowed only in the SELECT and ORDER BY clauses of a query.

An empty OVER() clause represents the entire underlying query’s result set.

The window-partition clause (PARTITION BY) restricts the window to the subset of rows that have the same values in the partitioning columns as in the current row.

The window-order clause (ORDER BY) defines ordering, but don’t confuse this with presentation ordering.

The window-frame filters a frame, or a subset, of rows from the window partition between the two specified delimiters, which is defined using the ROWS or RANGE clause.

-- compute the running-total for each employee and month
SELECT empid, ordermonth, val,
  SUM(val) OVER(
                PARTITION BY empid -- For an underlying row with employee ID `1`, the window exposed to the function filters only the rows where the employee ID is `1`.
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ) AS runval
FROM Sales.EmpOrders;

1	2020-07-01	1614.88	1614.88
1	2020-08-01	5555.90	7170.78
1	2020-09-01	6651.00	13821.78
. . .

Traditionally in SQL, a single grouped query defines a single grouping set.

-- set(empid, custid)
SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid;

-- set(empid)
SELECT empid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid;

-- set(custid)
SELECT custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY custid;

-- set()
SELECT SUM(qty) AS sumqty
FROM dbo.Orders;

Use UNION ALL with NULL placeholders to combine multiple queries into a single result set for reporting, but potentially lead to two main problems—the length of the code and performance due to multiple scans for separated query.

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid

UNION ALL

SELECT empid, NULL, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid

UNION ALL

SELECT NULL, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY custid

UNION ALL

SELECT NULL, NULL, SUM(qty) AS sumqty
FROM dbo.Orders;

T-SQL supports the standard GROUPING SETS, CUBE, and ROLLUP subclauses of the GROUP BY clause, and the GROUPING and GROUPING_ID functions to define multiple grouping sets in the same query for reporting and data analysis.

The CUBE subclause of the GROUP BY clause provides an abbreviated way to define multiple grouping sets.

The ROLLUP subclause of the GROUP BY clause also provides an abbreviated way to define multiple grouping sets.

The GROUPING and GROUPING_ID functions are used to identify which columns in a GROUP BY clause are included in a group set or or are represented by a NULL placeholder in the aggregated result set.

The DELETE statement is a standard statement used to delete data from a table based on an optional filter predicate.

DELETE FROM dbo.Orders
WHERE orderdate < '20210101';

The standard TRUNCATE statement deletes all rows from a table without filter.

TRUNCATE TABLE dbo.T1;

T-SQL supports a nonstandard DELETE syntax based on joins to delete rows from one table based on a filter against attributes in related rows from another table.

DELETE FROM O
 FROM dbo.Orders AS O
  INNER JOIN dbo.Customers AS C
  ON O.custid = C.custid
WHERE C.country = N'USA';

The UPDATE statement is a standard statement to update a subset of rows in a table.

The UPDATE statement also supports a nonstandard form based on joins that serves a filtering purpose.

UPDATE OD
  SET discount += 0.05
FROM dbo.OrderDetails AS OD
  INNER JOIN dbo.Orders AS O
  ON OD.orderid = O.orderid
WHERE O.custid = 1;

-- same task by using standard code (recommended)
UPDATE dbo.OrderDetails
  SET discount += 0.05
WHERE EXISTS
  (SELECT *
  FROM dbo.Orders AS O
  WHERE O.orderid = OrderDetails.orderid
    AND O.custid = 1);

T-SQL supports a proprietary UPDATE syntax that both updates data in a table and assigns values to variables at the same time.

DECLARE @nextval AS INT;

UPDATE dbo.MySequences
  SET @nextval = val += 1
WHERE id = 'SEQ1';

SELECT @nextval;