SQL Server to Snowflake Migration: Collation, T-SQL and the Results That Change

Last updated: October 2026

SQL Server and Snowflake both speak a dialect of SQL that looks familiar to the other, which makes a migration seem simpler than it is. Most T-SQL converts automatically. The defects that reach production usually come from code that converted without a warning and now behaves differently, for example a join on email addresses that stops matching or a full name that comes back empty.

This guide covers the schema, the T-SQL, the SSIS packages, the initial data load and validation. Ongoing change capture from SQL Server is a separate topic, and our article on SQL Server CDC with Openflow and ADF covers it in detail.

Tooling: SnowConvert AI, including SSIS

SnowConvert AI is Snowflake's free conversion tool, and SQL Server is one of its most complete sources, with direct database connection, data migration, deployment and AI-assisted code conversion all supported. For a full project Snowflake recommends the Snowflake AIM Agent for Data Warehouses, which wraps conversion, deployment, data migration and validation in one guided workflow.

SSIS conversion is in preview and available to all accounts. Each SSIS Data Flow becomes a separate dbt project with staging, intermediate and mart models, run through EXECUTE DBT PROJECT, and the Control Flow becomes Snowflake Tasks, stored procedures and inline SQL. Eligible flows from a flat file into an OLE DB destination become a single COPY INTO with no dbt project at all. The limits matter for planning:

• Script Tasks and Script Components are not supported, so any C# logic in a package needs rewriting by hand.
• Only Success precedence constraints are fully supported, and OnError, OnWarning and package-level event handlers may not fire automatically.
• Several ForEach enumerators, the XML Source and raw file sources and destinations are not yet supported.

Each conversion produces issue codes: EWI for code that needs a manual fix, FDM for converted code whose behaviour may differ, and PRF for performance reviews. The sections below cover the FDM-type differences that cause most of the trouble.

Case sensitivity and collation

A SQL Server instance installed with US English settings defaults to the SQL_Latin1_General_CP1_CI_AS collation, under which 'abc' equals 'ABC'. With no collation specified, Snowflake compares strings by Unicode code point, which is case-sensitive. Joins, filters, GROUP BY and DISTINCT on text columns can therefore return different results after migration, even though every statement converted without an error.

-- SQL Server with a case-insensitive collation returns the row.
-- Snowflake's default comparison is case-sensitive and returns nothing.
SELECT * FROM customers WHERE email = 'Jane.Doe@Example.com';

-- Option 1: a case-insensitive collation, set when the column is created
CREATE TABLE customers (
    customer_id NUMBER(38, 0),
    email       VARCHAR(320) COLLATE 'en-ci'
);

-- Option 2: compare normalised values
SELECT * FROM customers WHERE LOWER(email) = LOWER('Jane.Doe@Example.com');

Snowflake supports case-insensitive collation per column, per expression, or as a default through the DEFAULT_DDL_COLLATION parameter. It has limits worth knowing before choosing it. Collation only works on strings of 64 MB or less, many built-in functions return an error on collated input, a column's collation cannot be changed with ALTER TABLE after creation, and collated comparisons can be slower and prune less effectively. For key columns used in joins, we usually prefer to normalise the case during the load and keep the default collation.

Identifiers follow a separate rule. Unquoted names are stored in upper case, while double-quoted names are stored exactly as written and are case-sensitive. The QUOTED_IDENTIFIERS_IGNORE_CASE parameter treats quoted names as upper case, but switching it on after objects exist can make mixed-case quoted objects unreachable, so Snowflake recommends deciding on identifier handling at the start of the project.

Data types

These are SnowConvert's documented mappings, which are a sensible default for hand-written DDL too.

ROWVERSION needs the most attention. In SQL Server it changes on every insert and update, and incremental extracts often use it as a watermark. In Snowflake it becomes an ordinary BINARY(8) column (SSC-FDM-TS0046), so any process that reads changes from the migrated tables needs a different watermark, such as a timestamp column or a stream. A VARCHAR declared without a length also defaults to 16,777,216 characters, even though the maximum is now 128 MB, so declare lengths explicitly for very large text columns.

T-SQL that converts and still behaves differently

CONCAT is the most common surprise. SQL Server's CONCAT converts NULL arguments to empty strings, while Snowflake's CONCAT, and the || operator, return NULL if any argument is NULL. Name and address columns built with CONCAT can come back empty after a direct conversion.

-- SQL Server: CONCAT turns NULL into '', so this returns 'Jane '
-- Snowflake: CONCAT returns NULL if any argument is NULL
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM people;

-- Snowflake: wrap nullable arguments to keep the SQL Server result
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name
FROM people;

Constraints behave differently too. On standard Snowflake tables, NOT NULL and CHECK constraints are enforced, but PRIMARY KEY, UNIQUE and FOREIGN KEY constraints are not. A load or procedure that relied on a duplicate-key error to reject repeated rows will insert the duplicates without an error, so de-duplicate in the load or use MERGE.

Since bundle 2024_01, new Snowflake identity columns and sequences default to NOORDER, which does not guarantee increasing values, and Snowflake never guarantees values without gaps. SnowConvert writes IDENTITY(seed, increment) ORDER explicitly, but hand-written DDL inherits NOORDER. SCOPE_IDENTITY and @@IDENTITY have no direct equivalent, and SnowConvert's documentation suggests taking the value from a sequence before the insert.

Snowflake's MERGE fails by default when one target row matches several source rows, so the source needs de-duplicating first. It has no OUTPUT clause, and its documentation does not include WHEN NOT MATCHED BY SOURCE, so a T-SQL MERGE that deletes target rows missing from the source needs a separate DELETE step.

TRY and CATCH blocks become an EXCEPTION section in Snowflake Scripting, where SQLERRM, SQLCODE and SQLSTATE describe the error. ERROR_MESSAGE() maps to SQLERRM, though the message text can differ, and @@ROWCOUNT maps to SQLROWCOUNT. Local temporary tables such as #staging become Snowflake temporary tables, and SnowConvert renames them with a T_ prefix.

-- T-SQL
BEGIN TRY
    INSERT INTO dbo.orders_archive
        SELECT * FROM dbo.orders WHERE order_date < '2020-01-01';
    DELETE FROM dbo.orders WHERE order_date < '2020-01-01';
END TRY
BEGIN CATCH
    INSERT INTO dbo.etl_errors (message) VALUES (ERROR_MESSAGE());
END CATCH;

-- Snowflake Scripting
DECLARE
    err_msg VARCHAR;
BEGIN
    INSERT INTO orders_archive
        SELECT * FROM orders WHERE order_date < '2020-01-01';
    DELETE FROM orders WHERE order_date < '2020-01-01';
EXCEPTION
    WHEN OTHER THEN
        err_msg := SQLERRM;
        INSERT INTO etl_errors (message) VALUES (:err_msg);
END;

Snowflake has no triggers (SSC-FDM-TS0035). Audit and notification triggers move to streams and triggered tasks, which run after the change has committed, and our guide to streams and tasks covers the pattern. Finally, week-based DATEDIFF results depend on Snowflake's WEEK_START parameter, so compare week calculations against the SQL Server output.

Loading the history

For the initial load, export each table to compressed files, stage them and load them with COPY INTO. Snowflake recommends files of roughly 100 to 250 MB compressed, advises against files of 100 GB or more, and cannot load in parallel across more files than exist. Fields containing the delimiter or line breaks must be quoted with embedded quotes escaped, numbers must not contain thousands separators, and invalid dates such as 0000-00-00 cause errors.

Azure Data Factory can do the copy with its Snowflake V2 connector, since the V1 connector has been removed. ADF copies directly into Snowflake only from Azure Blob storage in specific file formats, so a SQL Server source goes through staged copy, with ADF writing to interim Blob storage, running COPY INTO and then cleaning up. Use key-pair authentication for the Snowflake linked service, because Snowflake's 2026 MFA rollout blocks password sign-in for service users.

If the source must stay live until cutover, the Openflow Connector for SQL Server takes a snapshot of each table and then applies changes. It replicates only tables with primary keys, and the CDC article linked above compares it with the ADF options.

Validating before cutover

SnowConvert AI's data validation supports SQL Server and is run with the scai data validate commands or from the AIM agent. Results land in tables in the SNOWCONVERT_AI.DATA_VALIDATION schema, with a Streamlit dashboard for reviewing them. The checks range from schema and row counts, through column metrics such as sums, minimums and distinct counts, to row-level hash comparison.

Matching data does not prove that converted code behaves the same. We add tests aimed at the differences above: joins and filters on text keys with mixed-case values, CONCAT over nullable columns, duplicate rows that SQL Server would have rejected, and any logic that depends on identity order. Our Snowflake migration checklist covers the surrounding planning and sign-off.