Query the SQL analytics endpoint or Warehouse in Microsoft Fabric

Applies to: SQL analytics endpoint, Warehouse, and Mirrored Database in Microsoft Fabric

To get started with this tutorial, check the following prerequisites:

• You should have access to a SQL analytics endpoint or Warehouse within a Premium capacity workspace with contributor or higher permissions.

• Choose your querying tool.

  • Use the SQL query editor in the Microsoft Fabric portal.
  • Use the Visual query editor in the Microsoft Fabric portal.

• Alternatively, you can use any of these tools to connect to your SQL analytics endpoint or Warehouse via a T-SQL connection string. For more information, see Connectivity.

  • Download SQL Server Management Studio (SSMS).
  • Download Azure Data Studio.

Note

Review the T-SQL surface area for SQL analytics endpoint or Warehouse in Microsoft Fabric.

Run a new query in SQL query editor

1. Open a New SQL query window.

   

2. A new tab appears for you to write a SQL query.

   

3. Write a SQL query and run it.

   

Run a new query in Visual query editor

1. Open a New visual query window.

   

2. A new tab appears for you to create a visual query.

   

3. Drag and drop tables from the object Explorer to Visual query editor window to create a query.

   

Write a cross-database query

You can write cross database queries to databases in the current active workspace in Microsoft Fabric.

There are several ways you can write cross-database queries within the same Microsoft Fabric workspace, in this section we explore examples. You can join tables or views to run cross-warehouse queries within current active workspace.

1. Add SQL analytics endpoint or Warehouse from your current active workspace to object Explorer using + Warehouses action. When you select SQL analytics endpoint or Warehouse from the dialog, it gets added into the object Explorer for referencing when writing a SQL query or creating Visual query.

   

2. You can reference the table from added databases using three-part naming. In the following example, use the three-part name to refer to ContosoSalesTable in the added database ContosoLakehouse.

   SELECT * 
   FROM ContosoLakehouse.dbo.ContosoSalesTable AS Contoso
   INNER JOIN Affiliation
   ON Affiliation.AffiliationId = Contoso.RecordTypeID;
   

3. Using three-part naming to reference the databases/tables, you can join multiple databases.

   SELECT * 
   FROM ContosoLakehouse.dbo.ContosoSalesTable AS Contoso
   INNER JOIN My_lakehouse.dbo.Affiliation
   ON My_lakehouse.dbo.Affiliation.AffiliationId = Contoso.RecordTypeID;
   

4. For more efficient and longer queries, you can use aliases.

   SELECT * 
   FROM ContosoLakehouse.dbo.ContosoSalesTable AS Contoso
   INNER JOIN My_lakehouse.dbo.Affiliation as MyAffiliation
   ON MyAffiliation.AffiliationId = Contoso.RecordTypeID;
   

5. Using three-part naming to reference the database and tables, you can insert data from one database to another.

   INSERT INTO ContosoWarehouse.dbo.Affiliation
   SELECT * 
   FROM My_Lakehouse.dbo.Affiliation;
   

6. You can drag and drop tables from added databases to Visual query editor to create a cross-database query.

   

Select Top 100 Rows from the Explorer

1. After opening your warehouse from the workspace, expand your database, schema and tables folder in the object Explorer to see all tables listed.

2. Right-click on the table that you would like to query and select Select TOP 100 rows.

   

3. Once the script is automatically generated, select the Run button to run the script and see the results.

   

Note

At this time, there's limited T-SQL functionality. See T-SQL surface area for a list of T-SQL commands that are currently not available.

Next step

Create reports on data warehousing in Microsoft Fabric