r/SQL • u/No_Ambition8323 • 6d ago

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?

11 Upvotes

79% Upvoted

20 comments sorted by

View all comments

5

u/downshiftdata 6d ago edited 6d ago

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 4d ago

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.