Content deleted Content added
Undid revisions 1177133978 and 946284627: ? |
|||
| (46 intermediate revisions by 33 users not shown) | |||
Line 3:
{{Use mdy dates|date=February 2023}}
{{Infobox software
| name
| logo = Microsoft SQL Server 2025
| screenshot =
| caption
| developer
| released
| latest release version = {{Microsoft SQL Server version}} (CU18 16.0.4185.3)<ref>[https://learn.microsoft.com/en-us/troubleshoot/sql/releases/download-and-install-latest-updates Latest updates and version history for SQL Server]</ref>
| latest release date
| programming language
| operating system
| platform
| language
| genre
| license
| website
}}
'''Microsoft SQL Server''' is a proprietary [[relational database management system]] developed by [[Microsoft]] using [[SQL|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 application]]s—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 user]]s.
== History ==
Line 30:
* 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
* 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 (operating system)|Ubuntu]] & [[Docker (software)|Docker Engine]].<ref>{{cite web |date=December 21, 2017 |title=Installation guidance for SQL Server on Linux |url=https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup#supportedplatforms |access-date=February 1, 2018 |website=microsoft.com |language=en-US}}
Line 41 ⟶ 43:
=== Currently ===
{{As of|2025|01}}, the following versions are supported by Microsoft:<ref name="SQL Server End of Support Options">{{cite web |date=Jan 2, 2025 |title= SQL Server end of support options|url=https://learn.microsoft.com/en-us/sql/sql-server/end-of-support/sql-server-end-of-support-overview?view=sql-server-ver16 |access-date=Jan 18, 2025 |website=learn.microsoft.com |publisher=learn.microsoft.com |language=en-US}}</ref>
* 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
The current version is Microsoft SQL Server 2022, released November 16, 2022. The RTM version is 16.0.1000.6.<ref>{{Cite web |title=SQL Server 2022 release notes |url=https://learn.microsoft.com/en-us/sql/sql-server/sql-server-2022-release-notes |access-date=February 14, 2023 |website=learn.microsoft.com |language=en-US}}</ref>
Line 65 ⟶ 63:
; 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.<ref name="Performance & Scalability" />
; 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.<ref name="download.microsoft.com" />
; 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
=== Specialized editions ===
Line 78 ⟶ 76:
; LocalDB: Introduced in SQL Server Express 2012, LocalDB is a minimal, on-demand, version of SQL Server that is designed for application developers.<ref name="LocalDB" /> It can also be used as an embedded database.<ref name="localdbblog" />
;Analytics Platform System (APS): Formerly Parallel Data Warehouse (PDW) A [[massively parallel|massively parallel processing]] (MPP) SQL Server appliance optimized for large-scale [[data warehouse|data warehousing]] such as hundreds of terabytes.<ref name="Analytics Platform System" />
;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.
===Discontinued editions===
;[[Microsoft Data Engine]]: Version 1.0 is based on SQL Server version 7.0.<ref>[http://images10.newegg.com/UploadFilesForNewegg/itemintelligence/Microsoft/ChoosEd1404197295752.doc Choosing an Edition of SQL Server 2000]</ref> 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
;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.<ref name="sqlmag.com"/> Similar to Standard Edition in SQL Server 2000, but Full-Text Search not working in Windows 98, transactional replication limited to subscriber.<ref>{{usurped|1=[https://web.archive.org/web/20080319025753/http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_1cdv.asp Features Supported by the Editions of SQL Server 2000]}}</ref>
;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.<ref name="StreamInsight" /> The Datacenter edition has been retired in SQL Server 2012; all of its features are available in SQL Server 2012 Enterprise Edition.<ref name="download.microsoft.com" />
;[[Windows CE]] Edition: Introduced in SQL Server 2000,<ref>{{Cite web |date=November 1, 2010 |title=Chapter 3 - Choosing an Edition of SQL Server 2000 |url=http://technet.microsoft.com/en-us/library/cc917618.aspx |archive-url=https://web.archive.org/web/20101101230646/http://technet.microsoft.com/en-us/library/cc917618.aspx |archive-date=November 1, 2010 |access-date=November 4, 2022 }}</ref> and was replaced by SQL Server 2005 Mobile Edition.
Line 92 ⟶ 90:
Tools published by Microsoft include:
*SQL Server 2000:
:* Samples:<ref name=ss2000addon>{{usurped|1=[https://web.archive.org/web/20060414164621/http://www.microsoft.com/technet/prodtechnol/sql/2000/downloads/default.mspx SQL Server 2000 - Downloads]}}</ref> 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:<ref>{{usurped|1=[https://web.archive.org/web/20060420044425/http://www.microsoft.com/sql/prodinfo/previousversions/proddoc.mspx SQL Server 2000 Product Documentation]}}</ref> SQL Server 2000 Books Online, SQL Server 2000 System Table Map, Resource Kit, SQL Server 2000 — Getting Started Guide.
== Architecture ==
Line 114 ⟶ 112:
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: [[Concurrency control|pessimistic concurrency]] and [[Optimistic concurrency control|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 [[Deadlock (computer science)|deadlock]]s—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.<ref name="storageengine" /> 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 <code>Tempdb</code>. 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.<ref name="storageengine" />
Line 142 ⟶ 140:
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 [[Common Language Runtime|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 procedure]]s and [[trigger (database)|trigger]]s can be written in any [[managed code|managed]] [[List of CLI languages|.NET language]], including [[C Sharp (programming language)|C#]] and [[VB.NET]]. [[Managed code]] can also be used to define UDT's ([[user defined type]]s), which can persist in the database. [[Managed code]] is compiled to [[Assembly (CLI)|CLI assemblies]] and after being verified for [[type safety]], registered at the database. After that, they can be invoked like any other procedure.<ref name="clroverview" /> 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.<ref name="clroverview" />
When writing code for SQL CLR, data stored in SQL Server databases can be accessed using the [[ADO.NET]] [[API]]s like any other [[managed code|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
== Service ==
Line 163 ⟶ 161:
* 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 (SQL)|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
Also, service broker supports security features like network authentication (using [[NT LAN Manager|NTLM]], [[Kerberos (protocol)|Kerberos]], or [[authorization certificate]]s), integrity checking, and message [[encryption]].<ref name="Pro SQL Server 2008 Service Broker" />
Line 171 ⟶ 169:
; 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.<ref name="Transactional Replication" />
; 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.
; 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.<ref name="Snapshot replication" />
Line 183 ⟶ 181:
{{Main|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
Reports can be designed using recent versions of [[Microsoft Visual Studio]] (Visual Studio.NET 2003, 2005, and 2008)<ref name="Reporting Services" /> with
=== Notification Services ===
Line 204 ⟶ 202:
The Full Text Search engine is divided into two processes: the ''Filter Daemon'' process (<code>msftefd.exe</code>) and the ''Search'' process (<code>msftesql.exe</code>). 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 [[iFilter]]s 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.<ref name="fts" />
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
=== SQLCMD ===
Line 211 ⟶ 209:
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.
===
{{Main|Microsoft Visual Studio}}
Line 227 ⟶ 225:
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.<ref name="Microsoft SQL Operations Studio" />
It was released to General Availability in September 2018.
=== Business Intelligence Development Studio ===
{{Main|Business Intelligence Development Studio}}
== See also ==
* [[Comparison of relational database management systems]]
* [[Comparison of
* [[Comparison of data modeling tools]]
* [[List of relational database management systems]]
Line 273 ⟶ 271:
<ref name="Merge Replication">{{ cite web | url = http://msdn.microsoft.com/en-us/library/ms152746.aspx | title = Merge Replication Overview | access-date = December 3, 2007 }}</ref>
<ref name="Snapshot replication">{{ cite web | url = http://msdn.microsoft.com/en-us/library/ms151832.aspx | title = Snapshot replication Overview | access-date = December 3, 2007 }}</ref>
<ref name="SSAS">{{ cite web | url = http://www.agiledesignllc.com/Products | archive-url = https://web.archive.org/web/20110929151255/http://www.agiledesignllc.com/Products | url-status = usurped | archive-date = September 29, 2011 | title = SSAS Entity Framework Provider | access-date = September 29, 2011 }}</ref>
<ref name="Analysis Services">{{ cite web | url = http://msdn.microsoft.com/en-us/library/ms174918.aspx | title = Analysis Services Architecture | access-date = December 3, 2007 }}</ref>
<ref name="Data Mining">{{ cite web | url = http://msdn.microsoft.com/en-us/library/ms174949.aspx | title = Data Mining Concepts | access-date = December 3, 2007 }}</ref>
Line 324 ⟶ 322:
[[Category:Windows Server System|SQL Server]]
[[Category:Relational database management software for Linux]]
| |||