Familiar syntax can carry unfamiliar assumptions into a migration. MySQL habits around limits, generated keys, grouping, and case rules need an explicit SQL Server equivalent.
Replace LIMIT With a Declared Order
MySQL's LIMIT has no direct T-SQL spelling. Use TOP for a simple first group of rows, or ORDER BY with OFFSET and FETCH for pages. A page without a deterministic ORDER BY can repeat or skip rows as data changes. I add a unique tie breaker even when the visible sort column looks unique. For a search screen, keyset pagination can be better than a large OFFSET because the engine need not discard all earlier pages. Do not convert LIMIT 10 to TOP (10) and forget the order the application expected.
SELECT TOP (10) object_id, name
FROM sys.objects
ORDER BY name, object_id;
SELECT object_id, name
FROM sys.objects
ORDER BY name, object_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;Use IDENTITY for a Generated Key
AUTO_INCREMENT maps most directly to an IDENTITY column for a table-local generated key. Use SCOPE_IDENTITY() or an OUTPUT clause to get the row's generated value from the insert. Do not use @@IDENTITY when triggers can insert into other tables. I prefer OUTPUT for multirow inserts because it returns the keys associated with that statement. Remember that IDENTITY values can have gaps and are not a business sequence. An explicit key from an import requires a controlled IDENTITY_INSERT operation and careful review.
CREATE TABLE #NewItems (ItemID int IDENTITY(1,1) PRIMARY KEY, ItemName nvarchar(50) NOT NULL);
INSERT #NewItems(ItemName) OUTPUT inserted.ItemID VALUES (N'Widget');Quote Identifiers for SQL Server
Backticks are not T-SQL identifier delimiters. Square brackets work, and QUOTENAME safely wraps a dynamic identifier. Better still, choose simple names that need no quoting. I inspect generated SQL from application frameworks because a single backtick can turn a migration into a syntax error. Do not confuse quoting an identifier with parameterizing a value. QUOTENAME helps with table or column names; sp_executesql parameters are for data values.
SELECT [name], [object_id]
FROM sys.objects
WHERE [type] = 'U';
SELECT QUOTENAME(N'Order Details') AS safe_identifier;Test names that collide with reserved words, contain spaces, or differ only by case. A script that worked on one collation can fail on another. Avoid creating new objects whose names demand permanent quoting.
Make GROUP BY Explicit
Some MySQL configurations allow a SELECT to return a nonaggregated column that is not in GROUP BY, choosing an arbitrary value from each group. SQL Server requires every nonaggregated selected expression to be grouped or aggregated. That strictness is useful: it forces the query to define which value is wanted. Use MIN, MAX, or a windowed ROW_NUMBER with an explicit order when you need a representative row. I ask the report owner which one is correct instead of adding a random aggregate to satisfy the parser.
SELECT type, COUNT_BIG(*) AS object_count
FROM sys.objects
GROUP BY type
ORDER BY type;
Replace INSERT IGNORE With a Clear Rule
INSERT IGNORE can hide duplicate or other insertion problems. In SQL Server, define a unique constraint, then use a transactionally safe existence check for an insert-only-if-absent pattern. Under concurrency, use appropriate locking on the key range or handle a unique-key violation deliberately. A plain NOT EXISTS without a constraint is not enough to prevent two sessions from inserting the same key. I prefer the constraint as the final guard and a clear application response for duplicates.
CREATE TABLE #Codes (Code varchar(20) NOT NULL PRIMARY KEY);
INSERT #Codes(Code)
SELECT v.Code
FROM (VALUES ('A1')) AS v(Code)
WHERE NOT EXISTS
(SELECT 1 FROM #Codes WITH (UPDLOCK, HOLDLOCK) WHERE Code = v.Code);MySQL Habits Around Case and Collation
String equality and ordering depend on collation. SQL Server installations can be case-insensitive or case-sensitive, and databases or columns can override defaults. Do not assume the behavior from a MySQL deployment carries over. Test equality, uniqueness, and ORDER BY for realistic names and codes. I use an explicit collation only where the domain needs one, then document it. A case-sensitive product code and a case-insensitive customer search can coexist, but the choice should be deliberate.
SELECT CASE WHEN N'abc' COLLATE Latin1_General_100_CI_AS =
N'ABC' COLLATE Latin1_General_100_CI_AS THEN 1 ELSE 0 END AS case_insensitive_match,
CASE WHEN N'abc' COLLATE Latin1_General_100_CS_AS =
N'ABC' COLLATE Latin1_General_100_CS_AS THEN 1 ELSE 0 END AS case_sensitive_match;MySQL Habits in Errors and Transactions
Syntax is only the first migration gate. MySQL and SQL Server can differ in how statements treat invalid data, duplicate keys, implicit conversions, and transaction boundaries. I capture the old application's expected result and error behavior for each critical operation. A translated INSERT can compile and still return a different error to the caller. If the old code relied on INSERT IGNORE, decide whether a duplicate means "already done" or a genuine conflict. Use a unique constraint and handle that condition explicitly.
Review transaction isolation and retry logic around the changed statements. An existence check followed by an insert can race in either database unless protected. The SQL Server pattern needs a key-range lock or a unique constraint that catches the second writer. I test with two sessions rather than one migration script. Data correctness is the outcome, not familiar-looking syntax.
Use Representative Strings and Dates
Collation differences appear with mixed case, accents, trailing spaces, and Unicode values. Date handling changes with data types and implicit string parsing. I use typed date parameters or unambiguous ISO literals and test the actual application driver. A local test database with a different collation from production can hide the problem. Inspect column-level collations as well as the database default, especially after bulk import.
What does the user see when two names differ only by case? What does a unique index allow? Those answers belong in migration tests. I keep a small dataset that includes duplicates, NULLs, ties in sort order, and international text. Run every translated idiom against it and compare output to the approved behavior. A migration that passes compile checks but changes customer matching rules is not ready.
Generated SQL from an ORM deserves the same review as handwritten queries. Check whether it emits TOP, OFFSET, quoted identifiers, parameterized values, and deterministic ORDER BY under the SQL Server provider. I inspect a sample of actual commands after migration. A configuration flag can change emitted SQL without the application source showing any obvious difference. The parser has no nostalgia for backticks.
Test MySQL Habits Against the Application Contract
Run the translated queries against a representative test set with NULLs, duplicates, mixed case, and tied sort values. Compare rows and error behavior, not only syntax. I check data types and implicit conversions before performance tuning. What did the old application expect when a duplicate arrived or two rows shared the same sort value? The migration should answer those questions explicitly.
Keep the MySQL habits you changed in a review checklist for the next module. A compiler can catch backticks and LIMIT. Only realistic tests catch a case rule or arbitrary group value that silently changed meaning.
Related reading on this blog: Retrieving N Rows After Ordering Query With OFFSET and Common Mistakes to Avoid for DBAs Working with MySQL Databases.
A migration is not a syntax translation, it is a review of the behavior behind each statement.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
