Hacker News Reader

Comment by wvenable | Hacker News Reader

This is also my big complaint when it comes to MS SQL -- there has been precious little development on their SQL language. It's almost like they're in a complete feature-freeze when it comes to the SQL language.

MS SQL is missing some very basic SQL functionality.

View on HN

SQL Server was based on Sybase SQL Server. https://en.wikipedia.org/wiki/Microsoft_SQL_Server

Microsoft rewrote the code so there was no more Sybase code, but it still followed Transact SQL standards. T-SQL is not as complete as PL/SQL and others. It is just that Microsoft is that 800 pound gorilla so people use their products.

My last job I did migration from Excel and MS-Access to SQL Server using VB 6.0 code and ADO recordsets to copy and sync up data. They key was to use a column named date modified that had a time stamp when it was last modified to figure out which record was the newest and back up the old record to a history table in case it needed to be reverted later on.

I thought Oracle or even db2 would be the Gorillas for serious corporate/goverment databases, and MySql would be the Gorilla for cheap + web stuff.

I suppose Microsoft is pretty popular in beetween those 2 markets.

Oracle IS the gorilla, but with their recent pricing/licensing moves, more large orgs are exploring moving to SQL Server, especially ones that are already a Microsoft shop. My company is doing just that.

Why not explore PostgreSQL? You would be free of licensing/pricing moves.

Not GP, but I've found SQL Server significantly easier to both install and manage. Performance-wise I've yet to encounter a significant difference.

What in particular do you miss from other SQL languages?

UPSERT (or similar logic, not merge).

Oh, then that's your problem. MERGE is standard, and has clear semantics. UPSERT is nonstandard, and semantics vary among implementations.

Microsoft doesn't need to make SQL Server look like MySQL. Quite the other way around.

There's not really a standard anymore for UPSERT to be added to. SQL has fragmented and since the last few iterations of the standard allowed for "additional non standard features", adding an UPSERT is perfectly compliant with the SQL standards.

UPSERT should be a standard, why isn't it?

And it's very useful in a ton of situations which is why so many other SQL dialects have added it. It's not like SQL Server is completely standards compliant so I'd rather have the functionality then have to use MERGE because it's "standard".

Date handling, simple things like postgres date_trunc('month', now()) and especially looping over records in stored procedures without using cursors (that are prone to crashes).

I work with both on a daily basis and there is a mile of difference. Yes I can accomplish whatever I want in sql server but there are so many small things that could improve.

Some of other pet peevees are: You can't use a lot of left joins, it slows down the query dramatically. If there is only one to one relation, use subselects instead.

Thou shall not do queries like this: select foo into baz from bar where fooid not in (select fooid from bar) They are the death of the engine.

And many "unwritten rules" like that.

Postgres just accepts the queries and run them..

SELECT DATEPART(month,GETUTCDATE()) ? Either you are using a very old version or not using SQL Server at all cause I pretty much do everything you are complaining about on a daily basis and on a very large set of data.

Your query only extracts the month integer from a date, it does not floor it to the month. The equivalent for sql server is this ugly thing: select dateadd(month,datediff(month,0,getdate()),0)

We are on SQL server 2014. I have had a query go from 2-3 hours down to 7 minutes by replacing left joins with subselects within the query for instance (and yes all the indices are in place as recommended by sql server query planer)

The one with a insert based on a query on the same table I have never managed to run at all and I have to use a temporary table as an intermediary instead.

The last one might be heped by trace flag 4199. You can apply it to individual queries with option (querytraceon 4199):

https://blogs.msdn.microsoft.com/psssql/2010/09/01/slow-quer...

I have seen Sql server query planer do stupid shit too. Almost always Statistics where to blame. Are you sure those where up to date? For some reason the "auto update statistics" functionality doesn't seem to work reliably, you have to update them in a maintenance job to fix this.

Reply on news.ycombinator.com