Lesson 1 of 5

Syntax & Data Types

MySQL and SQL Server 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 backticks `like_this` for square brackets [like_this] when quoting identifiers, swap VARCHAR for NVARCHAR and stop worrying about charsets (but start prefixing Unicode string literals with N), swap DATETIME/ TIMESTAMP for DATETIME2, swap TINYINT(1) for a proper BIT type, and swap the CONCAT() function for the + string operator. Get those five swaps automatic and most MySQL scripts port over with only minor friction.

Quoting identifiers

MySQL (what you know)

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

SQL Server (the new equivalent)

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

📋 Key differences

MySQL quotes identifiers with backticks `Order Date`; SQL Server uses square brackets [Order Date] (double quotes also work in SQL Server as long as QUOTED_IDENTIFIER is on, which it is by default — but brackets are the idiomatic T-SQL choice and work regardless of that setting).
SQL Server table names are almost always schema-qualified, e.g. [dbo].[Orders] — every table lives inside a schema (dbo by default) inside a database, a third namespace level MySQL doesn't have. MySQL treats each database as its own flat namespace with no schema layer in between.
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 MySQL script into SQL Server Management Studio with the backticks left in place doesn't throw a helpful "wrong quote character" error — T-SQL just doesn't recognize `User` as an identifier at all and throws a syntax error pointing at the backtick itself. If you see a syntax error right at a stray `, that's almost always this.

String types: VARCHAR vs NVARCHAR (and the N'' prefix)

MySQL (what you know)

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

SQL Server (the new equivalent)

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

MySQL doesn't split string types at the type level — VARCHAR stores whatever character set the column (or table, or database) is configured with, and you get full Unicode support by setting the charset to utf8mb4. SQL Server takes the opposite approach: it splits string types into VARCHAR (single-byte, non-Unicode, tied to a collation's code page) and NVARCHAR (UCS-2/UTF-16 Unicode, always safe for any language or emoji). The practical translation: a MySQL VARCHAR(100) CHARACTER SET utf8mb4 column becomes NVARCHAR(100), full stop — no charset clause needed, because Unicode-ness is baked into the type itself rather than configured separately.

⚠️ Gotcha

MySQL string literals are just 'plain text', no matter what's inside them. In SQL Server, a string literal assigned to (or compared against) an NVARCHAR column should be prefixed with a capital N: N'plain text'. Without it, SQL Server treats the literal as the non-Unicode VARCHAR type and implicitly converts it — which usually works for plain ASCII text but silently mangles any character outside the current collation's code page (accented letters, non-Latin scripts, emoji) into question marks or best-fit substitutes. It won't error; it'll just quietly corrupt the data you inserted.

Date and time: DATETIME/TIMESTAMP vs DATETIME2

MySQL (what you know)

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

SQL Server (the new equivalent)

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

📋 Key differences

MySQL's DATETIME with an explicit fractional-second precision (e.g. DATETIME(3) for milliseconds, up to 6 digits) is the type most MySQL developers reach for; SQL Server's closest match is DATETIME2, its modern, high-precision date/time type (up to 100ns precision, i.e. 7 fractional digits).
Avoid SQL Server's older, legacy DATETIME type even though the name looks familiar — it rounds to the nearest 1/300th of a second (an odd, non-decimal precision left over from an old SQL Server internal format) and has a smaller date range than DATETIME2. Always reach for DATETIME2 on new tables.
MySQL's TIMESTAMP type (UTC-converting, 1970–2038 range) has no direct SQL Server equivalent — the nearest concept is DATETIMEOFFSET, which stores an explicit UTC offset alongside the value instead of silently converting based on session time zone.

Booleans: TINYINT(1) vs BIT

MySQL (what you know)

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

SQL Server (the new equivalent)

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

MySQL has no true boolean type — BOOLEAN/BOOL are just aliases for TINYINT(1), a one-byte integer that behaves like a boolean in practice but doesn't stop anyone from inserting 5 into it. SQL Server has a dedicated BIT type that stores exactly 0, 1, or NULL and is treated as a true boolean by tools and drivers — no accidental "5 is truthy" values are possible, because BIT rejects anything else at the type level.

Concatenating strings: CONCAT() vs +

MySQL (what you know)

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

SQL Server (the new equivalent)

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

⚠️ Gotcha

SQL Server's + operator does double duty as both numeric addition and string concatenation — T-SQL figures out which one you mean from the operand types. That's convenient until NULL gets involved: 'Hello' + NULL returns NULL, not 'Hello', silently erasing the whole expression. MySQL developers used to CONCAT() quietly ignoring NULL arguments (or, in CONCAT_WS()'s case, skipping them) will find T-SQL's all-or-nothing NULL propagation the more surprising behavior here — wrap nullable columns in ISNULL(col, '') before concatenating with +, or use SQL Server's own CONCAT() function, which does ignore NULLs the way MySQL's does.

🔧 Try It Yourself

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

🙋 There Are No Dumb Questions

Q: If I forget the N prefix and just write 'some text' against an NVARCHAR column in SQL Server, what happens?
A: It still runs — SQL Server implicitly converts the plain string literal to Unicode. The risk isn't a broken query, it's silent data loss: if the literal contains a character outside the database's default (non-Unicode) collation's code page — an emoji, an accented letter outside that code page, non-Latin script — that character gets replaced with ? or a best-fit substitute during the implicit conversion, before it ever reaches the Unicode column. Being explicit about N'' on any literal that might contain non-ASCII text avoids that surprise later.

✅ Quick Check

A MySQL developer writes SELECT FirstName + LastName FROM Users against SQL Server, where LastName is NULL for one row. What happens for that row?

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

← Back to