🏭🗒️Microsoft Fabric: Result Set Caching in SQL Analytics Endpoints [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 21-Jun-2025

[Microsoft Fabric] Result Set Caching in SQL Analytics Endpoints

• {def} built-in performance optimization for Warehouse and Lakehouse that improves read latency [1]
• fully transparent to the user [3]
• persists the final result sets for applicable SELECT T-SQL queries
• caches all the data accessed by a query [3]
• subsequent runs that "hit" cache will process just the final result set
• can bypass complex compilation and data processing of the original query[1]
• ⇐ returns subsequent queries faster [1]
• the cache creation and reuse is applied opportunistically for queries
• works on
• warehouse tables
• shortcuts to OneLake sources
• shortcuts to non-Azure sources
• the management of cache is handled automatically [1]
• regularly evicts cache as needed
• as data changes, result consistency is ensured by invalidating cache created earlier [1]
• {operation} enable setting
• via ALTER DATABASE <database_name> SET RESULT_SET_CACHING ON
• {operation} validate setting
• via SELECT name, is_result_set_caching_on FROM sys.databases
• {operation} configure setting
• configurable at item level
• once enabled, it can then be disabled 
• at the item level
• for individual queries
• e.g. debugging or A/B testing a query
• via OPTION ( USE HINT ('DISABLE_RESULT_SET_CACHE') 
• {default} during the preview, result set caching is off for all items [1]
• [monitoring] 
• via Message Output
• applicable to Fabric Query editor, SSMS
• the statement "Result set cache was used" is displayed after query execution if the query was able to use an existing result set cache
• via queryinsights.exec_requests_history system view
• result_cache_hit displays indicates result set cache usage for each query execution [1]
• {value} 2: the query used result set cache (cache hit)
• {value} 1: the query created result set cache
• {value} 0: the query wasn't applicable for result set cache creation or usage [1]
• {reason} the cache no longer exists
• {reason} the cache was invalidated by a data change, disqualifying it for reuse [1]
• {reason} query isn't deterministic
• isn't eligible for cache creation [1]
• {reason} query isn't a SELECT statement
• [warehousing] 
• {scenario} analytical queries that process large amounts of data to produce a relatively small result [1]
• {scenario} workloads that trigger the same analytical queries repeatedly [1]
• the same heavy computation can be triggered multiple times, even though the final result remains the same [1]

Previous Post <<||>> Next Post

References:

[1] Microsoft Learn (2025) Result set caching (preview) [link]

[2] Microsoft Fabric Update Blog (2025) Result Set Caching for Microsoft Fabric Data Warehouse (Preview) [link|aka]

[3] Microsoft Learn (2025) In-memory and disk caching [link]

[4] Microsoft Learn (2025) Performance guidelines in Fabric Data Warehouse [link] 

Resources:
[R1] Microsoft Fabric (2025) Fabric Update - June 2025 [link]

Acronyms:

MF - Microsoft Fabric

SSMS - SQL Server Management Studio

🏭🗒️Microsoft Fabric: Security [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 25-May-2025

Microsoft Fabric Security [2]

[Microsoft Fabric] Security

• {def} a comprehensive security framework designed for the Microsoft Fabric platform [1]
• {goal} always on 
• every interaction with Fabric is encrypted by default and authenticated using Microsoft Entra ID [1]
• all communication between Fabric experiences travels through the Microsoft backbone internet [1]
• data at rest is automatically stored encrypted [1]
• support for extra security features [1]
• ⇐ allow to regulate access to Fabric [1]
• Private Links 
• enable secure connectivity to Fabric by 
• restricting access to the Fabric tenant from an Azure VPN
• blocking all public access
• ensures that only network traffic from that VNet is allowed to access Fabric features [1]
• used to provide secure access for data traffic in Fabric including specific workspaces [4]
• Azure Private Link and Azure Networking private endpoints sends traffic privately using Microsoft’s backbone network instead of using the public internet [4]
• Entra Conditional Access 
• the connection to data is protected by a firewall or a private network using trusted access [1]
• access firewall enabled ADL Gen2 accounts securely [1]
• can be limited to specific workspaces [1]
• workspaces that have a workspace identity can securely access ADL Gen 2 accounts with public network access enabled, from selected virtual networks and IP addresses [1]
• workspace identities can only be created in workspaces associated with a Fabric F SKU capacity [1]
• helps users connect to services quickly and easily from any device and any network [1]
• each request to connect to Fabric is authenticated with Microsoft Entra ID [1]
• allows users to safely connect to Fabric from their corporate office, when working at home, or from a remote location [1]
• {feature} Conditional Access
• allows to secure access to Fabric on every connection by
• defining a list of IPs for inbound connectivity to Fabric [1]
• using MFA [1]
• restricting traffic based on parameters such as country of origin or device type [1]
• conditional access policies
• implemented through Microsoft Entra
• restrict access based on user, group, network location, application, device, and risk detection [4]
• {goal} compliant
• data sovereignty provided out-of-box with multi geo capacities [1]
• support for a wide range of compliance standards [1]
• Fabric services follow the SDL)
• a set of strict security practices that support security assurance and compliance requirements [2]
• helps developers build more secure software by reducing the number and severity of vulnerabilities in software, while reducing development cost [2]
• {goal} governable
• leverages a set of governance tools
• data lineage
• information protection labels
• data loss prevention 
• Purview integration 
• configurable
•  in accordance with organizational policies [1]
• evolving 
• new features and controls are added regularly [1]
{feature} managed private endpoints 
• allow secure connections to data sources without exposing them to the public network or requiring complex network configurations [1]
• e.g. as Azure SQL databases
• secure and private access to data sources from certain Fabric workloads [4]
{feature} managed virtual networks
• virtual networks that are created and managed by Microsoft Fabric for each Fabric workspace [1]
• provide network isolation for Fabric Spark workloads
• the compute clusters are deployed in a dedicated network and are no longer part of the shared virtual network [1]
• enable network security features
• managed private endpoints
• private link support
{feature} data gateway
• allows to connect to on-premises data sources or a data source that might be protected by a firewall or a virtual network
• {option} On-premises data gateway
• acts as a bridge between on-premises data sources and Fabric 1[]
• installed on a server within the network [1]
• allows Fabric to connect to data sources through a secure channel without the need to open ports or make changes to the network [1]
• {option} Virtual network (VNet) data gateway
• allows to connect from Microsoft Cloud services to Azure data services within a VNet, without the need of an on-premises data gateway [1]
{feature} Azure service tags
• allows to ingest data from data sources deployed in an Azure virtual network without the use of data gateways [1]
• e.g. VMs, Azure SQL MI and REST APIs
• can be used to get traffic from a virtual network or an Azure firewall
• e.g. outbound traffic to Fabric so that a user on a VM can connect to Fabric SQL connection strings from SSMS, while blocked from accessing other public internet resources [1]
• minimize the complexity of updating network security rules using Azure service tags to group and manage IP addresses for a service [4]
{feature} IP allow-lists
• allows to enable an IP allow-list on organization's network to allow traffic to and from Fabric
• useful for data sources that don't support service tags [1]
• e.g. on-premises data sources
{feature} Telemetry
• used to maintain performance and reliability of the Fabric platform [2]
• the telemetry store is designed to be compliant with data and privacy regulations for customers in all regions where Fabric is available [2]
{feature} trusted workspace access
allows to ccess firewall-enabled ADLS Gen2 accounts in a secure manner from Fabric [4]
{process} authentication
• relies on Microsoft Entra ID to authenticate users (or service principals) [2]
• when authenticated, users receive access tokens from Microsoft Entra ID [2]
• used to perform operations in the context of the user [2]
• {feature} conditional access
• ensures that tenants are secure by enforcing multifactor authentication [2]
• allows only Microsoft Intune enrolled devices to access specific services [1] 
• restricts user locations and IP ranges.
{process} authorization
• all Fabric permissions are stored centrally by the metadata platform
• Fabric services query the metadata platform on demand to retrieve authorization information and to authorize and validate user requests [2]
• authorization information is sometimes encapsulated into signed tokens [2]
• only issued by the back-end capacity platform [1]
• include the access token, authorization information, and other metadata [1]
{concept} tenant metadata 
• information about the tenant 
• is stored in a metadata platform cluster to which the tenant is assigned
• located in a single region that meets the data residency requirements of that region's geography [2]
• include customer data 
• customers can control where their workspaces are located
• in the same geography as the metadata platform cluster
• by explicitly assigning workspaces on capacities in that region [2]
• by implicitly using Fabric Trial, Power BI Pro, or Power BI Premium Per User license mode [2]
• all customer data is stored and processed in this single geography [2]
• in Multi-Geo capacities located in geographies (geos) other than their home region [2]
• compute and storage is located in the multi-geo region [2]
• (including OneLake and experience-specific storage [2]
• {exception} the tenant metadata remains in the home region
• customer data will only be stored and processed in these two geographies [2]
{concept} data-at-rest
• all Fabric data stores are encrypted at rest [2]
• by using Microsoft-managed keys
• includes customer data as well as system data and metadata [2]
• ⇒ data is never persisted to permanent storage while in an unencrypted state [1]
• data can be processed in memory in an unencrypted state [2]
• {default} encrypted using platform managed keys (PMK)
• Microsoft is responsible for all aspects of key management [2]
• data-at-rest on OneLake is encrypted using its keys [3]
• {alternative} Customer-managed keys (CMK) 
• allow to encrypt data at-rest using customer keys [3]
• ⇒  customer assumes full control of the key [3]
• {recommendation} use cloud storage services with CMK encryption enabled and access data from Fabric using OneLake shortcuts [3]
• data continues to reside on a cloud storage service or an external storage solution where encryption at rest using CMK is enabled [3]
• customers can perform in-place read operations from Fabric whilst staying compliant [3] 
• shortcuts can be accessed by other Fabric experiences [3]
{concept} data-in-transit
• refers to traffic between Microsoft services routed over the Microsoft global network [2]
• inbound communication
• always encrypted with at least TLS 1.2. Fabric negotiates to TLS 1.3 whenever possible [2]
• inbound protection
•  concerned with how users sign in and have access to Fabric [3]
• outbound communication to customer-owned infrastructure 
• adheres to secure protocols [2]
• {exception} might fall back to older, insecure protocols when newer protocols aren't supported [2]
• incl. TLS 1
• outbound protection
• concerned with securely accessing data behind firewalls or private endpoints [3]
• OneLake security
• allows defining access permissions once [4]
• ⇐ Fabric enforces the permissions consistently across all engines
• security propagates automatically
• data owners can 
• create security roles
• refine permissions
• control access at the row and column levels to securely share data [4]
• workspace security
• managed by assigning users to workspace roles [4]
• item security
• grant access to an individual Fabric item without granting access to the entire workspace [4]
• data encryption
• encrypts data and metadata at-rest with Microsoft-managed keys [4]
• encrypts data in-transit with at least TLS 1.2 and TLS 1.3 when possible [4]
• customer lockbox
• allows to control how Microsoft engineers access data [4]
• [warehouse] dynamic data masking 
• prevents unauthorized viewing of sensitive data by specifying how much sensitive data to reveal, with minimal effect on the application layer [4]
• [warehouse] granular permissions
• standard SQL allows more granular control 
• [Purview] sensitivity labels
• same labels used in Microsoft 365 apps
• [Purview] Information Protection policies
• allows to automatically enforce access permissions to sensitive information [4]
• [Purview] Data Loss Prevention
• allows to automatically identify the upload of sensitive information to Fabric and trigger automatic risk remediation actions [4]
• [Purview] Data Security Posture Management
• allows to discover data risks with Copilot in Fabric and immediately take action [4]
• e.g. sensitive data in user prompts and responses 

Previous Post <<||>> Next Post 

References:

[1] Microsoft Learn (2024) Security in Microsoft Fabric [link]

[2] Microsoft Learn (2024) Microsoft Fabric security fundamentals [link]

[3] Microsoft Learn (2024) Microsoft Fabric end-to-end security scenario [link]

[4] (2025) Connect to your most sensitive data with end-to-end network security in Fabric [link] 

Resources:
[R1] Microsoft Learn (2024) Microsoft Fabric security [link]

[R2] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

Acronyms:

ADL - Azure Data Lake
API - Application Programming Interface
CMK - Customer-Managed Keys

MF - Microsoft Fabric
MFA - Multifactor Authentication 
MI - Managed Instance 
PMK - Platform-Managed Keys

REST - REpresentational State Transfer
SDL - Security Development Lifecycle

SKU - Stock Keeping Unit
TLS  - Transport Layer Security

VM - Virtual Machine
VNet - virtual network

VPN - Virtual Private Network

💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part XI: Database and Server Properties)

When taking over a SQL Server, respectively database, one of the first checks I do focuses on the overall configuration, going through the UI available for admins to see if I can find anything that requires further investigation. If no documentation is available on the same, I run a few scripts and export their output as baseline. 

Especially when documenting the configuration, it's useful to export the database options and properties defined at database level. Besides the collation and probably the recovery mode, typically the rest of the configuration is similar, though in exceptional cases one should expect also surprises that require further investigation! 

The following query retrieves in a consolidated way all the options and properties of a SQL database in Microsoft Fabric. 

-- database settings/properties 
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') Collation
--, DATABASEPROPERTYEX(DB_NAME(), 'ComparisonStyle')  ComparisonStyle
, DATABASEPROPERTYEX(DB_NAME(), 'Edition') Edition
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullDefault') IsAnsiNullDefault
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiNullsEnabled') IsAnsiNullsEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiPaddingEnabled') IsAnsiPaddingEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAnsiWarningsEnabled') IsAnsiWarningsEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsArithmeticAbortEnabled') IsArithmeticAbortEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoClose') IsAutoClose
, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatistics') IsAutoCreateStatistics
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoCreateStatisticsIncremental') IsAutoCreateStatisticsIncremental
--, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoShrink') IsAutoShrink
, DATABASEPROPERTYEX(DB_NAME(), 'IsAutoUpdateStatistics') IsAutoUpdateStatistics
--, DATABASEPROPERTYEX(DB_NAME(), 'IsClone') IsClone
--, DATABASEPROPERTYEX(DB_NAME(), 'IsCloseCursorsOnCommitEnabled') IsCloseCursorsOnCommitEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsDatabaseSuspendedForSnapshotBackup') IsDatabaseSuspendedForSnapshotBackup
, DATABASEPROPERTYEX(DB_NAME(), 'IsFulltextEnabled') IsFulltextEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsInStandBy') IsInStandBy
--, DATABASEPROPERTYEX(DB_NAME(), 'IsLocalCursorsDefault') IsLocalCursorsDefault
--, DATABASEPROPERTYEX(DB_NAME(), 'IsMemoryOptimizedElevateToSnapshotEnabled') IsMemoryOptimizedElevateToSnapshotEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsMergePublished') IsMergePublished
--, DATABASEPROPERTYEX(DB_NAME(), 'IsNullConcat') IsNullConcat
--, DATABASEPROPERTYEX(DB_NAME(), 'IsNumericRoundAbortEnabled') IsNumericRoundAbortEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsParameterizationForced') IsParameterizationForced
--, DATABASEPROPERTYEX(DB_NAME(), 'IsQuotedIdentifiersEnabled') IsQuotedIdentifiersEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsPublished') IsPublished
--, DATABASEPROPERTYEX(DB_NAME(), 'IsRecursiveTriggersEnabled') IsRecursiveTriggersEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsSubscribed') IsSubscribed
--, DATABASEPROPERTYEX(DB_NAME(), 'IsSyncWithBackup') IsSyncWithBackup
--, DATABASEPROPERTYEX(DB_NAME(), 'IsTornPageDetectionEnabled') IsTornPageDetectionEnabled
--, DATABASEPROPERTYEX(DB_NAME(), 'IsVerifiedClone') IsVerifiedClone
--, DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported') IsXTPSupported
, DATABASEPROPERTYEX(DB_NAME(), 'LastGoodCheckDbTime') LastGoodCheckDbTime
, DATABASEPROPERTYEX(DB_NAME(), 'LCID') LCID
--, DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') MaxSizeInBytes
, DATABASEPROPERTYEX(DB_NAME(), 'Recovery') Recovery
--, DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') ServiceObjective
--, DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjectiveId') ServiceObjectiveId
, DATABASEPROPERTYEX(DB_NAME(), 'SQLSortOrder') SQLSortOrder
, DATABASEPROPERTYEX(DB_NAME(), 'Status') Status
, DATABASEPROPERTYEX(DB_NAME(), 'Updateability') Updateability
, DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') UserAccess
, DATABASEPROPERTYEX(DB_NAME(), 'Version') Version
--, DATABASEPROPERTYEX(DB_NAME(), 'ReplicaID') ReplicaID

Output:

Collation

Edition

IsAutoCreateStatistics

IsAutoUpdateStatistics

IsFulltextEnabled

LastGoodCheckDbTime

LCID

Recovery

SQLSortOrder

Status

Updateability

UserAccess

Version

SQL_Latin1_General_CP1_CI_AS

FabricSQLDB

1

1

1

12/31/1899

1033

FULL

52

ONLINE

READ_WRITE

MULTI_USER

981

The query can be run also against the SQL analytics endpoints available for warehouses in Microsoft Fabric.

Output:

Collation

Edition

IsAutoCreateStatistics

IsAutoUpdateStatistics

IsFulltextEnabled

LastGoodCheckDbTime

LCID

Recovery

SQLSortOrder

Status

Updateability

UserAccess

Version

Latin1_General_100_BIN2_UTF8

DataWarehouse

1

1

1

12/31/1899

1033

SIMPLE

0

ONLINE

READ_WRITE

MULTI_USER

987

Respectively, for lakehouses:

Collation

Edition

IsAutoCreateStatistics

IsAutoUpdateStatistics

IsFulltextEnabled

LastGoodCheckDbTime

LCID

Recovery

SQLSortOrder

Status

Updateability

UserAccess

Version

Latin1_General_100_BIN2_UTF8

LakeWarehouse

1

1

1

12/31/1899

1033

SIMPLE

0

ONLINE

READ_WRITE

MULTI_USER

987

A similar output is obtained if one runs the query against SQL database's SQL analytics endpoint:

Output:

Collation

Edition

IsAutoCreateStatistics

IsAutoUpdateStatistics

IsFulltextEnabled

LastGoodCheckDbTime

LCID

Recovery

SQLSortOrder

Status

Updateability

UserAccess

Version

Latin1_General_100_BIN2_UTF8

LakeWarehouse

1

1

1

12/31/1899

1033

SIMPLE

0

ONLINE

READ_WRITE

MULTI_USER

987

SQL databases seem to inherit the collation from the earlier versions of SQL Server.

Another meaningful value for SQL databases is MaxSizeInBytes, which in my environment had a value of 3298534883328 bytes ÷ 1,073,741,824 = 3,072 GB.

There are however also server properties. Here's the consolidated overview:

-- server properties
SELECT --SERVERPROPERTY('BuildClrVersion') BuildClrVersion
 SERVERPROPERTY('Collation') Collation
--, SERVERPROPERTY('CollationID') CollationID
, SERVERPROPERTY('ComparisonStyle') ComparisonStyle
--, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ComputerNamePhysicalNetBIOS
, SERVERPROPERTY('Edition') Edition
--, SERVERPROPERTY('EditionID') EditionID
, SERVERPROPERTY('EngineEdition') EngineEdition
--, SERVERPROPERTY('FilestreamConfiguredLevel') FilestreamConfiguredLevel
--, SERVERPROPERTY('FilestreamEffectiveLevel') FilestreamEffectiveLevel
--, SERVERPROPERTY('FilestreamShareName') FilestreamShareName
--, SERVERPROPERTY('HadrManagerStatus') HadrManagerStatus
--, SERVERPROPERTY('InstanceDefaultBackupPath') InstanceDefaultBackupPath
, SERVERPROPERTY('InstanceDefaultDataPath') InstanceDefaultDataPath
--, SERVERPROPERTY('InstanceDefaultLogPath') InstanceDefaultLogPath
--, SERVERPROPERTY('InstanceName') InstanceName
, SERVERPROPERTY('IsAdvancedAnalyticsInstalled') IsAdvancedAnalyticsInstalled
--, SERVERPROPERTY('IsBigDataCluster') IsBigDataCluster
--, SERVERPROPERTY('IsClustered') IsClustered
, SERVERPROPERTY('IsExternalAuthenticationOnly') IsExternalAuthenticationOnly
, SERVERPROPERTY('IsExternalGovernanceEnabled') IsExternalGovernanceEnabled
, SERVERPROPERTY('IsFullTextInstalled') IsFullTextInstalled
--, SERVERPROPERTY('IsHadrEnabled') IsHadrEnabled
--, SERVERPROPERTY('IsIntegratedSecurityOnly') IsIntegratedSecurityOnly
--, SERVERPROPERTY('IsLocalDB') IsLocalDB
--, SERVERPROPERTY('IsPolyBaseInstalled') IsPolyBaseInstalled
--, SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') IsServerSuspendedForSnapshotBackup
--, SERVERPROPERTY('IsSingleUser') IsSingleUser
--, SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') IsTempDbMetadataMemoryOptimized
, SERVERPROPERTY('IsXTPSupported') IsXTPSupported
, SERVERPROPERTY('LCID') LCID
, SERVERPROPERTY('LicenseType') LicenseType
, SERVERPROPERTY('MachineName') MachineName
, SERVERPROPERTY('NumLicenses') NumLicenses
, SERVERPROPERTY('PathSeparator') PathSeparator
--, SERVERPROPERTY('ProcessID') ProcessID
, SERVERPROPERTY('ProductBuild') ProductBuild
--, SERVERPROPERTY('ProductBuildType') ProductBuildType
--, SERVERPROPERTY('ProductLevel') ProductLevel
--, SERVERPROPERTY('ProductMajorVersion') ProductMajorVersion
--, SERVERPROPERTY('ProductMinorVersion') ProductMinorVersion
--, SERVERPROPERTY('ProductUpdateLevel') ProductUpdateLevel
--, SERVERPROPERTY('ProductUpdateReference') ProductUpdateReference
--, SERVERPROPERTY('ProductUpdateType') ProductUpdateType
, SERVERPROPERTY('ProductVersion') ProductVersion
, SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime
, SERVERPROPERTY('ResourceVersion') ResourceVersion
, SERVERPROPERTY('ServerName') ServerName
, SERVERPROPERTY('SqlCharSet') SqlCharSet
, SERVERPROPERTY('SqlCharSetName') SqlCharSetName
, SERVERPROPERTY('SqlSortOrder') SqlSortOrder
, SERVERPROPERTY('SqlSortOrderName') SqlSortOrderName
, SERVERPROPERTY('SuspendedDatabaseCount') SuspendedDatabaseCount

Output (consolidated):

Property	SQL database	Warehouse	Lakehouse
Collation	SQL_Latin1_General_CP1_CI_AS	SQL_Latin1_General_CP1_CI_AS	SQL_Latin1_General_CP1_CI_AS
ComparisonStyle	196609	196609	196609
Edition	SQL Azure	SQL Azure	SQL Azure
EngineEdition	12	11	11
InstanceDefaultDataPath	NULL	NULL	NULL
IsAdvancedAnalyticsInstalled	1	1	1
IsExternalAuthenticationOnly	1	0	0
IsExternalGovernanceEnabled	1	1	1
IsFullTextInstalled	1	0	0
IsXTPSupported	1	1	1
LCID	1033	1033	1033
LicenseType	DISABLED	DISABLED	DISABLED
MachineName	NULL	NULL	NULL
NumLicenses	NULL	NULL	NULL
PathSeparator	\	\	\
ProductBuild	2000	502	502
ProductVersion	12.0.2000.8	12.0.2000.8	12.0.2000.8
ResourceLastUpdateDateTime	11/6/2024 3:41:27 PM	3/5/2025 12:05:50 PM	3/5/2025 12:05:50 PM
ResourceVersion	16.00.5751	17.00.502	17.00.502
ServerName	...	....	....
SqlCharSet	1	1	1
SqlCharSetName	iso_1	iso_1	iso_1
SqlSortOrder	52	52	52
SqlSortOrderName	nocase_iso	nocase_iso	nocase_iso
SuspendedDatabaseCount	NULL	0	0

It's interesting that all three instances have the same general collation, while the Engine Edition of SQL databases is not compatible with the others [2]. The Server Names has been removed manually from the output from obvious reasons. The warehouse and lakehouse are in the same environment (SQL Azure instance, see sys.databases), and therefore the same values are shown (though this might happen independently of the environments used).

The queries were run in a trial Microsoft Fabric environment. Other environments can have upon case different properties. Just replace the "--" from the commented code to get a complete overview.

The queries should run also in the other editions of SQL Server. If DATABASEPROPERTYEX is not supported, one should try DATABASEPROPERTY instead.

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) SQL Server 2022: DATABASEPROPERTYEX (Transact-SQL) [link]
[2] Microsoft Learn (2024) SQL Server 2022: SERVERPROPERTY (Transact-SQL) [link]

🏭💠🗒️Microsoft Fabric: T-SQL Notebook [Notes] 🆕

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 25-Feb-2024

[Microsoft Fabric] T-SQL notebook

• {def} notebook that enables to write and run T-SQL code within a notebook [1]
• {feature} allows to manage complex queries and write better markdown documentation [1]
• {feature} allows the direct execution of T-SQL on
• connected warehouse
• SQL analytics endpoint
• ⇐ queries can be run directly on the connected endpoint [1]
• multiple connections are allowed [1]
• allows running cross-database queries to gather data from multiple warehouses and SQL analytics endpoints [1]
• the code is run by the primary warehouse
• used as default in commands which supports three-part naming, though no warehouse was provided [1]
• three-part naming consists of 
• database name
• the name of the warehouse or SQL analytics endpoint [1]
• schema name
• table name
• {feature} autogenerate T-SQL code using the code template from the object explorer's context [1] menu
• {concept} code cells
• allow to create and run T-SQL code
• each code cell is executed in a separate session [1]
• {limitation} the variables defined in one cell are not available in another cell [1]
• one can check the execution summary after the code is executed [1]
• cells can be run individually or together [1]
• one cell can contain multiple lines of code [1]
• users can select and run subparts of a cell’s code [1]
• {feature} Table tab
• lists the records from the returned result set
• if the execution contains multiple result set, you can switch from one to another via the dropdown menu [1]
• a query can be saved as 
• view
• via 'Save as' view
• {limitation} does not support three-part naming [1]
• the view is always created in the primary warehouse [1]
• by setting the warehouse as the primary warehouse [1]
• table
• via 'Save as' table
• saved as CTAS 
• ⇐ 'Save as' is only available for the selected query text
• the query text must be selected before using the Save as options
• {limitation} doesn’t support 
• parameter cell
• the parameter passed from pipeline or scheduler can't be used [1]
• {feature} Recent Run 
• {workaround} use the current data warehouse monitoring feature to check the execution history of the T-SQL notebook [1]
• {feature} the monitor URL inside the pipeline execution
• {feature} snapshot 
• {feature} Git support 
• {feature} deployment pipeline support 

Previous Post  <<||>> Next Post 

References:

[1] Microsoft Learn (2025) T-SQL support in Microsoft Fabric notebooks [link] 

[2] Microsoft Learn (2025) Create and run a SQL Server notebook [link] 

[3] Microsoft Learn (2025) T-SQL surface area in Microsoft Fabric [link] 

[4] Microsoft Fabric Updates Blog (2024) Announcing Public Preview of T-SQL Notebook in Fabric [link]

Resources:

[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]

Acronyms
CTAS - Create Table as Select
T-SQL - Transact SQL

🏭💠🗒️Microsoft Fabric: SQL Analytics Endpoint [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)! 

Last updated: 12-Feb-2024

[Microsoft Fabric] SQL Analytics Endpoint

• {def} a service that listens actively for requests providing a SQL-based experience for lakehouse Delta tables [1]
  • enables to query data in the lakehouse using T-SQL language and TDS protocol [1]
  • created by default for each lakehouses and mirrored databases provisioned in a workspace
  • every lakehouse has one SQL analytics endpoint [1]
  • manages the automatically generated tables so the workspace users can't modify them [1]
• {feature} a background process is responsible for 
• scanning lakehouse for changes [1]
• automatically generates a table in the appropriate schema for every Delta table in the Lakehouse
• tables are created with a minor delay [1]
• data is refreshed automatically
• the amount of time it takes to refresh the table is related to how optimized the Delta tables are [1]
• {alternative} one can manually force a refresh of the automatic metadata scanning in the Fabric portal [1]
• keeping SQL analytics endpoint up-to-date for all the changes committed to lakehouses in a workspace [1]
• {characteristic} transparently managed
• ⇐ the sync process is transparently managed by Microsoft Fabric platform [1]
• when a change is detected in the lakehouse
• a background process updates metadata [1]
• the SQL analytics endpoint reflects the changes committed to lakehouse tables [1]
• {characteristic} low latency
• under normal operating conditions, the lag between a lakehouse and SQL analytics endpoint is less than one minute [1]
• the actual length of time can vary from a few seconds to minutes [1]
• the metadata synchronization is automatically triggered when the SQL Endpoint is opened [3]
• ensures the SQL Analytics Endpoint remains up to date without the need for manual syncs [3]
•  ⇐ provides a seamless experience [3]
• {feature} can be enriched by adding database objects
• schemas
• views
• procedures
• other database objects
• {feature} automatic metadata discovery
• tracks changes committed to lakehouses [1]
• is a single instance per Fabric workspace [1]
• {issue} increased latency for changes to sync between lakehouses and SQL analytics endpoint, it could be due to large number of lakehouses in one workspace [1]
• {resolution} migrate each lakehouse to a separate workspace [1]
• allows automatic metadata discovery to scale [1]
• {issue} changes committed to a lakehouse are not visible in the associated SQL analytics endpoint
• create a new table in the  [1]
• many committed rows may not be visible in the endpoint [1]
• ⇐ may be impacted by ETL processing that generate large volumes of changes
• {recommendation} initiate an on-demand metadata sync, triggered from the SQL query editor Refresh ribbon option [1]
• forces an on-demand metadata sync, rather than waiting on the background metadata sync to finish [1]
• {issue} if there's no maintenance scheduled for the parquet files, this can result in read overhead and this impacts time it takes to sync changes to SQL analytics endpoint [1]
• {recommendation}schedule regular lakehouse table maintenance operations [1]
• {limitation} not all Delta features are understood by the automatic sync process [1]
• {feature} endpoint reprovisioning
• if the initial provisioning attempt fails, users have the option to try again [2]
• ⇐ without the need to create an entirely new lakehouse [2]
• {benefit} empowers users to self-mitigate provisioning issues in convenient way in the UI avoiding the need for complete lakehouse re-creation [2]

Previous Post  <<||>> Next Post

References:
[1] Microsoft Learn (2024) Microsoft Fabric: SQL analytics endpoint performance considerations [link]
[2] Microsoft Learn (2024) Microsoft Fabric: What is the SQL analytics endpoint for a lakehouse? [link]
[3] Microsoft Learn (2024) Microsoft Fabric: What’s new in the Fabric SQL Analytics Endpoint? [link]

Resources:

[R1] Microsoft Learn (2025) Fabric: What's new in Microsoft Fabric? [link]