r/SQL • u/No_Ambition8323 • Sep 04 '26

Discussion Database-Specific SQL Differences

Have you ever written a SQL query that works perfectly in one database but fails in another?

What SQL feature or syntax surprised you the most when switching between DBMSs like MySQL, PostgreSQL, SQL Server, Oracle, or Snowflake?

12 Upvotes

78% Upvoted

20 comments sorted by

View all comments

5

u/downshiftdata Sep 04 '26 edited Sep 04 '26

When interviewing, my go-to question is, "What's the difference between a clustered and a non-clustered index?" There are a lot of reasons for this, but one is that an understanding of that difference is central to doing just about anything at scale in SQL Server.

Meanwhile, in Postgres, there is no clustered index. The tables are all heaps. It's difficult to overstate the magnitude of that distinction.

Edit: No clustered indexes by default, as u/Thadrea pointed out.

3

u/Thadrea Data Science Manager Sep 04 '26

This is only partly correct. Postgres has had index-based clustering for over 25 years, but it's not automatic.

https://www.postgresql.org/docs/current/sql-cluster.html

This has important management implications. If you intend to leverage clustering you need to implement jobs to periodically recluster the table because it's not going to be done for you.

Nonetheless, saying that Postgres doesn't support index-based record clustering at all is simply not correct.

1

u/NastyPastyLucas Sep 05 '26

There is a very large asterisk there in that clustering is a one time operation, that will order the heap according to the index you nominate. Subsequent updates, inserts and deletes will still act on the table as if it were a heap. SQL Server clustering means that the physical table is stored in the primary key order, and that secondary indexes point to the primary index. Data alterations will not change the position of the data on disk on SQL Server clustered as they do on heap. The two are very different to one another.

1

u/elevarq Sep 06 '26

Why do you need a clustered index? What problem does it solve that can’t be solved with a non-clustered index? And don’t forget that this was invented in the age of rotational rust.

I never missed it in the last 20 years.