How to: Create a warehouse with case-insensitive (CI) collation.

4d ago in Technical

All Fabric warehouses by default are configured with case-sensitive (CS) collation Latin1_General_100_BIN2_UTF8. You can also create warehouses with case-insensitive (CI) collation - Latin1_General_100_CI_AS_KS_WS_SC_UTF8.
Currently, the only method available for creating a case-insensitive data warehouse is via REST API.
Run the code in the Warehouse to check the collation (SELECT name, collation_name FROM sys.databases)
Copy the Attached code.
Go to workspace, create a new item (notebook), and paste the code into the Notebook.
Change the Workspace name in the notebook script(e.g., "displayName": "TestFabricDW",) to the name of the data warehouse you want to make case insensitive.
Run The Notebook.
You should receive a response with the status code 202 Accepted. To confirm the Notebook has run succesfully.
Go back to workspace, select workspace, Select TestFabricDW Data Warehouse, Click on SQL Query And Run This code (SELECT name, collation_name FROM sys.databases;)
You can see that the collation has changed for TestFabricDW data Warehouse.
Run A Couple Of SQL Scripts to Confirm.
sql
CREATE TABLE Employee_Test (
EmpId INT,
EmpName VARCHAR(100),
EmpSalary INT,
EmpLocation VARCHAR(100)
);
SELECT * FROM Employee_Test;
SELECT * FROM employee_test;
SELECT * FROM emPLOyee_TEST;
NOTE: Once a Warehouse is created, the collation setting cannot be changed. Carefully consider your needs before initiating the creation Processes.