topic SQL Differences When Using SSMS with Databricks Lakehouse Federation in Warehousing & Analytics

SQL Differences When Using SSMS with Databricks Lakehouse Federation

Akshay_Petkar — Thu, 22 Aug 2024 13:08:48 GMT

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?

Re: SQL Differences When Using SSMS with Databricks Lakehouse Federation

szymon_dybczak — Thu, 22 Aug 2024 16:13:47 GMT

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:

Re: SQL Differences When Using SSMS with Databricks Lakehouse Federation

-werners- — Fri, 23 Aug 2024 08:20:40 GMT

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.