Lesson 1 of 5

Syntax & Data Types

SQL Server and MySQL both speak "SQL," but the accent is different everywhere you look — starting with how each one lets you quote an identifier that collides with a reserved word, and continuing straight into the type system. Nothing here is exotic once you know it's coming, but every one of these differences will break a copy-pasted script on day one if you don't.

💡 The Bottom Line

Swap square brackets [like_this] for backticks `like_this` when quoting identifiers, swap NVARCHAR for VARCHAR with an explicit UTF-8 charset, swap DATETIME2 for DATETIME or TIMESTAMP depending on whether you need timezone-aware behavior, swap BIT for TINYINT(1), and swap the + string operator for CONCAT(). Get those five swaps automatic and most T-SQL scripts port over with only minor friction.

Quoting identifiers

SQL Server (T-SQL)

SELECT [Order Date], [User]
FROM [dbo].[Orders]
WHERE [User] = 'sdeiv';

MySQL

SELECT `Order Date`, `User`
FROM `orders`
WHERE `User` = 'sdeiv';

📋 Key differences

SQL Server quotes identifiers with square brackets [Order Date]; MySQL uses backticks `Order Date` (double quotes only work in MySQL if ANSI_QUOTES mode is enabled, so don't rely on them by default).
SQL Server's schema-qualified names look like [dbo].[Orders]; MySQL treats each database as its own namespace and doesn't use a dbo-style default schema — you'd write mydb.orders only when crossing databases.
Both engines let you skip the quoting entirely if the identifier isn't a reserved word and has no spaces or special characters — quoting is a safety net, not a requirement.

⚠️ Gotcha

Copy-pasting a T-SQL script into a MySQL client with the brackets left in place doesn't throw a helpful "wrong quote character" error — MySQL just doesn't recognize [User] as an identifier at all and throws a syntax error pointing at the bracket itself. If you see a syntax error right at a [, that's almost always this.

String types: NVARCHAR vs VARCHAR + charset

SQL Server (T-SQL)

CREATE TABLE Users (
    Name NVARCHAR(100) NOT NULL
);

MySQL

CREATE TABLE users (
    name VARCHAR(100)
        CHARACTER SET utf8mb4
        NOT NULL
);

SQL Server splits string types into VARCHAR (single-byte, non-Unicode) and NVARCHAR (Unicode) because its default collations are not Unicode-aware. MySQL doesn't make that split at the type level — VARCHAR stores whatever character set the column (or table, or database) is configured with. The practical translation: an NVARCHAR column becomes a VARCHAR column on a table (or database) whose default charset is utf8mb4 — MySQL's true, 4-byte-per-character UTF-8, not the older 3-byte utf8 alias, which can't store the full Unicode range (including emoji).

Date and time: DATETIME2 vs DATETIME/TIMESTAMP

SQL Server (T-SQL)

CREATE TABLE Events (
    StartsAt DATETIME2(3) NOT NULL
);

MySQL

CREATE TABLE events (
    starts_at DATETIME(3) NOT NULL
);

📋 Key differences

DATETIME2 is SQL Server's modern, high-precision date/time type (up to 100ns precision); MySQL's closest match is DATETIME with an explicit fractional-second precision, e.g. DATETIME(3) for milliseconds — MySQL supports up to 6 digits.
MySQL also has TIMESTAMP, which looks similar but stores values in UTC internally and converts on read/write based on the session time zone, and has a much smaller range (1970–2038). Use DATETIME when you want SQL Server-like "store exactly what I gave you" behavior; reach for TIMESTAMP only when you specifically want automatic UTC conversion or a self-updating "last modified" column.
Neither DATETIME nor DATETIME2 stores a time zone offset the way some other databases do — both are "wall clock" values unless you convert explicitly.

Booleans: BIT vs TINYINT(1)

SQL Server (T-SQL)

CREATE TABLE Users (
    IsActive BIT NOT NULL DEFAULT 1
);

MySQL

CREATE TABLE users (
    is_active TINYINT(1) NOT NULL DEFAULT 1
);

SQL Server has a dedicated BIT type that stores 0, 1, or NULL and is treated as a boolean by tools and drivers. MySQL has no true boolean type — BOOLEAN/BOOL are just aliases for TINYINT(1), a one-byte integer. It behaves like a boolean in practice (most drivers map it to a language-level bool), but nothing stops someone from inserting 5 into it — MySQL won't reject it the way a strictly boolean type would.

Concatenating strings: + vs CONCAT()

SQL Server (T-SQL)

SELECT FirstName + ' ' + LastName AS FullName
FROM Users;

MySQL

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

⚠️ Gotcha

MySQL's + operator exists — but it means numeric addition, not string concatenation. SELECT 'Full' + 'Name' in MySQL tries to convert both strings to numbers (usually landing on 0) instead of throwing an error or concatenating them. This is one of the quietest bugs a T-SQL developer can ship: it runs, it just returns nonsense. Always use CONCAT() (or CONCAT_WS() for a separator-aware version) in MySQL.

🔧 Try It Yourself

Take a T-SQL CREATE TABLE statement you've written before — ideally one with a name column, a flag column, and a timestamp column — and rewrite it for MySQL: backticks instead of brackets, VARCHAR(...) CHARACTER SET utf8mb4 instead of NVARCHAR, TINYINT(1) instead of BIT, and DATETIME instead of DATETIME2. Then rewrite one SELECT that concatenates two columns, using CONCAT() instead of +.

🙋 There Are No Dumb Questions

Q: If I forget the charset and just write VARCHAR(100) in MySQL, what happens?
A: It still works — MySQL falls back to the table's (or database's) default character set. The risk isn't a broken column, it's an inconsistent one: if your database's default charset is the older 3-byte utf8 (a common legacy default) instead of utf8mb4, you can hit "Incorrect string value" errors the moment someone inserts an emoji or certain Asian-language characters. Being explicit about utf8mb4 on columns that store user input avoids that surprise later.

✅ Quick Check

A T-SQL developer writes SELECT 'Hello' + ' World' against a MySQL database. What happens?

Next up: once the tables exist, the next friction point is how you query them — TOP, OFFSET-FETCH, and a few string/NULL-handling functions all change names or shape.

← Back to