When working with relational databases, Microsoft SQL Server (MS-SQL) and MySQL are among the most widely used systems today. While both adhere to the SQL (Structured Query Language) standard, their specific implementations and syntax often differ. Developers frequently encounter challenges when moving between the two, particularly with Data Definition Language (DDL), Data Manipulation Language (DML), and SELECT queries.
This article highlights the key differences between MS-SQL and MySQL through clear examples and explanations to help you understand how queries vary across these platforms.
1. Data Definition Language (DDL)
DDL commands define and manage database structures such as tables. Below are examples of creating a table in each system:
Example 1: Creating a Table
MS-SQL:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY IDENTITY(1,1),
EmpName VARCHAR(100) NOT NULL,
Salary DECIMAL(10,2),
HireDate DATETIME DEFAULT GETDATE()
);
MySQL:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY AUTO_INCREMENT,
EmpName VARCHAR(100) NOT NULL,
Salary DECIMAL(10,2),
HireDate DATETIME DEFAULT CURRENT_TIMESTAMP
);
Key Differences:
- MS-SQL uses IDENTITY for auto-increment columns, while MySQL uses AUTO_INCREMENT.
- MS-SQL uses GETDATE() for default date values, whereas MySQL uses CURRENT_TIMESTAMP.
2. Data Manipulation Language (DML)
DML commands modify data in tables, such as inserting, updating, or deleting records.
Example 2: Inserting Data
MS-SQL and MySQL:
INSERT INTO Employees (EmpName, Salary)
VALUES ('John Doe', 55000.50);
Note:
Both systems use the same syntax for inserting records.
Example 3: Updating Data
MS-SQL and MySQL:
UPDATE Employees
SET Salary = Salary + 5000
WHERE EmpID = 1;
Note:
The update syntax is identical in both systems.
Example 4: Deleting Data with a Limit
MS-SQL:
DELETE TOP (1)
FROM Employees
WHERE Salary < 30000;
MySQL:
DELETE FROM Employees
WHERE Salary < 30000
LIMIT 1;
Key Difference:
- MS-SQL limits rows using TOP (n).
- MySQL uses the LIMIT n clause.
3. SELECT Queries
SELECT statements retrieve data from the database, and this is where some differences become clear.
Example 5: Selecting Top N Rows
MS-SQL:
SELECT TOP 5 EmpName, Salary
FROM Employees
ORDER BY Salary DESC;
MySQL:
SELECT EmpName, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5;
Difference:
- MS-SQL uses TOP n.
- MySQL uses LIMIT n.
Example 6: String Concatenation
MS-SQL:
SELECT EmpName + ' earns ' + CAST(Salary AS VARCHAR)
FROM Employees;
MySQL:
SELECT CONCAT(EmpName, ' earns ', Salary)
FROM Employees;
Difference:
- MS-SQL uses + for string concatenation.
- MySQL uses the CONCAT() function.
Example 7: Current Date and Time
MS-SQL:
SELECT GETDATE() AS CurrentDate;
MySQL:
SELECT NOW() AS CurrentDate;
Difference:
- MS-SQL uses GETDATE().
- MySQL uses NOW().
Conclusion
Although MS-SQL and MySQL both follow the SQL standard, their query syntax and functions can vary in key areas. When transitioning between the two, pay close attention to auto-increment methods, date/time functions, string concatenation, and row limiting techniques. For basic DML operations like INSERT, UPDATE, and DELETE (without row limits), both systems are very similar. Understanding these differences will help you write more portable SQL queries and avoid common compatibility issues.