cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Warehousing & Analytics
Engage in discussions on data warehousing, analytics, and BI solutions within the Databricks Community. Share insights, tips, and best practices for leveraging data for informed decision-making.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

SQL Differences When Using SSMS with Databricks Lakehouse Federation

Akshay_Petkar
New Contributor III

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?

2 REPLIES 2

szymon_dybczak
Contributor III

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: 

Slash_0-1724342883081.png

 



-werners-
Esteemed Contributor III

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.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group