In the latest version of the SQL standard (SQL:2016) the LISTAGG function was added for this purpose. I find it "disturbing" that the next SQL Server release gets a function for this, but the new function doesn't follow the new standard.
Even worse: the name "string_agg" was apperently borrowed from PostgreSQL. But if you think that SQL Server will stick to the syntax of PostgreSQL's string_agg — nope. They use the same function name, but a different syntax.
Microsoft double fail, I'd say.
I've just written an article about LISTAGG, btw: http://modern-sql.com/feature/listagg
This is even more ludicrous when you consider all the extremely cool stuff the team responsible for porting MSSQL to other platforms accomplished in order to get it functioning on Linux[1].
1: https://arstechnica.com/information-technology/2016/12/how-a...
If your database design and code frequently deadlocks, I'd argue it is not a sign of the database engine being incompetent. After all, it's doing what you asked it to do. The comparison to MySQL is simply not true and it ignores the years of new features and enhancements to the product.
What I do know is that I routinely get single, non-transactional select queried deadlocked and killed. That seems like something that shouldn't happen. If I had to guess, it's likely because of the subselects to approximate group_concat, as outlined above, and they have different lock times than the main query, allowing for this single query to attampt to get locks at different times. Unfortunately, this is an ORM generated query, so sticking NOLOCK all over the place is easier said than done, and since I don't control the DB, I can't change default transactional levels. Perhaps that would solve it too.
It just seems odd that what appears to be (from my admittedly non SQL server expert) eyes to be a fairly standard database schema is so easily susceptible to this problem.
But ultimatley, the fact that queries are so tied to the flat resultset is infuriating, and that sucks for every SQL engine. I want a graph of results, not a goddamned glorified spreadsheet.
There are ORMs that mitigate that limitation, but ultimately they're getting compiled down to SQL so in the middle there's a big flat resultset coming back or a bunch of redundant queries.
Yes. It's important to be very aware of this. Letting the ORM do this for you automatically for a few chained joins can result in a massive resultset that is literally 90%+ redundant information depending on the strategy the ORM uses.
We're past due for something new. Something that natively works with the typed-graph thinking of the client code instead of forcing everything into the flat resultset, but otherwise keeps the ACID guarantees and the relational model.
SELECT
movie.id,
movie.name,
array_remove(array_agg(producer.last_name), NULL) AS last_names
FROM movie
LEFT JOIN producer ON movie.id = producer.movie_id
GROUP BY
movie.id,
movie.name;> array_remove(array_agg(producer.last_name), NULL) AS last_names
to that
> array_agg(producer.last_name) FILTER(WHERE producer.last_name IS NOT NULL) AS last_names
I wrote about FILTER here: http://modern-sql.com/feature/filter
And how to use ARRAYs rather than concatenated strings here: http://modern-sql.com/feature/listagg#alternative-array
It's certainly more ergonomic than STUFF, and I've found its performance to be comparable or better.
As much as you might recoil in horror at this (and I still do), it's actually fairly performant because the optimizer recognizes the subquery is dependent on the main query, and does the equivalent of a join under the covers or something.
E.g.