The tables move, but familiar SQL starts failing in surprising places. When migrating from MySQL, translate the behavior behind each expression before translating its spelling.
Migrating From MySQL AUTO_INCREMENT to IDENTITY
The source definition OrderID INT AUTO_INCREMENT PRIMARY KEY becomes an integer identity column and a separate key constraint. SQL Server generates the value during insertion. The key constraint enforces uniqueness. Identity alone does not promise that, and neither system promises a gapless sequence.
I inventory generated keys before moving any parent and child tables. Preserve existing identifiers when relationships depend on them. SQL Server supports IDENTITY_INSERT for that controlled load. Only one table per session can have it enabled. Keep imported keys separate from newly generated ones, and verify the next generated value afterward.
Use a scratch database for this target example. Its temporary tables stay in your current session. The seed and increment are explicit. Match the integer range to the source, especially when unsigned values exceed the signed target's capacity.
CREATE TABLE #Orders
(
OrderID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
Amount decimal(12,2) NOT NULL
);
INSERT #Orders (CustomerID, Amount)
VALUES (10, 25.00), (10, 40.00), (20, 15.00);
SELECT OrderID, CustomerID, Amount
FROM #Orders
ORDER BY OrderID;LIMIT Needs a Stable Order
The source SELECT OrderID FROM Orders ORDER BY OrderID LIMIT 2 becomes SELECT TOP (2). The source LIMIT 2 OFFSET 1 becomes OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY. Both target forms need an order that reflects the requested result.
An offset without a unique ordering key invites duplicate or missing entries across pages. Concurrent changes still affect pages even with a unique key. Decide whether the application needs a fixed snapshot or accepts a changing list. Also compare deep pages, since skipping rows requires work.
SELECT TOP (2) OrderID, Amount
FROM #Orders
ORDER BY OrderID;
SELECT OrderID, Amount
FROM #Orders
ORDER BY OrderID
OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;Identifier Quotes and Boolean Values
Backtick quoting around the source table name becomes [Orders] in T-SQL. Quoted names protect identifiers, not string values. Text constants still use single quotes. Avoid moving unusual naming habits into every new procedure when simpler names work.
A source TINYINT(1) does not restrict stored values to zero and one. The parenthesized width is not a boolean constraint. SQL Server bit does restrict its stored values, while conversion of a nonzero numeric value produces one. That can hide dirty source values.
For example, source SELECT IFNULL(IsActive, 0) becomes an explicit null replacement before conversion. Validate the raw values first. The following staging query exposes values outside the accepted set rather than silently treating every nonzero value as true.
DECLARE @Flags table (SourceID int, RawFlag int NULL);
INSERT @Flags VALUES (1, 0), (2, 1), (3, 2), (4, NULL);
SELECT SourceID, RawFlag
FROM @Flags
WHERE RawFlag NOT IN (0, 1);
SELECT SourceID, CONVERT(bit, COALESCE(RawFlag, 0)) AS CleanFlag
FROM @Flags
WHERE RawFlag IN (0, 1) OR RawFlag IS NULL;Dates Require a Time-Zone Decision
Source DATETIME describes a date and clock time without automatic time-zone conversion. Source TIMESTAMP participates in session time-zone conversion around stored UTC values. SQL Server datetime2 stores a date and time without a time-zone offset. Its timestamp type is rowversion, which contains no date at all.
Translate CreatedAt DATETIME(6) to CreatedAt datetime2(6) when preserving that wall-clock meaning. For an exported UTC timestamp, use a clearly named datetime2 column with an agreed UTC contract. Use datetimeoffset when the stored offset matters. Do not choose a destination type from matching names alone.
A source automatic update timestamp also needs explicit target behavior. A SQL Server default supplies an insert value, not an update value. Put subsequent timestamp changes in the approved write path. During export, fix the source session time zone and reject invalid dates instead of quietly normalizing them.
ENUM Becomes a Visible Rule
A source declaration Status ENUM('New','Paid','Void') becomes a character column with a CHECK constraint. For a larger, maintained list, use a lookup table and foreign key. Preserve business values rather than the source enum's internal numeric positions.
Check both nullability and collation. A CHECK expression alone does not reject NULL. Case-insensitive comparison also treats some different spellings as equal. If exact spelling matters, define that requirement explicitly. A short list is a rule, not a storage trick.
CREATE TABLE #StatusExample
(
ExampleID int NOT NULL PRIMARY KEY,
Status varchar(8) NOT NULL
CHECK (Status COLLATE Latin1_General_100_BIN2
IN ('New', 'Paid', 'Void')),
CreatedUtc datetime2(6) NOT NULL DEFAULT SYSUTCDATETIME()
);
INSERT #StatusExample (ExampleID, Status) VALUES (1, 'New');
SELECT ExampleID, Status, CreatedUtc FROM #StatusExample;Null Replacement and Joined Strings
Source IFNULL(DisplayName, 'Unknown') translates to ISNULL or COALESCE. Those target expressions differ in type selection and metadata. ISNULL generally uses the first argument's type. COALESCE follows type precedence across its arguments. Cast to the intended result type when lengths differ.
Source GROUP_CONCAT(OrderID ORDER BY OrderID SEPARATOR ',') becomes STRING_AGG with WITHIN GROUP. Source GROUP_CONCAT can also request DISTINCT directly. Deduplicate in a target subquery first when that behavior is required. Check each engine's output length and ordering rules.
SELECT ISNULL(CAST(NULL AS varchar(20)), 'Unknown') AS DisplayName;
SELECT CustomerID,
STRING_AGG(CONVERT(varchar(max), OrderID), ',')
WITHIN GROUP (ORDER BY OrderID) AS OrderList
FROM #Orders
GROUP BY CustomerID;Upserts After Migrating From MySQL Need Locking
Source INSERT ... ON DUPLICATE KEY UPDATE reacts to a conflicting unique key. An UPDATE followed by INSERT needs a transaction and suitable key-range protection. Decide which unique key identifies the row. Do not assume every uniqueness conflict represents the same customer.
The target example creates a small keyed table. Run the whole block together. Its lookup locks protect a missing key while the transaction decides whether to insert. Retain the unique constraint as the final guard. MERGE is another target syntax, but it still requires careful concurrency and match rules.
CREATE TABLE #CustomerTotals
(
CustomerID int NOT NULL PRIMARY KEY,
TotalAmount decimal(12,2) NOT NULL
);
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE #CustomerTotals WITH (UPDLOCK, HOLDLOCK)
SET TotalAmount = 80.00
WHERE CustomerID = 10;
IF @@ROWCOUNT = 0
INSERT #CustomerTotals VALUES (10, 80.00);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
SELECT CustomerID, TotalAmount FROM #CustomerTotals;Collation Tests When Migrating From MySQL
When migrating from MySQL, compare the actual source and target collations. Names with similar wording do not guarantee identical comparison rules. Test case, accents, trailing spaces, sorting, and unique keys with representative business values.
I check collision candidates before creating target unique indexes. Do your customer codes distinguish upper and lower case? That answer belongs in the migration contract. A successful import is a very quiet way to miss a semantic error.
For a source comparison such as WHERE CustomerCode = 'ABC', keep the predicate but choose its target collation deliberately. Compare these expressions before selecting the target column collation. The first requests case-insensitive equality, while the second requests binary equality. Apply the chosen rule at column definition time for routine queries. Scattered comparison overrides make indexing and maintenance harder. Also test imported keys that differ only in case before enforcing uniqueness.
SELECT
CASE WHEN 'abc' COLLATE Latin1_General_100_CI_AS = 'ABC'
THEN 1 ELSE 0 END AS CaseInsensitiveMatch,
CASE WHEN 'abc' COLLATE Latin1_General_100_BIN2 = 'ABC'
THEN 1 ELSE 0 END AS BinaryMatch;Finish with paired tests for these translations, including nulls and boundaries. Record the expected meaning, then compare both sides. That gives migrating from MySQL a clear acceptance standard beyond merely counting imported rows.
Related reading on this blog: Retrieve TOP 10 Rows Without Using TOP or LIMIT? Interview Question of the Week #247 and Find Missing Identity Values.
A migration is not a syntax substitution, it is a transfer of data meaning.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
