3 ms·

I've recently used T-SQL / MSSQL and was surprised how starkly it differs from your typical "foss sql" (be it postgres, sqlite or mysql). One really obvious ex

I've recently used T-SQL / MSSQL and was surprised how starkly it differs from your typical "foss sql" (be it postgres, sqlite or mysql). One really obvious example is how [] are used for qualifying names, or how there is no LIMIT clause (instead you use SELECT TOP(n), but you still use an OFFSET n ROWS clause after the ORDER BY clause for an OFFSET; there is also OFFSET n ROWS FETCH NEXT m ROWS ONLY). Another example are curious limits to programmability, e.g. TEXT can't be used for procedure parameters. There also seem to be small limits on BLOBs. No NATURAL JOIN (which I mostly use for ad-hoc queries). It is also very different deployment wise (as are all Microsoft products). You don't have a client library or anything like that, but a system-wide database driver instead. Applications use a driver interface and could (most don't) support other database versions or even databases. You can't "just" throw a MS SQL install on a machine, it needs to be properly installed system-wide and register all its components or it won't work properly etc. — so spinning an instance up for testing really isn't nearly as easy as with postgres.

TomMarius 8y ago
Windows (for test/dev): https://hub.docker.com/r/microsoft/mssql-server-windows-developer/ https://hub.docker.com/r/microsoft/mssql-server-windows-deve... MSSQL also runs on Linux: https://hub.docker.com/r/microsoft/mssql-server/ https://hub.docker.com/r/microsoft/mssql-server/
Lifesnoozer 8y ago
T-SQL might have some oddities, but Microsoft distributes SQL Server as a Docker image, very easy to setup. https://hub.docker.com/r/microsoft/mssql-server/ https://hub.docker.com/r/microsoft/mssql-server/
- Nullabillity 8y ago
  That the setup is complex enough that they need a container should tell you enough.
  - manigandham 8y ago
    Containers have nothing to do with the complexity of the software, they are just a packaging format. Yes, SQL server is complex software, and can be installed via apt-get as well.
lowercased 8y ago
2004 I remember working in a larger enterprise and was forced to use MSSQL. Now... I know there's a lot of 'good' in it, and compared to MySQL at the time, many benefits (though many of those benefits were for the admins, not so much the developers in my org). We had a requirement for paginating through records, and the group of MSSQL admins were... flummoxed at the request. "Why would you need to do that? Just use top(X)!" Umm... when there's 48000 records, and you're trying to see records 47000-47100.... wth? They spent days back and forth trying to come up with reusable sprocs with odd combinations of top/reversing/top/sorting - SQL Server 2000 at the time. I'm not sure the rownumber() thing was available at the time. And... there was much grumbling about this 'stupid' request. I was honestly a bit shocked (I was still a bit naive about 'enterprise' folks) that this was such a chore, and not there. We also needed in-place encryption/decryption, which one of the prototypes had with mysql aes_encrypt(), but we had to use MSSQL, so... thousand of dollars later for some db tool, this was available. I know MSSQL has many advantages, and is very powerful in many situations, but MS-first people sometimes are oblivious to 'standard' functionality other products have.
kodablah 8y ago
> You don't have a client library or anything like that, but a system-wide database driver instead. That's a choice ala ODBC. A simple TDS client library works just fine. In fact, it's not a complex protocol for basic uses and is fairly easy to write a library for.
bdcravens 8y ago
> You can't "just" throw a MS SQL install on a machine, it needs to be properly installed system-wide and register all its components or it won't work properly etc. — so spinning an instance up for testing really isn't nearly as easy as with postgres. As another commenter pointed out, you can run MSSQL in Docker. We do this in CI. You can also install it pretty easily on a standard Linux machine, without much more complexity than Postgresql. For example, you can `apt install mssql-server` https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/linux/quickstart-instal...
manigandham 8y ago
You don't need a system-wide database driver, it has its own client protocol and uses drivers just like any other database: https://docs.microsoft.com/en-us/sql/connect/sql-connection-libraries https://docs.microsoft.com/en-us/sql/connect/sql-connection-... It can also run in Docker containers on Windows, Mac or Linux and spins up in seconds, along with the usual package manager installs on Redhat/Ubuntu: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker https://docs.microsoft.com/en-us/sql/linux/quickstart-instal...
panarky 8y ago
I cringe every time I have to use SQL Server. 1) Weird gaps in ANSI compatibility (no concatenation with ||, no current_date, top n instead of limit/offset, etc.) 2) Just yesterday a client's SQL Server started selecting deadlock victims to be killed, so the DBA's workaround was to throw (nolock) hints on every table and read dirty data. It happens when concurrent queries update records in a different sequence, but the same application on Postgres never deadlocks. 3) The query planner seems amazingly naive for such a mature product. Hard to understand how a query with three where-clause constraints can execute in 20 ms, but adding a fourth constraint makes it run 240000 ms, but I see this kind of insanity every day.
- gaius 8y ago
  It happens when concurrent queries update records in a different sequence, but the same application on Postgres never deadlocks. SQL Server has like 4 different isolation levels + MVCC, you just pick the one you need. Postgres just does MVCC so it sounds like you should just switch SQL Server to that mode. A poor workman blames his tools. Unless that tool is MongoDB.
  - panarky 8y ago
    > just switch SQL Server to MVCC In the real world of mediocre DBAs and databases managed by application vendors, it's never that easy, is it? Postgres just works with default settings while SQL Server chokes on the same transactions with the same volumes. SQL Server is legendary [0] for deadlocks even when the workload is mostly reads and rarely writes. You just don't see this with Oracle, Postgres or MySQL with default settings. Not sure why I'd pay for the privilege of extra headaches. [0] It was a problem in 2008, still not resolved https://blog.codinghorror.com/deadlocked/ https://blog.codinghorror.com/deadlocked/
    - manigandham 8y ago
      Yes the defaults are more pessimistic but why is such a problem to change the locking level? SQL Server has one of the most advanced query optimizers. Are you running on an old version or using out of date statistics?

TomMarius 8y ago

Windows (for test/dev): https://hub.docker.com/r/microsoft/mssql-server-windows-developer/ https://hub.docker.com/r/microsoft/mssql-server-windows-deve... MSSQL also runs on Linux: https://hub.docker.com/r/microsoft/mssql-server/ https://hub.docker.com/r/microsoft/mssql-server/

Lifesnoozer 8y ago

T-SQL might have some oddities, but Microsoft distributes SQL Server as a Docker image, very easy to setup. https://hub.docker.com/r/microsoft/mssql-server/ https://hub.docker.com/r/microsoft/mssql-server/

Nullabillity 8y ago
That the setup is complex enough that they need a container should tell you enough.
- manigandham 8y ago
  Containers have nothing to do with the complexity of the software, they are just a packaging format. Yes, SQL server is complex software, and can be installed via apt-get as well.

lowercased 8y ago

2004 I remember working in a larger enterprise and was forced to use MSSQL. Now... I know there's a lot of 'good' in it, and compared to MySQL at the time, many benefits (though many of those benefits were for the admins, not so much the developers in my org). We had a requirement for paginating through records, and the group of MSSQL admins were... flummoxed at the request. "Why would you need to do that? Just use top(X)!" Umm... when there's 48000 records, and you're trying to see records 47000-47100.... wth? They spent days back and forth trying to come up with reusable sprocs with odd combinations of top/reversing/top/sorting - SQL Server 2000 at the time. I'm not sure the rownumber() thing was available at the time. And... there was much grumbling about this 'stupid' request. I was honestly a bit shocked (I was still a bit naive about 'enterprise' folks) that this was such a chore, and not there. We also needed in-place encryption/decryption, which one of the prototypes had with mysql aes_encrypt(), but we had to use MSSQL, so... thousand of dollars later for some db tool, this was available. I know MSSQL has many advantages, and is very powerful in many situations, but MS-first people sometimes are oblivious to 'standard' functionality other products have.

kodablah 8y ago

> You don't have a client library or anything like that, but a system-wide database driver instead. That's a choice ala ODBC. A simple TDS client library works just fine. In fact, it's not a complex protocol for basic uses and is fairly easy to write a library for.

bdcravens 8y ago

> You can't "just" throw a MS SQL install on a machine, it needs to be properly installed system-wide and register all its components or it won't work properly etc. — so spinning an instance up for testing really isn't nearly as easy as with postgres. As another commenter pointed out, you can run MSSQL in Docker. We do this in CI. You can also install it pretty easily on a standard Linux machine, without much more complexity than Postgresql. For example, you can `apt install mssql-server` https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/linux/quickstart-instal...

manigandham 8y ago

You don't need a system-wide database driver, it has its own client protocol and uses drivers just like any other database: https://docs.microsoft.com/en-us/sql/connect/sql-connection-libraries https://docs.microsoft.com/en-us/sql/connect/sql-connection-... It can also run in Docker containers on Windows, Mac or Linux and spins up in seconds, along with the usual package manager installs on Redhat/Ubuntu: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker https://docs.microsoft.com/en-us/sql/linux/quickstart-instal...

panarky 8y ago

I cringe every time I have to use SQL Server. 1) Weird gaps in ANSI compatibility (no concatenation with ||, no current_date, top n instead of limit/offset, etc.) 2) Just yesterday a client's SQL Server started selecting deadlock victims to be killed, so the DBA's workaround was to throw (nolock) hints on every table and read dirty data. It happens when concurrent queries update records in a different sequence, but the same application on Postgres never deadlocks. 3) The query planner seems amazingly naive for such a mature product. Hard to understand how a query with three where-clause constraints can execute in 20 ms, but adding a fourth constraint makes it run 240000 ms, but I see this kind of insanity every day.

gaius 8y ago
It happens when concurrent queries update records in a different sequence, but the same application on Postgres never deadlocks. SQL Server has like 4 different isolation levels + MVCC, you just pick the one you need. Postgres just does MVCC so it sounds like you should just switch SQL Server to that mode. A poor workman blames his tools. Unless that tool is MongoDB.
- panarky 8y ago
  > just switch SQL Server to MVCC In the real world of mediocre DBAs and databases managed by application vendors, it's never that easy, is it? Postgres just works with default settings while SQL Server chokes on the same transactions with the same volumes. SQL Server is legendary [0] for deadlocks even when the workload is mostly reads and rarely writes. You just don't see this with Oracle, Postgres or MySQL with default settings. Not sure why I'd pay for the privilege of extra headaches. [0] It was a problem in 2008, still not resolved https://blog.codinghorror.com/deadlocked/ https://blog.codinghorror.com/deadlocked/
  - manigandham 8y ago
    Yes the defaults are more pessimistic but why is such a problem to change the locking level? SQL Server has one of the most advanced query optimizers. Are you running on an old version or using out of date statistics?