Introduction In this article, we are going to see how the Hibernate StatelessSession Upsert method works. As I explained in this article, the UPSERT operation allows you to INSERT a record if there is no such record matching the filtering criteria or to UPDATE it in case the record exists. While many relational database systems offer the standard MERGE command…
#sql server
10 posts
19 Dec 2023
7 Aug 2023
Introduction In this article, we are going to see how the SQL Server useBulkCopyForBatchInsert configuration property works when using JDBC, JPA, or Hibernate. SQL Server PreparedStatement batching When using JPA and Hibernate, the generated SQL statements are going to be executed using the JDBC PreparedStatement because prepared statements increase the likelihood of statement caching, and you to avoid SQL injection…
17 May 2023
Introduction In this article, we are going to see what is the standard SQL operation order. Once you understand the order in which SQL operations are executed, then it will be clear why the Oracle legacy pagination query required a Derived Table in order to make sure that the ROWNUM pseudocolumn is computed after executing the ORDER BY clause. SQL…
19 Apr 2023
Introduction In this article, we are going to see what is the best way to call SQL Server stored procedures with jOOQ. I decided to write this article because stored procedures and database functions are extremely useful for data-intensive applications, and sometimes, they are the only solution to process data efficiently. While SQL remains the de-facto way to query data,…
16 Nov 2018
A customer of my popular SQL training (which you should book!) has recently challenged me to optimise a hierarchical query that merges an archive log’s deltas in order to obtain a snapshot of some record at a given point in time. In this article, I will reproduce their problem statement in a simplified version and … Continue reading How to…
13 Jul 2017
You know JDBC, right? It’s that really easy, concise API that we love to use to work with virtually any database, relational or not. It has essentially three types that you need to care about: Connection Statement (and its subtypes) ResultSet All the other types some sort of utilities. Now, with the above three, we … Continue reading How I…
10 Jan 2017
SQL Server has this nice feature called table-valued parameters (TVP), where users can pass table variables to a stored procedure for bulk data processing. This is particularly nice when the stored procedure is an inline table valued function, i.e. a function that returns a table as well. For instance: The above function creates a cross … Continue reading jOOQ 3.10…
18 Jan 2016
I’ve recently encountered a very interesting question on Stack Overflow by an unnamed user. The question was about generating a table of the following form in Oracle, using a table valued function: Description COUNT ------------------- TEST1 10 TEST2 15 TEST3 25 TEST4 50 The logic that should be implemented for the COUNT column is the … Continue reading Impress Your…
5 Aug 2014
Every once in a while, we run into these rare SQL issues where we’d like to do something that seems out of the ordinary. One of these things is pivoting rows to columns. A recent question on Stack Overflow by Valiante asked for precisely this. Going from this table: +------+------------+----------------+-------------------+ | dnId | propNameId | … Continue reading Are You…
13 May 2014
SQL Server is known to have a very strict interpretation of the SQL standard. For instance, the following expressions or statements are not possible in SQL Server: Strictly speaking, that limitation makes sense because the above ROW_NUMBER() or OFFSET expressions are non-deterministic. Two subsequent executions of the same query might produce different results. But then … Continue reading SQL Server…