Use Additional Keywords in your Queries

There are many additional keywords which your queries can contain to generate different types of results in SQL Server.
The following table outlines these additional keywords:

ASC	Sorts records in one or more columns in ascending order
CONTAINS	Allows you to specify a wildcard in searching for data in the database
DESC	Sorts records in one or more columns in descending order
DISTINCT	Considering all columns in the resultset, duplicate values will not be returned
EXISTS subquery	Tests to see if rows exist in the database, based on the subquery specified by subquery
HAVING	Specifies a search condition for groups when using a GROUP BY clause. This is similar to a WHERE clause in a SELECT statement.
OPTION	Indicates to SQL Server which Query Plan to use. Query Plans are discussed in greater detail in the last course in this series
TOP x	Returns the top x records in the recordset
TOP x PERCENT	Returns the top x percentage of records in the recordset

In addition to the SQL Server keywords listed above, SQL Server 2022 introduces several new functions and enhancements to Transact-SQL (T-SQL) that can simplify query writing and improve performance. Here are some notable additions:

1. GREATEST and LEAST Functions
   • GREATEST: Returns the highest value from a list of expressions.
   • LEAST: Returns the lowest value from a list of expressions.
   • Example:

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

   • These functions simplify comparisons across multiple columns or expressions without the need for complex CASE statements.
2. STRING_SPLIT with Ordinal
   • The STRING_SPLIT function now includes an optional parameter to return the ordinal position of each substring, preserving the original order of elements in a delimited string.
   • Example:

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

   • This enhancement is useful when the sequence of elements is significant.
3. DATE_BUCKET Function
   • DATE_BUCKET allows grouping of date/time values into fixed-size buckets, facilitating time series analysis.
   • Example:

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

   • This function helps in aggregating data over uniform time intervals.
4. GENERATE_SERIES Function
   • GENERATE_SERIES generates a series of values in a specified range, which is useful for creating sequences or filling gaps in data.
   • Example:

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

   • This function simplifies the creation of number sequences without the need for recursive queries or loops.
5. IS [NOT] DISTINCT FROM Operator
   • This operator compares two expressions and returns TRUE or FALSE, treating NULL values as comparable, which simplifies null-safe comparisons.
   • Example:

     SELECT *
     FROM Employees
     WHERE Salary IS DISTINCT FROM PreviousSalary;
     

   • This operator eliminates the need for additional NULL handling logic in comparisons.
6. WINDOW Clause
   • The WINDOW clause allows the definition of window specifications that can be reused across multiple window functions within the same query, enhancing readability and maintainability.
   
   Example:

   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);
   

This feature reduces redundancy when applying multiple window functions with the same specifications.
These enhancements in SQL Server 2022 provide more straightforward and efficient ways to handle common querying scenarios, reducing the complexity of T-SQL code and improving performance.

As I mentioned earlier, the rules for naming in SQL Server are fairly relaxed, allowing things like embedded spaces and even keywords in names. Like most freedoms, however, its easy to make some bad choices and get yourself into trouble. Here are the main rules:

1. The name of your object must start with any letter, as defined by the specification for Unicode 3.2. This includes the letters most Westerners are used to: A-Z and a-z.
2. Whether "A" is different from "a" depends on the way your server is configured, but either makes for a valid beginning to 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 116 for temporary objects.
5. Any names that are the same as SQL Server keywords or contain embedded spaces must be enclosed in double quotes ("") or square brackets ([]).
6. Which words are considered keywords varies depending on the compatibility level to which you have set your database.

Note that double quotes are acceptable as a delimiter for column names only if you have SET QUOTED_IDENTIFIER ON. Using square brackets ([ and ]) avoids the chance that your users will have the wrong setting.
These rules are generally referred to as the rules for identifiers and are in force for any objects you name in SQL Server, but may vary slightly if you have a localized version of SQL Server (one adapted for certain languages, dialects, or regions). Additional rules may exist for specific object types.

• Naming of Objects.
  SQL Server has the ability to embed spaces in names and, in some cases, to use keywords as names. One should resist the temptation to do either of these things. Columns with embedded spaces in their names have nice headers when you make a SELECT statement, but there are other ways to achieve the same result. Using embedded spaces and keywords for column names is begging for bugs, confusion, and other disasters. I will discuss later why Microsoft has elected to allow this, but for now, just remember to associate embedded spaces or keywords in names with something that should be avoided.