Microsoft SQL Server

from Wikipedia, the free encyclopedia
Microsoft SQL Server

logo
Basic data

developer Microsoft
Publishing year April 24, 1989
Current  version 2019
( November 4, 2019 )
operating system Windows , Linux
programming language C , C ++
category RDBMS
License Microsoft EULA ( Closed Source )
German speaking Yes
http://www.microsoft.com/de-de/sql-server/

The Microsoft SQL Server is a relational database management system from Microsoft .

history

Microsoft SQL Server timetable
Version ID year Release name Project name
1.0
( OS / 2 )		 1989 SQL Server 1.0
(16bit)		 -
1.1
( OS / 2 )		 1990 SQL Server 1.1
(16bit)		 -
1.11
( OS / 2 )		 1991 SQL Server 1.1
(16bit)		 -
4.2
( OS / 2 )		 1992 SQL Server 4.2
(16bit)		 -
4.21
( WinNT )		 1993 SQL Server 4.21 SQLNT
6.0 1995 SQL Server 6.0 SQL95
6.5 1996 SQL Server 6.5 Hydra
7.0 1998 SQL Server 7.0 sphinx
- 1999 SQL Server 7.0
OLAP Tools		 plato
8.0 2000 SQL Server 2000 Shiloh
8.0 2003 SQL Server 2000
64-bit Edition		 Liberty
9.0 2005 SQL Server 2005 Yukon
10.0 2008 SQL Server 2008 Katmai
10.25 2010 SQL Azure Matrix (aka CloudDB)
10.5 2010 SQL Server 2008 R2 Kilimanjaro (aka KJ)
11.0 2012 SQL Server 2012 Denali
12.0 2014 SQL Server 2014 Hekaton
13.0 2016 SQL Server 2016
14.0 2017 SQL Server 2017 SQL Server vNext
15.0 2019 SQL Server 2019

Microsoft SQL Server emerged from a collaboration between Microsoft and Sybase in the late 1980s. In 1989 the first version for the OS / 2 operating system developed by Microsoft and IBM was published. In principle, the product corresponded to Sybase SQL Server 4.0 for Unix and VMS . In 1992 Microsoft SQL Server 4.2 for OS / 2 1.3 appeared. Following the release of Windows NT in 1993, Microsoft SQL Server 4.21 soon appeared, which relied on Windows NT as the operating system instead of OS / 2 . During this time, the cooperation between Microsoft and Sybase also loosened. In 1995, Microsoft SQL Server 6.0 was an independent further development of the cooperation, which was followed in 1996 by version 6.5. With version 7.0, which appeared in 1999, Microsoft said goodbye to the code base developed with Sybase and brought a completely new database engine onto the market. This was also the basis in the subsequent versions from SQL Server 2000.

As of version SQL Server 2017, support for the following Linux systems has been added: Red Hat Enterprise Linux , SUSE Linux Enterprise Server , Ubuntu and Docker .

properties

The SQL Server is a relational database management system that is based on the standard of the current SQL version. The Microsoft SQL Server is available in different editions that cover a wide range. The editions differ mainly in price, their functions and hardware restrictions. The MSSQL server can also be used as a data warehouse by giving employees in a company a view of the business and its data. With its business intelligence platform, it offers a scalable infrastructure that enables IT to promote the use of business intelligence throughout the company and to provide business intelligence where users want it. The SQL Server consists of many services, such as B. Analysis Services, Reporting Services and Integration Services , and tools, e.g. B. the SQL Server Data Tools (SSDT).

Microsoft SQL Server uses the SQL variant T-SQL ( Transact-SQL ) for database queries . T-SQL mainly adds additional syntax for use in stored procedures and transactions . MSSQL also supports OLE DB and ODBC (Open Database Connectivity).

Since SQL Server 2005 (code name “Yukon”), programming languages ​​that run on the .NET CLR have been supported for creating stored procedures. A suitable IDE has been supplied with Visual Studio since 2005 .

In a Windows installation (both on servers and on individual systems) several (identical or different) MSSQL servers can run simultaneously, which are referred to as instances. Each instance can in turn contain several databases.

Redundancy / failsafe

Microsoft offers a number of techniques for storing data redundantly.

Clustering Replication Log Shipping Mirroring AlwaysOn
Availability Groups 		AlwaysOn
Failover Cluster Instance
introduction SQL Server 6.5 SQL Server 7.0 SQL Server 2000 SQL Server 2005 SQL Server 2012 SQL Server 2012
Minimum. License Standard / (Web) Enterprise: Peer-To-Peer
Standard: Snapshot / Transactional / Merge 		Standard / (Web) default Enterprise default
Max. Number of copies 15th unlimited unlimited 1 3 unlimited
Additional infrastructure shared network resource (Distributor Server) Monitoring server
(optional) 		Witness Server
(Optional) 		- shared network resource
Failover manual /
automatic 		only manually only manually manual /
automatic 		manual /
automatic 		manual /
automatic
Designation source Node Publisher Primary Principal Primary Node
Designation copy Node Subscriber Secondary Mirror Secondary Node
Backup of Server / instance Database (objects) Database Database Database (group) Server / instance

It is possible to operate certain redundancy technologies in parallel at the same time - for example database replication together with an AlwaysOn availability group. “Database replication” is further divided into the sub-variants “Snapshot Replication”, “Transactional Replication”, “Merge Replication” and “Peer-To-Peer-Replication”.

Versions

SQL Server 2012

The SQL Server 2012 developed under the code name Denali was released on March 6, 2012. In the case of the editions, Microsoft dropped the Datacenter Edition that was only introduced in the predecessor and instead introduced a new Business Intelligence edition . The innovations or improvements took place in all areas of the SQL Server, including in the database module, the analysis service, the reporting service and the replication.

The most important changes and innovations include performance optimization for SharePoint, the high availability solution “Always On”, the cloud solution “ Azure ”, the data quality services “DQS”, contained database and column store index.

In addition, the SQL programming interface T-SQL has been expanded to include additional functions. On the other hand, some features of the previous versions have been removed in SQL Server 2012 and many features have been marked as deprecated , which means that these features will be removed in future versions.

New T-SQL functions in SQL Server 2012
  • Conversion:
    • PARSE ()
    • TRY_CONVERT ()
    • TRY_PARSE ()
  • Date and Time:
    • DATEFROMPARTS ()
    • DATETIME2FROMPARTS ()
    • DATETIMEOFFSETFROMPARTS ()
    • EOMONTH ()
    • SMALLDATETIMEFROMPARTS ()
    • TIMEFROMPARTS ()
  • Logic:
    • CHOOSE ()
    • IIF ()
  • String:
    • CONCAT ()
    • FORMAT()
  • Analytics:
    • CUME_DIST ()
    • FIRST_VALUE ()
    • LAG ()
    • LAST_VALUE ()
    • LEAD ()
    • PERCENTILE_CONT ()
    • PERCENTILE_DISC ()
    • PERCENT_RANK ()

SQL Server 2014

The 2014 version (code name Hekaton , released April 1, 2014) of the SQL Server has been further optimized with regard to high-performance processing of large amounts of data in order to improve real-time transaction processing . This includes the ability to keep tables or entire databases completely in main memory ( in-memory database ) as well as improved handling of column-based indexes.

It is now also possible to store SQL Server data files in Azure and to host a SQL Server database on a virtual machine in Azure.

Encryption of backups during the backup process using AES 128, AES 192, AES 256 and Triple DES has been added.

The support for failover cluster instances has been improved.

SQL Server 2017

The current version 2017 was released on October 2, 2017.

The SQL Server 2017 can now be run from multiple Linux distributions that can run on Docker containers and SQL VM in Azure.

The support for machine learning has been expanded. R and Python are now supported as the language for data analysis.

The engine now also includes an automatic database optimization.

Extensions of dynamic management views and functions

new T-SQL text commands

Editions

There are different editions of the product depending on the version of Microsoft SQL Server. The editions differ either in their functional scope or the maximum hardware support. Thus, higher-quality editions have access to more memory or more processors , which means they offer more performance. The respective name of an edition indicates its intended location or purpose. For example, the Datacenter Edition, the most expensive version under SQL Server 2008, is used for large data centers , while the SQL Server Web Edition is specially designed for web hosts or websites .

The following table lists an overview of the various SQL Server versions and their available editions:

version Enterprise Data center Business intelligence default express Workgroup Web Developer
SQL Server 2000 Yes No No Yes yes ( MSDE ) Yes No Yes
SQL Server 2005 Yes No No Yes Yes Yes No Yes
SQL Server 2008/2008 R2 Yes Yes No Yes Yes Yes Yes Yes
SQL Server 2012 Yes No Yes Yes Yes No Yes Yes
SQL Server 2014 Yes No Yes Yes Yes No Yes Yes
SQL Server 2016 Yes No No Yes Yes No Yes Yes
SQL Server 2017 Yes No No Yes Yes No Yes Yes

Data types

The data types supported by Microsoft SQL are listed below. Each of the types supports the zero value in addition to its value range .

group Subgroup Surname Memory requirement in bytes Range of values support comment
Numerical values Integer bit 1 0 to 1 0 or 1 or NULL, up to 8 columns in a table share the byte
tinyint 1 0 to 255
smallint 2 −32768 to 32767
int 4th −2147483648 to 2147483647
bigint 8th −9223372036854775808 to 9223372036854775807
Fixed point smallmoney 4th −214748.3648 to 214748.3647 int / 10000
money 8th −922337203685477.5808 to 922337203685477.5807 bigint / 10000
numeric / decimal 5-17 −10 ^ 38 +1 to 10 ^ 38 −1
Floating / floating point real 4th −3.40E + 38 to 3.40E + 38 IEEE 754
float 8th −1.79E + 308 to 1.79E + 308 IEEE 754
date and time smalldatetime 4th 1900-01-01 00:00 to 2079-06-06 23:59 only minute
datetime 8th 1753-01-01 00:00:00 to 9999-12-31 23: 59: 59.997
datetime2 6-8 0001-01-01 00:00:00 to 9999-12-31 23: 59: 59.9999999 since 2008
datetimeoffset 8-10 0001-01-01 00:00:00 to 9999-12-31 23: 59: 59.9999999 since 2008 with time zone
date 3 0001-01-01 to 9999-12-31 since 2008 only days
time 3-5 00: 00: 00.0000000 to 23: 59: 59.9999999 since 2008 100 ns
Strings Non-Unicode char 1-8000 variable CodePage
varchar 1-8000 variable CodePage
varchar (max) 1 byte – 2 GB variable CodePage
text 16 bytes – 2 GB Obsolete (from 2012) Alias: CLOB, variable CodePage
Unicode (UTF16) nchar 2-8000 CodePage 1200 Unicode
nvarchar 2-8000 CodePage 1200 Unicode
nvarchar (max) 2 bytes – 2 GB CodePage 1200 Unicode
ntext 16 bytes – 2 GB Obsolete (from 2012) Alias: NCLOB, CodePage 1200 Unicode
Binary binary 1-8000
varbinary 1-8000
varbinary (max) 1 byte – 2 GB
image 16 bytes – 2 GB Obsolete (from 2012) Alias: BLOB
Other for tables sqlvariant can be anything
xml has methods
uniqueidentifier 16 GUID
timestamp 8th auto update, record change counter, bigint binary
geography since 2008 has methods
geometry since 2008 Geometry data, has methods
hierarchyid since 2008 has methods
only procedures cursor virtual list
table virtual list

literature

SQL Server 2016
SQL Server 2014
SQL Server 2012
  • Kalen Delaney: Microsoft SQL Server 2012 Internals . 2013, ISBN 978-0-7356-5856-1 .
  • Itzik Ben-Gan: Microsoft SQL Server 2012 T-SQL Fundamentals . 2012, ISBN 978-0-7356-5814-1 .
  • Grant Fritchey: SQL Server 2012 Query Performance Tuning . 3. Edition. 2012, ISBN 978-1-4302-4203-1 .
  • Christian Bolton, Justin Langford, Glenn Berry, Gavin Payne, Amit Banerjee, Rob Farley: Professional SQL Server 2012 Internals and Troubleshooting . 2012, ISBN 978-1-118-17765-5 .
  • Jason Brimhall, David Dye, Jonathan Gennick, Andy Roberts, and Wayne Sheffield: SQL Server 2012 T-SQL Recipes . 3. Edition. 2012, ISBN 978-1-4302-4200-0 .
  • Jason Strate, Ted Krueger: Expert Performance Indexing for SQL Server 2012 . 2012, ISBN 978-1-4302-3741-9 .
SQL Server 2008
Further
  • Michael Wagner: SQL / XML: 2006 - Evaluation of the standard conformity of selected database systems . Diplomica Verlag, 2010, ISBN 3-8366-9609-6 .

Web links

Commons : Microsoft SQL Server  - collection of pictures, videos and audio files

Individual evidence

  1. ↑ a b Installing SQL Server 2017 on Linux , December 21, 2017, accessed March 5, 2018
  2. ↑ SQL Server Version and SP IDs on Microsoft SQL Server Version List (accessed April 26, 2018)
  3. ↑ SQL Server 7 "Sphinx" on Winsupersite.com (accessed September 13, 2011)
  4. ↑ PRB: An error message on Microsoft.com (accessed September 13, 2011)
  5. ↑ MSDN : SQL MythBusters - "SQL Server is really a Sybase product not a Microsoft one."
  6. ↑ http://www.microsoft.com/germany/sql/2008/default.mspx
  7. ↑ Microsoft MSDN: Configure Replication for AlwaysOn Availability Groups (SQL Server)
  8. ^ What's New in SQL Server 2012
  9. ↑ Microsoft MSDN: Discontinued Database Engine Functionality in SQL Server 2012
  10. ↑ a b c d Microsoft TechNet : Deprecated Database Engine Features in SQL Server 2012
  11. ^ Microsoft MSDN: Programmability Enhancements
  12. ↑ SQL Server Blog: SQL Server 2014 Now Generally Available
  13. ↑ MSDN: What's New (Database Engine) - SQL Server 2014
  14. ↑ SQL Server Blog: SQL Server 2017 Now Generally Available
  15. ↑ Microsoft Cloud: SQL Server 2017: What's New in the Database Engine? April 19, 2017. Retrieved May 17, 2019 .
  16. ↑ Prashanth Jayaram: What's new in SQL Server 2017. In: sqlshack. Accessed May 17, 2019 .
  17. ↑ Microsoft: SQL Server 2000 - Editions
  18. ↑ Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Version A from the Microsoft Download Center, accessed February 15, 2013.
  19. ↑ Microsoft: SQL Server 2005 - Editions
  20. ↑ Microsoft: SQL Server 2012 - Editions
  21. ↑ MSDN: Editions and Components of SQL Server 2014
  22. ↑ SQL Server Pricing , accessed June 8, 2016
  23. ↑ SQL Server 2017 SP1 - Editions | Microsoft. Retrieved on March 14, 2018 (German).
  24. ↑ http://msdn.microsoft.com/de-de/library/ms186939.aspx