Tools: 🚨 Why Your SQL Window Functions Betray You in Cloud SSMS vs Snowflake

🚨 Why Your SQL Window Functions Betray You in Cloud SSMS vs Snowflake
You run the same query. Same data. Same logic.
But your numbers don’t match.
Welcome to the sneaky world of window functions — where defaults quietly change your results between Microsoft SQL Server (Cloud SSMS) and Snowflake.
Let’s break down the drama. 🎭 The Silent Villain: Default Window Frames
Here’s a classic trap:
LAST_VALUE(amount) OVER ( PARTITION BY customer_id ORDER BY order_date
)
In Snowflake, the default frame is:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Translation? LAST_VALUE() returns the current row’s value, not the actual last value in the partition.
In SQL Server, you might assume it works differently — or you might get lucky depending on how you've tested it.
💥 Result: mismatched reports, confused stakeholders, late-night debugging.
Pro Tip: Always define your frame explicitly:
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
If it matters, don’t leave it to defaults. 🧨 NULLs: The Chaos Agents
Sorting rules differ:
Snowflake → NULL comes first in ascending order
SQL Server → NULL comes last Now imagine using:
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY bonus)
If bonus has NULLs?
Your row numbers shift.
Your rankings change.
Your dashboard breaks. 🎲 Ties = Non-Deterministic Madness
This one burns teams during migrations.
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary)
If two employees earn the same salary:
SQL Server might return one order.
Snowflake (distributed engine) might return another.
Re-run it? You might get a different result again. Because neither engine guarantees deterministic ordering unless you make it deterministic.
🔥 Add a tie-breaker:
ORDER BY salary, employee_id
Always. 🧬 Collation & Case Sensitivity
SQL Server respects database-level collation. Snowflake handles text comparisons differently.
If you're partitioning by strings, grouping may not match exactly after migration.
Subtle. Painful. Real. ☁️ Engine vs. Cloud Architecture
SQL Server executes in a traditional engine model.
Snowflake distributes computation across clusters.
Distributed systems expose sloppy ordering assumptions fast.
What “worked fine” before? Was probably relying on physical storage order.
Snowflake doesn’t care about your assumptions. 🛡️ The Migration Survival Checklist
If you want consistent results:
✅ Explicit ROWS BETWEEN ✅ Deterministic ORDER BY ✅ Explicit NULL handling ✅ Test ties ✅ Test edge cases ✅ Never trust defaults 🎯 Bottom Line
Window functions aren’t broken.
Your assumptions are.
When moving from SQL Server to Snowflake, make everything explicit.
Because in analytics…
“Almost the same result” is not the same result. Templates let you quickly answer FAQs or store snippets for re-use. Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse