Content deleted Content added
m Fixed k capitalization in frequency and storage capacity units (via WP:JWB) |
Undid revisions 1177133978 and 946284627: ? |
||
| (35 intermediate revisions by 26 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.
| latest release date
| programming language
| operating system
| platform
| language
| genre
| license
| website
}}
'''Microsoft SQL Server'''
== History ==
Line 32:
* 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 (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 GHz processor as a minimum, 2.0 GHz or faster is recommended.<ref name="2016 Requirements">{{cite web |date=May 2, 2016 |title=Requirements for Installing SQL Server 2016 |url=https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx |access-date=July 28, 2016 |website=docs.microsoft.com |publisher=msdn.microsoft.com |language=en-US}}</ref>
Line 77 ⟶ 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 91 ⟶ 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 103 ⟶ 102:
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 [[view (database)|views]], [[stored procedure]]s, [[index (database)|indexes]] and [[constraint (database)|constraints]], along with a transaction log. A SQL Server database can contain a maximum of 2<sup>31</sup> objects, and can span multiple OS-level files with a maximum file size of 2<sup>60</sup> bytes (1 exabyte).<ref name="storageengine" /> The data in the database are stored in primary data files with an extension <code>.mdf</code>. Secondary data files, identified with a <code>.ndf</code> 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 <code>.ldf</code> extension.<ref name="storageengine" />
Storage space allocated to a database is divided into sequentially numbered ''pages'', each 8
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 [[cluster computing|computer cluster]]. Rows in each partition are stored in either [[B-tree]] or [[heap (data structure)|heap]] structure. If the table has an associated, clustered [[index (database)|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 (database)|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.<ref name="table" />
=== Buffer management ===
SQL Server [[data buffer|buffer]]s pages in RAM to minimize disk I/O. Any 8
=== Concurrency and locking ===
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 184 ⟶ 183:
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 [[Report Definition Language|RDL]] files.<ref name="SQL Server Reporting Services" />
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 231 ⟶ 230:
{{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 272 ⟶ 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 312 ⟶ 311:
* {{Official website|http://www.microsoft.com/sqlserver}}
* {{Official website|name=2nd official website at Microsoft TechNet|1=https://technet.microsoft.com/en-us/sqlserver/default}}
{{Databases}}
| |||