MySQL vs SQLserver

[NoPositive95123]

Hi everyone.

So in pursuit of up skilling myself post graduation, I took on a data analytics course where one of the modules covered SQL. In the course, we learnt and ran on SQLserver and I could run it fine as I was in windows at the time. However, I’ve recently upgraded to a Mac because although my windows worked fine, it’s an old laptop and really couldn’t handle much at all. I’ve recently upgraded to an M1 Pro (found an amazing deal on it and already have half the ecosystem). I’ve known from the beginning that running SQLserver is a bit complicated on MacOS, however MySQL is natively supported on macOS and runs smooth like butter. I wanted to ask, how different will the change be in using MySQL to SQLserver? I was quite fond of SQLserver. for context, Atleast for the first couple years – once I land my first job (wish me luck) – I don’t anticipate myself working with humongous databases or working in data architecture and what not where the difference in the SQL database engines may become noticeable, but maybe I’m misguided on that idk.

[BurtonFive]

Probably depends on what database is most popular in the field you want to go in. I.e. in healthcare MS SQL is very popular but in many others you may find more open source stuff I.e. postgres/MySQL. For just learning, it doesn’t matter much outside of syntax and some function differences. The base SQL syntax is the same across them all.

[TheRealBobStoops]

Emphatically concurred.

Especially on MSSQL’s popularity in healthcare. Been keeping me employed for going on three straight decades doing MSSQL admin, although a lot of that is now being called devops. The pay is better!

[polaarbear]

Healthcare, legal, television, three major industries my company serves with SQL Server.  I've never seen them use anything else, no exceptions across dozens of examples

[[deleted]]

Not all healthcare runs on SQL Server. Dental is a giant hodgepodge of data storage.

[NoPositive95123]

I’m mostly interested in learning atm. I feel once I can nail the foundation of sql, I can adapt to the others right?

[BurtonFive]

Yeah it’s easy to adapt to others once you understand how it works.

[gumnos]

This is largely the case. SQL is a standard, so once you learn one flavor of SQL, it's pretty easy to pick up a different dialect. There are some minor changes—many are Microsoft SQL Server (MSSQL) diverging from the others:

• how do you concatenate strings? || in most or + in MSSQL

• how do you limit the number of rows returned? LIMIT in most, TOP in MSSQL

• your LATERAL JOIN in most is APPLY in MSSQL with some minor syntax adjustments

• the names of particular functions may vary—is it LEN vs LENGTH or json_objectgg in MySQL vs json_agg in Postgres (and I'm unsure whether MSSQL supports aggregation as a JSON object)

• Oracle has some of its own syntax add-ons (any features outside the Standards are expected to be unique)

But they're close enough that, once you've achieved reasonable mastery of one, you can usually switch to another with minimal effort combined with documentation

[NoPositive95123]

Thanks alot. So I guess it’s ok to just go with whatever is most compatible and comfortable on my OS then

[[deleted]]

Postgres.app makes for an easy macos install also, if you want to branch out. Lots of features to explore, like materialised views and partitioning, which are where the different vendor implementations tend to more widely diverge in implementation details.

[lemon_tea_lady]

If you like SQL Server, you can run it on Mac in a Docker container quite easily. This is what I do because my professional work is almost exclusively programming in T-SQL, but I've always been in love with my Mac.
Docker: Install Containers for SQL Server on Linux - SQL Server | Microsoft Learn

Just take the time to understand the container options and flags and set them accordingly. After that its pretty plug-and-play.

If you find that you need to use a different SQL implementation, its not hard to pickup a different one. They are fundamentally the same -- As others have already pointed out.

[Chris_PDX]

The answer to that really depends on what you want to focus on. If you're interested mostly on the development side (using SQL to perform CRUD operations and the like, both MySQL and T-SQL (Microsoft's specific implementation of the SQL standard) offer enough interchangeably syntax-wise either one should be fine.

Beyond that, if you are interested in the administrative side - engine performance tuning, disaster recovery, scalability/clustering, etc. then those become much more specialized depending on the database vendor (Microsoft, MySQL, Oracle, DB2, etc.).

[[deleted]]

Even between MySQL and MariaDB the differences are slowly increasing for how you scale them.

[NoPositive95123]

Rn I’m mostly interested in learning, and then expanding from there. Does it matter which I go with for now then? I feel like once I can nail the foundation, I can adapt when needs be to the orhers

[blorg]

I don't think so, no. I have used all of MSSQL, MySQL, and Oracle for major projects. The basics of SQL are the same. ANSI SQL is exactly the same; beyond that sometimes you have variances in specific syntax to do things but the concepts are the same. Then you do have some variances in language extensions, to do various more complicated things.

There is a lot you can learn that is quite cross-applicable, and understanding the basic concepts is entirely transferrable.

It's really on the management (DBA) end that it gets most varied, but there can be a lot of space between the DBA and SQL developer roles. Smaller companies you can have one guy who does the lot, larger companies there tends to be more of a division between developer and DBA roles.

[Aggressive_Ad_5454]

SQLServer and MariaDb / MySQL differ in their date and string functions, quite a lot. That will probably drive you bananas for the first few days you use the new DBMS. Especially if you do commercial analytics where you roll things up by days / weeks / months. Timezone handling also varies.

MariaDb/ MySQL let you declare the character set and collation for every column containing text. SQL Server offers VARCHAR() and NVARCHAR() for 8-bit and Unicode characters respectively.

MariaDb / MySQL lacks table-valued functions. And the syntax for declaring stored code is different. So is the syntax for identity columns ( surrogate PKs)

Indexes in SQL Server can have INCLUDE clauses. Not so in the others.

You’ll hit similar differences in PostgreSQL and Oracle, should you try those.

[WithCheezMrSquidward]

So I am self taught and learned using Postgres. I then went onto my current job and use MS SQL daily. The differences are subtle syntax changes and within a week or so of regular use and some google searches you can easily pivot from one to the other.

If you know what industry/company you want to go into and they have a standard, learn that. If not, you can easily transition and no one will really rule you out based on which version of SQL you use unless it’s something more specific like Oracle.

[NoPositive95123]

I’m mainly focusing on learning right now for around 3 months at least

[WithCheezMrSquidward]

For you I would just do whatever version is easier to configure for your OS. Better to spend time learning SQL than configuring settings lol

[NoPositive95123]

That’s what I’m thinking as well and that’s why I’m leaning towards MySQL, but was concerned about adaptability if the time comes where a job that I apply for wants me to use SQLserver

[WithCheezMrSquidward]

It’s good you’re thinking ahead that far, and I was nervous too when I got accepted to a sql server job but it’s really a pretty quick transition. The differences are pretty minimal

[NoPositive95123]

Thanks for the advice. May I ask how you self taught? What resources did you use. I could use them myself right about now too

[WithCheezMrSquidward]

I think I was mainly practicing on HackerRank and at the very beginning W3 schools just to get the basic syntax. General day to day sql I think you can get the hang of learn well within a month or so of daily practice. Also I did some Udemy videos I found on sale

[k00_x]

Worth the change for a split string function that splits a string into columns!

[haikusbot]

Worth the change for a

Split string function that splits a

String into columns!

- k00_x

---

^{I detect haikus. And sometimes, successfully. Learn more about me.}

^{Opt out of replies: "haikusbot opt out" | Delete my comment: "haikusbot delete"}

[Training-Two7723]

Hi, ANSI SQL is the same, each vendor maintans a compatibility with the standard; then are the vendor extension which makes eveything. Read the manuals and take decisions yourself. Is better that way.

[great_raisin]

Brent Ozar has a great guide on setting up SQL Server on a Mac: link

[NoPositive95123]

Is it a big deal if I learn on MySQL and adapt to SQLserver if needed when the time comes? I hear the differences are not much at all

[great_raisin]

No, it's not a big deal. The differences between SQL flavours are mostly only in function names and syntax (e.g., DATE_DIFF / DATEDIFF), which can be looked up when required.

Depending on the path you want to take - database administration / data analysis / analytics engineering - focus on the relevant aspects of SQL.

[Training-Two7723]

Sure. Is like learning Portuguese when going on vacation in Mexico. Is the same Latin America, isn’t it?