Hubbry Logo
Microsoft SQL ServerMicrosoft SQL ServerMain
Open search
Microsoft SQL Server
Community hub
Microsoft SQL Server
logo
8 pages, 0 posts
0 subscribers
Be the first to start a discussion here.
Be the first to start a discussion here.
Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server
View on Wikipedia
from Wikipedia

Microsoft SQL Server
DeveloperMicrosoft
Initial releaseApril 24, 1989; 36 years ago (1989-04-24), as SQL Server 1.0
Stable release
SQL Server 2022[1] Edit this on Wikidata (CU18 16.0.4185.3)[2] / 16 November 2022; 2 years ago (16 November 2022)
Written inC, C++[3]
Operating systemLinux, Microsoft Windows Server, Microsoft Windows
Available inEnglish, Chinese, French, German, Italian, Japanese, Korean, Portuguese (Brazil), Russian, Spanish and Indonesian[4]
TypeRelational database management system
LicenseProprietary software
Websitewww.microsoft.com/sql-server

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel"). As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet). Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

History

[edit]
Main article: History of Microsoft SQL Server

The history of Microsoft SQL Server begins with the first Microsoft SQL Server product—SQL Server 1.0, a 16-bit server for the OS/2 operating system in 1989—and extends to the current day. Its name is entirely descriptive, it being server software that responds to queries in the SQL language.

Milestones

[edit]
  • MS SQL Server for OS/2 began as a project to port Sybase SQL Server onto OS/2 in 1989, by Sybase, Ashton-Tate, and Microsoft.
  • SQL Server 4.2 for NT is released in 1993, marking the entry onto Windows NT.
  • SQL Server 6.0 is released in 1995, marking the end of collaboration with Sybase; Sybase would continue developing their own variant of SQL Server, Sybase Adaptive Server Enterprise, independently of Microsoft.
  • SQL Server 7.0 is released in 1998, marking the conversion of the source code from C to C++.
  • SQL Server 2000, released in 2000. SQL Server 2000 SQL Server 8 SQL Server 8.0 codename Shiloh Release date: 2000-11-30.
  • SQL Server 2005, released in 2005, finishes the complete revision of the old Sybase code into Microsoft code.
  • SQL Server 2008, released in 2008, supports hierarchical data, adds FILESTREAM and SPATIAL data types.
  • SQL Server 2012, released in 2012, adds columnar in-memory storage aka xVelocity.
  • SQL Server 2017, released in 2017, adds Linux support for these Linux platforms: Red Hat Enterprise Linux, SUSE Linux Enterprise Server, Ubuntu & Docker Engine.[5]
  • SQL Server 2019, released in 2019, adds Big Data Clusters, enhancements to the "Intelligent Database", enhanced monitoring features, updated developer experience, and updates/enhancements for Linux based installations.[6]
  • SQL Server 2022, released in 2022.

Currently

[edit]

As of January 2025, the following versions are supported by Microsoft:[7]

  • SQL Server 2016
  • SQL Server 2017
  • SQL Server 2019
  • SQL Server 2022


From SQL Server 2016 onward, the product is supported on x64 processors only and must have 1.4 GHz processor as a minimum, 2.0 GHz or faster is recommended.[8]

The current version is Microsoft SQL Server 2022, released November 16, 2022. The RTM version is 16.0.1000.6.[9]

Editions

[edit]

Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users. These editions are:[10][11]

Mainstream editions

[edit]
Enterprise
SQL Server Enterprise Edition includes both the core database engine and add-on services, with a range of tools for creating and managing a SQL Server cluster. It can manage databases as large as 524 petabytes and address 12 terabytes of memory and supports 640 logical processors (CPU cores).[12]
Standard
SQL Server Standard edition includes the core database engine, along with the stand-alone services. It differs from Enterprise edition in that it supports fewer active instances (number of nodes in a cluster) and does not include some high-availability functions such as hot-add memory (allowing memory to be added while the server is still running), and parallel indexes.
Web
SQL Server Web Edition is a low-TCO option for Web hosting.
Business intelligence
Introduced in SQL Server 2012 and focusing on Self Service and Corporate Business Intelligence. It includes the Standard Edition capabilities and Business Intelligence tools: Power Pivot, Power View, the BI Semantic Model, Master Data Services, Data Quality Services and xVelocity in-memory analytics.[13]
Workgroup
SQL Server Workgroup Edition includes the core database functionality but does not include the additional services. Note that this edition has been retired in SQL Server 2012.[14]
Express
SQL Server Express Edition is a scaled down, free edition of SQL Server, which includes the core database engine. While there are no limitations on the number of databases or users supported, it is limited to using one processor, 1 GB memory and 10 GB database files (4 GB database files prior to SQL Server Express 2008 R2).[15] It is intended as a replacement for MSDE. Two additional editions provide a superset of features not in the original Express Edition. The first is SQL Server Express with Tools, which includes SQL Server Management Studio Basic. SQL Server Express with Advanced Services adds full-text search capability and reporting services.[16]

Specialized editions

[edit]
Azure
Microsoft Azure SQL Database is the cloud-based version of Microsoft SQL Server, presented as a platform as a service offering on Microsoft Azure.
Azure MPP
Azure SQL Data Warehouse is the cloud-based version of Microsoft SQL Server in a MPP (massively parallel processing) architecture for analytics workloads, presented as a platform as a service offering on Microsoft Azure.
Compact (SQL CE)
The compact edition is an embedded database engine. Unlike the other editions of SQL Server, the SQL CE engine is based on SQL Mobile (initially designed for use with hand-held devices) and does not share the same binaries. Due to its small size (1 MB DLL footprint), it has a markedly reduced feature set compared to the other editions. For example, it supports a subset of the standard data types, does not support stored procedures or Views or multiple-statement batches (among other limitations). It is limited to 4 GB maximum database size and cannot be run as a Windows service, Compact Edition must be hosted by the application using it. The 3.5 version includes support for ADO.NET Synchronization Services. SQL CE does not support ODBC connectivity, unlike SQL Server proper.
Developer
SQL Server Developer Edition includes the same features as SQL Server Enterprise Edition, but is limited by the license to be only used as a development and test system, and not as production server. Starting early 2016, Microsoft made this edition free of charge to the public.[17]
Embedded (SSEE)
SQL Server 2005 Embedded Edition is a specially configured named instance of the SQL Server Express database engine which can be accessed only by certain Windows Services.
Evaluation
SQL Server Evaluation Edition, also known as the Trial Edition, has all the features of the Enterprise Edition, but is limited to 180 days, after which the tools will continue to run, but the server services will stop.[18]
Fast Track
SQL Server Fast Track is specifically for enterprise-scale data warehousing storage and business intelligence processing, and runs on reference-architecture hardware that is optimized for Fast Track.[19]
LocalDB
Introduced in SQL Server Express 2012, LocalDB is a minimal, on-demand, version of SQL Server that is designed for application developers.[20] It can also be used as an embedded database.[21]
Analytics Platform System (APS)
Formerly Parallel Data Warehouse (PDW) A massively parallel processing (MPP) SQL Server appliance optimized for large-scale data warehousing such as hundreds of terabytes.[22]
Datawarehouse Appliance Edition
Pre-installed and configured as part of an appliance in partnership with Dell & HP base on the Fast Track architecture. This edition does not include SQL Server Integration Services, Analysis Services, or Reporting Services.
Windows Internal Database (WID)

Discontinued editions

[edit]
Microsoft Data Engine
Version 1.0 is based on SQL Server version 7.0.[23] Afterwards, it was replaced by Microsoft SQL Server Data Engine.
Microsoft SQL Server Data Engine
Also called Desktop Engine, Desktop Edition, it is based on SQL Server 2000. Included with some versions of Microsoft Access, Microsoft development tools, and other editions of SQL Server.[24] Intended for use with those management and development tools, it did not include any other GUI. Later, Microsoft also made available a web admin tool. After SQL Server 2000, it was replaced by SQL Server 2005 Express Edition.
Personal Edition
SQL Server 2000. Had workload or connection limits like MSDE, but no database size limit. Includes standard management tools. Intended for use as a mobile / disconnected proxy, licensed for use with SQL Server 2000 Standard edition.[24] Similar to Standard Edition in SQL Server 2000, but Full-Text Search not working in Windows 98, transactional replication limited to subscriber.[25]
Datacenter
SQL Server 2008 R2 Datacenter is a full-featured edition of SQL Server and is designed for datacenters that need high levels of application support and scalability. It supports 256 logical processors and virtually unlimited memory and comes with StreamInsight Premium edition.[26] The Datacenter edition has been retired in SQL Server 2012; all of its features are available in SQL Server 2012 Enterprise Edition.[14]
Windows CE Edition
Introduced in SQL Server 2000,[27] and was replaced by SQL Server 2005 Mobile Edition.
SQL Server 2005 Mobile Edition
Replaced by SQL Server 2005 Compact Edition after 1 release.
SQL Server 2005 Compact Edition
Replaced by SQL Server Compact 3.5 after 1 release.

Supplemental packages

[edit]

Tools published by Microsoft include:

  • SQL Server 2000:
  • Samples:[28] Northwind and pubs Sample Databases, Updated Samples for SQL Server 2000.
  • Tools: Stress Testing and Performance Analysis tools (Read80Trace and OSTRESS), PSSDIAG Data Collection Utility, Notification services (up to service pack 1), Security Tools, Best Practices Analyzer 1.0, Reporting Services (up to Service Pack 2), Reporting Services Report Packs, SQL Server 2000 Driver for JDBC (up to service pack 3), SQLXML 3.0 (up to service pack 3).
  • Documentation:[29] SQL Server 2000 Books Online, SQL Server 2000 System Table Map, Resource Kit, SQL Server 2000 — Getting Started Guide.

Architecture

[edit]

The protocol layer implements the external interface to SQL Server. All operations that can be invoked on SQL Server are communicated to it via a Microsoft-defined format, called Tabular Data Stream (TDS). TDS is an application layer protocol, used to transfer data between a database server and a client. Initially designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984, and later by Microsoft in Microsoft SQL Server, TDS packets can be encased in other physical transport dependent protocols, including TCP/IP, named pipes, and shared memory. Consequently, access to SQL Server is available over these protocols. In addition, the SQL Server API is also exposed over web services.[11]

Data storage

[edit]

Data storage is a database, which is a collection of tables with typed columns. SQL Server supports different data types, including primitive types such as Integer, Float, Decimal, Char (including character strings), Varchar (variable length character strings), binary (for unstructured blobs of data), Text (for textual data) among others. The rounding of floats to integers uses either Symmetric Arithmetic Rounding or Symmetric Round Down (fix) depending on arguments: SELECT Round(2.5, 0) gives 3.

Microsoft SQL Server also allows user-defined composite types (UDTs) to be defined and used. It also makes server statistics available as virtual tables and views (called Dynamic Management Views or DMVs). In addition to tables, a database can also contain other objects including views, stored procedures, indexes and constraints, along with a transaction log. A SQL Server database can contain a maximum of 231 objects, and can span multiple OS-level files with a maximum file size of 260 bytes (1 exabyte).[11] The data in the database are stored in primary data files with an extension .mdf. Secondary data files, identified with a .ndf extension, are used to allow the data of a single database to be spread across more than one file, and optionally across more than one file system. Log files are identified with the .ldf extension.[11]

Storage space allocated to a database is divided into sequentially numbered pages, each 8 KB in size. A page is the basic unit of I/O for SQL Server operations. A page is marked with a 96-byte header which stores metadata about the page including the page number, page type, free space on the page and the ID of the object that owns it. The page type defines the data contained in the page. This data includes: data stored in the database, an index, an allocation map, which holds information about how pages are allocated to tables and indexes; and a change map which holds information about the changes made to other pages since last backup or logging, or contain large data types such as image or text. While a page is the basic unit of an I/O operation, space is actually managed in terms of an extent which consists of 8 pages. A database object can either span all 8 pages in an extent ("uniform extent") or share an extent with up to 7 more objects ("mixed extent"). A row in a database table cannot span more than one page, so is limited to 8 KB in size. However, if the data exceeds 8 KB and the row contains varchar or varbinary data, the data in those columns are moved to a new page (or possibly a sequence of pages, called an allocation unit) and replaced with a pointer to the data.[30]

For physical storage of a table, its rows are divided into a series of partitions (numbered 1 to n). The partition size is user defined; by default all rows are in a single partition. A table is split into multiple partitions in order to spread a database over a computer cluster. Rows in each partition are stored in either B-tree or heap structure. If the table has an associated, clustered index to allow fast retrieval of rows, the rows are stored in-order according to their index values, with a B-tree providing the index. The data is in the leaf node of the leaves, and other nodes storing the index values for the leaf data reachable from the respective nodes. If the index is non-clustered, the rows are not sorted according to the index keys. An indexed view has the same storage structure as an indexed table. A table without a clustered index is stored in an unordered heap structure. However, the table may have non-clustered indices to allow fast retrieval of rows. In some situations the heap structure has performance advantages over the clustered structure. Both heaps and B-trees can span multiple allocation units.[31]

Buffer management

[edit]

SQL Server buffers pages in RAM to minimize disk I/O. Any 8 KB page can be buffered in-memory, and the set of all pages currently buffered is called the buffer cache. The amount of memory available to SQL Server decides how many pages will be cached in memory. The buffer cache is managed by the Buffer Manager. Either reading from or writing to any page copies it to the buffer cache. Subsequent reads or writes are redirected to the in-memory copy, rather than the on-disc version. The page is updated on the disc by the Buffer Manager only if the in-memory cache has not been referenced for some time. While writing pages back to disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/O operation to complete. Each page is written along with its checksum when it is written. When reading the page back, its checksum is computed again and matched with the stored version to ensure the page has not been damaged or tampered with in the meantime.[32]

Concurrency and locking

[edit]

SQL Server allows multiple clients to use the same database concurrently. As such, it needs to control concurrent access to shared data, to ensure data integrity—when multiple clients update the same data, or clients attempt to read data that is in the process of being changed by another client. SQL Server provides two modes of concurrency control: pessimistic concurrency and optimistic concurrency. When pessimistic concurrency control is being used, SQL Server controls concurrent access by using locks. Locks can be either shared or exclusive. An exclusive lock grants the user exclusive access to the data—no other user can access the data as long as the lock is held. Shared locks are used when some data is being read—multiple users can read from data locked with a shared lock, but not acquire an exclusive lock. The latter would have to wait for all shared locks to be released.

Locks can be applied on different levels of granularity—on entire tables, pages, or even on a per-row basis on tables. For indexes, it can either be on the entire index or on index leaves. The level of granularity to be used is defined on a per-database basis by the database administrator. While a fine-grained locking system allows more users to use the table or index simultaneously, it requires more resources, so it does not automatically yield higher performance. SQL Server also includes two more lightweight mutual exclusion solutions—latches and spinlocks—which are less robust than locks but are less resource intensive. SQL Server uses them for DMVs and other resources that are usually not busy. SQL Server also monitors all worker threads that acquire locks to ensure that they do not end up in deadlocks—in case they do, SQL Server takes remedial measures, which in many cases are to kill one of the threads entangled in a deadlock and roll back the transaction it started.[11] To implement locking, SQL Server contains the Lock Manager. The Lock Manager maintains an in-memory table that manages the database objects and locks, if any, on them along with other metadata about the lock. Access to any shared object is mediated by the lock manager, which either grants access to the resource or blocks it.

SQL Server also provides the optimistic concurrency control mechanism, which is similar to the multiversion concurrency control used in other databases. The mechanism allows a new version of a row to be created whenever the row is updated, as opposed to overwriting the row, i.e., a row is additionally identified by the ID of the transaction that created the version of the row. Both the old as well as the new versions of the row are stored and maintained, though the old versions are moved out of the database into a system database identified as Tempdb. When a row is in the process of being updated, any other requests are not blocked (unlike locking) but are executed on the older version of the row. If the other request is an update statement, it will result in two different versions of the rows—both of them will be stored by the database, identified by their respective transaction IDs.[11]

Data retrieval and programmability

[edit]

The main mode of retrieving data from a SQL Server database is querying for it. The query is expressed using a variant of SQL called T-SQL, a dialect Microsoft SQL Server shares with Sybase SQL Server due to its legacy. The query declaratively specifies what is to be retrieved. It is processed by the query processor, which figures out the sequence of steps that will be necessary to retrieve the requested data. The sequence of actions necessary to execute a query is called a query plan. There might be multiple ways to process the same query. For example, for a query that contains a join statement and a select statement, executing join on both the tables and then executing select on the results would give the same result as selecting from each table and then executing the join, but result in different execution plans. In such case, SQL Server chooses the plan that is expected to yield the results in the shortest possible time. This is called query optimization and is performed by the query processor itself.[11]

SQL Server includes a cost-based query optimizer which tries to optimize on the cost, in terms of the resources it will take to execute the query. Given a query, then the query optimizer looks at the database schema, the database statistics and the system load at that time. It then decides which sequence to access the tables referred in the query, which sequence to execute the operations and what access method to be used to access the tables. For example, if the table has an associated index, whether the index should be used or not: if the index is on a column which is not unique for most of the columns (low "selectivity"), it might not be worthwhile to use the index to access the data. Finally, it decides whether to execute the query concurrently or not. While a concurrent execution is more costly in terms of total processor time, because the execution is actually split to different processors might mean it will execute faster. Once a query plan is generated for a query, it is temporarily cached. For further invocations of the same query, the cached plan is used. Unused plans are discarded after some time.[11][33]

SQL Server also allows stored procedures to be defined. Stored procedures are parameterized T-SQL queries, that are stored in the server itself (and not issued by the client application as is the case with general queries). Stored procedures can accept values sent by the client as input parameters, and send back results as output parameters. They can call defined functions, and other stored procedures, including the same stored procedure (up to a set number of times). They can be selectively provided access to. Unlike other queries, stored procedures have an associated name, which is used at runtime to resolve into the actual queries. Also because the code need not be sent from the client every time (as it can be accessed by name), it reduces network traffic and somewhat improves performance.[34] Execution plans for stored procedures are also cached as necessary.

T-SQL

[edit]
Main article: T-SQL

T-SQL (Transact-SQL) is Microsoft's proprietary procedural language extension for SQL Server. It provides REPL (Read-Eval-Print-Loop) instructions that extend standard SQL's instruction set for Data Manipulation (DML) and Data Definition (DDL) instructions, including SQL Server-specific settings, security and database statistics management.

It exposes keywords for the operations that can be performed on SQL Server, including creating and altering database schemas, entering and editing data in the database as well as monitoring and managing the server itself. Client applications that consume data or manage the server will leverage SQL Server functionality by sending T-SQL queries and statements which are then processed by the server and results (or errors) returned to the client application. For this it exposes read-only tables from which server statistics can be read. Management functionality is exposed via system-defined stored procedures which can be invoked from T-SQL queries to perform the management operation. It is also possible to create linked Servers using T-SQL. Linked servers allow a single query to process operations performed on multiple servers.[35]

SQL Server Native Client (a.k.a. SNAC)

[edit]

SQL Server Native Client is the native client side data access library for Microsoft SQL Server, version 2005 onwards. It natively implements support for the SQL Server features including the Tabular Data Stream implementation, support for mirrored SQL Server databases, full support for all data types supported by SQL Server, asynchronous operations, query notifications, encryption support, as well as receiving multiple result sets in a single database session. SQL Server Native Client is used under the hood by SQL Server plug-ins for other data access technologies, including ADO or OLE DB. The SQL Server Native Client can also be directly used, bypassing the generic data access layers.[36]

On November 28, 2011, a preview release of the SQL Server ODBC driver for Linux was released.[37]

SQL CLR

[edit]
Main article: SQL CLR

Microsoft SQL Server 2005 includes a component named SQL CLR ("Common Language Runtime") via which it integrates with .NET Framework. Unlike most other applications that use .NET Framework, SQL Server itself hosts the .NET Framework runtime, i.e., memory, threading and resource management requirements of .NET Framework are satisfied by SQLOS itself, rather than the underlying Windows operating system. SQLOS provides deadlock detection and resolution services for .NET code as well. With SQL CLR, stored procedures and triggers can be written in any managed .NET language, including C# and VB.NET. Managed code can also be used to define UDT's (user defined types), which can persist in the database. Managed code is compiled to CLI assemblies and after being verified for type safety, registered at the database. After that, they can be invoked like any other procedure.[38] However, only a subset of the Base Class Library is available, when running code under SQL CLR. Most APIs relating to user interface functionality are not available.[38]

When writing code for SQL CLR, data stored in SQL Server databases can be accessed using the ADO.NET APIs like any other managed application that accesses SQL Server data. However, doing that creates a new database session, different from the one in which the code is executing. To avoid this, SQL Server provides some enhancements to the ADO.NET provider that allows the connection to be redirected to the same session which already hosts the running code. Such connections are called context connections and are set by setting context connection parameter to true in the connection string. SQL Server also provides several other enhancements to the ADO.NET API, including classes to work with tabular data or a single row of data as well as classes to work with internal metadata about the data stored in the database. It also provides access to the XML features in SQL Server, including XQuery support. These enhancements are also available in T-SQL Procedures in consequence of the introduction of the new XML Datatype (query, value, nodes functions).[39]

Service

[edit]

SQL Server also includes an assortment of add-on services. While these are not essential for the operation of the database system, they provide value added services on top of the core database management system. These services either run as a part of some SQL Server component or out-of-process as Windows Service and presents their own API to control and interact with them.

Machine Learning Services

[edit]

The SQL Server Machine Learning services operates within the SQL server instance, allowing people to do machine learning and data analytics without having to send data across the network or be limited by the memory of their own computers. The services come with Microsoft's R and Python distributions that contain commonly used packages for data science, along with some proprietary packages (e.g. revoscalepy, RevoScaleR, microsoftml) that can be used to create machine models at scale.

Analysts can either configure their client machine to connect to a remote SQL server and push the script executions to it, or they can run a R or Python scripts as an external script inside a T-SQL query. The trained machine learning model can be stored inside a database and used for scoring.[40]

Service Broker

[edit]

Used inside an instance, programming environment. For cross-instance applications, Service Broker communicates over TCP/IP and allows the different components to be synchronized, via exchange of messages. The Service Broker, which runs as a part of the database engine, provides a reliable messaging and message queuing platform for SQL Server applications.[41]

Service broker services consists of the following parts:[42]

  • message types
  • contracts
  • queues
  • service programs
  • routes

The message type defines the data format used for the message. This can be an XML object, plain text or binary data, as well as a null message body for notifications. The contract defines which messages are used in an conversation between services and who can put messages in the queue. The queue acts as storage provider for the messages. They are internally implemented as tables by SQL Server, but do not support insert, update, or delete functionality. The service program receives and processes service broker messages. Usually the service program is implemented as stored procedure or CLR application. Routes are network addresses where the service broker is located on the network.[42]

Also, service broker supports security features like network authentication (using NTLM, Kerberos, or authorization certificates), integrity checking, and message encryption.[42]

Replication Services

[edit]

SQL Server Replication Services are used by SQL Server to replicate and synchronize database objects, either in entirety or a subset of the objects present, across replication agents, which might be other database servers across the network, or database caches on the client side. Replication Services follows a publisher/subscriber model, i.e., the changes are sent out by one database server ("publisher") and are received by others ("subscribers"). SQL Server supports three different types of replication:[43]

Transaction replication
Each transaction made to the publisher database (master database) is synced out to subscribers, who update their databases with the transaction. Transactional replication synchronizes databases in near real time.[44]
Merge replication
Changes made at both the publisher and subscriber databases are tracked, and periodically the changes are synchronized bi-directionally between the publisher and the subscribers. If the same data has been modified differently in both the publisher and the subscriber databases, synchronization will result in a conflict which has to be resolved, either manually or by using pre-defined policies.Rowguid needs to be configured on a column if merge replication is configured.[45]
Snapshot replication
Snapshot replication publishes a copy of the entire database (the then-snapshot of the data) and replicates out to the subscribers. Further changes to the snapshot are not tracked.[46]

Analysis Services

[edit]
Main article: SQL Server Analysis Services

SQL Server Analysis Services (SSAS) adds OLAP and data mining capabilities for SQL Server databases. The OLAP engine supports MOLAP, ROLAP and HOLAP storage modes for data. Analysis Services supports the XML for Analysis standard as the underlying communication protocol. The cube data can be accessed using MDX and LINQ[47] queries.[48] Data mining specific functionality is exposed via the DMX query language. Analysis Services includes various algorithms—Decision trees, clustering algorithm, Naive Bayes algorithm, time series analysis, sequence clustering algorithm, linear and logistic regression analysis, and neural networks—for use in data mining.[49]

Reporting Services

[edit]
Main article: SQL Server Reporting Services

SQL Server Reporting Services (SSRS) is a report generation environment for data gathered from SQL Server databases. It is administered via a web interface. Reporting services features a web services interface to support the development of custom reporting applications. Reports are created as RDL files.[50]

Reports can be designed using recent versions of Microsoft Visual Studio (Visual Studio.NET 2003, 2005, and 2008)[51] with Business Intelligence Development Studio, installed or with the included Report Builder. Once created, RDL files can be rendered in a variety of formats,[52][53] including Excel, PDF, CSV, XML, BMP, EMF, GIF, JPEG, PNG, and TIFF,[54] and HTML Web Archive.

Notification Services

[edit]
Main article: SQL Server Notification Services

Originally introduced as a post-release add-on for SQL Server 2000,[55] Notification Services was bundled as part of the Microsoft SQL Server platform for the first and only time with SQL Server 2005.[56][57] SQL Server Notification Services is a mechanism for generating data-driven notifications, which are sent to Notification Services subscribers. A subscriber registers for a specific event or transaction (which is registered on the database server as a trigger); when the event occurs, Notification Services can use one of three methods to send a message to the subscriber informing about the occurrence of the event. These methods include SMTP, SOAP, or by writing to a file in the filesystem.[58] Notification Services was discontinued by Microsoft with the release of SQL Server 2008 in August 2008, and is no longer an officially supported component of the SQL Server database platform.

Integration Services

[edit]
Main article: SQL Server Integration Services

SQL Server Integration Services (SSIS) provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs. Integration Services includes GUI tools to build workflows such as extracting data from various sources, querying data, transforming data—including aggregation, de-duplication, de-/normalization and merging of data—and then exporting the transformed data into destination databases or files.[59]

Full Text Search Service

[edit]
The SQL Server Full Text Search service architecture

SQL Server Full Text Search service is a specialized indexing and querying service for unstructured text stored in SQL Server databases. The full text search index can be created on any column with character based text data. It allows for words to be searched for in the text columns. While it can be performed with the SQL LIKE operator, using SQL Server Full Text Search service can be more efficient. Full allows for inexact matching of the source string, indicated by a Rank value which can range from 0 to 1000—a higher rank means a more accurate match. It also allows linguistic matching ("inflectional search"), i.e., linguistic variants of a word (such as a verb in a different tense) will also be a match for a given word (but with a lower rank than an exact match). Proximity searches are also supported, i.e., if the words searched for do not occur in the sequence they are specified in the query but are near each other, they are also considered a match. T-SQL exposes special operators that can be used to access the FTS capabilities.[60][61]

The Full Text Search engine is divided into two processes: the Filter Daemon process (msftefd.exe) and the Search process (msftesql.exe). These processes interact with the SQL Server. The Search process includes the indexer (that creates the full text indexes) and the full text query processor. The indexer scans through text columns in the database. It can also index through binary columns, and use iFilters to extract meaningful text from the binary blob (for example, when a Microsoft Word document is stored as an unstructured binary file in a database). The iFilters are hosted by the Filter Daemon process. Once the text is extracted, the Filter Daemon process breaks it up into a sequence of words and hands it over to the indexer. The indexer filters out noise words, i.e., words like A, And, etc., which occur frequently and are not useful for search. With the remaining words, an inverted index is created, associating each word with the columns they were found in. SQL Server itself includes a Gatherer component that monitors changes to tables and invokes the indexer in case of updates.[62]

When a full text query is received by the SQL Server query processor, it is handed over to the FTS query processor in the Search process. The FTS query processor breaks up the query into the constituent words, filters out the noise words, and uses an inbuilt thesaurus to find out the linguistic variants for each word. The words are then queried against the inverted index and a rank of their accurateness is computed. The results are returned to the client via the SQL Server process.[62]

SQLCMD

[edit]

SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt. It can also act as a scripting language to create and run a set of SQL statements as a script. Such scripts are stored as a .sql file, and are used either for management of databases or to create the database schema during the deployment of a database.

SQLCMD was introduced with SQL Server 2005 and has continued through SQL Server versions 2008, 2008 R2, 2012, 2014, 2016 and 2019. Its predecessor for earlier versions was OSQL and ISQL, which were functionally equivalent as it pertains to T-SQL execution, and many of the command line parameters are identical, although SQLCMD adds extra versatility.

Visual Studio

[edit]
Main article: Microsoft Visual Studio

Microsoft Visual Studio includes native support for data programming with Microsoft SQL Server. It can be used to write and debug code to be executed by SQL CLR. It also includes a data designer that can be used to graphically create, view or edit database schemas. Queries can be created either visually or using code. SSMS 2008 onwards, provides intellisense for SQL queries as well.

SQL Server Management Studio

[edit]
Main article: SQL Server Management Studio

SQL Server Management Studio is a GUI tool included with SQL Server 2005 and later for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools that work with objects and features of the server.[63] SQL Server Management Studio replaces Enterprise Manager as the primary management interface for Microsoft SQL Server since SQL Server 2005. A version of SQL Server Management Studio is also available for SQL Server Express Edition, for which it is known as SQL Server Management Studio Express (SSMSE).[64]

A central feature of SQL Server Management Studio is the Object Explorer, which allows the user to browse, select, and act upon any of the objects within the server.[65] It can be used to visually observe and analyze query plans and optimize the database performance, among others.[66] SQL Server Management Studio can also be used to create a new database, alter any existing database schema by adding or modifying tables and indexes, or analyze performance. It includes the query windows which provide a GUI based interface to write and execute queries.[11]

Azure Data Studio

[edit]

Azure Data Studio is a cross platform query editor available as an optional download. The tool allows users to write queries; export query results; commit SQL scripts to Git repositories and perform basic server diagnostics. Azure Data Studio supports Windows, Mac and Linux systems.[67]

It was released to General Availability in September 2018. Prior to release the preview version of the application was known as SQL Server Operations Studio.

Business Intelligence Development Studio

[edit]
Main article: Business Intelligence Development Studio

Business Intelligence Development Studio (BIDS) is the IDE from Microsoft used for developing data analysis and Business Intelligence solutions utilizing the Microsoft SQL Server Analysis Services, Reporting Services and Integration Services. It is based on the Microsoft Visual Studio development environment but is customized with the SQL Server services-specific extensions and project types, including tools, controls and projects for reports (using Reporting Services), Cubes and data mining structures (using Analysis Services).[68] For SQL Server 2012 and later, this IDE has been renamed SQL Server Data Tools (SSDT).

See also

[edit]

References

[edit]

Further reading

[edit]
[edit]
Revisions and contributorsEdit on WikipediaRead on Wikipedia

Microsoft SQL Server

View on Grokipedia
from Grokipedia
Microsoft SQL Server is a management system (RDBMS) developed by , designed to store and retrieve data as requested by other software applications via (T-SQL), a Microsoft-specific implementation of the SQL standard. At its core, SQL Server's handles storage, processing, and security for relational, , spatial, and XML data, while additional services enable advanced analytics, , and reporting. Key components include Integration Services (SSIS) for data extraction, transformation, and loading (ETL); Analysis Services (SSAS) for modeling; Machine Learning Services supporting and Python scripting; and Reporting Services (SSRS), which in SQL Server 2025 (17.x) are consolidated under Power BI Report Server, with use rights included in Standard and Enterprise editions. The product traces its origins to 1989, when partnered with Sybase and to develop the initial version as a 16-bit server for , evolving through numerous releases to become a cornerstone of enterprise . Supported versions range from SQL Server 2016 (13.x) onward, with SQL Server 2025 (17.x) as the current major release, available under both perpetual and subscription licensing models, offering editions like Enterprise for high-performance workloads and Standard for departmental use. SQL Server deploys on-premises on Windows and , in containers, or in the cloud via Azure Virtual Machines and Azure Arc for hybrid and multicloud scenarios, with deep integration to Azure services like Synapse Analytics.

Overview

Introduction

Microsoft SQL Server is a management system (RDBMS) developed by since 1989, designed for the storage, retrieval, and management of structured data. It adheres to the SQL standard while extending it through (T-SQL), a procedural extension that enables advanced querying, programming, and administration. As a core component of 's data platform, SQL Server facilitates efficient data handling in diverse environments, from on-premises servers to deployments. The system supports key use cases such as (OLTP) for high-volume transactional workloads, (OLAP) for complex data analysis, and data warehousing for large-scale . It integrates seamlessly with the Microsoft ecosystem, including Azure services for hybrid cloud scenarios and .NET frameworks for application development. At its foundation, SQL Server employs a client-server architecture, where client applications connect to server instances over networks to execute queries and manage data. Licensing follows per-core or server plus Client Access License (CAL) models, offering flexibility for varying deployment scales, with free editions like Developer and Express available for non-production development and small-scale applications. SQL Server holds a prominent position in enterprise , valued for its scalability to handle petabyte-scale workloads, advanced features like Always Encrypted, and tight integration with tools. It powers mission-critical applications across industries, enabling organizations to derive insights from while maintaining high availability and performance.

Key Features

Microsoft SQL Server offers robust scalability features to handle large-scale deployments. It supports databases up to 524,272 terabytes (524 PB) in size, enabling organizations to manage petabyte-scale volumes within a single instance. Always On Availability Groups provide by allowing up to eight secondary replicas, with five supporting synchronous replication for minimal , and facilitate automatic , with replicas distributed across nodes in a Windows Server Cluster supporting up to 64 nodes. SQL Server supports table and index partitioning to distribute across multiple filegroups within a database, improving query and manageability for very large tables. Horizontal scaling can be achieved through features like Availability Groups or manual sharding across instances. Performance optimizations in SQL Server focus on accelerating both transactional and analytical workloads. In-Memory OLTP, originally developed under the project name Hekaton, stores tables in memory to eliminate disk I/O bottlenecks, delivering up to 30 times faster for high-throughput scenarios like data ingestion and session management. Columnstore indexes organize data in columnar format for analytics, achieving up to 10 times compression and 2-4 times query speed improvements through batch-mode execution, making them ideal for data warehousing on fact tables. Intelligent Query Processing further enhances efficiency with features like adaptive joins, which dynamically switch between nested loop and algorithms at runtime based on actual data , reducing execution time without manual tuning. Security in SQL Server emphasizes protection at rest, in transit, and during access. (TDE) encrypts entire databases at the file level, safeguarding against unauthorized access to storage media without impacting application performance. Row-level security (RLS) restricts access at the row level using policies tied to user context or group membership, enabling fine-grained control for multi-tenant applications. Dynamic masking (DDM) obscures sensitive in query results for non-privileged users by applying predefined masking rules, such as partial or , to simplify compliance without altering stored . SQL Server integrates natively with modern data formats and hybrid environments. It provides built-in support for through functions like JSON_VALUE and OPENJSON, allowing efficient storage, querying, and modification of with indexing for performance. XML support includes the XML for storing and querying hierarchical data, with capabilities and schema validation to ensure integrity in document-centric applications. Geospatial data types, such as and , enable storage and analysis of location-based data, supporting operations like distance calculations and spatial indexing for GIS applications. For hybrid cloud connectivity, Azure Arc extends Azure management to on-premises SQL Server instances, enabling unified monitoring, security scanning, and policy enforcement across environments. SQL Server 2025 introduces AI-ready features, including a native vector data type for storing embeddings, T-SQL functions for vector similarity search, and direct integration with Azure OpenAI for generative AI tasks within the database. Unique integrations with Microsoft ecosystem tools enhance SQL Server's utility in enterprise analytics. It connects seamlessly with Power BI for direct querying and visualization, supporting live connections via gateways for on-premises data without importing datasets. Azure Synapse Link provides near-real-time analytics by replicating operational data from SQL Server (2022 and later) to Synapse workspaces, enabling HTAP (hybrid transactional/analytical processing) without ETL overhead. Authentication integrates with Windows Active Directory (now Microsoft Entra ID) for secure, centralized identity management, supporting integrated logins and role-based access across on-premises and cloud resources.

History

Origins and Early Development

In 1988, Microsoft entered into a partnership with Sybase and Ashton-Tate to develop a relational database management system (RDBMS) based on Sybase's SQL Server, initially targeting the OS/2 operating system as a joint product known as Ashton-Tate/Microsoft SQL Server. This collaboration aimed to provide Microsoft with a competitive RDBMS to challenge established players in the database market. The first versions, such as SQL Server 1.0 in 1989, were 16-bit implementations for OS/2, leveraging Sybase's core engine while integrating with Microsoft's ecosystem. By 1993, following the release of , Microsoft launched its first standalone, Microsoft-branded version of SQL Server 4.21, which was a port of the Sybase codebase optimized for the platform. This release marked the product's shift toward Windows-centric deployment, taking advantage of NT's multitasking and security features, though it retained significant Sybase heritage in its query processing and storage mechanisms. The partnership with Sybase began to diverge around this time, as Microsoft acquired a non-exclusive for the source code in 1993, allowing independent development while Sybase continued its Unix-focused trajectory. In 1995, Microsoft released SQL Server 6.0, a substantial rewrite tailored exclusively for , which introduced key programmability features like stored procedures and triggers to enhance application integration and . This version improved performance over its Sybase-derived predecessors by optimizing for NT's kernel and adding support for distributed transactions, positioning SQL Server as a viable enterprise option within the Windows environment. By 1996, had achieved full ownership and control over its SQL Server codebase, severing remaining ties with Sybase and enabling a dedicated focus on Windows-native innovations. The 1998 release of SQL Server 7.0 represented a pivotal milestone, featuring a complete architectural overhaul that eliminated dependencies on legacy indexed sequential access methods (ISAM) and adopted a more modular, scalable design using C++ for the core engine. It supported databases up to 32 GB in size and introduced OLAP Services for analytical processing, allowing multidimensional data analysis directly within the SQL Server environment. Throughout its early years, SQL Server faced significant challenges in competing with , which dominated on Unix platforms with superior for large-scale deployments. Microsoft's emphasis on tight Windows integration provided ease of use for NT users but initially limited adoption in heterogeneous or high-volume Unix environments, where SQL Server's performance and clustering capabilities lagged behind rivals.

Major Version Releases

SQL Server 2000 marked a significant milestone in the product's evolution, with native XML support, allowing users to store, retrieve, and query XML data directly within the database using and XML data type, facilitating web-centric applications. English Query enabled querying, permitting users to pose questions in that the system translated into SQL statements for easier data access. Merge replication was added as a new replication type, supporting offline synchronization for mobile and distributed environments by resolving conflicts during data resubmission. SQL Server 2005 introduced (CLR) integration, enabling the execution of .NET Framework code such as stored procedures, functions, triggers, and user-defined types directly within the database for enhanced programmability and performance in complex computations. Service Broker was launched as a native messaging framework for building scalable, asynchronous applications with reliable message queuing and conversation management inside the database. Notification Services provided a platform for event-driven notifications, allowing applications to monitor data changes and deliver alerts via , , or other channels based on predefined rules. The release also added support for 64-bit processors and extended memory capacity to 64 GB on 32-bit systems, improving scalability for large-scale deployments. SQL Server 2008 and its update SQL Server 2008 R2 advanced with database mirroring, an early precursor to Always On technologies, enabling automatic for databases across servers. FILESTREAM was introduced to efficiently store and manage unstructured data like files alongside relational data, integrating binary large objects (BLOBs) with for better performance and transactional consistency. The Resource Governor provided workload management by classifying sessions and limiting resource usage, ensuring predictable performance in multi-tenant environments. SQL Server 2012 and 2014 brought columnstore indexes, which compressed data column-wise to accelerate analytical queries by up to 10 times compared to rowstore indexes, ideal for data warehousing. In-memory OLTP (Hekaton) was previewed, allowing tables to reside in memory for lock-free, latch-free transactions that reduced latency for high-throughput OLTP workloads. Always On Availability Groups succeeded , offering flexible clustering with readable secondaries for load balancing. Extended Events replaced SQL Trace for lightweight diagnostics, capturing detailed event data with minimal overhead for . SQL Server 2016 enhanced query optimization and scalability, while SQL Server 2017 added support for , allowing deployment on and for hybrid environments. SQL Server 2019 introduced intelligent query processing, using adaptive techniques like batch mode on rowstore and approximate query processing to automatically improve performance without manual tuning. clusters integrated SQL Server with Spark and HDFS for scalable analytics on Hadoop ecosystems. collation support enabled storage of international data more efficiently, and ledger tables provided tamper-evident auditing for data integrity. SQL Server 2022 focused on hybrid and cloud integration with Azure Link, enabling real-time analytics between SQL Server and Azure without ETL processes. Query Store hints allowed developers to override optimizer choices for specific queries, improving control over . Enhanced features, including for blockchain-like auditing, ensured verifiable data history and immutability against tampering. Additionally, SQL Server 2022 integrates with Fabric, enabling unified data analytics across on-premises and cloud environments through features like external tables and data mirroring for real-time synchronization.

Recent Developments and 2025 Preview

As of November 2025, SQL Server 2019 has transitioned out of mainstream support, which ended on February 28, 2025, with extended support continuing until January 8, 2030. SQL Server 2022 remains in mainstream support until January 11, 2028, followed by extended support through January 11, 2033. Microsoft encourages organizations running older versions to migrate to Azure SQL Database or Managed Instance for seamless access to ongoing security updates, performance enhancements, and cloud-native scalability without on-premises hardware constraints. Post-release updates for SQL Server 2022 have focused on advancing intelligent query processing. Additionally, SQL Server 2022 integrates with Fabric, enabling unified data analytics across on-premises and cloud environments through features like external tables and data mirroring for real-time synchronization. SQL Server 2025 (version 17.x), announced in public preview on May 19, 2025, builds on these foundations with native enhancements for AI and hybrid deployments. Key additions include built-in vector data types optimized for applications, supporting efficient storage, indexing, and similarity searches using algorithms like approximate nearest neighbors (ANN). The release emphasizes a unified and feature parity between on-premises, Azure SQL, and edge environments, reducing migration friction. Improved integration with Fabric allows for multi-database querying via database mirroring, enabling seamless data movement to OneLake for advanced analytics without replication overhead. Looking ahead, SQL Server's future directions prioritize hybrid cloud capabilities through Azure Arc, which extends Azure management, monitoring, and security to on-premises SQL Server instances for consistent governance across environments. is also advancing initiatives, such as query optimizations that reduce computational overhead and in data centers, aligning with broader corporate goals for carbon efficiency. The SQL Server ecosystem continues to grow, with adoption reaching approximately 25% of monitored instances for the 2022 version as of late 2025, reflecting strong enterprise uptake for mission-critical workloads. Over 100,000 enterprises rely on SQL Server for relational data management, bolstered by updated certifications like the Microsoft Certified: Azure Database Administrator Associate, now incorporating SQL Server 2025-specific AI and hybrid skills. In response to cloud-native alternatives like , Microsoft has enhanced SQL Server's developer tools and open compatibility features to maintain competitiveness in polyglot environments.

Editions

Current Mainstream Editions

Microsoft SQL Server offers several mainstream editions designed for production environments, catering to varying scales of deployment from small applications to enterprise-level systems. These editions share core capabilities but differ in feature sets, resource limits, and licensing models to align with different organizational needs.

Express Edition

The Express Edition is a free, entry-level version intended for lightweight applications, development, and small-scale production use. It includes essential features such as the Database Engine, full-text search, and basic integration with SQL Server Management Studio (SSMS) for management. It supports in-memory OLTP and columnstore indexing, limited by the edition's memory constraints. Key limitations include a maximum database size of 10 GB per database, support for up to 4 cores (or 1 socket), and 1410 MB of RAM for the buffer pool. This edition does not include advanced tools like SQL Server Agent for job scheduling. It supports basic high availability via log shipping and database mirroring (witness only).

Standard Edition

Standard Edition serves as a mid-tier option suitable for departmental and small-to-medium business workloads, providing robust data management, , and basic features. It supports up to 128 GB of RAM for the buffer pool, a maximum of 32 cores (or 4 sockets), and includes capabilities like Always On Basic Availability Groups and log shipping for failover clustering. It includes limited in-memory OLTP (up to 32 GB memory-optimized data) and columnstore indexing (32 GB segment cache). Database size is limited to 524 PB, aligning with modern storage needs. This edition balances cost and functionality for non-mission-critical environments.

Enterprise Edition

Enterprise Edition is the flagship offering for mission-critical, high-volume workloads, delivering comprehensive features for , , and optimization. It includes advanced through Always On Availability Groups, columnstore indexing for analytics, big data clusters integration, and in-memory OLTP for real-time processing, with no imposed limits on cores or RAM beyond the operating system's capacity. This edition supports unlimited and is optimized for data warehousing and AI-driven applications in SQL Server 2025. Database size reaches up to 524 PB, enabling handling of massive datasets.

Web Edition

The Web Edition, which was available in previous versions such as SQL Server 2022 for hosting providers and web application scenarios under the Services Provider License Agreement (SPLA), has been discontinued in SQL Server 2025. SQL Server 2025 supports both perpetual and subscription licensing models for its mainstream editions. Perpetual licensing involves a one-time purchase for indefinite use of a specific version, available primarily for on-premises deployments. Optional Software Assurance (SA) can be added at the time of purchase to provide benefits such as new version rights, unlimited virtualization rights (for Enterprise Edition), and failover rights. Subscription licensing is time-based (such as 1-year or 3-year terms) through Cloud Solution Provider (CSP) partners or pay-as-you-go via Azure Arc-enabled SQL Server. Subscription licenses include benefits equivalent to Software Assurance, such as Azure Hybrid Benefit, failover rights, and virtualization rights. The core licensing options remain Per Core (available for both Enterprise and Standard Editions) or Server + CAL (available only for Standard Edition). There is no major shift away from perpetual licensing, as both models coexist to provide flexibility. Express Edition remains free with no additional costs or CALs required. With the release of SQL Server 2025, Power BI Report Server use rights are included with both Enterprise and Standard Editions, even without active Software Assurance. All editions benefit from Azure Hybrid Benefit, allowing on-premises licenses to reduce costs in Azure deployments, and volume licensing programs provide flexibility for enterprises.
EditionMax Database SizeMax RAM (Buffer Pool)Max Cores/SocketsKey High Availability FeaturesLicensing Model
Express10 GB1410 MB4 / 1Log shipping, Database mirroring (witness only)Free
Standard524 PB128 GB32 / 4Basic Availability Groups, Log ShippingPer-core or Server + CAL
Enterprise524 PBOS MaxOS MaxAlways On Availability GroupsPer-core

Developer and Specialized Editions

The Developer Edition of Microsoft SQL Server is a free offering designed exclusively for development, testing, and demonstration purposes, providing developers with access to core database functionalities without production licensing restrictions. It includes all the features available in the Enterprise Edition but is explicitly prohibited from use in production environments, making it ideal for building applications, prototyping, and internal evaluations. This edition supports unlimited database sizes and is commonly utilized by independent developers, small teams, and educational institutions to experiment with SQL Server capabilities in non-commercial scenarios. In SQL Server 2025, introduced a distinction within the Developer Edition lineup to better align testing with production tiers: the Enterprise Developer Edition retains full access to advanced Enterprise features such as intelligent query processing and options, while the new Standard Developer Edition limits functionality to Standard Edition capabilities, including up to 128 GB of RAM and 32 CPU cores. This split enables more precise replication of tiered environments during development, reducing discrepancies between test and live deployments, and supports broader adoption in continuous integration/continuous deployment () pipelines where resource constraints mirror Standard production limits. The Standard Developer Edition is particularly beneficial for vendors and organizations testing applications against cost-optimized configurations without needing full Enterprise entitlements. Complementing the Developer Edition, the Evaluation Edition serves as a time-limited version of the full Enterprise Edition, allowing users to assess advanced features for up to 180 days before the instance automatically disables. It provides unrestricted access to all Enterprise functionalities during the trial period, making it suitable for proofs-of-concept, pilot projects, and short-term evaluations of high-scale scenarios like AI-integrated applications. Post-trial, users must upgrade to a licensed edition or reinstall, ensuring compliance with production use cases. For specialized scenarios, has offered editions tailored to embedded or analytics-focused needs, though some have transitioned to legacy or cloud-integrated support. Edition, a file-based, engine, was designed for lightweight applications on mobile devices and desktops, supporting with full SQL Server instances without requiring a separate server . However, it has been discontinued, with mainstream support ending in July 2021 and extended support concluding thereafter, leaving it available only for legacy in existing deployments. The Parallel Data Warehouse (PDW) edition, now integrated into the Analytics Platform System (APS) and Azure Synapse Analytics, provides massively parallel processing (MPP) capabilities optimized for large-scale data warehousing and workloads. It enables distributed query execution across appliance hardware or resources, handling petabyte-scale datasets with features like columnstore indexing and external table support for hybrid . As of 2025, PDW services remain operational within APS configurations, focusing on on-premises appliances that can federate with Azure for scalable, high-performance querying. Licensing guidelines for these editions strictly enforce non-production boundaries, prohibiting the use of real or live workloads to avoid compliance issues; instead, they emphasize synthetic datasets in automation, academic training, and vendor demonstrations. In 2025, enhancements include companion free trials for Azure SQL Database, allowing seamless hybrid testing between on-premises Developer Editions and environments for modern application development.

Discontinued Editions

The Workgroup Edition, introduced with SQL Server 2005, was designed as an affordable and easy-to-manage database solution for small to medium-sized organizations, providing core database functionality without advanced services like Analysis Services or Reporting Services. It supported basic features such as merge replication publishing and was limited to 4 GB per database, targeting environments with limited hardware resources like single or dual CPUs and up to 3 GB of memory. This edition was discontinued starting with SQL Server 2008 and replaced by the free Express Edition, which offered similar entry-level capabilities with expanded limits. The Standard x86 Edition, a 32-bit version available through SQL Server 2016, catered to departmental and small-scale deployments but became obsolete as Microsoft shifted focus to 64-bit architectures for better performance and scalability. Support for x86 installations ended after SQL Server 2016, with Microsoft recommending migration to the 64-bit Standard Edition to leverage modern hardware and avoid compatibility issues. The Datacenter Edition, offered in SQL Server 2008 and 2012, was an unlimited RAM variant of the Enterprise Edition, supporting up to 256 logical processors and designed for large-scale, mission-critical data warehousing and consolidation scenarios. It included all Enterprise features plus enhanced scalability for high-volume environments. This edition was retired with SQL Server 2014, with its capabilities fully consolidated into the Enterprise Edition to streamline licensing and reduce redundancy. SQL Server Compact Edition 3.5 and 4.0 provided embedded database functionality for .NET applications, enabling lightweight, file-based storage without a full server installation, suitable for mobile and desktop scenarios. Mainstream support for version 3.5 ended on April 9, 2013, while extended support for version 4.0 concluded on July 13, 2021. recommends migrating to alternatives like for open-source embedded needs or Azure SQL for edge computing deployments, as Compact no longer receives updates or security patches. For migrations from these discontinued editions, Microsoft provides tools such as the Data Migration Assistant (DMA) to assess compatibility and upgrade schemas, data, and objects to current editions like Express or Standard, ensuring minimal downtime and feature preservation. For SQL Server 2014, mainstream support ended on July 9, 2019, with extended support concluding in 2024, prompting upgrades via DMA to maintain security and performance.

Architecture

Core Components

The Relational Database Engine serves as the primary component of Microsoft SQL Server, responsible for parsing, optimizing, and executing queries to manage data operations. It processes incoming queries by first parsing them into a logical structure, then optimizing them through the query optimizer to generate efficient execution plans based on database statistics and information. The engine subsequently executes these plans, coordinating with other components to retrieve or modify data while ensuring transaction integrity and . Additionally, it incorporates a protocol layer utilizing the (TDS) protocol, which enables communication between client applications and the SQL Server instance by formatting data streams for efficient transfer over networks. SQL Server relies on several system databases to maintain instance configuration and support operational tasks. The master database stores system-level metadata, including information on all databases, logins, and server configurations, functioning as the central repository for the entire instance. The model database acts as a template for creating new user databases, defining default attributes such as file sizes, collation settings, and recovery models that are inherited by newly created databases. The msdb database manages SQL Server Agent components, storing details for scheduled jobs, alerts, and operator notifications to facilitate automated maintenance and monitoring. The tempdb database provides temporary storage for transient objects like temporary tables, table variables, and intermediate query results, with its contents recreated each time the instance starts. Finally, the resource database is a read-only repository containing all system objects, such as stored procedures and views, that appear in the sys schema across every database, ensuring consistency without duplication. The Storage Engine in SQL Server manages physical and retrieval, supporting a hybrid approach that combines row-based (rowstore) and column-based (columnstore) formats to accommodate diverse workloads. Rowstore is optimized for transactional processing with frequent updates and point queries, storing data in rows for efficient access to related columns, while columnstore excels in analytical scenarios by compressing and scanning large datasets column-wise for faster aggregations and reduced I/O. This hybrid model allows tables to use either format via indexes, enabling SQL Server to handle both OLTP and OLAP operations within the same instance. For tempdb specifically, the engine employs specialized mechanisms to handle temporary data efficiently, including spill-to-tempdb operations during query execution when memory limits are reached. Metadata management in SQL Server is facilitated through catalog views and dynamic management views (DMVs), providing structured access to system information for administrative and diagnostic purposes. Catalog views, such as those in the sys schema (e.g., sys.tables, sys.columns), offer a , forward-compatible interface to query persistent metadata about databases, objects, and configurations, replacing older system tables for better reliability. DMVs, prefixed with sys.dm_ (e.g., sys.dm_exec_sessions, sys.dm_os_performance_counters), deliver real-time, dynamic insights into server state, including query , usage, and locking details, aiding in and optimization without requiring restarts. These views enable and monitoring, with permissions typically requiring VIEW SERVER STATE or higher for comprehensive access. SQL Server's instance architecture supports flexibility in deployment, allowing a single server to host one default instance or multiple named instances, each running as an independent service (sqlservr.exe). The default instance listens on port 1433 by default and does not require a name in connection strings, whereas named instances use dynamic ports or configurable static ports and must be specified in connections (e.g., servername\instancename). Resources such as memory, CPU threads, and disk space can be shared across instances on the same hardware but are isolated per instance for security and performance, with configurable limits via server options or Resource Governor to prevent contention. Resource Governor manages SQL Server resource usage by classifying sessions into resource pools (e.g., the default pool) and workload groups, applying limits such as MAX_MEMORY_PERCENT to control allocation; the default pool restricts memory for user queries to prevent overuse, which can lead to error 701 if a query demands more than the allocated amount, even with sufficient total server RAM.

Data Storage Mechanisms

SQL Server organizes its databases into physical files that store data and transaction logs separately to ensure durability and recoverability. The primary data file, typically with a .mdf extension, contains the startup information for the database and serves as the main repository for user data and database objects such as tables and indexes. Secondary data files, using the .ndf extension, can be added to expand storage capacity and contain additional data or indexes. Transaction log files, identified by the .ldf extension, record all modifications to ensure atomicity and . Databases can be partitioned into filegroups, such as the default PRIMARY filegroup or user-defined ones, which group logical files for placement on specific disk volumes to optimize performance and manage storage. At the lowest level, SQL Server manages data in fixed-size units called pages, each 8 KB in size, which form the basic I/O boundary for reading and writing to disk. Pages are allocated in groups known as extents, consisting of eight contiguous 8 KB pages totaling 64 KB, to reduce fragmentation and improve efficiency. Allocation of these structures is tracked using specialized pages: the Global Allocation Map (GAM) records which extents are allocated, the Shared Global Allocation Map (SGAM) identifies extents with at least one free page for mixed usage, and Index Allocation Maps (IAM) chain the extents belonging to specific objects like tables or indexes. The buffer pool serves as SQL Server's primary memory cache for data pages, loading them from disk as needed to minimize physical I/O operations during query execution. To maintain available space in the buffer pool, the lazy writer process periodically scans for infrequently used or modified (dirty) pages and writes them to disk asynchronously, avoiding the need for constant checkpoints. Checkpoints flush dirty pages to disk at intervals, ensuring that the can be truncated up to the target recovery time objective set for the database, which controls the maximum recovery duration after a failure. SQL Server employs a concurrency model that balances isolation and performance through locking, row versioning, and deadlock prevention mechanisms. Locks are acquired in shared mode for read operations to allow concurrent access or in exclusive mode for writes to prevent conflicts, with granularities ranging from row to table level. For higher concurrency without blocking readers, snapshot isolation uses row versioning to maintain a consistent view of data as of the transaction's start, storing versions in the tempdb database. Deadlocks, where transactions cyclically wait for each other's locks, are detected by analyzing the and resolved by automatically rolling back one victim transaction with minimal cost. Latch-free structures, such as those in memory-optimized tables, further enhance scalability by avoiding traditional latches in favor of . In deployments of SQL Server on Azure Virtual Machines, Microsoft recommends placing tempdb files on the ephemeral (temporary) local SSD disk, typically at D:, to benefit from its low latency and high IOPS for enhanced performance. The operating system pagefile may optionally be relocated there as well. However, ephemeral storage lacks durability, with data loss occurring upon VM restart or deallocation, so it should not be used for user databases, transaction logs, or backups. For persistent storage requirements, attach Premium SSD or Ultra Disks instead. Administrators should monitor tempdb usage and scale to larger VM sizes if resource limits are approached. Indexes in SQL Server provide efficient access by organizing rows in structured formats, with the clustered index serving as the default storage mechanism for tables. A clustered index rearranges the actual rows on disk according to the index key values using a structure, allowing only one per table as it defines the physical order. Nonclustered indexes maintain a separate of key values pointing to the rows, enabling multiple per table or heap without altering the underlying storage. Filtered indexes apply a WHERE to index only a subset of rows, reducing storage and improving performance for selective queries. Tables without a clustered index are stored as heaps, where rows are unmanaged in terms of order, relying on nonclustered indexes for access.

Query Processing and Execution

SQL Server's query processing begins with the parsing and algebrizer phases, which prepare Transact-SQL (T-SQL) statements for optimization. The parser scans the input query, breaking it into logical units such as keywords, expressions, operators, and identifiers, while performing syntax checking to ensure compliance with SQL grammar rules. It then constructs a parse tree representing the query's structure. The algebrizer takes this parse tree and converts it into a relational algebra representation, resolving object names to database schema definitions, validating object existence, permissions, and data types, and performing semantic analysis. This bound tree, or algebrizer tree, serves as input to the query optimizer, ensuring the query is both syntactically and semantically valid. The query optimizer employs cost-based optimization to generate efficient execution plans. It evaluates thousands of potential plans, estimating costs in terms of computing resources like CPU, I/O, and , and selects the one with the lowest estimated . Central to this process is estimation, which predicts the number of rows processed at each plan step using statistics on data distribution, , and selectivity from column histograms and index metadata. These statistics, automatically maintained or manually updated, help the optimizer choose access methods such as index scans or seeks, join strategies (e.g., hash or merge joins), and aggregation operators. Adaptive query processing features, like batch mode on rowstore introduced in SQL Server 2019, enable vector-based execution on traditional rowstore tables to leverage modern hardware for improved performance in analytical workloads. Once generated, the execution plan is handed to the execution engine, which processes data through a series of operators implemented as iterators. These iterators handle operations like scans, joins, and aggregates, pulling and pushing rows as needed. Parallelism is supported via the degree of parallelism (DOP), configurable at the server, database, or query level through the MAXDOP setting, allowing multiple threads to execute plan portions simultaneously for resource-intensive queries exceeding the cost threshold for parallelism (default 5). Exchange operators manage data redistribution and flow control in parallel plans. If memory is insufficient for hash joins or sorts, the engine spills intermediate results to tempdb, using worktables for operations like GROUP BY or ORDER BY. To promote efficiency, SQL Server caches execution plans for reuse, reducing compilation overhead. Ad-hoc queries generate plans stored in the SQL Plans cache, initially as lightweight stubs under the "optimize for ad hoc workloads" option to mitigate cache bloat, expanding to full plans on reuse. Prepared plans, created via APIs for parameterized queries, are cached with handles for rapid reuse. Forced parameterization, enabled at the database level with ALTER DATABASE ... PARAMETERIZATION FORCED, converts literals in ad-hoc queries to parameters (e.g., int or varchar(8000)), standardizing plans and preventing recompilations for similar queries, though it excludes cases like variables or certain clauses. The Query Store, introduced in SQL Server 2016, persists plans, runtime statistics, and query text in the database for troubleshooting, allowing analysis of plan changes, forcing of optimal plans, and tuning based on historical performance data. Monitoring query execution aids in by comparing estimated and actual behaviors. Execution plans can be viewed using SHOWPLAN options in (SSMS), such as SET SHOWPLAN_XML for textual or graphical representations without execution, revealing operators, costs, and warnings. The actual execution plan, enabled via SET STATISTICS XML ON, includes runtime metrics like elapsed time, CPU usage, and row counts. Live Query Statistics provide real-time progress, updating every second to show actual rows flowing through operators during execution. Discrepancies between estimated rows (from optimizer statistics) and actual rows often indicate stale statistics or skewed data, guiding updates for better plan accuracy.

Programmability

Transact-SQL Language

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL standard, serving as the primary query and programming language for SQL Server. It extends ANSI SQL with procedural programming capabilities, enabling developers to write scripts that include control-of-flow statements, variables, and error handling within database operations. T-SQL is used to define, manipulate, and query data across SQL Server instances, Azure SQL Database, and related services. Key extensions include control-of-flow constructs such as IF...ELSE for conditional execution, WHILE loops for iteration, and cursors for row-by-row processing of result sets. Error handling is facilitated by the TRY...CATCH block, which allows structured exception management similar to programming languages. These features make T-SQL suitable for complex stored procedures, triggers, and functions that go beyond basic declarative queries. T-SQL supports a wide range of types to handle diverse requirements. Built-in types include exact numerics like int for integers, approximate numerics like float, character strings such as varchar for variable-length text, and date/time types like datetime2 for precise temporal . SQL Server does not support a "bitmap" data type, and it is absent from the official list of supported data types. The closest alternatives are the "bit" type for storing a single bit (0, 1, or NULL) or "varbinary(max)" for storing bitmap images or bit arrays. There is no native bitmap type for bitsets or similar structures. User-defined types (UDTs) allow customization based on types or classes via CREATE TYPE. Spatial types, geometry for planar and geography for geodetic , enable location-based queries. Temporal tables, introduced in SQL Server 2016, leverage datetime2 periods to automatically track historical changes for point-in-time analysis using FOR SYSTEM_TIME clauses. SQL Server 2025 introduces the vector for storing and manipulating binary vectors used in AI and applications, supporting operations like similarity searches with cosine or Euclidean distances. For example:

sql

CREATE TABLE Documents ( Id INT PRIMARY KEY IDENTITY, Content NVARCHAR(MAX), Embedding VECTOR(1536) );

CREATE TABLE Documents ( Id INT PRIMARY KEY IDENTITY, Content NVARCHAR(MAX), Embedding VECTOR(1536) );

This declaration uses the default float32 precision for the embedding column. Data definition language (DDL) statements in T-SQL manage database schema, with CREATE and ALTER used to define or modify objects like tables, views, and indexes, while DROP removes them. Data manipulation language (DML) statements handle data operations: INSERT adds rows, UPDATE modifies existing data, and DELETE removes rows, all supporting the OUTPUT clause to capture affected rows or values for further processing. The MERGE statement performs upsert operations by conditionally inserting, updating, or deleting based on a source-target match. T-SQL functions enhance query expressiveness. Scalar functions return a single value and operate on inputs, such as STRING_AGG for concatenating strings with separators (introduced in SQL Server 2017). Table-valued functions return result sets: inline use a single SELECT for , while multi-statement build tables through multiple operations. functions provide runtime information, including @@ERROR for the of the last statement and @@ROWCOUNT for the number of affected rows. SQL Server 2025 adds new scalar functions for vector operations, such as VECTOR_DISTANCE for computing distances between vectors, and enhances with comprehensive functions like REGEXP_MATCH and REGEXP_REPLACE. Additionally, JSON aggregation functions JSON_OBJECTAGG and JSON_ARRAYAGG enable efficient construction of objects and arrays from query results. T-SQL complies with core elements of the SQL:2011 standard (ISO/IEC 9075) for foundational syntax, including SELECT, DDL, and DML, as detailed in Microsoft's open specifications. However, it includes proprietary extensions that diverge from other implementations; for instance, unlike PostgreSQL's , T-SQL uses double at-sign (@@) prefixes for system functions, and compared to MySQL's SQL dialect, T-SQL lacks native support for certain procedural elements like DO blocks but offers deeper integration with Windows authentication. For advanced logic beyond native T-SQL, CLR integration allows embedding .NET code in procedures.

CLR Integration

SQL Server's Common Language Runtime (CLR) integration enables the execution of managed code written in .NET Framework languages directly within the database engine, extending programmability beyond native Transact-SQL capabilities. Introduced in SQL Server 2005, this feature hosts the CLR version 4 from the .NET Framework 4.x, allowing developers to create server-side database objects such as stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates using languages like C# or Visual Basic .NET. As of 2025, CLR integration remains based on the .NET Framework with no official deprecation announced, though Microsoft has noted potential compatibility issues with future .NET upgrades beyond Framework 4.8, and community discussions highlight ongoing limitations in supporting .NET 5 and later for CLR routines. Assemblies in CLR integration are categorized by permission levels to balance functionality and : SAFE assemblies are restricted to internal computations without access to external resources like files or networks; EXTERNAL_ACCESS assemblies permit such external interactions but require additional signing; and UNSAFE assemblies grant full access, including unmanaged code execution and pointer manipulation, suitable for advanced scenarios but posing higher risks. The original Code Access (CAS) model, which enforced these permissions, has been deprecated since .NET Framework 4.0 and is no longer a reliable boundary; instead, SQL Server 2017 and later enforce clr strict security by default, treating SAFE and EXTERNAL_ACCESS assemblies as UNSAFE unless they are signed with a strong name key or certificate and explicitly trusted via sys.sp_add_trusted_assembly. To deploy an assembly, administrators use the CREATE ASSEMBLY statement with the appropriate permission set, and enabling CLR integration server-wide requires the sp_configure 'clr enabled', 1 command, which demands ALTER SETTINGS permission. Developers can implement stored procedures, scalar- or table-valued functions, triggers, and custom aggregates in C# or VB.NET to handle object-oriented logic that would be cumbersome in Transact-SQL, such as replacing intricate procedural code with reusable classes—for instance, defining a custom aggregation function to compute statistical measures like standard deviation across result sets. These objects leverage the Microsoft.SqlServer.Server namespace for SQL Server-specific interactions, including context connections via SqlContext and data access through SqlPipe for output or SqlConnection marked with Context Connection=true for input queries. Compilation involves referencing SQL Server system assemblies like System.Data and deploying the resulting DLL to the database, often via Visual Studio integration or T-SQL scripts. Performance in CLR integration involves trade-offs compared to native Transact-SQL execution: while CLR excels in compute-intensive tasks due to just-in-time compilation and optimized garbage collection, it incurs overhead from data serialization and marshaling when passing parameters between SQL Server and the CLR—such as converting SQL data types to .NET equivalents, which can impact latency for simple operations but yields net gains for complex ones like string manipulations. Debugging is facilitated through Visual Studio by attaching to the SQL Server process, though it requires enabling CLR debugging and may face limitations in multi-threaded scenarios or when using UNSAFE code. Common use cases for CLR integration include implementing regular expressions for in text data, performing complex mathematical computations that exceed T-SQL's built-in functions, and integrating with external .NET libraries for tasks like image processing or cryptographic operations, all executed server-side to minimize network latency. For example, a CLR user-defined function can invoke the .NET System.Text.RegularExpressions.Regex class to validate or parse strings more efficiently than equivalent T-SQL loops. Note that on Linux-hosted SQL Server instances (since ), EXTERNAL_ACCESS and UNSAFE permissions are not supported, restricting usage to SAFE assemblies.

Client Interfaces and APIs

Microsoft SQL Server provides a range of client interfaces and APIs that enable applications to connect to the , submit queries in or other formats, and retrieve results. These interfaces rely on the () protocol as the foundational communication mechanism, which operates as an application-layer protocol for interactions such as , SQL command execution, and transfer between clients and the server. TDS uses a binary format to encode queries, results, and metadata efficiently, supporting features like bulk operations and transaction management. The protocol has evolved through multiple versions, with TDS 8.0 introduced in SQL Server 2022 to enhance and performance, including improved encryption handling and compatibility with modern methods. For native Windows-based applications, the SQL Server Native Client (SNAC) served as a legacy OLE DB and ODBC driver from SQL Server 2005 through 2012, providing direct access to SQL Server features like and . However, SNAC has been deprecated and is no longer shipped with SQL Server 2022 or later versions, nor recommended for new development due to its outdated support for modern protocols and security standards. It has been replaced by the Microsoft ODBC Driver for SQL Server (version 17 and later) for ODBC connectivity and the Microsoft Driver for SQL Server for OLE DB access, which offer improved performance, always-encrypted support, and broader platform compatibility. In .NET environments, the primary interface is the .Data.SqlClient library, a data provider that facilitates connections, command execution, and retrieval for SQL Server from .NET Framework and .NET Core applications. This provider supports asynchronous operations, bulk copy operations, and integration with higher-level frameworks like , which uses it as the underlying ORM mechanism to map database schemas to .NET objects without direct SQL management. Microsoft.Data.SqlClient has superseded the older System.Data.SqlClient namespace, incorporating enhancements for cross-platform support and Azure SQL Database compatibility. For cross-platform development, SQL Server offers drivers in other languages, including the , a Type 4 JDBC 4.2-compliant driver that enables applications to connect via standard JDBC APIs, supporting features like connection pooling and always encrypted columns. Similarly, the provide PHP extensions for reading and writing data, compatible with PHP 7.2 through 8.3 and integrating with ODBC for underlying connectivity. For RESTful access, Data API Builder allows developers to expose SQL Server data as OData v4-compatible endpoints, enabling query, filter, and pagination operations without custom development; this tool, updated in 2025, supports both on-premises SQL Server and Azure SQL via configurable connection strings. Key connectivity features across these interfaces include connection pooling to optimize resource usage by reusing persistent connections, reducing overhead in high-throughput scenarios; this is implemented driver-aware in the ODBC driver, separating pools based on context and attributes like settings. Transport-level is enforced via TLS (formerly SSL) with options such as the Encrypt keyword in connection strings, ensuring data in transit is protected, while (TDE) handles at-rest protection separately. Multi-subnet failover accelerates recovery in Always On Availability Groups by enabling parallel TCP connections to listener IPs, configurable via the MultiSubnetFailover=True option in supported drivers like .Data.SqlClient and JDBC, minimizing downtime to seconds during failovers.

Business Intelligence Services

Integration Services

SQL Server Integration Services (SSIS) is a platform for building enterprise-level and transformation solutions, enabling the extraction, transformation, and loading (ETL) of from various sources to destinations. It serves as a engine that manages the execution of packages, which are defined in XML format and consist of control flows for task and flows for . SSIS supports graphical tools like SSIS Designer for package creation without extensive coding, alongside programmatic options via its object model. Key components in SSIS include data sources such as Flat File for extracting from text files and for connecting to relational databases like SQL Server. Transformations encompass operations like Lookup for matching data against reference tables and Merge Join for combining sorted datasets from multiple inputs. Destinations allow loading into targets including SQL Server tables or Excel workbooks, while Script Tasks enable custom logic using C# or . Deployment of SSIS packages occurs through the SSISDB catalog, a centralized database that stores projects, parameters, and environments for execution, , and . Execution is handled via stored procedures like catalog.create_execution and catalog.start_execution, with configurable and AES-256 for sensitive . Security uses folder-based permissions, and scalability is enhanced by the Scale Out feature for distributed execution, as well as integration with Azure Data Factory through the SSIS Integration Runtime for cloud-based workloads. In SQL Server 2025, SSIS introduces support for the Microsoft SqlClient Data Provider in the ADO.NET connection manager, improving connectivity options. Breaking changes require rebuilding packages that use certain .NET APIs or SQL Server Management Objects, while deprecations include the legacy Integration Services service, 32-bit mode, and the SqlClient Data Provider connection type. Removed features encompass Change Data Capture (CDC) components by Attunity, the Microsoft Connector for Oracle, and Hadoop-related tasks. Common use cases for SSIS include from diverse sources to SQL Server, through transformations to ensure quality, and ETL processes for populating data warehouses. Historically, it supported real-time streaming via for capturing insert, update, and delete operations, though these components are now removed in favor of alternative Azure-integrated solutions.

Analysis Services

SQL Server Analysis Services (SSAS) is a platform for creating, managing, and querying multidimensional and tabular data models to support (OLAP) and . It enables organizations to perform complex by organizing data into semantic models that facilitate applications, such as slicing, dicing, and drilling down into datasets for insights. SSAS integrates with tools like Excel and Power BI to deliver interactive reporting and analytics, focusing on pre-aggregated data for fast query performance. SSAS operates in two primary modes: multidimensional and tabular. The multidimensional mode uses cubes, dimensions, and measures to represent in a schema-on-write approach, where is pre-processed and stored in MOLAP (Multidimensional OLAP) structures for optimized querying. Dimensions include hierarchies for navigating levels, such as time (year-quarter-month), while measures define numeric values like totals. Key indicators (KPIs) track metrics against targets, and calculated members allow custom computations, such as year-over-year growth, using Multidimensional Expressions (MDX). To handle large datasets, multidimensional models support partitions to divide cubes into manageable segments and aggregations to pre-compute summaries at various levels, enhancing query speed by reducing on-the-fly calculations. In contrast, the tabular mode employs an in-memory columnar called VertiPaq, which compresses data for efficient storage and querying in a schema-on-read model. Tabular models organize data into relational tables with relationships, supporting hierarchies and KPIs similar to multidimensional but with simpler development. Measures and calculated columns are defined using (DAX), a formula language that includes functions like CALCULATE for modifying filter contexts and RELATED for traversing relationships across tables. For example, a DAX measure might compute total sales filtered by region using CALCULATE(SUM(Sales[Amount]), Sales[Region] = "North"). Partitions enable processing subsets of large tables independently, and while tabular models primarily rely on in-memory caching rather than explicit aggregations, they optimize performance through column compression and query folding. MDX queries multidimensional models for complex set-based operations, whereas DAX powers tabular queries and integrates seamlessly with Power BI for visual explorations. Deployment options for SSAS include on-premises installations within SQL Server or cloud-based Azure Analysis Services for scalable, managed instances. Models are developed in using SQL Server Data Tools and deployed as databases to servers, supporting and deployment pipelines. Processing modes load and aggregate data, with options for full processing (rebuilding everything) or incremental updates to minimize ; lazy processing defers aggregation until queried, while eager modes pre-compute for immediate performance. is managed through roles that define user permissions on databases, cubes, or tables, with row-level security (RLS) in tabular models using to filter data dynamically based on user identity, ensuring granular . SSAS tabular models connect directly to Power BI via live queries, allowing real-time analysis without data duplication. In SQL Server 2025, SSAS introduces enhancements focused on performance and usability, including improved MDX query execution for models with calculation groups and dynamic format strings, parallel processing for DirectQuery modes, and the Horizontal Fusion optimization to consolidate multiple SQL queries into fewer roundtrips. New DAX functions like for and for advanced aggregations expand analytical capabilities, while selection expressions in calculation groups simplify dynamic measure adjustments.

Reporting Services

SQL Server Reporting Services (SSRS) is a server-based platform for creating, managing, and delivering paginated and mobile reports from a variety of sources. It enables organizations to generate interactive reports with precise layout control, suitable for and pixel-perfect distribution. SSRS integrates seamlessly with other SQL Server components and external systems, providing a robust solution for reporting needs. The of SSRS centers on the report server, which serves as the core component for storing and processing reports, data sources, schedules, and subscriptions. Reports are defined using Report Definition Language (RDL), an XML-based schema that specifies the layout, data retrieval, and rendering instructions for paginated reports. The report model interprets the RDL to process data queries, while rendering extensions transform the processed data and layout into device-specific formats such as , PDF, or Excel. This extensible design allows customization through additional extensions for data processing and delivery. SSRS supports diverse report types, including tabular layouts for structured presentation, charts for visualizing trends, and maps for geospatial . Reports can incorporate parameters to enable user-driven filtering, drill-through actions for navigating to detailed subreports, and subreports for embedding nested content. Mobile-optimized reports, created using the .rsmobile format, provide responsive designs viewable on devices via Power BI Report Server. Data sources in SSRS can be embedded within individual reports or shared across multiple reports for reusability, supporting connections to relational databases like SQL Server, multidimensional sources such as SQL Server Analysis Services (SSAS), and external systems including databases. Datasets define the queries that retrieve data from these sources, with credentials managed securely to ensure controlled access. Report delivery in SSRS includes on-demand access through the and automated subscriptions that distribute reports via or to file shares. Data-driven subscriptions enable bursting, where reports are personalized and sent to multiple recipients based on query results. Security is enforced through at the folder and item levels, with data-driven roles allowing dynamic permissions tied to report content. In SQL Server 2025, SSRS capabilities are consolidated under Power BI Report Server, serving as the primary on-premises reporting platform with support for both paginated reports and interactive Power BI reports.

Advanced Features

Machine Learning and AI Capabilities

SQL Server Machine Learning Services, introduced in SQL Server 2016 as R Services and expanded to include Python support in SQL Server 2017, enables the execution of and Python scripts directly within the to perform advanced analytics on relational data. This feature processes data in place, minimizing data movement and leveraging SQL Server's security and scalability. Scripts are executed using the sp_execute_external_script, which integrates external runtimes with T-SQL queries, allowing users to pass input data from SQL tables to scripts and return results as output datasets. The service includes Microsoft-developed packages like RevoScaleR for and revoscalepy for Python, which provide scalable functions for data preparation, modeling, and parallel processing across multiple cores without requiring data export. Introduced in SQL Server 2019, the Extensibility Framework extends Services to support additional languages such as and C#, facilitating broader integration of external code while maintaining database isolation. This framework replaces less secure mechanisms like execution by using a managed architecture with the SQL Server Launchpad service, which launches external processes in isolated AppContainers and enforces trusted worker accounts for security. External scripts are disabled by default for governance, requiring explicit enabling via sp_configure 'external scripts enabled', 1 to ensure controlled access and compliance. Common use cases include , such as in time-series data, and tasks like on textual datasets stored in SQL Server. Prior to SQL Server 2025, versions such as SQL Server 2022 lack a native vector data type, requiring workarounds like storing embeddings in VARBINARY(MAX) columns for binary representations or as JSON arrays in VARCHAR(MAX), with custom T-SQL functions for manual distance calculations such as cosine or Euclidean similarity. SQL Server 2025 enhances AI capabilities with native support for vector data types, enabling storage and manipulation of embeddings for models directly in the database. This includes the VECTOR_SEARCH function for searches using approximate nearest neighbors algorithms, optimized for applications like recommendation systems and content retrieval. Native integration with ONNX models is supported via the PREDICT T-SQL function, allowing deployment of pre-trained models for inference without external dependencies. Additionally, SQL Server 2025 introduces management of external AI models via endpoints for embedding generation, bridging on-premises data with cloud-based services like Fabric for end-to-end ML operations. Performance and resource management for ML workloads are handled by the External Resource Governor, which uses dedicated resource pools to limit CPU and memory allocation for external scripts, preventing interference with core database operations. For large-scale processing, RevoScaleR's satellite architecture distributes computations across SQL Server instances or compute contexts, enabling efficient handling of distributed datasets.

Replication and High Availability

SQL Server provides several replication mechanisms to distribute data across multiple servers, enabling scenarios such as load balancing, data warehousing, and offline synchronization. Replication involves three primary roles: the publisher, which holds the data to be replicated; the , which manages the replication metadata and log; and the subscriber, which receives the replicated data. Snapshot replication delivers a complete copy of the data at a point in time, suitable for initial loads or infrequent updates, as it generates and distributes database snapshots without tracking incremental changes. Transactional replication, in contrast, captures and propagates committed transactions from the publisher to subscribers in near real-time, supporting push subscriptions (where the pushes changes) or pull subscriptions (where subscribers request changes), and is ideal for continuously connected environments requiring low latency. Merge replication allows independent changes at multiple sites, merging them during synchronization to handle conflicts, and supports bidirectional topologies like transactional replication for load distribution across active-active nodes. For , SQL Server offers Always On Availability Groups, which provide database-level without shared storage. These groups support up to nine replicas, with synchronous-commit mode ensuring zero by waiting for secondary replicas to acknowledge transactions before committing on the primary, enabling automatic in under 30 seconds for synchronized setups. Asynchronous-commit mode prioritizes performance by allowing the primary to commit without waiting, suitable for distant replicas but risking during . Readable secondary replicas in availability groups can offload read-only workloads, such as reporting, from the primary, and are compatible with both commit modes. Cluster Instances (FCI) deliver instance-level by clustering SQL Server across Cluster nodes, allowing automatic of the entire instance to another node in case of hardware , typically within minutes. Legacy options include log shipping, which automates the backup and restore of transaction logs from a primary to one or more secondary servers for manual , and database , a per-database solution that maintains a hot standby using principal and mirror roles with automatic or manual . Database , deprecated since SQL Server 2012 and unavailable in later versions on , has been superseded by availability groups for enhanced flexibility. In SQL Server 2025, enhancements to availability groups include improved support for distributed availability groups, which span multiple independent groups across regions or clusters for cross-site disaster recovery, with simplified configuration between contained availability groups and better synchronization performance. Auto-failover capabilities have been extended with deeper Azure integration, enabling seamless in hybrid environments using Azure Virtual Machines for cluster hosting and automatic policy-based recovery. Monitoring tools ensure the reliability of these features: the Replication Monitor tracks publication status, agent history, and performance metrics for all replication types, while the Always On Dashboard provides real-time views of availability group health, synchronization states, and readiness. In merge replication, conflicts are resolved automatically based on predefined priorities (e.g., publisher wins), with options for interactive resolution via the Replication Conflict Viewer to review and manually adjust conflicting changes.

Security and Compliance Features

Microsoft SQL Server supports two primary authentication modes to verify user identities: Windows Authentication mode, which uses Windows security mechanisms for integrated access without requiring separate credentials, and mixed mode, which combines Windows Authentication with SQL Server Authentication for username and password-based logins. Windows Authentication is recommended for its reliance on operating system-level security, reducing credential management overhead. For hybrid environments, SQL Server integrates with (formerly Azure AD) to enable and , allowing users to authenticate using Entra ID credentials across on-premises and cloud deployments. Authorization in SQL Server is managed through a hierarchical model of roles and permissions, enabling fine-grained control over access. Server-level roles, such as sysadmin and securityadmin, group logins to administer instance-wide operations, while database-level roles like db_owner and db_datareader define access within specific databases. Permissions are assigned using statements like GRANT, DENY, and REVOKE, allowing granular control on securables such as , tables, and procedures—for instance, granting SELECT on a specific without broader database access. Contained databases enhance portability and by isolating user and metadata within the database itself, supporting contained users that authenticate directly without relying on server-level logins. SQL Server provides multiple encryption options to protect data at rest and in transit. Transparent Data Encryption (TDE) encrypts entire database files using a Database Encryption Key (DEK) protected by a certificate or asymmetric key, ensuring data remains encrypted on disk without application changes. Always Encrypted enables client-side encryption of sensitive columns, such as credit card numbers, where encryption keys are managed externally and never exposed to the Database Engine, supporting operations like pattern matching on encrypted data. Column-level encryption complements these by allowing manual encryption of individual columns via built-in functions. For network communications, SQL Server supports TLS 1.3 starting with version 2022, providing stronger cipher suites and reduced handshake latency for secure connections. Auditing and compliance features in SQL Server facilitate monitoring and regulatory adherence. SQL Server Audit captures server- and database-level events, such as logins and data modifications, writing them to files, the Windows event log, or Azure Storage for analysis. Extended Events offers lightweight, flexible tracing for detailed diagnostics, including security-related activities like permission changes. Introduced in SQL Server 2022, ledger tables provide tamper-evident storage with cryptographic hashing and blockchains, ensuring immutable audit trails for verifying against alterations. These capabilities align with standards like GDPR for data protection and HIPAA for healthcare privacy, enabling organizations to demonstrate accountability through auditable logs and encrypted data handling. Microsoft Defender for SQL, available since SQL Server 2022, provides AI-driven threat detection, using to identify anomalies such as unusual access patterns or potential attempts in real time. In SQL Server 2025 (preview as of November 2025), security enhancements include targeted invalidation of security caches for specific logins, support for OAEP padding in RSA , as the default for hashing, and expanded TLS 1.3 support across features like replication. These updates build on features by incorporating secure mechanisms to maintain and auditing continuity during replication.

Management and Development Tools

Graphical Management Tools

(SSMS) serves as the primary for administering SQL Server instances, providing an integrated environment for developers and database administrators to configure, monitor, and develop components of SQL Server, Azure SQL Database, Azure SQL Managed Instance, and related services. Multiple major versions of SSMS can be installed side-by-side on the same system without conflicts. It includes tools for T-SQL editing through rich query and text editors with IntelliSense support, enabling interactive script building and execution. The Object Explorer pane allows hierarchical navigation and management of database objects such as tables, views, and stored procedures, while visual database tools facilitate query tuning, table design, and database diagramming to optimize performance. As of November 2025, SSMS version 22 is the latest generally available release, offering support for SQL Server 2025 (17.x) features, including configuration options like FULLTEXT_INDEX_VERSION in database properties dialogs. Azure Data Studio provides a lightweight, cross-platform alternative to SSMS, running on Windows, macOS, and to connect to and query SQL Server instances, Azure SQL, and other databases like and . It features a modern editor with IntelliSense for T-SQL, code snippets, and an integrated terminal for development tasks, alongside Jupyter notebook support for interactive data exploration and analysis. Extensions enhance its functionality, including integration for , customizable dashboards for performance insights via widgets, and tools for database migrations and charting. Designed for data professionals, it emphasizes productivity in cloud and on-premises environments, with compatibility for SQL Server 2025 features through its connection capabilities. Azure Data Studio is scheduled to retire on February 28, 2026, with Microsoft recommending migration to and the MSSQL extension for continued development. SQL Server Configuration Manager is a dedicated graphical tool for managing services and network configurations associated with SQL Server installations. It enables starting, stopping, pausing, or resuming services like the SQL Server Database Engine and SQL Server Agent, along with configuring startup modes and service accounts, integrating seamlessly with Windows Management Console (MMC) snap-ins for centralized administration. Users can configure network protocols such as TCP/IP, Named Pipes, and Shared Memory, enable or disable them per instance, and manage client aliases to simplify connectivity, though aliases for SQL Server 2022 and later require the separate SQL Server Client Network Utility. This tool ensures reliable instance settings and troubleshooting of network-related issues without relying on command-line interfaces. Activity Monitor, accessible within SSMS, offers real-time graphical monitoring of SQL Server instance activity to identify performance bottlenecks. It displays an overview pane summarizing CPU usage, wait types, and resource consumption, with expandable sections for processes showing active sessions and their resource impact, including top resource-consuming queries. Additional panes cover resource waits to highlight blocking operations, data file I/O for storage performance, and recent or active expensive queries to pinpoint tuning opportunities. Administrators can sort, filter, and rearrange data in these views for quick diagnosis of issues like high CPU utilization or blocking processes.

Command-Line and Scripting Tools

Microsoft SQL Server provides several command-line and scripting tools designed for automating database administration, executing queries, and managing data operations without graphical interfaces. These tools enable , , and integration into scripts for deployment pipelines, supporting tasks from querying to bulk data handling. The sqlcmd utility serves as the primary command-line tool for interacting with SQL Server instances. It allows users to execute (T-SQL) statements, system procedures, and script files in interactive or batch modes, facilitating of T-SQL scripting tasks. sqlcmd supports scripting variables through commands like :setvar and :r for including external files, enabling dynamic parameterization for reusable scripts. For , it includes an OSQL mode that emulates the older osql utility's behavior, activated via specific options or environment settings. Output can be directed to files in various formats; for instance, (CSV) are achieved using the -s"," separator option, while JSON output leverages the FOR JSON clause within T-SQL queries executed by sqlcmd. In SQL Server 2025, sqlcmd enhancements support vector queries through the new vector and scalar functions, allowing binary vector operations directly in scripts for AI and workloads. PowerShell integration extends scripting capabilities via the SqlServer module, which provides cmdlets for comprehensive database management. The Invoke-Sqlcmd cmdlet mirrors sqlcmd functionality, executing T-SQL scripts with support for SQLCMD commands like GO and variables, and outputs results in formats including . Additional cmdlets handle operations, such as Backup-SqlDatabase for creating full, differential, or log backups, and Restore-SqlDatabase for . The module integrates with Desired State Configuration (DSC) through the SqlServerDsc resource, enabling declarative configuration of SQL Server installations and features on Windows servers. For cross-platform use, the SqlServer module is compatible with 7 and later, including scenarios involving Azure Arc for hybrid management of on-premises SQL Server instances. The Bulk Copy Program (bcp) utility facilitates high-speed data import and export between SQL Server and flat files, optimized for large-scale operations. It supports multiple formats, including native binary for efficient transfers, character-based for text files, , and custom layouts defined via format files generated with the -f option. Error handling includes the -e flag to log rejected rows to an error file, though error messages are limited to the first 512 bytes for display purposes; full details require reviewing the error log. bcp performs bulk operations faster than row-by-row inserts, making it suitable for data warehousing and ETL processes. Database Application Close Packages (DACPAC) and the SqlPackage utility support automated schema management and deployment for database projects. A DACPAC file encapsulates the schema definition of a database, serving as a portable artifact for version-controlled deployments built from SQL database projects in tools like . SqlPackage, a to the Data-Tier Application Framework (DacFx), enables actions such as publishing a DACPAC to update a target database incrementally, extracting schemas from live databases, and performing schema comparisons. In SQL Server 2025, SqlPackage includes support for vector indexes, enhancing deployment automation for AI-enabled databases. These tools integrate into pipelines for consistent, scripted database lifecycle management.

Integrated Development Environments

SQL Server developers commonly use integrated development environments (IDEs) that provide robust support for creating, managing, and deploying database projects, with seamless integration for (T-SQL) editing and application building. These tools emphasize workflow efficiency, from design to testing and deployment, often extending general-purpose IDEs like . A primary IDE for SQL Server development is , enhanced by SQL Server Data Tools (SSDT), which enables the creation of database projects that represent schemas including tables, views, stored procedures, and functions. SSDT facilitates schema comparison, deployment to SQL Server or Azure SQL Database, and through integration with 's testing framework, allowing developers to validate changes in isolated environments before production rollout. Its tight coupling with .NET development workflows supports building hybrid applications that interact with SQL Server data, such as those using or . In 2022, SSDT includes project templates compatible with Fabric, enabling state-based deployments to Fabric Data Warehouses and Synapse Analytics environments. For lighter, cross-platform development, serves as an effective IDE via the official MSSQL extension, which delivers IntelliSense for T-SQL autocompletion, , and query execution directly within the editor. The extension supports debugging SQL scripts, connection management to local or remote SQL Server instances, and visualization of query execution plans, streamlining iterative development without requiring a full installation. It also integrates with Jupyter notebooks for interactive SQL exploration, embedding results and charts akin to Azure Data Studio's capabilities. Prior to SQL Server 2012, Development Studio (BIDS) was the legacy IDE bundled with 2005 through 2008, specifically tailored for developing (SSIS), Reporting Services (SSRS), and Analysis Services (SSAS) projects. BIDS provided designers and wizards for BI artifacts but has been fully migrated to SSDT since 2010, with no ongoing support in modern versions. Third-party extensions augment these IDEs for enhanced productivity; for instance, SQL Prompt integrates with and (SSMS) to offer advanced , automated formatting, and refactoring tools that accelerate T-SQL writing by suggesting context-aware snippets and identifying potential errors. Similarly, dbForge Studio for SQL Server functions as a standalone IDE with strong diagramming features, allowing visual database design, schema synchronization, and data comparison to support complex development tasks. As of 2025, Visual Studio 2022 incorporates AI-assisted features like for T-SQL , generating suggestions based on natural language prompts and code context to boost developer efficiency in SSDT projects. These updates, including support for SQL Server 2025 schemas, ensure compatibility with emerging data platforms like Fabric.

References

  1. https://learn.microsoft.com/en-us/sql/sql-server/what-is-sql-server?view=sql-server-ver17
  2. https://learn.microsoft.com/en-us/sql/reporting-services/reporting-services-consolidation-faq?view=sql-server-ver17
  3. https://www.microsoft.com/licensing/guidance/SQL
  4. https://www.microsoft.com/en-us/sql-server/blog/2012/02/15/new-video-the-history-of-sql-server/
  5. https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2025?view=sql-server-ver17
  6. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-on-azure-vm-iaas-what-is-overview?view=azuresql
  7. https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage-scenarios?view=sql-server-ver17
  8. https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/online-analytical-processing
  9. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/client-network-configuration?view=sql-server-ver17
  10. https://www.microsoft.com/en-us/licensing/product-licensing/sql-server
  11. https://download.microsoft.com/download/9/3/d/93d32de6-f268-45ed-ba25-2f9a6756b6af/SQL_Server_2022_Licensing_guide.pdf
  12. https://www.microsoft.com/en-us/sql-server
  13. https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver17
  14. https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver17
  15. https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver17
  16. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver17
  17. https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver17
  18. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver17
  19. https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver17
  20. https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver17
  21. https://learn.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-ver17
  22. https://learn.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-types-overview?view=sql-server-ver17
  23. https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/overview?view=sql-server-ver17
  24. https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2025?view=sql-server-ver17
  25. https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial
  26. https://learn.microsoft.com/en-us/azure/synapse-analytics/synapse-link/sql-synapse-link-overview
  27. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql
  28. https://bytescout.com/blog/2014/09/ms-sql-server-history-and-advantages.html
  29. https://www.theregister.com/2020/12/28/sql_server/
  30. https://community.sap.com/t5/additional-blog-posts-by-sap/a-deeper-look-at-sybase-history-of-ase/ba-p/12923660
  31. https://licenseware.io/history-of-microsoft-sql-server/
  32. https://www.fundinguniverse.com/company-histories/sybase-inc-history/
  33. https://www.itprotoday.com/early-versions/what-s-new-in-sql-server-7-0-
  34. https://news.microsoft.com/source/1998/09/14/microsoft-unveils-enterprise-olap-features-for-microsoft-sql-server-7-0/
  35. https://dbplus.tech/en/2025/02/06/microsoft-versus-oracle/
  36. https://www.oracle.com/technetwork/articles/wp-oracle-msft-ss-2009-133600.pdf
  37. https://www.sqlshack.com/history-sql-server-evolution-sql-server-features/
  38. https://learn.microsoft.com/en-us/archive/msdn-magazine/2000/march/sql-server-2000-new-xml-features-streamline-web-centric-app-development
  39. https://learn.microsoft.com/en-us/archive/msdn-magazine/2000/august/sql-server-2000-new-features-provide-unmatched-ease-of-use-and-scalability-to-admins-and-users
  40. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sql-server-common-language-runtime-integration
  41. https://learn.microsoft.com/en-us/archive/msdn-magazine/2009/september/sql-server-2005-service-broker-microsoft%25E2%2580%2599s-new-messaging-technology
  42. https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms218893%28v=sql.90%29
  43. https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2019?view=sql-server-ver17
  44. https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver17
  45. https://learn.microsoft.com/en-us/lifecycle/products/sql-server-2019
  46. https://endoflife.date/mssqlserver
  47. https://learn.microsoft.com/en-us/sql/sql-server/end-of-support/sql-server-end-of-support-overview?view=sql-server-ver17
  48. https://www.microsoft.com/en-us/sql-server/blog/2025/05/19/announcing-sql-server-2025-preview-the-ai-ready-enterprise-database-from-ground-to-cloud/
  49. https://www.linkedin.com/pulse/announcing-sql-server-2025-public-preview-bob-ward-4rz2c
  50. https://blogs.microsoft.com/on-the-issues/2025/05/29/environmental-sustainability-report/
  51. https://www.brentozar.com/archive/2025/10/sql-constantcare-population-report-fall-2025/
  52. https://www.certlibrary.com/blog/comprehensive-overview-of-microsoft-sql-server-2025-certification/
  53. https://db-engines.com/en/ranking
  54. https://learn.microsoft.com/en-us/answers/questions/5571460/sql-server-developer-edition
  55. https://www.dbi-services.com/blog/sql-server-2025-standard-developer-edition/
  56. https://www.microsoft.com/en-us/evalcenter/sql-server-2025-download
  57. https://learn.microsoft.com/en-us/answers/questions/1078293/is-evaluation-version-of-sql-server-2019-same-as-s
  58. https://learn.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-compact-40
  59. https://help.meridian360.com/KnowledgeBase/Content/Articles/MS_SQL_Compact_End_of_Support.htm
  60. https://learn.microsoft.com/en-us/sql/analytics-platform-system/pdw-services-status?view=aps-pdw-2016-au7
  61. https://learn.microsoft.com/en-us/sql/analytics-platform-system/parallel-data-warehouse-overview?view=aps-pdw-2016-au7
  62. https://news.microsoft.com/source/2005/02/24/microsoft-announces-expanded-sql-server-2005-product-line-to-meet-customers-growing-demands/
  63. https://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/compact_express_comparison.doc
  64. https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/install/windows/support-policy-sql-server
  65. https://learn.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-2008
  66. https://learn.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-2008-r2
  67. https://learn.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-compact-35
  68. https://learn.microsoft.com/en-us/answers/questions/382364/alternate-to-sql-server-compact-edition
  69. https://learn.microsoft.com/en-us/answers/questions/1684655/whats-the-upgrade-or-alternative-component-for-sql
  70. https://learn.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant?view=sql-server-ver17
  71. https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver17
  72. https://learn.microsoft.com/en-us/sql/relational-databases/databases/system-databases?view=sql-server-ver17
  73. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/catalog-views-transact-sql?view=sql-server-ver17
  74. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver17
  75. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-instances-sql-server?view=sql-server-ver17
  76. https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor
  77. https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver17
  78. https://learn.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver17
  79. https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-buffer-manager-object?view=sql-server-ver17
  80. https://learn.microsoft.com/en-us/sql/relational-databases/writing-pages?view=sql-server-ver17
  81. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver17
  82. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver17
  83. https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-ver17
  84. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/tempdb-ephemeral-storage?view=azuresql
  85. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage?view=azuresql
  86. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver17
  87. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver17
  88. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes?view=sql-server-ver17
  89. https://learn.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver17
  90. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver17
  91. https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver17
  92. https://learn.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver17
  93. https://learn.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics?view=sql-server-ver17
  94. https://learn.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver17
  95. https://learn.microsoft.com/en-us/training/modules/get-started-transact-sql-programming/
  96. https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver17
  97. https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver17
  98. https://learn.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-ver17
  99. https://learn.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-ver17
  100. https://learn.microsoft.com/en-us/openspecs/sql_standards/ms-tsqliso02/b909df7f-14fb-499e-83a8-0f42f94b2160
  101. https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/common-language-runtime-integration-overview?view=sql-server-ver17
  102. https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration
  103. https://github.com/dotnet/SqlClient/issues/2838
  104. https://www.sqlservercentral.com/forums/topic/announcing-sql-server-2025
  105. https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-enabling?view=sql-server-ver17
  106. https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/common-language-runtime-clr-integration-programming-concepts?view=sql-server-ver17
  107. https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/getting-started-with-clr-integration?view=sql-server-ver17
  108. https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/b46a581a-39de-4745-b076-ec4dbb7d13ec
  109. https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tds-8?view=sql-server-ver17
  110. https://learn.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-client?view=sql-server-ver15
  111. https://learn.microsoft.com/en-us/sql/connect/connect-history?view=sql-server-ver17
  112. https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver17
  113. https://learn.microsoft.com/en-us/sql/connect/ado-net/overview-sqlclient-driver?view=sql-server-ver17
  114. https://learn.microsoft.com/en-us/sql/connect/ado-net/introduction-microsoft-data-sqlclient-namespace?view=sql-server-ver17
  115. https://learn.microsoft.com/en-us/ef/ef6/what-is-new/microsoft-ef6-sqlserver
  116. https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?view=sql-server-ver17
  117. https://learn.microsoft.com/en-us/sql/connect/php/microsoft-php-driver-for-sql-server?view=sql-server-ver17
  118. https://learn.microsoft.com/en-us/azure/data-api-builder/concept/api/rest
  119. https://learn.microsoft.com/en-us/sql/connect/odbc/windows/driver-aware-connection-pooling-in-the-odbc-driver-for-sql-server?view=sql-server-ver17
  120. https://learn.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver17
  121. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover?view=sql-server-ver17
  122. https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver17
  123. https://learn.microsoft.com/en-us/sql/integration-services/data-flow/data-flow?view=sql-server-ver17
  124. https://learn.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-ver17
  125. https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver17
  126. https://learn.microsoft.com/en-us/sql/integration-services/scale-out/integration-services-ssis-scale-out?view=sql-server-ver17
  127. https://learn.microsoft.com/en-us/sql/integration-services/what-s-new-in-integration-services-in-sql-server-2025?view=sql-server-ver17
  128. https://learn.microsoft.com/en-us/sql/integration-services/load-data-to-sql-database-with-ssis?view=sql-server-ver17
  129. https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver17
  130. https://learn.microsoft.com/en-us/analysis-services/ssas-overview?view=sql-analysis-services-2025
  131. https://learn.microsoft.com/en-us/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas?view=sql-analysis-services-2025
  132. https://learn.microsoft.com/en-us/analysis-services/tabular-models/tabular-models-ssas?view=sql-analysis-services-2025
  133. https://learn.microsoft.com/en-us/analysis-services/tabular-models/understanding-dax-in-tabular-models-ssas-tabular?view=sql-analysis-services-2025
  134. https://learn.microsoft.com/en-us/analysis-services/tabular-models/measures-ssas-tabular?view=sql-analysis-services-2025
  135. https://learn.microsoft.com/en-us/analysis-services/what-s-new-in-sql-server-analysis-services?view=sql-analysis-services-2025
  136. https://learn.microsoft.com/en-us/sql/reporting-services/reporting-services-concepts-ssrs?view=sql-server-ver17
  137. https://learn.microsoft.com/en-us/sql/reporting-services/reports/report-definition-language-ssrs?view=sql-server-ver17
  138. https://learn.microsoft.com/en-us/sql/reporting-services/extensions/rendering-extension/rendering-extensions-overview?view=sql-server-ver17
  139. https://learn.microsoft.com/en-us/sql/reporting-services/reports/reporting-services-reports-ssrs?view=sql-server-ver17
  140. https://learn.microsoft.com/en-us/sql/reporting-services/subscriptions/subscriptions-and-delivery-reporting-services?view=sql-server-ver17
  141. https://learn.microsoft.com/en-us/power-bi/report-server/whats-new
  142. https://learn.microsoft.com/en-us/sql/machine-learning/sql-server-machine-learning-services?view=sql-server-ver17
  143. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-execute-external-script-transact-sql?view=sql-server-ver17
  144. https://learn.microsoft.com/en-us/sql/language-extensions/concepts/extensibility-framework?view=sql-server-ver17
  145. https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install?view=sql-server-ver15
  146. https://learn.microsoft.com/en-us/sql/t-sql/data-types/vector-data-type?view=sql-server-ver17
  147. https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-search-transact-sql?view=sql-server-ver17
  148. https://learn.microsoft.com/en-us/sql/t-sql/queries/predict-transact-sql?view=sql-server-ver17
  149. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-model-transact-sql?view=sql-server-ver17
  150. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-resource-pool-transact-sql?view=sql-server-ver17
  151. https://learn.microsoft.com/en-us/sql/machine-learning/concepts/extensibility-framework?view=sql-server-ver17
  152. https://learn.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication?view=sql-server-ver17
  153. https://learn.microsoft.com/en-us/sql/relational-databases/replication/snapshot-replication?view=sql-server-ver17
  154. https://learn.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-ver17
  155. https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication?view=sql-server-ver17
  156. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver17
  157. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver17
  158. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-ver17
  159. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver17
  160. https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-ver17
  161. https://learn.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-ver17
  162. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-ver17
  163. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-overview?view=azuresql
  164. https://learn.microsoft.com/en-us/sql/relational-databases/replication/microsoft-replication-conflict-viewer-merge-replication?view=sql-server-ver17
  165. https://learn.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-merge-agent?view=sql-server-ver17
  166. https://learn.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-ver17
  167. https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/authentication-sql-server?view=sql-server-ver17
  168. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/azure-ad-authentication-sql-server-overview?view=sql-server-ver17
  169. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver17
  170. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver17
  171. https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver17
  172. https://learn.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=sql-server-ver17
  173. https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver17
  174. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver17
  175. https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-encryption?view=sql-server-ver17
  176. https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tls-1-3?view=sql-server-ver17
  177. https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver17
  178. https://learn.microsoft.com/en-us/sql/relational-databases/security/security-center-for-sql-server-database-engine-and-azure-sql-database?view=sql-server-ver17
  179. https://learn.microsoft.com/en-us/sql/relational-databases/security/ledger/ledger-overview?view=sql-server-ver17
  180. https://learn.microsoft.com/en-us/compliance/regulatory/gdpr
  181. https://learn.microsoft.com/en-us/azure/azure-sql/database/azure-defender-for-sql?view=azuresql
  182. https://learn.microsoft.com/en-us/ssms/sql-server-management-studio-ssms
  183. https://learn.microsoft.com/en-us/ssms/install/side-by-side
  184. https://learn.microsoft.com/en-us/ssms/release-notes-22
  185. https://learn.microsoft.com/en-us/azure-data-studio/what-is-azure-data-studio
  186. https://learn.microsoft.com/en-us/azure-data-studio/whats-happening-azure-data-studio
  187. https://learn.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-configuration-manager?view=sql-server-ver17
  188. https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/activity-monitor?view=sql-server-ver17
  189. https://learn.microsoft.com/en-us/sql/tools/command-prompt-utility-reference-database-engine?view=sql-server-ver17
  190. https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-use-utility?view=sql-server-ver17
  191. https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-use-scripting-variables?view=sql-server-ver17
  192. https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver17
  193. https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver17
  194. https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps
  195. https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-with-powershell-desired-state-configuration?view=sql-server-ver17
  196. https://learn.microsoft.com/en-us/powershell/sql-server/download-sql-server-ps-module?view=sqlserver-ps
  197. https://learn.microsoft.com/en-us/azure/azure-arc/servers/prerequisites
  198. https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver17
  199. https://learn.microsoft.com/en-us/sql/relational-databases/import-export/specify-file-storage-type-by-using-bcp-sql-server?view=sql-server-ver17
  200. https://learn.microsoft.com/en-us/sql/relational-databases/import-export/use-a-format-file-to-bulk-import-data-sql-server?view=sql-server-ver17
  201. https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/data-tier-applications/overview?view=sql-server-ver17
  202. https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver17
  203. https://learn.microsoft.com/en-us/sql/tools/sqlpackage/release-notes-sqlpackage?view=sql-server-ver17
  204. https://learn.microsoft.com/en-us/sql/ssdt/sql-server-data-tools?view=sql-server-ver17
  205. https://visualstudio.microsoft.com/vs/features/ssdt/
  206. https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver17
  207. https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver17
  208. https://learn.microsoft.com/en-us/sql/tools/visual-studio-code-extensions/mssql/mssql-extension-visual-studio-code?view=sql-server-ver17
  209. https://marketplace.visualstudio.com/items?itemName=ms-mssql.mssql
  210. https://code.visualstudio.com/docs/languages/tsql
  211. https://learn.microsoft.com/en-us/sql/ssdt/previous-releases-of-sql-server-data-tools-ssdt-and-ssdt-bi?view=sql-server-ver17
  212. https://www.red-gate.com/products/sql-prompt/
  213. https://www.devart.com/dbforge/sql/studio/
  214. https://learn.microsoft.com/en-us/visualstudio/ide/ai-assisted-development-visual-studio?view=vs-2022
  215. https://learn.microsoft.com/en-us/visualstudio/releases/2022/release-notes
Add your contribution
Related Hubs
User Avatar
No comments yet.