In Fabric KQL database, we can copy the Query URI link and using it (without the https://) as the Server Name in SSMS and use Microsoft Entra MFA authentication to connect to the KQL database and query the data using T-SQL syntax.
Alternative, we can using the T-SQL syntax in the Fabric UI query tab but add the T-SQL comment line “–” as the first line of the query. That will indicate to the engine that the query syntax is T-SQL instead of KQL.
--
select top 10 * from bikes
KQL database is case-sensitive. For KQL syntax, it’s case-sensitive everwhere, including the table/column names, keywords, string value data etc. For SQL endpoint, table name and string value data are case-sensitive, but Keywords is not case-sensitive and column name doesn’t appear to be case-sensitive either. When SQL endpoint translates the T-SQL syntax to KQL syntax, it auto maps the keyword and column name to the right case. However, it’s recommended to stick with the case-sensitive behaviour for column name when writing T-SQL syntax.
The SQL endpoint is read-only.
To see how the engine translate the T-SQL syntax into KQL syntax, add the keyword explain in front of the query and run it. That will display the equivalent KQL syntax instead of actually execute the query.
--
explain select top 10 * from bikes
To workaround the case-sensitive data value, we can use tolower/toupper (KQL) or lower/upper (SQL) functions.
bikes
| where tolower(Neighbourhood) == "old ford"
| project street=Street
| take 10
--
SELECT TOP 10 street=Street FROM bikes
WHERE LOWER(Neighbourhood) = 'old ford'