Edit

Share via

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

  • Article

Applies to: ✅ Warehouse in Microsoft Fabric

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. This article provides a step-by-step guide on how to create a warehouse with case-insensitive collation through the REST API. It also explains how to use Visual Studio Code with the REST Client extension to facilitate the process.

Important

Once a warehouse is created, the collation setting cannot be changed. Carefully consider your needs before initiating the creation process.

Prerequisites

API endpoint

To create a warehouse with REST API, use the API endpoint: POST https://api.fabric.microsoft.com/v1/workspaces/<workspace-id>/items

Here's a sample JSON request body for creating a warehouse:

{ 
  "type": "Warehouse", 
  "displayName": "CaseInsensitiveAPIDemo", 
  "description": "New warehouse with case-insensitive collation", 
  "creationPayload": { 
    "defaultCollation": "Latin1_General_100_CI_AS_KS_WS_SC_UTF8" 
  } 
}

Use Visual Studio Code to invoke the REST API

You can easily create a new warehouse with case-insensitive collation using Visual Studio Code (VS Code) and the REST Client extension. Follow these steps:

  1. Create a new text file in VS Code with the .http extension.

  2. Input the request details in the file body. Note that there should be a blank space between the header and the body, placed after the "Authorization" line.

      POST https://api.fabric.microsoft.com/v1/workspaces/<workspaceID>/items HTTP/1.1
  Content-Type: application/json
  Authorization: Bearer <bearer token>

{ 
   "type": "Warehouse", 
   "displayName": "<Warehouse name here>", 
   "description": "<Warehouse description here>", 
   "creationPayload": { 
     "defaultCollation": "Latin1_General_100_CI_AS_KS_WS_SC_UTF8" 
   } 
 }

  3. Replace the placeholder values:

    • <workspaceID>: Find the workspace GUID in the URL after the /groups/ section, or by running SELECT @@SERVERNAME in an existing warehouse.

    • <bearer token>: Obtain this by following these steps:

      1. Open your Microsoft Fabric workspace in a browser (Microsoft Edge or Google Chrome).
      2. Press F12 to open Developer Tools.
      3. Select the Console tab. If necessary, select Expand Quick View to reveal the console prompt >.
      4. Type the command copy(powerBIAccessToken) and press Enter. While the console responds undefined, the bearer token will be copied to your clipboard.
      5. Paste it in place of <bearer token>.

      Note

      In some scenarios, copy(powerBIAccessToken) may fail with the error uncaught TypeError: copy is not a function if there is any element id named "copy" in the page. In such cases, manually remove the element id using the following command before using the "copy" function: document.querySelector('#copy').remove().

    • <Warehouse name here>: Enter the desired warehouse name.

    • <Warehouse description here>: Enter the desired warehouse description.

  4. Select the Send Request link displayed over your POST command in the VS Code editor.

  5. You should receive a response with the status code 202 Accepted, along with additional details about your POST request.

  6. Go to the newly created warehouse in the Fabric portal.

  7. Execute the following T-SQL statement in the Query editor to confirm that the collation for your warehouse aligns with what you specified in the JSON above:

    SELECT name, collation_name FROM sys.databases;

Related content