Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. Spil a database server, it is a software product with the primary function of storing and retrieving gegevens spil requested by other software applications—which may run either on the same laptop or on another pc 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 petite single-machine applications to large Internet-facing applications with many mededinger users.

Contents

The history of Microsoft SQL Server embarks with the very first Microsoft SQL Server product – SQL Server 1.0, a 16-bit server for the OS/Two operating system ter 1989 – and extends to the current day.

Milestones Edit

  • MS SQL Server for OS/Two began spil a project to port Sybase SQL Server onto OS/Two te 1989, by Sybase, Ashton-Tate, and Microsoft.
  • SQL Server Four.Two for NT is released ter 1993, marking the entry onto Windows NT.
  • SQL Server 6.0 is released ter 1995, marking the end of collaboration with Sybase, Sybase would proceed developing their own variant of SQL Server, Sybase Adaptive Server Enterprise, independently of Microsoft.
  • SQL Server 7.0 is released ter 1998, marking the conversion of the source code from C to C++.
  • SQL Server 2005, released ter 2005, finishes the finish revision of the old Sybase code into Microsoft code.
  • SQL Server 2018, released te 2018, adds Linux support for thesis Linux platforms: Crimson Hat Enterprise Linux, SUSE Linux Enterprise Server, Ubuntu &, Docker Engine. [Trio]

Presently Edit

Spil of October 2018 [update] the following versions are supported by Microsoft:

  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016
  • SQL Server 2018

From SQL Server 2016 onward, the product is supported on x64 processors only. [Four]

The current version is Microsoft SQL Server 2018, released October Two, 2018. The RTM version is 14.1709.3807.1.

Microsoft makes SQL Server available ter numerous editions, with different feature sets and targeting different users. Thesis editions are: [Five] [6]

Mainstream editions Edit

Specialized editions Edit

Discontinued editions Edit

The protocol layer implements the outer interface to SQL Server. All operations that can be invoked on SQL Server are communicated to it via a Microsoft-defined format, called Tabular Gegevens Stream (TDS). TDS is an application layer protocol, used to transfer gegevens inbetween a database server and a client. Primarily designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine te 1984, and straks by Microsoft ter Microsoft SQL Server, TDS packets can be encased te other physical vrachtvervoer dependent protocols, including TCP/IP, named pipes, and collective memory. Consequently, access to SQL Server is available overheen thesis protocols. Ter addition, the SQL Server API is also exposed overheen web services. [6]

Gegevens storage is a database, which is a collection of tables with typed columns. SQL Server supports different gegevens types, including primary types such spil Rechtschapen, Float, Decimal, Char (including character strings), Varchar (variable length character strings), binary (for unstructured blobs of gegevens), Text (for textual gegevens) among others. The rounding of floats to integers uses either Symmetric Arithmetic Rounding or Symmetric Round Down (fix) depending on arguments: SELECT Round(Two.Five, 0) gives Three.

Microsoft SQL Server also permits user-defined composite types (UDTs) to be defined and used. It also makes server statistics available spil virtual tables and views (called Dynamic Management Views or DMVs). Ter addition to tables, a database can also contain other objects including views, stored procedures, indexes and constraints, along with a transaction loom. A SQL Server database can contain a maximum of Two 31 objects, and can span numerous OS-level files with a maximum verkeersopstopping size of Two 60 bytes (1 exabyte). [6] The gegevens te the database are stored ter primary gegevens files with an extension .mdf . Secondary gegevens files, identified with a .ndf extension, are used to permit the gegevens of a single database to be spread across more than one opstopping, and optionally across more than one opstopping system. Loom files are identified with the .ldf extension. [6]

Storage space allocated to a database is divided into sequentially numbered pages, each 8 KB te size. A pagina is the basic unit of I/O for SQL Server operations. A pagina is marked with a 96-byte header which stores metadata about the pagina including the pagina number, pagina type, free space on the pagina and the ID of the object that possesses it. Pagina type defines the gegevens contained ter the pagina: gegevens stored ter the database, index, allocation schrijfmap which holds information about how pages are allocated to tables and indexes, switch opbergmap which holds information about the switches made to other pages since last backup or logging, or contain large gegevens types such spil picture or text. While pagina is the basic unit of an I/O operation, space is actually managed te terms of an extent which consists of 8 pages. A database object can either span all 8 pages ter an extent (“uniform extent”) or share an extent with up to 7 more objects (“mixed extent”). A row ter a database table cannot span more than one pagina, so is limited to 8 KB te size. However, if the gegevens exceeds 8 KB and the row contains varchar or varbinary gegevens, the gegevens te those columns are moved to a fresh pagina (or possibly a sequence of pages, called an allocation unit) and substituted with a pointer to the gegevens. [20]

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 te a single partition. A table is split into numerous partitions te order to spread a database overheen a laptop cluster. Rows ter each partition are stored ter either B-tree or heap structure. If the table has an associated, clustered index to permit rapid retrieval of rows, the rows are stored in-order according to their index values, with a B-tree providing the index. The gegevens is ter the leaf knot of the leaves, and other knots storing the index values for the leaf gegevens reachable from the respective knots. If the index is non-clustered, the rows are not sorted according to the index keys. An indexed view has the same storage structure spil an indexed table. A table without a clustered index is stored ter an unordered heap structure. However, the table may have non-clustered indices to permit quick retrieval of rows. Ter some situations the heap structure has spectacle advantages overheen the clustered structure. Both heaps and B-trees can span numerous allocation units. [21]

Buffer management Edit

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

Concurrency and locking Edit

SQL Server permits numerous clients to use the same database concurrently. Spil such, it needs to control mededinger access to collective gegevens, to ensure gegevens integrity—when numerous clients update the same gegevens, or clients attempt to read gegevens that is te the process of being switched 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 mededinger access by using locks. Locks can be either collective or special. Special lock grants the user sensational access to the data—no other user can access the gegevens spil long spil the lock is held. Collective locks are used when some gegevens is being read—multiple users can read from gegevens locked with a collective lock, but not acquire an sensational lock. The latter would have to wait for all collective locks to be released.

Locks can be applied on different levels of granularity—on entire tables, pages, or even on a per-row onderstel 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 poot by the database administrator. While a fine-grained locking system permits more users to use the table or index at the same time, it requires more resources, so it does not automatically yield higher show. SQL Server also includes two more lightweight mutual exclusion solutions—latches and spinlocks—which are less sturdy 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 ter deadlocks—in case they do, SQL Server takes remedial measures, which te many cases are to kill one of the threads entangled ter a deadlock and roll back the transaction it began. [6] 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 collective 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 ter other databases. The mechanism permits a fresh version of a row to be created whenever the row is updated, spil 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 spil well spil the fresh versions of the row are stored and maintained, however the old versions are moved out of the database into a system database identified spil Tempdb . When a row is ter 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 ter two different versions of the rows—both of them will be stored by the database, identified by their respective transaction IDs. [6]

The main mode of retrieving gegevens from a SQL Server database is querying for it. The query is voiced using a variant of SQL called T-SQL, a flinterdun 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 gegevens. The sequence of deeds necessary to execute a query is called a query project. There might be numerous 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 spil selecting from each table and then executing the join, but result ter different execution plans. Te such case, SQL Server chooses the project that is expected to yield the results te the shortest possible time. This is called query optimization and is performed by the query processor itself. [6]

SQL Server includes a cost-based query optimizer which attempts to optimize on the cost, te 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 geyser at that time. It then determines which sequence to access the tables referred te 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 katern which is not unique for most of the columns (low “selectivity”), it might not be worthwhile to use the index to access the gegevens. Ultimately, it determines whether to execute the query concurrently or not. While a mededinger execution is more costly ter terms of total processor time, because the execution is actually split to different processors might mean it will execute quicker. Once a query project is generated for a query, it is temporarily cached. For further invocations of the same query, the cached project is used. Unused plans are discarded after some time. [6] [23]

SQL Server also permits stored procedures to be defined. Stored procedures are parameterized T-SQL queries, that are stored te the server itself (and not issued by the client application spil is the case with general queries). Stored procedures can accept values sent by the client spil input parameters, and send back results spil output parameters. They can call defined functions, and other stored procedures, including the same stored proces (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 (spil it can be accessed by name), it reduces network traffic and somewhat improves spectacle. [24] Execution plans for stored procedures are also cached spil necessary.

T-SQL Edit

T-SQL (Transact-SQL) is the secondary means [ citation needed ] of programming and managing SQL Server. It exposes keywords for the operations that can be performed on SQL Server, including creating and altering database schemas, coming in and editing gegevens te the database spil well spil monitoring and managing the server itself. Client applications that consume gegevens 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. SQL Server permits it to be managed using T-SQL. 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 permit a single query to process operations performed on numerous servers. [25]

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

SQL Server Native Client is the native client side gegevens access library for Microsoft SQL Server, version 2005 onwards. It natively implements support for the SQL Server features including the Tabular Gegevens Stream implementation, support for mirrored SQL Server databases, utter support for all gegevens types supported by SQL Server, asynchronous operations, query notifications, encryption support, spil well spil receiving numerous result sets ter a single database session. SQL Server Native Client is used under the fetish mask by SQL Server plug-ins for other gegevens access technologies, including ADO or OLE DB. The SQL Server Native Client can also be directly used, bypassing the generic gegevens access layers. [26]

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

SQL CLR Edit

Microsoft SQL Server 2005 includes a component named SQL CLR (“Common Language Runtime”) via which it integrates with .Televisiekanaal Framework. Unlike most other applications that use .Televisiekanaal Framework, SQL Server itself hosts the .Netwerk Framework runtime, i.e., memory, threading and resource management requirements of .Televisiekanaal Framework are sated by SQLOS itself, rather than the underlying Windows operating system. SQLOS provides deadlock detection and resolution services for .Televisiekanaal code spil well. With SQL CLR, stored procedures and triggers can be written te any managed .Nipt language, including C# and VB.Nipt. Managed code can also be used to define UDT’s (user defined types), which can persist ter 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 proces. [28] 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. [28]

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

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

Service Broker Edit

Used inwards an example, programming environment. For cross-instance applications, Service Broker communicates overheen TCP/IP and permits the different components to be synchronized, via exchange of messages. The Service Broker, which runs spil a part of the database engine, provides a reliable messaging and message queuing toneel for SQL Server applications. [30]

Replication Services Edit

SQL Server Replication Services are used by SQL Server to replicate and synchronize database objects, either ter 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. Lulla goes after a publisher/subscriber monster, i.e., the switches are sent out by one database server (“publisher”) and are received by others (“subscribers”). SQL Server supports three different types of replication: [31]

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 ter near real time. [32] Merge replication Switches made at both the publisher and subscriber databases are tracked, and periodically the switches are synchronized bi-directionally inbetween the publisher and the subscribers. If the same gegevens has bot modified differently te both the publisher and the subscriber databases, synchronization will result ter a conflict which has to be resolved, either by hand or by using pre-defined policies. rowguid needs to be configured on a katern if merge replication is configured. [33] Snapshot replication Snapshot replication publishes a copy of the entire database (the then-snapshot of the gegevens) and replicates out to the subscribers. Further switches to the snapshot are not tracked. [34]

Analysis Services Edit

SQL Server Analysis Services adds OLAP and gegevens mining capabilities for SQL Server databases. The OLAP engine supports MOLAP, ROLAP and HOLAP storage modes for gegevens. Analysis Services supports the XML for Analysis standard spil the underlying communication protocol. The cube gegevens can be accessed using MDX and LINQ [35] queries. [36] Gegevens 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 ter gegevens mining. [37]

Reporting Services Edit

SQL Server Reporting Services is a report generation environment for gegevens 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-made reporting applications. Reports are created spil RDL files. [38]

Reports can be designed using latest versions of Microsoft Visual Studio (Visual Studio.Netwerken 2003, 2005, and 2008) [39] with Business Intelligence Development Studio, installed or with the included Report Builder. Once created, RDL files can be rendered te a multiplicity of formats, [40] [41] including Excel, PDF, CSV, XML, BMP, EMF, Toxine, JPEG, PNG, and TIFF, [42] and HTML Web Archive.

Notification Services Edit

Originally introduced spil a post-release add-on for SQL Server 2000, [43] Notification Services wasgoed bundled spil part of the Microsoft SQL Server verhoging for the very first and only time with SQL Server 2005. [44] [45] 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 spil 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. Thesis methods include SMTP, SOAP, or by writing to a opstopping ter the filesystem. [46] Notification Services wasgoed discontinued by Microsoft with the release of SQL Server 2008 te August 2008, and is no longer an officially supported component of the SQL Server database podium.

Integration Services Edit

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

Utter Text Search Service Edit

SQL Server Utter Text Search service is a specialized indexing and querying service for unstructured text stored te SQL Server databases. The total text search index can be created on any katern with character based text gegevens. It permits for words to be searched for te the text columns. While it can be performed with the SQL LIKE technicus, using SQL Server Total Text Search service can be more efficient. Utter permits 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 permits linguistic matching (“inflectional search”), i.e., linguistic variants of a word (such spil a verb ter a different tense) will also be a match for a given word (but with a lower rank than an precies match). Proximity searches are also supported, i.e., if the words searched for do not occur ter the sequence they are specified ter 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. [48] [49]

The Utter Text Search engine is divided into two processes: the Filterzakje Daemon process ( msftefd.exe ) and the Search process ( msftesql.exe ). Thesis processes interact with the SQL Server. The Search process includes the indexer (that creates the utter text indexes) and the utter text query processor. The indexer scans through text columns ter the database. It can also index through binary columns, and use iFilters to samenvatting meaningful text from the binary blob (for example, when a Microsoft Word document is stored spil an unstructured binary verkeersopstopping ter a database). The iFilters are hosted by the Filterzakje Daemon process. Once the text is extracted, the Filterzakje Daemon process cracks it up into a sequence of words and forearms it overheen 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 ter. SQL Server itself includes a Gatherer component that monitors switches to tables and invokes the indexer ter case of updates. [50]

When a total text query is received by the SQL Server query processor, it is transferred overheen to the FTS query processor ter the Search process. The FTS query processor violates 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. [50]

SQLCMD Edit

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

SQLCMD wasgoed introduced with SQL Server 2005 and this proceeds with SQL Server 2012, 2014 and 2016. Its predecessor for earlier versions wasgoed OSQL and ISQL, which is functionally omschrijving spil it pertains to TSQL execution, and many of the directive line parameters are identical, albeit SQLCMD adds toegevoegd versatility.

Visual Studio Edit

Microsoft Visual Studio includes native support for gegevens programming with Microsoft SQL Server. It can be used to write and debug code to be executed by SQL CLR. It also includes a gegevens 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 spil well.

SQL Server Management Studio Edit

SQL Server Management Studio is a GUI contraption included with SQL Server 2005 and straks for configuring, managing, and administering all components within Microsoft SQL Server. The instrument includes both script editors and graphical devices that work with objects and features of the server. [51] SQL Server Management Studio substitutes Enterprise Manager spil 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 spil SQL Server Management Studio Express (SSMSE). [52]

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

SQL Server Operations Studio Edit

SQL Server Operations Studio (Preview) is a cross toneel query editor available spil an optional download. The contraption permits users to write queries, uitvoer query results, commit SQL scripts to GIT repositories and perform basic server diagnostics. SQL Server Operations Studio supports Windows, Mac and Linux systems. [55]

Business Intelligence Development Studio Edit

Business Intelligence Development Studio (BIDS) is the IDE from Microsoft used for developing gegevens 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 implements, controls and projects for reports (using Reporting Services), Cubes and gegevens mining structures (using Analysis Services). [56] For SQL Server 2012 and straks, this IDE has bot renamed SQL Server Gegevens Implements (SSDT).

Related movie: MacBook price at Dubai Duty Free


Leave a Reply

Your email address will not be published. Required fields are marked *