Home
/
Blog
/
MySQL vs MSSQL Detailed Comparison

MySQL vs MSSQL Detailed Comparison

MySQL and SQL Server are relational database management systems. The major difference between these two databases is that MySQL is open source whereas SQL Server is developed by Microsoft. Both databases support various programming languages.

For instance, MySQL supports Java, C++, TCL, Perl, and Haskel. SQL Server supports Java, C++, Visual Basic, Delphi, Ruby, and R. Many database administrators think that the performance of MySQL is better than MsSQL. On the other hand, MSSQL has more reliable recovery methods than MySQL. The most significant thing is MySQL supports both UNIX and Linux. The thing to remember is that both are active and robust databases and are widely used in the market. This post will explain the technical difference between both databases.

MSSQL Case Sensitive vs MYSQL Case Sensitive

In MSSQL if a table or a database is made using the Case Sensitive relationship also known as COLLATION then all the created objects such as table names and column names are case sensitive., if the database is created using Case Insensitive Collation then all the names are case insensitive.

MSSQL Create Table Example

CREATE TABLE EMP
( emp_no INT NOT NULL,
  f_name VARCHAR(60) NOT NULL,
  l_name VARCHAR(60),
  salary MONEY
);

MySQL Create Table Example

CREATE TABLE IF NOT EXISTS jobs (
    job_id INT AUTO_INCREMENT PRIMARY KEY,
    jobtitle VARCHAR(250) NOT NULL,
    join_date DATE,
    end_date DATE,
    status TINYINT NOT NULL,
    jobdetails TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)  ENGINE=INNODB;

MSSQL TOP ‘n’ Rows vs MySQL LIMIT

In MSSQL, if you want to view top ‘n’ rows then you have to use the TOP keyword after the SELECT clause. For instance, if you have a table employee and you want to view the top 10 salaries then it is done by using the TOP keyword after the select statement.

MSSQL Top ‘n’ rows Example

 SELECT TOP 5 [Empid]
,[EName]
,[ESalary]
,[Jdate]
FROM [Scott].[dbo].[Employee] order by salary desc

In MySQL, the TOP n rows can be obtained by using LIMIT n keyword. For example, a similar query for the same MSSQL query would be:

MySQL LIMIT Example

Select * from emp order by sal desc limit 10;

MSSQL Stored Procedure vs MySQL Stored Procedure

A stored procedure is a saved SQL code. The stored procedure is the most crucial component of a relational database management system. In MSSQL, users can write T-SQL code and save that code as a stored procedure. This code or stored procedure can be called or reused again and again. It is like automation of a particular task. You just have to enter values in the parameter and then it will be executed automatically to complete the given task.

A MySQL stored procedure is a stored subprogram in a conventional SQL language, saved in the database. In MySQL, stored procedures must have a name, a parameter listing, and a SQL statement(s). Almost all relational database systems maintain the stored procedure. The main distinction is that UDFs can be managed like any other character within SQL statements, while stored procedures must be called using the CALL statement.

MSSQL Stored Procedure Example

USE TestDB;  
GO  
CREATE PROCEDURE MyProce.uspGetEmpTestDB2   
    @LName nvarchar(70),   
    @FName nvarchar(70)   
AS   

    SET NOCOUNT ON;  
    SELECT FName, LName, Dept 
    FROM MyProce.vEmp 
    WHERE FName = @FName AND LName = @LName  
    AND EndDate IS NULL;  
GO

MySQL Stored Procedure Example

DELIMITER //
CREATE PROCEDURE CountryPopulation(IN CName VARCHAR(255), OUT totalpopulation BIGINT default 0, INOUT  yearNumber INT)
BEGIN
SELECT totalpopulation, populationyear INTO population, yearNumber
FROM CountryPopulation
WHERE country = CName;
END //
DELIMITER;

MSSQL Backup and Restoration vs MySQL Backup and Restoration

In a MySQL database, if you use the backup utility then the data is derived as SQL statements. Hence, backing up and restoring large data can take a huge time because of the performance of various SQL statements. Moreover, to bypass inequality, MySQL secures the database throughout the backup method. Still, database administrators can revoke this by applying the mysqldump utility using a particular flag “:–single-transaction”.

It backups the tables without locking them. If database administrators want to take a backup of large tables then they use the “–quick” flag. While SQL Server users don’t encounter the locking situation. SQL Server avoids locking databases, and database administrators or developers can use the database even during the backup process. Also in MySQL, users, tables, and rows can be separated in various forms. Still, it asks users to execute various queries on a specific database only. While SQL Server contains data and row-based filtering. MySQL supports its database files to be modified and obtained by other methods during runtime. This, though, isn’t possible with MSSQL, as its users are expected to execute a case to make the function. Due to this MSSQL is less inclined to attacks as the data cannot be handled or obtained immediately.

MSSQL Backup command

BACKUP DATABASE MyDB2020
TO DISK='A:\SQLServerBackups\MyDB1.bak',
DISK='B:\SQLServerBackups\MyDB2.bak',
DISK='C:\SQLServerBackups\MyDB3.bak'
WITH FORMAT,
  MEDIANAME = 'MyDB2020StripedSet0',
  MEDIADESCRIPTION = 'Striped media set for MyDB2020 database';
GO

MySQL Backup command

# mysqldump -u root -ptecmint --all-databases > all-databases.sql

MSSQL Triggers vs MySQL Triggers

In any relational database management system, Trigger is just like a stored procedure that is event-driven. In MSSQL, triggers are special stored procedures that are implicitly or explicitly fired automatically in reply to a particular event, object, database, and other server events. MSSQL contains three types of triggers. These are Data manipulation language (DML) triggers, Data definition language (DDL) triggers, and Logon triggers.

In MySQL, a trigger is a saved subprogram called or fired automatically in reply to various events like insert, update, or delete that happens in the table. For instance, you can set a trigger that is called automatically before inserting a new row into a table. The difference between MSSQL and MySQL triggers is that MySQL contains only row-level triggers and not statement-level triggers.

MSSQL Trigger Example

CREATE TABLE prod.audits(
    item_id INT IDENTITY PRIMARY KEY,
    item_id INT NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    brand_number INT NOT NULL,
    category_number INT NOT NULL,
    model SMALLINT NOT NULL,
    li_price DEC(10,2) NOT NULL,
    modified_at DATETIME NOT NULL,
    operation CHAR(3) NOT NULL,
    CHECK(operation = 'INS' or operation='DEL')
);

MySQL Trigger Example

CREATE TRIGGER before_empupdate 
    BEFORE UPDATE ON emp
    FOR EACH ROW 
 INSERT INTO empaudit
 SET action = 'update',
     empno = OLD.empno,
     empname = OLD.empname,
     changedat = NOW();