[pmarreck]

LLM's might be able to translate the stored procedure code without too much difficulty, and assuming you have test coverage. Might also do a good job of translating DDL code such as index definitions etc.

I too was impressed with SQL Server last time I used it (big note: SQL Server is one of the few commercial DB's that does real nested transactions; PG does not), but I get it.

[dspillett]

> SQL Server is one of the few commercial DB's that does real nested transactions

More or less. The default ROLLBACK behaviour is to roll back the whole nest. You have to use SAVE TRANSACTION instead of BEGIN TRANSACTION and specify the name in ROLLBACK. If doing this in a procedure that may or may not be part of a nested transaction (an explicit transaction wasn't started before it was called) you have to test to see if you need to BEGIN or SAVE¹ and what to do if a rollback is needed (you likely don't want to ROLLBACK everything if you used SAVE, but have no option if you used BEGIN). Not exactly intuitive. And saved transactions can cause excessive lock escalation, impacting concurrent performance.

SQL Server is generally a damned fine product overall, both generally and compared to a lot of other things emitted by Microsoft, but it isn't even close to perfect in some areas.

----

[1] ref: https://learn.microsoft.com/en-us/sql/t-sql/language-element...

[pmarreck]

Thanks for the clarification.

I'm not one to compliment Microsoft software products, but Analysis Services and SQL Server, at least when I used them at the time over 10 years ago, seemed like darn fine products (which they purchased from someone else, I believe...)

[dspillett]

SQL Server itself came from Sybase originally, with them and MS working on it as partners for a number of years when it was an OS/2 product, so it wasn't a purchase-a-working-product-and-rebrand-it deal. As Windows NT gained ground, MS & Sybase parted ways (amicably IIRC, when the partnership agreements expired MS licensened the source that Sybase owned and bought out exclusive use of the product name in relation to products for Windows – for a time MS's SQL Server still carried Sybase copyright messaging along with MS's own).

SQL Server v7 was a rewrite of significant parts of the internals, so from that point it is probably fair to call it an MS product quite distinct from Sybase's (which itself continued to be separately developed including significant but different improvements/reengineering of the internals), though still showing some signs of its heritage.

[werdnapk]

What's a real nested transaction? What's postgresql missing? https://www.postgresql.org/docs/current/subxacts.html

[jiggawatts]

Transactions are the only composable concurrency control primitive, but this requires that they be nestable.

It’s a key “primitive” missing from almost all high-level programming languages, with the notable exception of some SQL dialects.

[pmarreck]

Savepoints are not considered legitimately isolated nested transactions.

To the best of my knowledge.

Note: Am not a DBA, merely a full stack dev that has worked with various databases over the years.

[pmarreck]

Had a fascinating conversation with Claude about this (don't worry about the unhinged personality I gave it with preprompting):

https://gist.github.com/pmarreck/970e5d040f9f91fd9bce8a4bcee...

Pasted it there for brevity here. Also includes an easy way to export a Claude conversation (made with the help of Claude, of course).

[ants_a]

LLMs are not a great source for this type of information. This mess in no way articulated in what way savepoints and transactions are different.

Savepoints are fully equivalent to nested transactions with the constraint that only one is concurrently active. This limitation is just from the SQL standard programming model. At least in PostgreSQL implementation it would be reasonably simple to support multiple concurrent subtransactions. They are even called subtransactions internally.

[jiggawatts]

What on Earth are you using as your system prompt!!?

[pmarreck]

LOL, it's amazing

[BrentOzar]

> SQL Server is one of the few commercial DB's that does real nested transactions

Not sure where this myth keeps coming from, but no, it does not:

https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a...

[pmarreck]

Interesting. Is this still up-to-date? The link you posted is 15 years old

[BrentOzar]

Yep, nothing's changed there around transactions.

[cpursley]

You're getting downvoted by gatekeepers but LLMs are amazing with SQL and this is a great use-case.

[hnthrow90348765]

Anecdata, but I agree. I went from a recursive CTE (that occasionally would take 1GB+ of memory for zero rows) to a loop and used AI to do it, and it did a pretty good job

[pmarreck]

Possibly related, talked to an LLM about the whole "who actually does nested transactions" thing and commented about it above:

https://news.ycombinator.com/item?id=42702610