Hacker News new | ask | show | jobs
by darklajid 4067 days ago
Can someone explain this one to me?

PostgreSQL supports the RETURNING clause, allowing UPDATE, INSERT and DELETE statements to return values from affected rows. This is elegant and useful. MS SQL Server has the OUTPUT clause, which requires a separate table variable definition to function. This is clunky and inconvenient and forces a programmer to create and maintain unnecessary boilerplate code.

So I have the equivalent of the following in one of my projects:

    UPDATE sometable SET someField = @parameter
    OUTPUT Inserted.field1, Inserted.field2
    WHERE ...
Now, either I don't get the limitation the author describes or SQL Server can do that - returning information from the affected results. Works with DELETE as well. We can argue that 'inserted' is a crappy name here, but..
2 comments
CWuestefeld 4067 days ago
As a long-time SQL Server (and C#/.Net) developer, I can say that while SQL Server is a great database engine, TSQL is perhaps the worst language ever invented.

Between the overloading of "WITH" to seemingly every new feature they add, to the way semicolon terminators are only sometimes required (so I just automatically start all CTE declarations as ";WITH" to be sure it works), to the ways they break encapsulation by incorrectly scoping cursor names and limiting INSERT...EXEC so that it can't be nested, programming it at an advanced level is an acquired taste at best.

link
darklajid 4067 days ago
But that's not the point of this subthread. I wouldn't consider myself as a fan of SQL Server (or TSQL for that matter). I am not defending it.

1) I asked about a specific thing in the article. I think the author is wrong.

2) If 1 holds true I think that the article might be questionable - at least I don't trust the rest now. I am no expert on All Things SQL Server and if I spot a flawed point as a random dev in something that was supposedly written by someone working with databases for a decade [1], maybe people that actually know a lot more spot .. more flaws.

1: I'm a dev for longer than that and certainly know my way around sql and databases, but focus matters. The author claims "I know a fair bit about these things" and considers databases his main area of expertise it seems.

link
Aushin 4067 days ago
Honestly I don't know where he got the idea, but as someone who worked for five years as a SQL Server database admin/developer, I for some reason also thought you could only do OUTPUT Inserted.field1, ... INTO @TableVar

But I just tested what you claimed here and it definitely works exactly the way the RETURNING clause would.

Weird.

link