Hacker News Reader

Comment by MarkusWinand | Hacker News Reader

Although SQL Server vNext (14.x) will get string_agg: https://msdn.microsoft.com/en-us/library/mt790580.aspx

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

View on HN

That doesn't exactly surprise me. MSSQL feels like MySQL's old MyISAM table type technology that's been hacked and extended for decades rather than a shift to something more technologically competent. I still get errors on a regular basis about the deadlock manager killing queries because they became deadlocked. Because of course the way to deal with your system easily running into deadlocks is just to implement a checker that runs once a second and kills those queries that deadlock, and not to actually make the system work better....

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...

I find their locking issues interesting: they are supporting snapshot isolation for quite some time now, but it's still not default and the community even hardly knows about it. Hint's like NOLOCK are more often suggested as "fix" as considering snapshot isolation.

Unfortunately, my experience with SQL Server is almost entirely in interfacing with a third party applications SQL Server back-end. As such, I have zero control over the many queries that are run from the application clients and the schema in use. I just get to play in their sandbox. Unfortunately, like many sandboxes, it's also a litter box. :/

> Because of course the way to deal with your system easily running into deadlocks is just to implement a checker that runs once a second and kills those queries that deadlock, and not to actually make the system work better....

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.

I admit it's entirely possible most of my bad experiences with SQL Server is due to a horrible schema. I don't control the schema in this case, nor the majority of different types of queries that run against that schema.

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.

Reply on news.ycombinator.com