I was asked a very interesting question on Twitter just now: https://twitter.com/connolly_s/status/1303957373107818497 Can we have "constant" foreign key columns in (PostgreSQL) tables? Luckily, yes, we can. Using a nice standard feature that is "computed columns" or "generated columns" Sometimes, you cannot completely normalise your schema for whatever reason. There may be a case where you … Continue reading Having “constant” columns in foreign keys
Tag: SQL Server
What’s Faster? COUNT(*) or COUNT(1)?
One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there's really no reason at all why one should be faster than the other. But is the myth justified? Let's measure! How does COUNT(...) work? But … Continue reading What’s Faster? COUNT(*) or COUNT(1)?
Calculate Percentiles to Learn About Data Set Skew in SQL
B-Tree indexes are perfect when your data is uniformly distributed. They are not really useful, when you have skewed data. I'll explain later why this is the case, but let's first learn how to detect "skew" What is skew? Skew is a term from statistics when a normal distribution is not symmetric. The example given … Continue reading Calculate Percentiles to Learn About Data Set Skew in SQL
How to Aggregate an Archive Log’s Deltas into a Snapshot with SQL
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 Aggregate an Archive Log’s Deltas into a Snapshot with SQL
JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage
The SQL language has one great advantage over procedural, object oriented, and "ordinary" functional programming languages. The fact that it is truly declarative (i.e. a 4GL / fourth generation programming language) means that a sophisticated optimiser can easily transform one SQL expression into another, equivalent SQL expression, which might be faster to execute. How does … Continue reading JOIN Elimination: An Essential Optimiser Feature for Advanced SQL Usage
How I Incorrectly Fetched JDBC ResultSets. Again.
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: ConnectionStatement (and its subtypes)ResultSet All the other types some sort of utilities. Now, with the above three, we can do … Continue reading How I Incorrectly Fetched JDBC ResultSets. Again.
How to Find Redundant Indexes in SQL
The following two indexes are redundant in most SQL databases: CREATE INDEX i_actor_1 ON actor (last_name); CREATE INDEX i_actor_2 ON actor (last_name, first_name); It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is … Continue reading How to Find Redundant Indexes in SQL
How to Execute a SQL Query Only if Another SQL Query has no Results
I stumbled upon an interesting question on Stack Overflow recently. A user wanted to query a table for a given predicate. If that predicate returns no rows, they wanted to run another query using a different predicate. Preferably in a single query. Challenge accepted! Canonical Idea: Use a Common Table Expression We're querying the Sakila … Continue reading How to Execute a SQL Query Only if Another SQL Query has no Results
How to Benchmark Alternative SQL Queries to Find the Fastest Query
Tuning SQL isn't always easy, and it takes a lot of practice to recognise how any given query can be optimised. One of the most important slides of my SQL training is the one summarising "how to be fast": Some of these bullets were already covered on this blog. For instance avoiding needless, mandatory work, … Continue reading How to Benchmark Alternative SQL Queries to Find the Fastest Query
How to Execute SQL Batches With JDBC and jOOQ
Some databases (in particular MySQL and T-SQL databases like SQL Server and Sybase) support a very nice feature: They allow for running a "batch" of statements in a single statement. For instance, in SQL Server, you can do something like this: -- Statement #1 DECLARE @table AS TABLE (id INT); -- Statement #2 SELECT * … Continue reading How to Execute SQL Batches With JDBC and jOOQ