💠🛠️🗒️SQL Server: Columnstore Indexes [Notes]

Disclaimer: This is work in progress intended to consolidate information from various sources. It considers only on-premise SQL Server, for other platforms please refer to the documentation.

Last updated: 15-Feb-2024

[SQL Server] columnstore indexes (CI)

• {def} a technology for storing, retrieving and managing data by using a columnar data format (aka columnstore) 
• store compressed data on a per-column rather than a per-row basis [5]
• {benefit} designed for analytics and data warehousing workloads
• data warehousing
• {scenario} store fact tables and large dimension tables
• ⇐ tend to require full table scans rather than table seeks
• analytics workloads
• {scenario} [SQL Server 2016 SP1] can be used for real-time analytics on operational databases
• ⇐ an updatable nonclustered columnstore index can be created on a rowstore table
• {benefit} performance increase 
• can achieve up to 100x better performance [4]
• offers an order of magnitude better performance than a rowstore index
• {feature} uses batch mode execution
• improves query performance typically by two to four times
• have high performance gains for analytic queries that scan large amounts of data, especially on large tables (>1 million rows) 
• {benefit} reduces significantly the data warehouse storage costs
• {feature} data compression
• ⇒ provides high compression rates, typically by 10 times
• ⇒ reduces total I/O from the physical media
• ⇐ queries often select only a few columns from a table
• minimizes or eliminates system I/O bottlenecks
• reduces significantly the memory footprint
• ⇒ query performance can improve 
• because SQL Server can perform more query and data operations in memory
• {benefit} built in memory
• ⇒ sufficient memory must be available 
• {benefit} part of the database engine
• no special hardware is needed
• {concept} columnstore 
• {def} data structure logically organized as a table with rows and columns, and physically stored in a column-wise data format
• stores values from the same domain which commonly have similar values
• when a query references a column, then only that column is fetched from disk [3]
• ⇐ the columns not requested are skipped 
• ⇒ they are not loaded into memory 
• when a query is executed, the rows must be reconstructed
• ⇒ row reconstruction takes some time and uses some CPU and memory resources [3]
• [SQL Server 2016] columnstore index on rowstore tables
• columnstore is updated when data changes in the rowstore table
• both indexes work against the same data
• {concept}rowstore
• {def} data that's logically organized as a table with rows and columns, and physically stored in a row-wise data format
• ⇐ the traditional way to store relational table data
• refers to a table where the underlying data storage format is either
• a heap
• a clustered index
• a memory-optimized table
• {concept} rowstore index
• performs best on queries that seek into the data, when searching for a particular value, or for queries on a small range of values
• ⇒ appropriate for transactional workloads 
• because they tend to require mostly table seeks instead of table scans
• {concept} rowgroup
• {def} a group of rows that are compressed into columnstore format at the same time
• {constraint} has a maximum number of rows per rowgroup, which is 1,048,576 =2^20 rows
• contains one column segment for every column in the table
• can have more than one delta rowgroup that form the deltastore
• e.g. when multiple threads create columnstore indexes using parallel execution plans [5]
• ⇐ each thread will work with its own subset of data, creating separate rowgroups [5]
• [partitions] each table partition has its own set of row groups [5]
• ⇐  too many partitions may prevent workloads from benefiting from a CCI [11]
• ⇐ data aren’t pushed into a compressed columnstore segment until the rowgroup limit is reached
• {event} rowgroup is compressed
• marked as read-only [16]
• a compressed rowgroup is considered as fragmented when either 
• row number < rowgroup limit but dictionary size reached the maximum
• nothing can be done to increase the number of rows [15]
• the trim_reason is other than DICTIONARY_SIZE
• it has nonzero deleted rows that exceeds a minimum threshold [15]
• {event} all data from rowgroup deleted 
• transitions from COMPRESSED into TOMBSTONE state
• later removed by the tuple-mover background process
• {event} rows in the columnstore indexes can be moved to different locations
• row-id in the nonclustered indexes aren’t updated 
• ⇐ the mappings between old and new row locations are stored in an internal structure (aka mapping index) 
• {event} rowgroup build
• all column data are combined on a per-row group basis, encoded and compressed [5]
• the rows within a row group can be rearranged if that helps to achieve a better compression rate [5]
• {feature} data compression
• the table is sliced into rowgroups, and each rowgroup is compresses in a column-wise manner
• the number of rows in the rowgroup must be 
• large enough to improve compression rates
• small enough to benefit from in-memory operations
• having too many small rowgroups decreases columnstore index’s quality
• uses its own compression mechanism 
• ⇒ row or page compression cannot be used on it [3]
• [SQL Server 2016] page compression has been removed
• ⇐ in some cases, page compression disallowed the creation of columnstore indexes with a very large number of columns [5]
• {feature} compression delay
• computed when a delta rowgroup is closed [7]
• keeps the ‘active’ rows in delta rowgroup and only transition these rows to compressed rowgroup after a specified delay [7]
• ⇐ reduces the overall maintenance overhead of NCCI [7]
• ⇒ leads to a larger number of delta rowgroups [7]
• {best practice} if the workload is primarily inserting data and querying it, the default COMPRESSION_DELAY of 0 is the recommended option [7]
• {best practice} [OLTP workload] if > 10% rows are marked deleted in recently compressed rowgroups, then consider a value that accommodates the behavior [7]
• via: create nonclustered columnstore index with (compression_delay= 150)
• {feature} data encoding
• all values in the data are replaced with 64-bit integers using one of two encoding algorithms
• {concept} dictionary encoding
• stores distinct values from the data in a separate structure (aka dictionary} 
• every value in a dictionary has a unique ID assigned [5]
• the ID is used for replacement
• {concept} global dictionary
• shared across all segments that belong to the same index partition [5]
• {concept} local dictionary
• created for individual segments using values that are not present in the global dictionary
• {concept} value-based encoding
• mainly used for numeric and integer data types that do not have enough duplicated values [5]
• dictionary encoding would be inefficient [5]
• converts integer and numeric values to a smaller range of 64-bit integers in 2 steps
• {step} [numeric data types] are converted to integers using the minimum positive exponent (aka magnitude that allows this conversion) [5]
• {goal} convert all numeric values to integers [5]
• [integer data types] the smallest negative exponent is chosen that can be applied to all values without losing their precision [5]
• {goal} reduce the interval between the minimum and maximum values stored in the segment [5]
• {step} the minimum value (aka base value) in the segment is identified and subtracted it from all other values [5]
• ⇒ makes the minimum value in the segment number 0 [5]
• after encoding the data are compressed and stored as a LOB allocation unit
• {concept} column segment 
• {def} a column of data from within the rowgroup
• is compressed together and stored on physical media
• SQL Server loads an entire segment to memory when it needs to access its data
• {concept} segment metadata 
• store metadata about each segment 
• e.g. minimum and maximum values
• ⇐ segments that do not have the required data are skipped [5]
• {concept} deltastore
• {def} all of the delta rowgroups of a columnstore index
• its operations are handled behind the scenes
• can be in either states
• {state} open (aka open delta store) 
• accepts new rows and allow modifications and deletions of data
• {state} closed (aka closed data store)
• a delta store is closed when it reaches its rowgroup limit
• {concept} delta rowgroup 
• {def} a clustered B-tree index that's used only with columnstore indexes
• improves columnstore compression and performance by storing rows until the number of rows reaches the rowgroup limit and are then moved into the columnstore
• {event} reaches the maximum number of rows
• it transitions from an ‘open’ to ‘closed’ state
• a closed rowgroup is compressed by the tuple-mover and stored into the columnstore as COMPRESSED rowgroup
• {event} compressed
• the existing delta rowgroup transitions into TOMBSTONE state to be removed later by the tuple-mover when there is no reference to it
• {concept} tuple-mover 
• background process that checks for closed row group
• if it finds a closed rowgroup, it compresses the delta rowgroup and stores it into the columnstore as a COMPRESSED rowgroup
• {concept} clustered columnstore index (CCI) 
• is the primary storage for the entire table
• {characteristic) updatable
• has two structures that support data modifications
• ⇐ both use the B-Tree format to store data [5]
• ⇐ created on demand [5]
• delete bitmap
• indicates which rows were deleted from a table
• upon deletion the row continues to be stored into the rowgroup
• during query execution SQL Server checks the delete bitmap and excludes deleted rows from the processing [5]
• delta store
• includes newly inserted rows
• updating a row triggers the deletion of the existing row and insertion of a new version of a row to a delta store
• ⇒ the update does not change the row data
• ⇒ the updated row is inserted to a delete bitmap
• [partitions] each partition can have a single delete bitmap and multiple delta stores
• ⇐ this makes each partition self-contained and independent from other partitions
• ⇒ allows performing a partition switch on tables that have clustered columnstore indexes defined [5]
• {feature} supports minimal logging for batch sizes >= rowgroup’s limit [12]
• [SQL Server 2017] supports non-persisted computed columns in clustered columnstore indexes [2]
• store some data temporarily into a clustered index (aka deltastore) and a btree list of IDs for deleted rows
• ⇐ {benefit} reduces fragmentation of the column segments and improves performance
• combines query results from both the columnstore and the deltastore to return the correct query results
• [partitions] too many partitions can hurt the performance of a clustered columnstore index [11]
• {concept} nonclustered columnstore index (NCCI)
• {def} a secondary index that's created on a rowstore table
• is defined as one or more columns of the table and has an optional condition that filters the rows
• designed to be used for workloads involving a mix of transactional and analytics workload*
• functions the same as a clustered columnstore index
• ⇐ has same performance optimizations (incl. batchmode operators)
• {exception} doesn’t supports persisted computed columns
• can’t be created on a columnstore index that has a computed column [2]
• however behave differently between the various versions of SQL Server
• [SQL Server 2012|2014] {restriction} readonly
• contains a copy of part or all of the rows and columns in the underlying table
• include a row-id , which is either the address of
• a row in a heap table 
• a clustered index key value
• includes all columns from the clustered index even when not explicitly defined in the CREATE statement
• the not specified columns will not be available in the sys.index_columns view
• [SQL Server 2016] multiple nonclustered rowstore indexes can be created on a columnstore index and perform efficient table seeks on the underlying columnstore
• ⇒ once created, makes it possible to drop one or more btree nonclustered indexes
• enables real-time operational analytics where the OLTP workload uses the underlying clustered index while analytics run concurrently on the columnstore index
• {concept} batch mode execution (aka vector-based execution, vectorized execution) 
• {def} query processing method used to process multiple rows together in groups of rows, or batches, rather than one row at a time
• SQL Server can push a predicate to the columnstore index scan operator, preventing unnecessary rows from being loaded into the batch [5]
• queries can process up to 900 rows together
• enables efficient query execution (by a 3-4x factor) [4]
• ⇐ the size of the batches varies to fit into the CPU cache
• ⇒ reduces the number of times that the CPU needs to request external data from memory or other components [5]
• improves the performance of aggregations, which can be calculated on a per-batch rather than a per-row basis [5]
• tries to minimize the copy of data between operators by creating and maintaining a special bitmap that indicates if a row is still valid in the batch [5]
• ⇐ subsequent operators will ignore the non-valid rows
• every operator has a queue of work items (batches) to process [5]
• worker threads from a shared pool pick items from queues and process them while migrating from operator to operator [5]
• is closely integrated with, and optimized around, the columnstore storage format.
• columnstore indexes use batch mode execution
• ⇐ improves query performance typically by two to four times
• {concept} tuple mover
• single-threaded process that works in the background, preserving system resources
• runs every five minutes
• converts closed delta stores to row groups that store data in a column-based storage format [5]
• can be disabled via trace flag T-634 
• ⇐ the conversion of closed delta stores to row groups can be forced by reorganizing an index [5]
• runs in parallel using multiple threads
• decreases significantly conversion time at a cost of extra CPU load and memory usage [5]
• via: ALTER INDEX REORGANIZE command
• it doesn’t prevent other sessions from inserting new data into a table [5]
• deletions and data modifications would be blocked for the duration of the operation [5]
• {recommendation} consider forcing index reorganization manually to reduce execution, and therefore locking, time [5]
• considered fragmented if it has
• multiple delta rowgroups
• deleted rows
• require maintenance like that of regular B-Tree indexes [5]
• {issue] partially populated row groups
• {issue} overhead of delta store and delete bitmap scans during query execution
• rebuilding the columnstore index addresses the issues
• the strategy depends on the volatility of the data and the ETL processes implemented in the system [5]
• {recommendation} rebuild indexes when a table has a considerable volme of deleted rows and/or a large number of partially populated rowgroups [5]
• {recommendation} rebuild partition(s) that still have a large number of rows in open delta stores after the ETL process has completed, especially if the ETL process does not use a bulk insert API [5]
• creating/dropping/disabling/rebuilding functions like any other index
• columnstore statistics 
• a statistics object is created at the time of columnstore index creation; however, it is neither populated nor updated afterward [5]
• ⇐ SQL Server relies on segment information, B-Tree indexes (when available), and column-level statistics when deciding if a columnstore index needs to be used [5]
• it is beneficial to create missing column-level statistics on the columns that participate in a columnstore index and are used in query predicates and as join keys [5]
• ⇐ statistics rarely update automatically on very large tables [5]
• ⇒ statistics must be updated ‘manually’
• [SQL Server 2019] included into the schema-only clone of a database functionality [8]
• enable performance troubleshooting without the need to manual capture the statistics information
• columnstore indexes has been added to sp_estimate_data_compression_savings. In SQL Server 2019 both 
• COLUMNSTORE and COLUMNSTORE_ARCHIVE have been added to allow you to estimate the space savings if 
• either of these indexes are used on a table.
• via DBCC CLONEDATABASE
• [in-memory tables] 
• {limitation} a columnstore index must include all the columns and can’t have a filtered condition [2]
• {limitation} queries on columnstore indexes run only in InterOP mode, and not in the in-memory native mode [2]
• {operation} designing columnstore indexes
• {best practice} understand as much as possible data’s characteristics
• {best practice} identify workload’s characteristics
• {operation} create a clustered columnstore index
• via CREATE CLUSTERED COLUMNSTORE INDEX command
• not needed to specify any columns in the statement
• ⇐ the index will include all table columns
• {operation} index rebuilding 
• forces SQL Server to remove deleted rows physically from the index and to merge the delta stores’ and row groups’ data [5]
• all column segments are recreated with row groups fully populated [5]
• [<SQL Server 2019] offline operation
• [SQL Server 2019 Enterprise] online operation
• ⇒ higher availability 
• ⇐ pausing and resuming create and rebuild operations are not supported [11]
• very resource intensive process
• holds a schema modification (Sch-M) lock on the table
• ⇒ prevents other sessions from accessing it [5]
• ⇐ the overhead can be mitigated by using table/index partitioning
• ⇒ indexes will be rebuild on a partition basis for those partition with volatile data [5]
• {operation} index reorganization 
• [<SQL Server 2019] a reorganize operation is required to merge smaller COMPRESSED rowgroups, following an internal threshold policy that determines how to remove deleted rows and combine the compressed rowgroups
• [SQL Server 2019] a background merge task also works to merge COMPRESSED rowgroups from where a large number of rows has been deleted
• ⇐ after merging smaller rowgroups, the index quality should be improved.
• the tuple-mover is helped by a background merge task that automatically compresses smaller OPEN delta rowgroups that have existed for some time as determined by an internal threshold, or merges COMPRESSED rowgroups from where a large number of rows has been deleted
• via: ALTER INDEX REORGANIZE command
• [SQL Server 2016] performs additional defragmentation
• removes deleted rows from row groups that have 10 or more percent of the rows logically deleted [5]
• merges closed row groups together, keeping the total number of rows less than or equal than rowgroup’s limit [5]
• ⇐ both processes can be done together [5]
• [SQL Server 2014] the only action performed is compressing and moving the data from closed delta stores to rowgroups [5] 
• ⇐ delete bitmap and open delta stores stay intact [5]
• via: ALTER INDEX REORGANIZE
• uses all available system resources while it is running [5]
• ⇒ speeds up the execution process 
• reduce the time during which other sessions cannot modify or delete data in a table [5]
• close and compress all open row groups
• via: ALTER INDEX REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)
• row groups aren’t merged during this operation [5]
• {operation} estimate compression savings
• [SQL Server 2019] COLUMNSTORE and COLUMNSTORE_ARCHIVE added
• allows estimating the space savings if either of these indexes are used on a table [8]
• {limitation} not available in all editions 
• via: sp_estimate_data_compression_savings 
• {operation} [bulk loads] when the number of rows is less than deltastore’s limit, all the rows go directly to the deltastore
• [large bulk load] most of the rows go directly to the columnstore without passing through the deltastore
• some rows at the end of the bulk load might be too few in number to meet the minimum size of a rowgroup
• ⇒ the final rows go to the deltastore instead of the columnstore
• bulk insert operations provide the number of rows in the batch as part of the API call [5]
• best results are achieved by choosing a batch size that is divisible by rowgroup’s limit [5]
• ⇐ guarantees that every batch produces one or several fully populated row groups [5]
• ⇒ reduce the total number of row groups in a table [5]
• ⇒ improves query performance
• ⇐ the batch size shouldn’t exceed rowgroup’s limit [5]
• row groups can be still created on the fly in a manner to similar a bulk insert when the size of the insert batch is close to or exceeds [5]
• {operation} [non-bulk operations] trickle inserts go directly to a delta store
• {feature} parallel inserts
• [SQL Server 2016] requires following conditions for parallel insert on CCI [6]
• must specify TABLOCK
• no NCI on the clustered columnstore index
• no identity column
• database compatibility is set to 130
• {recommendation} minimize the use of string columns in facts tables [5]
• string data use more space
• their encoding involves additional overhead during batch mode execution [5]
• queries with predicates on string columns may have less efficient execution plans that also require significantly larger memory grants as compared to their non-string counterparts [5]
• {recommendation} [SQL Server 2012|2014] do not push string predicates down toward the lowest operators in execution plans.
• {recommendation} add another dimension table and replace the string value in the facts table with a synthetic, integer-based ID key that references a new table [5]
• {operation} upgrading to SQL Server 2016
• make sure that queries against the tables with columnstore indexes can utilize parallelism in case if database compatibility level less than 130 [5]
• {feature} [SQL Server 2019] automated columnstore index maintenance [8]
• {improvement} [SQL Server 2019] better columnstore metadata memory management
• {improvement} [SQL Server 2019] low-memory load path for columnstore tables
• {improvement} [SQL Server 2019] improved performance for bulk loading to columnstore indexes
• {improvement} [SQL Server 2019] server startup process has been made faster for databases that use in-memory columnstore tables for HTAP
• {feature} DMVs
• sys.column_store_segments 
• returns one row for each column per segment
• sys.column_store_dictionaries 
• provides information about the dictionaries used by a columnstore index
• sys.dm_db_column_store_row_group_physical_stats
• rowgroup statuses
• sys.column_store_row_groups
• provides clustered columnstore index information on a per-segment basis

Previous Post <<||>> Next Post

References:
[1] SQL Docs (2020) Columnstore indexes: Overview [link]

[2] Microsoft Learn (2024) SQL: What's new in columnstore indexes  [link]
[3] Dejan Sarka et al (2012) Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012 (Training Kit)

[4] SQL Docs (2019) Columnstore indexes - Query performance [link]

[5] Dmitri Korotkevitch (2016) Pro SQL Server Internals 2nd Ed.

[6] Microsoft Learn (2016) Columnstore Index: Parallel load into clustered columnstore index from staging table [link]

[7] Microsoft Learn (2016) Columnstore Index Defragmentation using REORGANIZE Command [link]

[8] Microsoft (2018) Microsoft SQL Server 2019: Technical white paper [link]

Acronyms:
CCI - clustered columnstore index
CI - columnstore index
DBCC - Database Console Commands
DMV - Dynamic Management View
ETL - Extract, Transform, Load
HTAP - Hybrid Transactional/Analytical Processing 
LOB - Line of Business
NCCI - nonclustered columnstore index
OLTP - On-Line Transaction Processing
SP - Service Pack

🏭🗒️Microsoft Fabric: [Azure] Service Principals (SPN) [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: 20-Jan-2025

[Azure] Service Principal (SPN)  

• {def} a non-human, application-based security identity used by applications or automation tools to access specific Azure resources [1]
• can be assigned precise permissions, making them perfect for automated processes or background services
• allows to minimize the risks of human error and identity-based vulnerabilities
• supported in datasets, Gen1/Gen2 dataflows, datamarts [2]
• authentication type 
• supported only by [2]
• Azure Data Lake Storage
• Azure Data Lake Storage Gen2
• Azure Blob Storage
• Azure Synapse Analytics
• Azure SQL Database
• Dataverse
• SharePoint online
• doesn’t support
• SQL data source with Direct Query in datasets [2]
• when registering a new application in Microsoft Entra ID, a SPN is automatically created for the app registration [4]
• the access to resources is restricted by the roles assigned to the SPN
• ⇒ gives control over which resources can be accessed and at which level [4]
• {recommendation} use SPN with automated tools [4]
• rather than allowing them to sign in with a user identity  [4]
• {prerequisite} an active Microsoft Entra user account with sufficient permissions to 
• register an application with the tenant [4]
• assign to the application a role in the Azure subscription [4]
• ⇐ requires Application.ReadWrite.All permission [4]
• extended to support Fabric Data Warehouses [1]
• {benefit} automation-friendly API Access
• allows to create, update, read, and delete Warehouse items via Fabric REST APIs using service principals [1]
• enables to automate repetitive tasks without relying on user credentials [1]
• e.g. provisioning or managing warehouses
• increases security by limiting human error
• the warehouses thus created, will be displayed in the Workspace list view in Fabric UI, with the Owner name of the SPN [1]
• applicable to users with administrator, member, or contributor workspace role [3]
• minimizes risk
• the warehouses created with delegated account or fixed identity (owner’s identity) will stop working when the owner leaves the organization [1]
• Fabric requires the user to login every 30 days to ensure a valid token is provided for security reasons [1]
• {benefit} seamless integration with Client Tools: 
• tools like SSMS can connect to the Fabric DWH using SPN [1]
• SPN provides secure access for developers to 
• run COPY INTO
• with and without firewall enabled storage [1]
• run any T-SQL query programmatically on a schedule with ADF pipelines [1]
• {benefit} granular access control
• Warehouses can be shared with an SPN through the Fabric portal [1]
• once shared, administrators can use T-SQL commands to assign specific permissions to SPN [1]
• allows to control precisely which data and operations an SPN has access to  [1]
• GRANT SELECT ON <table name> TO <Service principal name>  
• warehouses' ownership can be changed from an SPN to user, and vice-versa [3]
• {benefit} improved DevOps and CI/CD Integration
• SPN can be used to automate the deployment and management of DWH resources [1]
• ⇐ ensures faster, more reliable deployment processes while maintaining strong security postures [1]
• {limitation} default semantic models are not supported for SPN created warehouses [3]
• ⇒ features such as listing tables in dataset view, creating report from the default dataset don’t work [3]
• {limitation} SPN for SQL analytics endpoints is not currently supported
• {limitation} SPNs are currently not supported for COPY INTO error files [3]
• ⇐ Entra ID credentials are not supported as well [3]
• {limitation} SPNs are not supported for GIT APIs. SPN support exists only for Deployment pipeline APIs [3]
• monitoring tools
• [DMV] sys.dm_exec_sessions.login_name column [3] 
• [Query Insights] queryinsights.exec_requests_history.login_name [3]
• Query activity
• submitter column in Fabric query activity [3]
• Capacity metrics app: 
• compute usage for warehouse operations performed by SPN appears as the Client ID under the User column in Background operations drill through table [3]

Previous Post  <<||>>   Next Post

References:

[1] Microsoft Fabric Updates Blog (2024) Service principal support for Fabric Data Warehouse [link]

[2] Microsoft Fabric Learn (2024) Service principal support in Data Factory [link]

[3] Microsoft Fabric Learn (2024) Service principal in Fabric Data Warehouse [link] 

[4] Microsoft Fabric Learn (2024) Register a Microsoft Entra app and create a service principal [link]

[5] Microsoft Fabric Updates Blog (2024) Announcing Service Principal support for Fabric APIs [link] 

 

Acronyms:

ADF - Azure Data Factory

API - Application Programming Interface

CI/CD - Continuous Integration/Continuous Deployment

DMV - Dynamic Management View

DWH - Data Warehouse

SPN - service principal

SSMS - SQL Server Management Studio

💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part VIII: Permissions) [new feature]

Data-based solutions usually target a set of users who (ideally) have restricted permissions to the functionality. Therefore, as part of the process are defined several personas that target different use cases, for which the permissions must be restricted accordingly. 

In the simplest scenario the user must have access to the underlying objects for querying the data. Supposing that an Entra User was created already, the respective user must be given access also in the Fabric database (see [1], [2]). From database's main menu follow the path to assign read permissions:
Security >> Manage SQL Security >> (select role: db_datareader)

Manage SQL Security

Manage access >> Add >> (search for User)

Manage access

(select user) >> Share database >> (select additional permissions) >> Save

Manage additional permissions

The easiest way to test whether the permissions work before building the functionality is to login over SQL Server Management Studio (SSMS) and check the access using the Microsoft Entra MFA. Ideally, one should have a User's credentials that can be used only for testing purposes. After the above setup was done, the new User was able to access the data. 

A second User can be created for testing with the maximum of permissions allowed on the SQL database side, which is useful for troubleshooting. Alternatively, one can use only one User for testing and assign or remove the permissions as needed by the test scenario. 

It's a good idea to try to understand what's happening in the background. For example, the expectation was that for the Entra User created above also a SQL user is created, which doesn't seem to be the case, at least per current functionality available. 

 Before diving deeper, it's useful to retrieve User's details: 

-- retrieve current user
SELECT SUser_Name() sys_user_name
, User_Id() user_id 
, USER_NAME() user_name
, current_user [current_user]
, user [user]; 

Output:

sys_user_name	user_id	user_name	current_user	user
JamesClavell@[domain].onmicrosoft.com	0	JamesClavell@[domain].onmicrosoft.com	JamesClavell@[domain].onmicrosoft.com	JamesClavell@[domain].onmicrosoft.com

Retrieving the current User is useful especially when testing in parallel functionality with different Users. Strangely, User's ID is 0 when only read permissions were assigned. However, a valid User identifier is added for example when to the User is assigned also the db_datawriter role. Removing afterwards the db_datawriter role to the User keeps as expected User's ID. For troubleshooting purposes, at least per current functionality, it might be a good idea to create the Users with a valid User ID (e.g. by assigning temporarily the db_datawriter role to the User). 

The next step is to look at the Users with access to the database:

-- database access 
SELECT USR.uid
, USR.name
--, USR.sid 
, USR.hasdbaccess 
, USR.islogin
, USR.issqluser
--, USR.createdate 
--, USR.updatedate 
FROM sys.sysusers USR
WHERE USR.hasdbaccess = 1
  AND USR.islogin = 1
ORDER BY uid

Output:

uid	name	hasdbaccess	islogin	issqluser
1	dbo	1	1	1
6	CharlesDickens@[...].onmicrosoft.com	1	1	0
7	TestUser	1	1	1
9	JamesClavell@[...].onmicrosoft.com	1	1	0

For testing purposes, besides the standard dbo role and two Entra-based roles, it was created also a SQL role to which was granted access to the SalesLT schema (see initial post):

-- create the user
CREATE USER TestUser WITHOUT LOGIN;

-- assign access to SalesLT schema 
GRANT SELECT ON SCHEMA::SalesLT TO TestUser;
  
-- test impersonation (run together)
EXECUTE AS USER = 'TestUser';

SELECT * FROM SalesLT.Customer;

REVERT; 

Notes:
1) Strangely, even if access was given explicitly only to the SalesLT schema, the TestUser User has access also to sys.sysusers and other DMVs. That's valid also for the access over SSMS
2) For the above created User there are no records in the sys.user_token and sys.login_token DMVs, in contrast with the user(s) created for administering the SQL database. 

Let's look at the permissions granted explicitly:

-- permissions granted explicitly
SELECT DPR.principal_id
, DPR.name
, DPR.type_desc
, DPR.authentication_type_desc
, DPE.state_desc
, DPE.permission_name
FROM sys.database_principals DPR
     JOIN sys.database_permissions DPE
	   ON DPR.principal_id = DPE.grantee_principal_id
WHERE DPR.principal_id != 0 -- removing the public user
ORDER BY DPR.principal_id
, DPE.permission_name;

Result:

principal_id	name	type_desc	authentication_type_desc	state_desc	permission_name
1	dbo	SQL_USER	INSTANCE	GRANT	CONNECT
6	CharlesDickens@[...].onmicrosoft.com	EXTERNAL_USER	EXTERNAL	GRANT	AUTHENTICATE
6	CharlesDickens@[...].onmicrosoft.com	EXTERNAL_USER	EXTERNAL	GRANT	CONNECT
7	TestUser	SQL_USER	NONE	GRANT	CONNECT
7	TestUser	SQL_USER	NONE	GRANT	SELECT
9	JamesClavell@[...].onmicrosoft.com	EXTERNAL_USER	EXTERNAL	GRANT	CONNECT

During troubleshooting it might be useful to check current user's permissions at the various levels via sys.fn_my_permissions:

-- retrieve database-scoped permissions for current user
SELECT *
FROM sys.fn_my_permissions(NULL, 'Database');

-- retrieve schema-scoped permissions for current user
SELECT *
FROM sys.fn_my_permissions('SalesLT', 'Schema');

-- retrieve object-scoped permissions for current user
SELECT *
FROM sys.fn_my_permissions('SalesLT.Customer', 'Object')
WHERE permission_name = 'SELECT';

Notes:
1) See also [1] and [4] in what concerns the limitations that apply to managing permissions in SQL databases.

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) Microsoft Fabric: Share your SQL database and manage permissions [link]
[2] Microsoft Learn (2024) Microsoft Fabric: Share data and manage access to your SQL database in Microsoft Fabric  [link]
[3] Microsoft Learn (2024) Authorization in SQL database in Microsoft Fabric [link]
[4] Microsoft Learn (2024) Authentication in SQL database in Microsoft Fabric [link]

[5] Microsoft Fabric Learn (2025) Manage access for SQL databases in Microsoft Fabric with workspace roles and item permissions [link] 

💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part VII: Things That Don't Work) [new feature]

Microsoft does relatively a good job in documenting what doesn't work in Microsoft Fabric's SQL Databases. There's a good overview available already in the documentation, though beyond this the current post lists my finding while testing the previously written code on this blog,

USE Database

The standard syntax allows to change via USE the database context to the specified database or database snapshot. Unfortunately, this syntax doesn't seem to be supported currently and unfortunately many scripts seem to abuse of it. Thus, the following line of code throws an error:

-- changing the context
USE master;
GO
USE tempdb;

"Msg 40508, Level 16, State 1, Line 1, USE statement is not supported to switch between databases. Use a new connection to connect to a different database"

However, one can use the 3-part naming convention to reference the various objects:

-- sys metadata - retrieving the database files

SELECT *

FROM tempdb.sys.database_files dbf

ORDER BY name;

Even if the tempdb is not listed in the sys.databases table, it's still available for querying, which can prove helpful for troubleshooting. 

DBCC commands 

The documentation warns that some DBCC commands won't work, though in some cases there are also alternatives. For example:

-- clearing the procedure cache via DBCC
DBCC FREEPROCCACHE;

Output:

"Msg 2571, Level 14, State 9, Line 1, User '<user>' does not have permission to run DBCC freeproccache."

Alternatively, one can use the following command, which seems to work:

-- clearing the procedure cash via ALTER
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

CHECKDB, which checks the logical and physical integrity of all the objects in the specified database, can't be used as well:

 

-- Checking the logical and physical integrity of a database
DBCC CHECKDB();

Output:

"Msg 916, Level 14, State 2, Line 1, The server principal "..." is not able to access the database "..." under the current security context."

The same error message is received for CHECKTABLE, utility which checks the integrity of all the pages and structures that make up the table (or indexed view):

-- checking a table's integrity
DBCC CHECKTABLE ('SalesLT.Address');

Output:

"Msg 916, Level 14, State 2, Line 2, The server principal "..." is not able to access the database "..." under the current security context."

A similar error messages is received for SQLPERF, which provides transaction log space usage statistics for all databases:

-- retrieving the LOGSPACE information for all databases
DBCC SQLPERF (LOGSPACE);

Output: 

"Msg 297, Level 16, State 10, Line 1, The user does not have permission to perform this action."

There are however DBCC commands like SHOW_STATISTICS or SHRINKDATABASE which do work. 

 

-- current query optimization statistics
DBCC SHOW_STATISTICS('SalesLT.Address','PK_Address_AddressID');

Output:

Name	Updated	Rows	Rows Sampled	Steps	Density	Average key length	String Index	Filter Expression	Unfiltered Rows	Persisted Sample Percent
PK_Address_AddressID	Dec 21 2024 3:02AM	450	450	197	1	4	NO		450	0

SHRINKDATABASE shrinks the size of the data and log files in the specified database:

-- shrinking database
DBCC SHRINKDATABASE([AdventureWorks01-...]) WITH NO_INFOMSGS;

Update 29-Jan-2025: According to an answer from Ask the Expert session on Fabric Database [3], Microsoft seems to be working in bringing more DBCC features to SQL databases.

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) SQL Server: USE <database> [link]
[2] Microsoft Learn (2024) Database console commands [link]
[3] Microsoft Reactor (2025) Ask The Expert - Fabric Edition - Fabric Databases [link]

💎🏭SQL Reloaded: Microsoft Fabric's SQL Databases (Part VI: Index Usage Analysis) [new feature]

There are several system dynamic management views (DMV) available in SQL Server, Azure SQL Server and now in SQL databases that allow to gather more information about indexes' fragmentation and usage. Let's look at the most important information available based on the indexes create in the previous posts. As the data were probably purged from the views, it's needed to run first the select queries based on the SalesLT.Product from the previous post. This step is important, otherwise the DMVs might return no records!

One starting point is to use the sys.dm_db_index_physical_stats DMV to look at the indexes' size and fragmentation information for a given table (or view). The table is used usually as starting point for analyzing indexes' fragmentation and then defragment the indexes with high fragmentation.

-- sys metadata - index & data size and fragmentation information for the data and indexes of the specified table or view
SELECT --db_name() db_name
--, object_name(IND.object_id) table_name
 IND.name index_name
, IND.type_desc
, IPS.page_count
, IPS.record_count
, IPS.index_level
, Cast(IPS.avg_fragmentation_in_percent as decimal(10,2)) avg_fragmentation_perc
, Cast(IPS.avg_page_space_used_in_percent as decimal(10,2)) space_used_perc
--, IPS.*
FROM sys.indexes IND
     CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), IND.object_id, IND.index_id, NULL, 'DETAILED') IPS
WHERE IND.object_id = OBJECT_ID(N'SalesLT.Product');

Output:

index_name	type_desc	page_count	record_count	index_level	avg_fragmentation_perc	space_used_perc
PK_Product_ProductID	CLUSTERED	101	295	0	0.99	87.90
PK_Product_ProductID	CLUSTERED	1	101	1	0.00	16.20
AK_Product_rowguid	NONCLUSTERED	2	295	0	50.00	74.69
AK_Product_rowguid	NONCLUSTERED	1	2	1	0.00	0.59
AK_Product_ProductNumber	NONCLUSTERED	2	295	0	50.00	85.79
AK_Product_ProductNumber	NONCLUSTERED	1	2	1	0.00	0.49
AK_Product_Name	NONCLUSTERED	3	295	0	33.33	87.32
AK_Product_Name	NONCLUSTERED	1	3	1	0.00	1.67
IX_SalesLT_Product_Color	NONCLUSTERED	1	295	0	0.00	79.24
IX_SalesLT_Product_Color_Size	NONCLUSTERED	1	295	0	0.00	94.12
IX_SalesLT_Product_ListPrice_IC	NONCLUSTERED	4	295	0	0.00	86.60
IX_SalesLT_Product_ListPrice_IC	NONCLUSTERED	1	4	1	0.00	1.01

In a second step one can look at the sys.dm_db_index_usage_stats DMV which provides the counts of the different types of index operations and the time each type of operation was last performed:

-- sys metadata - counts of different types of index operations and the time each type of operation was last performed.
SELECT -- db_name() db_name
--, object_name(IND.object_id) table_name
 IND.name
, IND.type_desc
, IUS.user_seeks 
, IUS.user_scans
, IUS.user_lookups 
, IUS.user_updates
, IUS.last_user_seek
, IUS.last_user_scan 
, IUS.last_user_lookup
, IUS.last_user_update
FROM sys.dm_db_index_usage_stats IUS
     JOIN sys.indexes IND
       ON IUS.index_id = IND.index_id
WHERE IND.object_id = OBJECT_ID(N'SalesLT.Product');

Output:

name	type_desc	user_seeks	user_scans	user_lookups	user_updates	last_user_seek	last_user_scan	last_user_lookup
PK_Product_ProductID	CLUSTERED	0	10	15	0		2025-01-06T14:23:54	2025-01-06T14:23:54
IX_SalesLT_Product_Color_Size	NONCLUSTERED	11	0	0	0	2025-01-06T14:23:54
IX_SalesLT_Product_ListPrice_IC	NONCLUSTERED	8	0	0	0	2025-01-06T13:38:03

Finally, it might be useful to look also at the sys.dm_db_index_operational_stats DMV which returns the current lower-level I/O, locking, latching, and access method activity for each partition of a table or index in the database (see the documentation for the full list of attrbutes):

-- sys metadata - index operations stats
SELECT -- db_name() db_name
--, object_name(IND.object_id) table_name
 IND.name index_name
, IND.type_desc
, IOS.range_scan_count
, IOS.singleton_lookup_count
, IOS.leaf_insert_count
, IOS.leaf_delete_count
, IOS.leaf_update_count
, IOS.nonleaf_insert_count
, IOS.nonleaf_delete_count
, IOS.nonleaf_update_count
FROM sys.indexes IND
     CROSS APPLY sys.dm_db_index_operational_stats(DB_ID(), IND.object_id, IND.index_id, NULL) IOS
WHERE IND.object_id = OBJECT_ID(N'SalesLT.Product')
 AND IOS.range_scan_count<>0
ORDER BY IND.name;

Output:

index_name	type_desc	range_scan_count	singleton_lookup_count	leaf_insert_count	leaf_delete_count	leaf_update_count	nonleaf_insert_count	nonleaf_delete_count	nonleaf_update_count
IX_SalesLT_Product_Color_Size	NONCLUSTERED	11	0	0	0	0	0	0	0
IX_SalesLT_Product_ListPrice_IC	NONCLUSTERED	8	0	0	0	0	0	0	0
PK_Product_ProductID	CLUSTERED	10	64	0	0	0	0	0	0

For more information on these DMVs check the documentation.

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) SQL Server: sys.dm_db_index_physical_stats [link]
[2] Microsoft Learn (2024) SQL Server: sys.dm_db_index_usage_stats [link]
[3] Microsoft Learn (2024) SQL Server: sys.dm_db_index_operational_stats [link]