USING TIPS WITH (NOLOCK OR READUNCOMMITTED)

A record that was inserted in the table in a concurrent transaction is automatically read even if the concurrent transaction has not yet ended, when using the SQL tips presented here.

NOLOCK - Is equivalent to READUNCOMMITTED

READUNCOMMITTED
Specifies that dirty reads are allowed.
READUNCOMMITTED and NOLOCK hints apply only to data locks.

USING APPLY COMMANDS

RESUME
CROSS APPLY - Similar to INNER JOIN
OUTER APPLY - Similar to LEFT JOIN

SQL Server APPLY operator has two variants; CROSS APPLY and OUTER APPLY

USING TEMPORARY TABLES

Temporary table at session level (one #) and Global level (two ##)
A session-level temporary table (#) only exists as long as the session in question exists. This temporary table remains accessible on the database server only at the level of this session in question for the user who created it.

USING CTEs (Common Table Expression)

Introduction to SQL Server recursive CTE
in this sample you will learn how to use the SQL Server recursive CTE to query hierarchical data.

A recursive common table expression (CTE) is a CTE that references itself. By doing so, the CTE repeatedly executes, returns subsets of data, until it returns the complete result set.

Great care must be taken when using CTEs to avoid falling into the scenario of creating an infinite loop.
To solve situations that can generate infinite loops, it is always recommended to use the MAXRECURSION command together with the CTEs.
MAXRECURSION can be used to prevent a malformed recursive CTE from entering an infinite loop, limiting the number of lines produced by its final result.
In summary, when using MAXRECURSION 10, for example, you tell SQL to finalize and return the result with a maximum of 10 lines.

USING PIVOT AND UNPIVOT TABLE

PIVOT AND UNPIVOT TABLE
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements. For a complete description of the syntax for PIVOT, see FROM (Transact-SQL).

USING STRING SPLIT SQL FUNCTION

FUNCTION STRING_SPLIT()
The STRING_SPLIT function applies from the version of SQL Server 2016 and later

A table-valued function that splits a string into rows of substrings, based on a specified separator character.

USING Spatial Data (SQL Server)

Spatial Data
Spatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes.

SQL Server supports two spatial data types: the geometry data type and the geography data type.

The geometry type represents data in a Euclidean (flat) coordinate system.

The geography type represents data in a round-earth coordinate system.

Both data types are implemented as .NET common language runtime (CLR) data types in SQL Server.