Motherson, a global manufacturing specialist industry and one of the world's leading automotive suppliers, faced significant challenges during their migration to Microsoft Fabric. They shared how the workspace collation settings transformed their experience:
"As part of our migration from Microsoft SQL Server to Microsoft Fabric, we hit a major roadblock with the default case-sensitive collation in Microsoft Fabric Data Warehouse T-SQL environment. Many of our legacy scripts failed due to case mismatches in table, column, and alias names, and refactoring the entire codebase was proving time-consuming and error-prone. Once we shared these challenges with the Microsoft Fabric team, they worked closely with us to enable case-insensitive (CI) collation in both Lakehouse and Warehouse and ability to manage collation settings at the workspace level. The change meant we could run most of our SQL Server scripts in Fabric Data Warehouse without significant rewrites, reducing friction in code conversion, validation, and deployment. Development is now faster and more efficient, letting our teams focus on delivering business value rather than fixing casing issues. This responsiveness from Microsoft has saved us valuable time and effort, and it’s been a critical enabler in keeping our migration on track."
— Motherson
This feedback underscores the power of workspace collation settings for Warehouses and SQL Endpoints, which now support both Case Sensitive (CS) and Case Insensitive (CI) collations. This blog delves into the recent change - updated collation configuration requirements at the workspace and item levels, offering insights into how these features enhance data workflows, migrations, and application development, with a look at what’s next for item-level collation settings.
Workspace-Level Collation: Driving Consistency
Workspace-level collation settings enable organizations to standardize collation across all Warehouses and SQL Endpoints within a Microsoft Fabric workspace, ensuring seamless integration and predictable behavior.
- Default Settings
- New and Existing Workspaces: Default to Case Sensitive (CS) collation, aligning with current default settings
- Backward Compatibility: Existing artifacts in older workspaces retain their original collation (often CS) to avoid disruptions.
- User Control
- Admins, Members, and Contributors: Can modify workspace collation settings at any time via the workspace settings panel. Changes apply only to new artifacts.
- Viewers: Can view collation settings but cannot edit them.
- Override Option: Users can override the workspace default during the creation of individual Warehouses or SQL Endpoints for specific use cases.
- SQL Endpoint Behavior
- SQL Endpoints, often tied to Lakehouse, Mirrored Database, or SQL Database Fabric artifacts, inherit the workspace collation unless a user specifies otherwise.
- For SQL Databases in existing workspaces, CI collation is applied until SQL Database supports independent collation settings.
Note: Changing the workspace collation does not affect existing artifacts but may influence query performance across Warehouses and SQL Endpoints.
What’s Next?
Enhancing Item-Level Collation for Tailored Flexibility
As part of ongoing improvements, Microsoft Fabric is set to expand item-level collation settings to offer even greater control and adaptability for specific use cases. These enhancements will empower users to fine-tune collation for individual Warehouses and SQL Endpoints, ensuring compatibility with diverse data sources and downstream applications.
Here’s what’s coming
- Flexible Creation-Time Options
- Users will continue to have the ability to select CI or CS collation during the creation of Warehouses or SQL Endpoints (e.g., for Lake houses or Mirrored Databases).
- Enhanced visual indicators in the creation interface will provide clearer feedback when overriding workspace defaults, improving user experience.
- Restoration and Snapshots
- Restored Warehouses will maintain their original collation, independent of workspace defaults.
- Snapshots will continue to inherit the Warehouse’s collation as well.
- Shortcut Enhancements
- Shortcuts in Lakehouse's will consistently adopt the SQL Endpoint’s collation, with future updates focusing on improved visibility and management of collation settings for shortcuts, ensuring alignment with complex data pipelines.
These planned enhancements aim to make item-level collation settings more intuitive and powerful, enabling users to tailor configurations to specific scenarios while maintaining alignment with workspace-level standards.
For more information about warehouse collations, refer to the documentation.
Conclusion
Microsoft Fabric’s enhanced collation settings for Warehouses and SQL Endpoints provide a robust framework for managing data consistency and flexibility. With workspace-level defaults, item-level overrides, and upcoming enhancements to item-level collation settings, users can streamline migrations, optimize greenfield applications, and simplify complex data workflows. The default CS collation aligns with current defaults, while planned updates will offer even greater control for tailored use cases.