Use Additional Keywords in Your Queries

There are many additional keywords your queries can contain to generate different types of results in SQL Server. The following table outlines some of the most commonly used:

Beyond the keywords above, several T-SQL functions and clauses introduced in SQL Server 2022 remain fully current in SQL Server 2025 and are worth knowing.

GREATEST and LEAST. GREATEST returns the highest value from a list of expressions; LEAST returns the lowest. Both simplify comparisons that used to require a CASE statement or a combination of MIN/MAX logic.

SELECT GREATEST(10, 20, 5) AS MaxValue; -- Returns 20
SELECT LEAST(10, 20, 5) AS MinValue;    -- Returns 5

STRING_SPLIT with an ordinal position. STRING_SPLIT now accepts an optional third parameter that returns the ordinal position of each substring, preserving the original order of a delimited string's elements.

DECLARE @list NVARCHAR(MAX) = N'Apple,Banana,Cherry';
SELECT value, ordinal
FROM STRING_SPLIT(@list, ',', 1);

DATE_BUCKET. Groups date and time values into fixed-size buckets, useful for time-series analysis.

SELECT DATE_BUCKET(day, 7, OrderDate) AS WeekStartDate, COUNT(*)
FROM Orders
GROUP BY DATE_BUCKET(day, 7, OrderDate);

GENERATE_SERIES. Generates a series of values across a specified range, useful for creating sequences or filling gaps in data without recursive queries or loops.

SELECT value
FROM GENERATE_SERIES(1, 5);
-- Returns values 1, 2, 3, 4, 5

IS [NOT] DISTINCT FROM. Compares two expressions and returns TRUE or FALSE, treating NULL as a comparable value rather than an unknown. This removes the need for extra NULL-handling logic in comparisons.

SELECT *
FROM Employees
WHERE Salary IS DISTINCT FROM PreviousSalary;

The WINDOW clause. Lets you define a named window specification once and reuse it across multiple window functions in the same query, reducing repetition. This requires database compatibility level 160 or higher; on a database still running an older compatibility level, queries using WINDOW will not execute.

SELECT
    EmployeeID,
    Salary,
    AVG(Salary) OVER win AS AvgSalary,
    SUM(Salary) OVER win AS TotalSalary
FROM Employees
WINDOW win AS (PARTITION BY DepartmentID ORDER BY HireDate);

SQL Server 2025 introduces its own set of additions, built around native AI and search capabilities that did not exist in T-SQL before this release.

The VECTOR data type and VECTOR_DISTANCE. SQL Server 2025 adds a native vector data type for storing the numeric embeddings produced by AI models, alongside business data in the same table. The VECTOR_DISTANCE function compares two vectors and returns a similarity score, using a specified distance metric such as cosine similarity.

SELECT ProductID, VECTOR_DISTANCE('cosine', embedding, @queryEmbedding) AS Similarity
FROM Products
ORDER BY Similarity;

This is what powers semantic, meaning-based search directly inside the database, rather than requiring a separate vector search system.

A native JSON data type. Earlier lessons in this module covered FOR JSON, which formats a result set as JSON text. SQL Server 2025 goes further, adding JSON as an actual column data type, along with JSON indexes for efficient filtering on values inside a JSON document.

SELECT CustomerID
FROM Customers
WHERE JSON_VALUE(Preferences, '$.newsletter') = 'true';

Regular expression support. T-SQL now includes REGEXP_LIKE, letting you match a column against a regular expression pattern directly in a query, rather than relying on the more limited LIKE wildcard patterns.

SELECT ProductName
FROM Products
WHERE REGEXP_LIKE(Category, '(new york|chicago) style');

Together, these additions are what the earlier reference to "vector search, native JSON, and regex functions" in this course's introduction was pointing toward: SQL Server 2025 extends T-SQL well beyond traditional row-and-column querying, without changing anything about the fundamentals covered earlier in this module.

The rules for naming objects in SQL Server are fairly relaxed, allowing things like embedded spaces and even reserved keywords in names. Like most freedoms, it is easy to make bad choices with this one and get yourself into trouble. The main rules:

1. The name of your object must start with any letter, as defined by the Unicode Standard 3.2. This includes the letters most Westerners are used to, A-Z and a-z, along with letter characters from other languages.
2. Whether "A" is treated as different from "a" depends on how your server is configured, but either is a valid way to begin an object name.
3. After that first letter, you are pretty much free to run wild; almost any character will do.
4. The name can be up to 128 characters for normal objects, and up to 116 characters for temporary objects.
5. Any name that matches a SQL Server keyword, or contains embedded spaces, must be enclosed in double quotes ("") or square brackets ([]).
6. Which words count as keywords varies depending on the compatibility level your database is set to.

Double quotes work as a delimiter for identifiers only if SET QUOTED_IDENTIFIER is ON. Square brackets avoid that dependency entirely, so they are the safer choice if you are not certain how a given connection has that setting configured.

These are generally called the rules for identifiers, and they apply to any object you name in SQL Server, though they may vary slightly on a localized version of SQL Server adapted for a particular language or region. Additional rules can apply to specific object types beyond these general ones.

A note on naming objects. SQL Server's ability to embed spaces in names, and in some cases use keywords as names, is a capability worth resisting rather than using. A column with embedded spaces in its name produces a nicely formatted header when you run a SELECT statement, but there are better ways to achieve that same readability without building it into the column name itself. Using embedded spaces or keywords as names is an invitation for bugs and confusion down the line; it is best to treat both as something to avoid, not a convenience to lean on.