1. Introduction to MySQL & MSSQL
1.1 MySQL
- Open-source relational database management system (RDBMS).
- Uses Structured Query Language (SQL).
- Commonly used for web applications (e.g., WordPress, e-commerce platforms).
1.2 MSSQL (Microsoft SQL Server)
- Developed by Microsoft, widely used for enterprise applications.
- Supports T-SQL (Transact-SQL), an extension of SQL with procedural programming.
- Integrates well with Microsoft Azure & .NET applications.
2. Basic SQL Commands
| Operation | MySQL Command | MSSQL Command |
| Create Database | CREATE DATABASE mydb; | CREATE DATABASE mydb; |
| Use Database | USE mydb; | USE mydb; |
| Drop Database | DROP DATABASE mydb; | DROP DATABASE mydb; |
| Create Table | CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); | CREATE TABLE users (id INT PRIMARY KEY, name NVARCHAR(50)); |
| Insert Data | INSERT INTO users (id, name) VALUES (1, ‘John’); | INSERT INTO users (id, name) VALUES (1, ‘John’); |
| Select Data | SELECT * FROM users; | SELECT * FROM users; |
| Update Data | UPDATE users SET name = ‘Jane’ WHERE id = 1; | UPDATE users SET name = ‘Jane’ WHERE id = 1; |
| Delete Data | DELETE FROM users WHERE id = 1; | DELETE FROM users WHERE id = 1; |
MySQL uses VARCHAR, while MSSQL prefers NVARCHAR for Unicode support.
3. Data Types in MySQL vs. MSSQL
| Category | MySQL | MSSQL |
| Integer | INT, TINYINT, BIGINT | INT, SMALLINT, BIGINT |
| Floating Point | FLOAT, DOUBLE | FLOAT, REAL |
| String | VARCHAR, TEXT | NVARCHAR, VARCHAR, TEXT |
| Date & Time | DATE, DATETIME, TIMESTAMP | DATE, DATETIME, DATETIME2 |
| Boolean | TINYINT(1) (0 = False, 1 = True) | BIT (0 = False, 1 = True) |
4. Joins in MySQL & MSSQL
4.1 Types of Joins
- INNER JOIN → Returns matching records from both tables.
- LEFT JOIN → Returns all records from the left table & matching records from the right.
- RIGHT JOIN → Returns all records from the right table & matching records from the left.
- FULL JOIN → Returns all records when there is a match in either table (Only in MSSQL).
Inner Join Example
SELECT u.id, u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
MySQL & MSSQL support similar JOIN syntax.
5. Indexing for Performance
5.1 Why use Indexes?
- Improves query performance by allowing faster data retrieval.
- Reduces the need for full table scans.
Create Index
CREATE INDEX idx_users_name ON users(name);
Check Index Usage (MSSQL Only)
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('users');
Best Practices:
- Index columns used in WHERE, JOIN, and ORDER BY clauses.
- Avoid too many indexes, which slow down INSERT/UPDATE operations.
6. Stored Procedures
6.1 Stored Procedures → Precompiled SQL statements for efficiency & security.
MySQL Stored Procedure
DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
SELECT * FROM users;
END //
DELIMITER ;
MSSQL Stored Procedure
CREATE PROCEDURE GetUsers
AS
BEGIN
SELECT * FROM users;
END;
Stored procedures help encapsulate business logic inside the database.
7. Triggers for Automation
7.1 Triggers → Automatically execute an action before/after an event.
MySQL Trigger
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();
MSSQL Trigger
CREATE TRIGGER trg_UserInsert
ON users
AFTER INSERT
AS
BEGIN
PRINT 'New user added!';
END;
Use triggers wisely to avoid performance overhead.
8. Backup & Restore
MySQL Backup & Restore
mysqldump -u root -p mydb > backup.sql # Backup
mysql -u root -p mydb < backup.sql # Restore
MSSQL Backup & Restore
BACKUP DATABASE mydb TO DISK = 'C:backupmydb.bak'; -- Backup
RESTORE DATABASE mydb FROM DISK = 'C:backupmydb.bak'; -- Restore
Regular backups prevent data loss in case of failure.
9. Security Best Practices
9.1 Security Measures for MySQL & MSSQL
- Use least privilege → Grant only necessary permissions.
- Encrypt sensitive data using AES_ENCRYPT() (MySQL) or Transparent Data Encryption (MSSQL).
- Use parameterized queries to prevent SQL Injection.
MySQL User Management
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON mydb.* TO 'admin'@'localhost';
MSSQL User Management
CREATE LOGIN admin WITH PASSWORD = 'password';
CREATE USER admin FOR LOGIN admin;
GRANT SELECT, INSERT ON users TO admin;
Never use root or sa user in production.
10. Query Optimization & Performance Tuning
10.1 Optimize Queries for Speed
| Optimization Technique | Example |
| Use EXPLAIN (MySQL) or EXECUTION PLAN (MSSQL) | EXPLAIN SELECT * FROM users; |
| Avoid SELECT *, fetch only required columns | SELECT name FROM users; |
| Use indexes on frequently queried columns | CREATE INDEX idx_name ON users(name); |
| Optimize JOINs by using indexes | EXPLAIN SELECT u.id, o.order_id FROM users u JOIN orders o ON u.id = o.user_id; |
| Use LIMIT for pagination in MySQL | SELECT * FROM users LIMIT 10 OFFSET 20; |
| Use TOP for pagination in MSSQL | SELECT TOP 10 * FROM users; |
Optimizing queries reduces response times & server load.
