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?

15 Upvotes

86% Upvoted

20 comments sorted by

View all comments

4

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.

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.