SQL Differences When Using SSMS with Databricks Lakehouse Federation

[Akshay_Petkar]

I'm planning to connect SQL Server Management Studio (SSMS) with Databricks using Lakehouse Federation. I understand that there are some differences in the SQL dialects between SSMS and Databricks SQL. For instance, in SSMS, we use TOP 10 to limit the number of rows, whereas in Databricks SQL, we use LIMIT 10.

Could someone clarify which query language will be used when querying Databricks from SSMS in this setup? Will SSMS automatically translate the queries, or do I need to adapt them for Databricks SQL manually?

[szymon_dybczak]

Hi @Akshay_Petkar ,

To be precise, you are going to connect SQL Server with Databricks using Lakehouse Federation 😉 SSMS is just a client tool you can use to query SQL Server database.
And answering your question, you are limited only to dialect supported by Spark SQL. So you cannot use TOP 10, which comes from T-SQL dialect, because you will get an error as below: 

[-werners-]

To add on this:
if you really have to use T-SQL (the MS dialect of SQL), you can define the SQL warehouse from databricks as a linked server on your SQL server.
As said: SSMS is merely a sql client, the SQL dialect to be used is defined by the database you are connecting to.