Features for SQL Server

Modified date: Monday, June 30, 2025

Introduction
General
Data Types
SQL
Storage and System
Benchmarking
Tools
Export Regulations
Internal
Internal - Optimizer
Internal - Runtime
Internal - Storage Engine

Introduction

In its own words, "Microsoft SQL Server is a relational database management system (RDBMS)". It is currently ranked #3 in DB-Engine rankings.

Specific Concepts

We list some concpets might be used in the sections to explain things.

Concept	Explaination
DMVs	Dynamic Management Views (DMVs) are special system views that expose real-time information about the state, health, and performance of the SQL Server instance. They are read-only views that provide a window into SQL Server’s internal engine structures — such as memory usage, query execution stats, indexing, locking, blocking, sessions, wait statistics, I/O bottlenecks, etc.
Resource Governor	Resource Governor allows fine-grained control over CPU, memory, and I/O resources for different workloads by defining resource pools and workload groups. This prevents resource-intensive queries from starving critical business operations, ensuring balanced system performance. It’s particularly valuable in multi-tenant environments or shared platforms where multiple applications run on the same SQL Server instance. DBAs can define classifier functions that dynamically assign incoming sessions to specific resource pools based on user, application, or workload characteristics.
Always On Avaliablity Group (AG)	Always On Availability Groups (AG) provide high availability by allowing multiple copies (replicas) of a database to exist across servers. One replica serves read-write traffic, while others can serve read-only workloads or act as failover targets. Automatic failover ensures minimal downtime during hardware or software failures. Beyond high availability, AGs also support load balancing by offloading read operations to secondary replicas. They support synchronous and asynchronous replication modes.
PoyBase	PolyBase allows SQL Server to query external data sources like Hadoop, Azure Blob Storage, Oracle, or flat files, all using standard T-SQL syntax. Originally introduced in the SQL Server Parallel Data Warehouse (PDW) product, it became part of the main SQL Server engine starting with SQL Server 2016.
Xevent

General

Feature	Value	Definition
intro	Microsoft SQL Server is a relational database management system (RDBMS).	in their own words - but I reserved the rights to remove some bold claims like "the best", unless it is widely recognized.
vendor	Microsoft
initial release	1989
latested version	2022 (16.x)	We don't put a release date here as the software is patching frequently. So tracking it is not much useful.
supported platforms	Windows, Linux	supported OS/CPU platforms
db-engines ranking	3	ranks from https://db-engines.com/en/ranking (06/25)
relational?	yes	Is it a relational database? (1) Most database are actually with some extensions, for example, nested data types, graph support, etc, which we usually called "multi-model". (2) Some of them are product family, meaning they have more than one database. Here we focus on the main one but explain others when needed.
open source?	no	mainly the engine code
license	commercial
price: box software	$3,586 ~ $13,748 (2024) 2024: Licensing costs range from $3,586 for the Standard Edition to $13,748 for the Enterprise Edition (for two cores); the server and CAL model run $899 for the server plus $209 per user.
on-premise offering	yes	if no means you can't buy "box" software from them
cloud offering	SQL Azure and other cloud vendors
technical doc	https://learn.microsoft.com/en-us/sql/sql-server/

Data Types

Feature	Value	Definition
int: 1-bytes int name	tinyint
int: 2-bytes int name	smallint
int: 4-bytes int name	int
int: 8-bytes int name	bigint
decimal: storage size	5 to 17
decimal: range	from -10^38 + 1 through 10^38 - 1	also called number, numeric in different systems
char(n): max bytes	8000

SQL

Feature	Value	Definition
SQL: standard complaince	high
max SQL length	64K	maximal SQL statement length
PL: main	T-SQL (procedure support included)	main programming lanage: most database suports SQL because SQL is a well established standard. However, each database would like to extend SQL more or less.
PL: other language support	yes	PL lanaguage other than PL/SQL, like PL/Java, PL/Rust etc
SP: max parameters	2100
UDF: max parameters	2100
SQL: max nested subqueries	32	Maximum levels of subqueries in a SQL statement
SQL: query hints	complete	if it allows use query hints to guide the optimizer
SQL: explicit locking	yes	Locking is usually an internal matter - so does it allow explicit locking? What levels do they support?
Triggers?	yes	If support triggers
Triggers: scope	tables, views	What objects can have triggers
Triggers: type	AFTER, INSTEAD OF	Types of triggers supported
Group By: max number of expressions	sum of all group by expressions bytes < 8060 Because SQL Server internally implements hash aggregation (or sometimes sort aggregation) using worktables or hash buckets stored in pages with 8KB size limit (8060 bytes of usable data per page). The combined size of the group key expressions must fit into one internal row structure, which is restricted by SQL Server’s page architecture.
Object-Relational?	some
Extension Mechanism	SQL level

Storage and System

Feature	Value	Definition
arch: server	C/S	Embedded or traditional C/S?
arch: run in browser?	no	It also known as a client-side database, is a database that is stored and managed within a user's web browser, rather than on a remote server.
arch: clustering/HA	Always On Aaliablity Group， Failover clustering
arch: in-memory support	In-memory OLTP (Hekaton)
arch: Multi-master support?	no	if multi-master support?
arch: primary/read replica?	yes	if primary + mulitiple read replica supported
replication: sync/async	both	Can commits wait or w/o wait for replicas to acknowledge
replication: WAL shipping	yes	Uses write-ahead log (WAL) shipping for replication
replication: quorum-based commit	yes	Multiple synchronous replicas with quorum for commit
tables: max number per database	MAXINT Tables, views, triggers etc are objects in SQL Server. They the same an integer range as object ID, so maximal number of tables is at most MAXINT if there is no other objects.
partitions: methods	Range, List, Hash, Composite (nested partitioning supported since 2016).	Supported partitioning strategies (range, list, hash, etc.).
rows: max rows per table	undefined	The actual number depends on storage etc
index: max allowed index	999 v14-17 allows 999, and 8 is allowed before that	Max number of indices allowed per table
index: max allowable size	900 clustered index, 1700 non-clustered index	Max index record size (bytes). This constraint is mainly coming from the fact of the database page size: if we exclude blob data types, database engine usally do not allow a record expand more than one page.
index: max number of fields	v14-17 allows 32 and 16 is allowed before that	Max number of columns allowed in one index
partition: max allowed partitions	15000 In versions earlier than SQL Server 2012 (11.x), the number of partitions was limited to 1,000 by default. The reason it struggles to increase the limit is due to the challenges I listed.	Meta data challenge: 1M partitions just like 1M tables, system have to hold them in memory. Optimizer challenge: O(N) algorithm may lead to very long planning time if there are excessive partitions.
ACID	yes/yes	for DML and DDL
ACID: max isolation level	Serializable SQL Server implements traditional two phase locking (2PL) locking for serializability. It also supports SI, similar to Oracle, it is not serializable.
ACID: max ANSI isolation level	Serializable
ACID: durability	yes
Materialized View: support?	yes

Benchmarking

Feature	Value	Definition
any official TPC benchmarks?	yes	The TPC benchmark includes a set of tests simulating real-world scenarios to evaluate database performance.
TPCC: most recent tpmC	1,207,982 System cost: 1,046,759 USD
TPCC: most recent submit date	11/14/2011
TPCC: most recent per thread perf	37749
TPCC: best tpmC	1807347 System cost: 879,563 USD
TPCC: best perf submit date	8/27/2010
TPCC: best perf per thread perf	28240

Tools

Feature	Value	Definition
command line client	sqlcmd	it means "sql client" for database supporting SQL. For embedded atabase, the client includes the server together.

Export Regulations

Feature	Value	Definition
Jurisdiction	US	Which country controls export
ECCN	5D992.c	An Export Control Classification Number (ECCN) is a five-character alphanumeric code used to categorize items on the Commerce Control List (CCL) for export control purposes. Most database may fall into 5D992.c category, "mass market encryption", which means it has some ordinary encryption related code, for example, the SSL connection code.
Eligible License Exception / CCATS	G065307 All big 3 (Oracle, DB2, SQL Server) shall be similar on this aspect.	A License Exception is an authorization that allows you to export or reexport items subject to the EAR without needing to obtain a specific export license, provided certain conditions are met. CCATS stands for Commodity Classification Automated Tracking System. The BIS assigns a CCATS number to products that it has classified under the Commerce Control List (CCL).
Encryption Components	TDE, SSL	Crypto functionality that triggers control

Internal

Feature	Value	Definition
concurrency control	Locking + MVCC
MVCC: implemented?	yes	if implement MVCC for concurrency control
MVCC: rollback segment	yes	if uses rollback segment (RS) to store old versions. Without RS, old versions and new versions are mixed stored, then the database engine has to find a way to efficiently drop the old versions at certain point.
Implementation language	C++	A DBMS may use mulitple programming languages, for example, supports its stored procedure. The major programming language used to implement the engine.

Internal - Optimizer

Feature	Value	Definition
CBO?	yes	if it employees a cost based optimizer
framework	Cascades	System-R is more like a dynamic programming, bottom up optimizer, while Cascades/Volcano gebaseerd optimizer is more like top-down optimizer.
join order search	Memo storage with a bunch of transformation rules like associative rule, commutative rule ec	How join order permutations are explored during plan generation.
stats: multi column	yes	Multi-column stats may cause storage space bloat: for example, if one dimension has 100 buckets, then three dimensions will require 1M buckets - but reducing the total number of buckets will result in reduced accuracy.
query hints?	complete	if it allows use query hints to guide the optimizer
plan guide?	yes	Can we use plan guide to correct the plan? This is a more systematic and accurate way to repair the plan than plan hints. Especially during system upgrades, if the plan becomes worse, we can use plan guide to force a query to use a previous plan.

Internal - Runtime

Feature	Value	Definition
resource management	complete: per query grant	How execution memory is allocated and limited.
spilling support	yes	"spilling" refers to the process of writing temporary data or intermediate results of a query to disk when the available memory is exhausted. This is crucial for handling large datasets or complex queries that require more memory than available.
model	Volcano + push for parallel run	The operator scheduling model: pull-gebaseerd (Volcano), push etc.
support intra-parallel query?	yes	meaning a single query can utilize multi hardware threads to run it
adaptive execution (AQP)?	Adaptive joins, adaptive memory grants, etc SQL Server has introduced these features since 2019, with name intelligent query processing (IQP).	Traditionally, after the optimizer determines the plan, the runtime must execute it completely without any room for adjustment, such as which of the two tables should be built. The adaptive method allows the runtime to make some adjustments based on the actual situation, and the optimizer must also prepare for this uncertainty, such as preparing an alternative plan.
vectorization	yes	Speed up OLAP queries using vectorized execution. A vectorized runtime exchange a bunch of rows between iterators, and these rows are physically sotre with column-oriented order.
iterator: join methods	all 3	Hash Join (HJ), Sort-Merge Join (MJ) and Nested loop Join (NLJ) are 3 major ones
Error: out-of-range and overflow	abort the transaction	To maintain atomic requirement of ACID, database engine usually fail the statement and abort the transaction.

Internal - Storage Engine

Feature	Value	Definition
Page Size	8K fixed	The physical disk block size for storage and I/O.
Storage Compression	Row, page, and columnstore compression supported.
Row Overflow / Large Row Handling	Row within a page and large columns pointing to LOB storage	How rows larger than page size are handled. Usually a row must be within one page, but big columns can only save a pointer with the row and pointing to the actual storage. This is also why there is a limit of maximal columns per table.
Index: primary cluster	Clustered index is default (one per table).	Whether physical rows are ordered by primary index.
Index: secondary	B+Tree, Bitmap, XML, Fulltext, Spatial, Columnstore.	Supported index types beyond the primary key.
index: column store as primary	yes	if column-store as primary storage supported
index: column store as secondary	yes	if column-store as secondary index supported
index: pause and resume	yes	Pause and resume index building. This gives DBA flexibility to schedule index rebuild without having to wait it done.
partition table	complete	Partitioning capabilities for large tables.
partition table: prunning	both	Ability to eliminate partitions at query compile time and runtime
ACID: durability mechanism	WAL	How is it implements durability. Database's classic way is write ahead logging (WAL).