THE KEY DIFFERENCES BETWEEN MYSQL AND MS SQL SERVER – DEVELOPPARADISE
17/07/2018

THE KEY DIFFERENCES BETWEEN MYSQL AND MS SQL SERVER

Introduction

The MySQL and MS SQL are the two of the most widely used database systems in the world. The first thing that you will note about these two database systems is how they are availed to users. MySQL is open source software. This means that the use of MySQL comes from the pooled knowledge of different people from different corners of the world. This can be arguably be the biggest plus for MySQL as users through the open source platform can continually improve the general program. MS SQL on the other hand uses a closed source development approach. By this, the development of the program is done in-house and all that the consumers get is a program that they are supposed to use.

It is also important to note that MySQL is free to use for any person that desires to use the program.  MS SQL being a closed source program means that a developer can only use the program after paying a fee to be given the rights to use the program. If one has a simple project, there is a SQL Server program that is offered free by MS SQL. However, let’s dig the key differences between MySQL and MS SQL Server.

MySQL vs MS SQL Server

THE KEY DIFFERENCES BETWEEN MYSQL AND MS SQL SERVER

 

Source: cdn-images-1.medium.com

Supported Platforms?

Compatibility across different platforms is also another important factor to consider. MS SQL Server was originally developed by Microsoft for Windows operating system exclusively. Microsoft recently announced its decision to make the RDBMS available on both Linux, and Mac OS X (via Docker). Hence, the enterprises now have option to run the database system on three distinct platforms. But they will lack the option to avail certain features while running SQL Server on Linux or Mac OS X. The enterprises can run MySQL smoothly on several popular operating systems including Windows, Linux and Mac OS X.

Storage Engine

MySQL supports a number of storage engines. While using MySQL, the programmers even have option to use a plug-in storage engine. But the earlier versions of the RDBMS supported only non-transactional storage engine. Hence, the programmers working with older versions of the database system need to upgrade the storage engine. At the same time, the developers have to use a single storage engine while working with SQL Server. But they have to switch to the most recent versions of the RDBMS to avail improved storage engine. The multiple storage engine support makes MySQL more flexible than MS SQL Server.

However, MySQL as a database system offers various options to users especially with regard to derived engines. It is based on Sybase, Berkeley DB, InnoDB and other engines. MS SQL is limited to use of only a single derived engine and this is Sybase.

MS SQL Server vs MySQL performance

In my experience MySQL is often far faster than this. And, SQL Server is often slower. For what it’s worth the MariaDB 10.0.x instance is not slow on this, taking times.

Many factors affect bulk insert performance. RDMS servers do a lot of invisible work when users insert rows, such as building BTREE indexes and maintaining transactional data integrity.

It’s unwise to extrapolate from simple test cases, especially of sequential values, to draw general conclusions about performance. Sequential value insertion can sometimes require BTREE rebalancing, which can be time consuming.

In MySQL, wrapping your insertion loops in a transaction makes a big performance difference. Like this:

Code snippet

BEGIN TRANSACTION;

test_loop : LOOP

IF (int_val > 998) THEN LEAVE test_loop END IF;SET int_val = int_val +1; insert into empmast (name) values (concat( ‘Client ‘ , int_val) ); END LOOP; COMMIT;

Can MySQL build up a batch of records to insert all in one go, and it can update your indexes in one go?

If we are using a program to insert a million rows, it’s often smart to wrap them in transactions of a few thousand rows each.

The Key difference between MySQL and MS SQL Server

The core key difference between MySQL and MS SQL Server matrix is given below:

MS SQL SERVER vs MySQL Matrix

Information provided by DB-Engines

 

Name

Microsoft SQL Server  X

MySQL  X

Description

Microsofts relational DBMS

Widely used open source RDBMS

Primary database model

Relational DBMS

Relational DBMS 

Secondary database models

Document store
Graph DBMS
Key-value store

Document store
Key-value store

DB-Engines Ranking 

 

Trend Chart

Score

1053.41

Rank

#3

  Overall

 

#3

  Relational DBMS

Score

1196.07

Rank

#2

  Overall

 

#2

  Relational DBMS

Website

www.microsoft.com/­en-us/­sql-server

www.mysql.com

Technical documentation

docs.microsoft.com/­en-ie/­sql/­sql-server/­sql-server-technical-documentation

dev.mysql.com/­doc

Developer

Microsoft

Oracle 

Initial release

1989

1995

Current release

SQL Server 2017, October 2017

8.0.11, April 2018

License 

commercial 

Open Source 

Cloud-based 

no

no

DBaaS offerings (sponsored links) 

 

Google Cloud SQL: A fully-managed database service for the Google Cloud Platform

Implementation language

C++

C and C++

Server operating systems

Linux
Windows

FreeBSD
Linux
OS X
Solaris
Windows

Data scheme

yes

yes

Typing 

yes

yes

XML support 

yes

yes

Secondary indexes

yes

yes

SQL 

yes

yes 

APIs and other access methods

OLE DB
Tabular Data Stream (TDS)
ADO.NET
JDBC
ODBC

Proprietary native API
ADO.NET
JDBC
ODBC

Supported programming languages

C#
C++
Delphi
Go
Java
JavaScript (Node.js)
PHP
Python
R
Ruby
Visual Basic

Ada
C
C#
C++
D
Delphi
Eiffel
Erlang
Haskell
Java
JavaScript (Node.js)
Objective-C
OCaml
Perl
PHP
Python
Ruby
Scheme
Tcl

Server-side scripts 

Transact SQL and .NET languages

yes 

Triggers

yes

yes

Partitioning methods 

tables can be distributed across several files (horizontal partitioning); sharding through federation

horizontal partitioning, sharding with MySQL Cluster or MySQL Fabric

Replication methods 

yes, but depending on the SQL-Server Edition

Master-master replication
Master-slave replication

MapReduce 

no

no

Consistency concepts 

Immediate Consistency

Immediate Consistency

Foreign keys 

yes

yes 

Transaction concepts 

ACID

ACID 

Concurrency 

yes

yes 

Durability 

yes

yes

In-memory capabilities 

yes

yes

User concepts 

fine grained access rights according to SQL-standard

Users with fine-grained authorization concept 

       

Source: db-engines.com

Difference between MS SQL Server vs MySQL syntax

In MS SQL Server the table names and column names are enclosed in double quotes or square brackets whereas in MySQL table names and column names are enclosed in backtick (“`”) character.

Example:

MS SQL Server (Code snippet)

CREATE TABLE “Employees” (“Empno”  VARCHAR(10),”EmpName” Varchar(100) ……

SELECT [Empno],[EmpName] from “Employees” ……

MySQL (Code snippet)

CREATE TABLE `Employees` (`Empno` VARCHAR(10),`EmpName` Varchar(100) ……

SELECT `Empno`,`EmpName` from `Employees` ……

Enclosing identifier names in enclosing character is optional in both database softwares, but if the identifier name contains blank spaces then it becomes mandatory to enclose it within double quotes or square brackets.

For Example, the following statement can be written like this

MS SQL Server (Code snippet)

CREATE TABLE “Employees” (“Empno”  VARCHAR(10),”EmpName” Varchar(100) ……

MySQL (Code snippet)

CREATE TABLE `Employees` (`Empno` VARCHAR(10),`EmpName` Varchar(100) ……

or without enclosing character like this.

MS SQL Server (Code snippet)

CREATE TABLE Employees (Empno  VARCHAR(10),EmpName Varchar(100) ……

MySQL (Code snippet)

CREATE TABLE Employees (Empno  VARCHAR(10),EmpName Varchar(100) ……

but if the identifier name contains blank spaces then you have to enclosed it with double quotes or square brackets

MS SQL Server (Code snippet)

CREATE TABLE “Employees Table” (“Emp No”  VARCHAR(10),”EmpName” Varchar(100) ……

MySQL (Code snippet)

CREATE TABLE `Employees Table` (`Emp No` VARCHAR(10),`EmpName` Varchar(100) ……

you can’t  write it like this

MS SQL Server (Code snippet)

CREATE TABLE Employees Table (Emp No  VARCHAR(10),EmpName Varchar(100) ……

MySQL (Code snippet)

CREATE TABLE Employees Table (Emp No VARCHAR(10),EmpName Varchar(100) ……

In MySQL if you turn on ANSI_QUOTES  SQL mode option with the following command

mysql> SET sql_mode=’ANSI_QUOTES’;

then MySQL also allows to quote identifiers within double quotation marks. But remember when you enable this option you cannot quote literal strings in double quotes in SQL statements. you have to use only single quotes for quoting literal strings in SQL statements

Case Sensitive

In MS SQL if a database is created with Case Sensitive COLLATION then table names and column names are case sensitive otherwise, if the database is created with a Case Insensitive Collation then identifier names are case insensitive

For Example:

If you created a table in Case Sensitive Collation database, like this

Code snippet

create table Employee (SNo int,Name Varchar(100),Sal money)

Notice the captial E in tablename, then if you give the following command

select * from employee

It will give error

Invalid object name ’employee’.

Diagram -1

THE KEY DIFFERENCES BETWEEN MYSQL AND MS SQL SERVER

Source: dbload.com

You have to mention the table name in same case as you have specified at the time of creation.

Diagram -2

THE KEY DIFFERENCES BETWEEN MYSQL AND MS SQL SERVER

Source: dbload.com

In MySQL there is no case sensitiveness in Schema Names

Diagram -3

THE KEY DIFFERENCES BETWEEN MYSQL AND MS SQL SERVER

Source: dbload.com

In MS SQL Server to view top ‘n’ rows we have to give TOP keyword after the SELECT clause. For Example, to view top 5 salaries of employees we have to give a query like this

Code snippet

SELECT TOP 5 [Empno] ,[Name],[Salary],[Jdate] FROM [Scott].[dbo].[Emp] order by salary desc;

Diagram-4

THE KEY DIFFERENCES BETWEEN MYSQL AND MS SQL SERVER

Source: dbload.com

In MySQL the equivalent o TOP n rows can be achieved by using LIMIT n keyword. 
For Example the equivalent query for the above MSSQL query would be

Code snippet

select * from emp order by sal desc limit 5;

MS SQL Server vs MySQL disk space utilization

A great feature that has also propelled MySQL is that it comes with many features but the disk capacity needs are very low. MS SQL on the other hand needs heavy use of disk space and there must be adequate space factored in the development to allow for the problem to work as expected. If you happen to be learning on how to use database systems, MySQL allows for learning with beginners. MS SQL on the other hand is not as easy to start off with and is mainly preferred to be used by professionals as it is more complex.

General performance of the two database systems favors MySQL.  It is quite easy to work with MySQL and its performance is great, a feature that is attributed to the use of MYISAM. The general performance of MS SQL is lower in comparison to MySQL, a factor that could be attributed to lack of use of MYISAM. In recovery, MS SQL wins this, handling recovery of database info efficiently as opposed to use of MySQL.

Conclusion

In this article i tried to cover almost all the key differences between Microsoft SQL Server and MySQL. I hope that you guys enjoy this article.

History

Initial post: July 16, 2018