SQL Recursive WITH CTE queries
Introduction In this article, we are going to see how SQL Recursive WITH CTE (Common Table Expression) queries work and how we can apply them for processing hierarchical data models.
SQL CROSS APPLY – A Beginner’s Guide
Introduction In this article, we are going to see how the SQL CROSS APPLY works and how we can use it to cross-reference rows from a subquery with rows in the outer table and build compound result sets. CROSS APPLY is basically equivalent to the LATERAL JOIN, and it’s been supported by SQL Server since version 2005 and Oracle since version 12c.
SQL Derived Table or Inline View
Introduction In this article, we are going to see what an SQL Derived Table or Inline View is and how you can use it to bypass the standard SQL operation order. While the SQL Standard uses the term Derived Table for this feature, some relational database systems call it Inline View.
SQL CTE – Common Table Expression
Introduction In this article, we are going to see what an SQL CTE (Common Table Expression) is and how you can use it to reuse subquery result sets or inline views.
Use Hibernate Dynamic Update for JSON properties
Introduction In this article, I’m going to explain why you should always use the Hibernate Dynamic Update feature for entities that map JSON properties. Since Hibernate ORM does not offer support for JSON column types, we are going to use the Hypersistence Utils library, which provides a JsonType that allows you to map String, Map, List, JsonNode, Java Records, or POJOs on JSON columns, no matter if you are using PostgreSQL, MySQL, Oracle, SQL Server, or H2.
SQL Server Foreign Key Locking
Introduction In this article, we are going to see how SQL Server Foreign Key constraints are locking the parent record when executing a child record UPDATE. This situation is specific to SQL Server and happens even when using the Read Committed Snapshot Isolation level.
Clustered Index
Introduction In this article, we are going to see what a Clustered Index is and why it’s very important to understand how tables are organized when using a relational database system.
