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.
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;
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;
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;
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
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();