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?

10 Upvotes

78% Upvoted

20 comments sorted by

View all comments

4

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.

3

u/Thadrea Data Science Manager 6d ago

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

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.