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.

SQL Server JDBC – Set sendStringParametersAsUnicode to false

Introduction In this article, I’m going to explain why you should always disable the sendStringParametersAsUnicode default JDBC Driver setting when using SQL Server.

SQL Server deadlock trace flags

Introduction In this article, I’m going to explain how you can find the cause of an SQL Server deadlock using trace flags and the SQL error log. As a rule of thumb, the more locks are acquired, the higher the probability of a deadlock. And, since SQL Server uses 2PL by default, it’s not uncommon to have to track deadlock issues that affect application performance.

Optimistic vs. Pessimistic Locking

Introduction In this article, I’m going to explain what is the difference between optimistic and pessimistic locking, as well as when you should employ one or the other concurrency control strategies.

Merge Join Algorithm

Introduction In this article, we are going to see how the Merge Join Algorithm, also known as Sort-Merge Join, works and when it’s suitable for a relational database system to employ it in order to execute an SQL JOIN query.

MySQL Query Profiling Using Performance Schema

Introduction In this article, I’m going to explain how to do query profiling using the MySQL Performance Schema. The Performance Schema has been available since MySQL 5.5.3 and allows MySQL to instrument SQL queries and store their trace information in various tables that you can later inspect in order to determine why a given SQL statement is slow. On older versions of MySQL, you might have used the SHOW PROFILE command, but since this feature has been deprecated, you should now use the Performance Schema for SQL query profiling.

SQL Server OPENJSON – Map JSON to a relational table

Introduction In this article, I’m going to explain how the SQL Server OPENJSON function works and how it can help you transform a JSON object into a relational database table. When using a relational database system, it’s a very good idea to design the database schema according to the relational model. However, there are specific situations when the relational model is way too strict, and that’s when a JSON column type might be very handy. For instance, as I explained in this article, when designing an audit log table, it’s much more… Read More

Hash Join Algorithm

Introduction In this article, we are going to see how the Hash Join Algorithm works and when it’s suitable for a relational database system to employ it in order to execute an SQL JOIN query.

Nested Loop Join Algorithm

Introduction In this article, we are going to see how the Nested Loop Join Algorithm works and when it’s suitable for a relational database system to employ it in order to execute an SQL JOIN query.

How to get a JSON property value using PostgreSQL

Introduction In this article, I’m going to explain how you can get a JSON property value using the ->> PostgreSQL operator. This way, we can transform a JSON object that’s stored in a json or jsonb column into a virtual relational database table. While it’s a good idea to design the database schema according to the relational model, there are also situations when the relational model is way too strict. For instance, as I explained in this article, when implementing an audit log mechanism using database triggers, it’s very convenient to store… Read More