1

My company is in the midst of an IT overhaul and considering a shift away from MS SQL Server (2016 Standard) to a MySQL database (version 5.6.10). I have used MS SQL Server for years, so I am apprehensive to make this move. Basically - our IT team is under the impression that MySQL is free and comes with only a few syntax changes.

I have spent the day reading up on the differences and honestly finding that they seem to be very similar. If I am going to make a case for MS SQL Server - then now is the time to do it. One key detail - we are an analytical company and our SQL queries are often complex - so I need to make sure that our RDBMS of the future is up to par.

I have encountered the following statement multiple times (about filtering) and I need a translation. My interpretation of this is that it is not possible to use a single query to join tables from differing databases. However I have seen youtube videos where people do just that in MySQL. Please help me understand this difference and whether it is important or not.

MySQL allow users to filter out tables, rows, and users in a number of ways. But it requires users to filter out the tables, rows, or users by individual databases. While filtering the data, the developers have to filter database tables individually by running multiple queries. On the other hand, SQL Server enables developers to take advantage of row-based filtering. The row-based filtering option filters data on a database by database way. Also, the filtered data is stored in a separate distribution database. Hence, it becomes easier for programmers to filter multiple rows without considering the number of databases.

- A Comparison between MySQL vs. MS SQL Server

Greatly appreciate your help with deciphering the quoted statement as well as any Pro/Cons you see for this move. Thanks!

  • Sorry, the quote you have included does not have even a modicum of sense. Please consider using some other resource for comparison. By the way, MySQL "databases" are essentially schemas, i.e. namespaces. – mustaccio Sep 7 at 16:09
5

"Very few syntax changes" -- Excuse my while I laugh! SQL Server has a lot of features that MySQL does not have. Any use of such is not just a syntax change, but possibly a query rewrite.

TOP 10 --> LIMIT 10 -- but only approximately.

ROW_NUMBER --> does not exist

SEQUENCE --> AUTO_INCREMENT -- but only approximately

dbname.dbo.tablename --> dbname.tablename

Which version of MySQL? MySQL 8.0 has a good chance of having some of the fancier features being used in SQL Server. MariaDB 10.4 is likely to come even closer to SQL Server, in that it handles some of the incompatibilities that MySQL does not cover.

JOINing across databases on the same instance of MySQL has never been a problem:

SELECT a.this, b.that
    FROM db1.tblx AS a
    JOIN db2.tbly AS b
    WHERE a.foo = 1
      AND b.blat < 5;

JOINing across two separate instances (of MySQL or MariaDB) on the same server, or separate instances on separate servers is a problem. See MariaDB's "Connect". The quote you provided leaves the term "database" ambiguous. (Or it was pulled out of context.) I hope that I have made it clear with the addition of the term "instance".

As for the rest of that link. It is aimed at CEOs, not us grunts who will have to make the transition. Too much 'hand-waving' and fluff; no meat.

I have not figured out what it means by "row-based filtering". Maybe it is a snazzy feature in SQL Server. But maybe it is not being used by your code.

Start a new Question and post in it "how do I change this query to MySQL" together with a non-trivial, but typical query. You may see some of the 4 incompatibilities I mentioned above, or there may be other things waiting to trip up the conversion.

  • One more thing - I don't know that I can function without Row_Number... that is a function/feature that I use practically daily. Thanks again sir, greatly appreciated! – BS Warmer Sep 6 at 18:38
  • @BSWarmer - For the windowing function ROW_NUMBER() OVER (...), you need MariaDB 10.2 or MySQL 8.0; for ROW_NUMBER as an indicator of a specific row, see AUTO_INCREMENT. (An example would help knowing how easy (or hard) the conversion will be.) – Rick James Sep 6 at 18:57
  • @BSWarmer - 5.6.10 is now 6 years old. I would hope they would use a newer version. It was the first GA version of 5.6. – Rick James Sep 6 at 19:00
  • @RickJames - I have posted one sample query as a separate question: dba.stackexchange.com/questions/247223/… – BS Warmer Sep 6 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.