How to get the query execution plan on SQL Server

Introduction In this article, we are going to see how we can get the query execution plan when using the SQL Server relational database system. The execution plan allows you to understand the operations done by the database engine when executing a specific SQL query.

How to get the SQL execution plan on Oracle

Introduction In this article, we are going to see how we can get the SQL execution plan on Oracle. When tuning performance, the execution plan is indispensable as it allows you to understand the operations done by the database when executing a certain SQL query.

How to limit the SQL query result set to Top-N rows only

Introduction In this article, we are going to see how we can limit the SQL query result set to the Top-N rows only. Limiting the SQL result set is very important when the underlying query could end up fetching a very large number of records, which can have a significant impact on application performance.

Pagination best practices

Introduction In this article, we are going to discuss several data pagination best and worst practices. Data pagination is omnipresent in enterprise applications. Yet, most solutions, not only they offer a bad user experience, but they are also inefficient.

SQL ORDER BY RANDOM

Introduction In this article, we are going to see how we can sort an SQL query result set using an ORDER BY clause that takes a RANDOM function provided by a database-specific function. This is a very handy trick, especially when you want to shuffle a given result set. Note that sorting a large result set using a RANDOM function might turn out to be very slow, so make sure you do that on small result sets. If you have to shuffle a large result set and limit it afterward, then it’s… Read More

How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL

Introduction Last week, Burkhard Graves asked me to answer the following StackOverflow question: And, since he wasn’t convinced about my answer: I decided to turn it into a dedicated article and explain how UPSERT and MERGE work in the top 4 most common relational database systems: Oracle, SQL Server, PostgreSQL, and MySQL.

Why you should definitely learn SQL Window Functions

Introduction I found this question on the Hibernate forum, and it’s a very good opportunity to show why mastering Windows Functions is a very important skill for any backend software developer.

How to map Java and SQL arrays with JPA and Hibernate

Introduction Hibernate custom Types allow you to map all sorts of database specific column types, like IP address, JSON columns, bit sets or SQL arrays. There are two ways to define a custom Hibernate Type: the UserType interface Java and SQL descriptors The latter option is preferred since it allows you to better split the Java-to-JDBC and the JDBC-to-SQL type handling. In this article, we are going to see how you can map SQL arrays to their Java counterpart.

How to install DB2 Express-C on Docker and set up the JDBC connection properties

Introduction While developing Hibernate, I need to test the code base against a plethora of relational database systems: Oracle, SQL Server, PostgreSQL, MySQL, MariaDB, Informix, and of course DB2. However, having all these databases installed on my system is far from ideal, so I rely a lot on Docker for this task. In this article, I’m going to show how easily you can install DB2 on Docker and set up the JDBC connection so that you can run Hibernate tests on DB2.

How does a relational database work

Introduction While doing my High-Performance Java Persistence training, I came to realize that it’s worth explaining how a relational database works, as otherwise, it is very difficult to grasp many transaction-related concepts like atomicity, durability, and checkpoints. In this post, I’m going to give a high-level explanation of how a relational database works internally while also hinting some database-specific implementation details.