What is the difference between "SQL Database" and "Lake Database" in Synapse

Question

What is the difference between "SQL Database" and "Lake Database" in Synapse. I was not able to find more info on this topic in Microsoft documentation. When should we use each of these?

Answer 1

Hello, the main I found is concerning the type of SQL pools used.

The Azure Synapse Analytics workspace lets you create two types of databases on top of a Spark data lake:

• Lake databases where you can define tables on top of lake data using Apache Spark notebooks, database templates, or Microsoft Dataverse (previously Common Data Service). These tables can be queried using T-SQL (Transact-SQL) language using the serverless SQL pool.
• SQL databases where you can define your own databases and tables directly using the serverless SQL pool**.** You can use T-SQL CREATE DATABASE, CREATE EXTERNAL TABLE to define the objects and add additional SQL views, procedures, and inline-table-value functions on top of the tables.

https://learn.microsoft.com/en-us/azure/synapse-analytics/metadata/database

Now, what is the difference between SQL pools.

The serverless SQL pool Control node utilizes Distributed Query Processing (DQP) engine.

The dedicated SQL pool Control node utilizes Massively Parallel Processing (MPP) engine.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/overview-architecture

Answer 2

I'm also looking for the difference, and find this post - https://www.evandata.com/blog/synapse-serverless-vs-lake-database