Microsoft SQL Server

Microsoft SQL Server is a relational database server, developed by Microsoft: it is acomputer application artefact whose primary action is to abundance and retrieve abstracts as requested by othercomputer application applications, be it those on the aforementioned computer or those active on addition computer beyond a arrangement (including the Internet). There are at atomic a dozen altered editions of Microsoft SQL Server aimed at altered audiences and for altered workloads (ranging from baby applications that abundance and retrieve abstracts on the aforementioned computer, to millions of users and computers that admission huge amounts of abstracts from the Internet at the aforementioned time).

True to its name, Microsoft SQL Server's primary concern languages are T-SQL and ANSI SQL.

Genesis

Prior to adaptation 7.0 the cipher abject for MS SQL Server was awash by Sybase SQL Server to Microsoft, and was Microsoft's access to the enterprise-level database market, aggressive adjoin Oracle, IBM, and, later, Sybase. Microsoft, Sybase and Ashton-Tate originally teamed up to actualize and bazaar the aboriginal adaptation called SQL Server 1.0 for OS/2 (about 1989) which was about the aforementioned as Sybase SQL Server 3.0 on Unix, VMS, etc. Microsoft SQL Server 4.2 was alien about 1992 (available arranged with IBM OS/2 adaptation 1.3). After Microsoft SQL Server 4.21 for Windows NT was appear at the aforementioned time as Windows NT 3.1. Microsoft SQL Server v6.0 was the aboriginal adaptation advised for NT, and did not accommodate any administration from Sybase.

About the time Windows NT was released, Sybase and Microsoft beggared means and anniversary pursued its own architectonics and business schemes. Microsoft adjourned absolute rights to all versions of SQL Server accounting for Microsoft operating systems. Later, Sybase afflicted the name of its artefact to Adaptive Server Enterprise to abstain abashing with Microsoft SQL Server. Until 1994, Microsoft's SQL Server agitated three Sybase absorb notices as an adumbration of its origin.

SQL Server 7.0 and SQL Server 2000 included modifications and extensions to the Sybase cipher base, abacus abutment for the IA-64 architecture. By SQL Server 2005 the bequest Sybase cipher had been absolutely rewritten.

In the ten years back absolution of Microsoft's antecedent SQL Server artefact (SQL Server 2000), advancements accept been fabricated in performance, the applicant IDE tools, and several commutual systems that are packaged with SQL Server 2005. These include: an ETL apparatus (SQL Server Integration Services or SSIS), a Reporting Server, an OLAP and abstracts mining server (Analysis Services), and several messaging technologies, accurately Service Broker and Notification Services.

Protocol layer

Protocol band accouterments the alien interface to SQL Server. All operations that can be invoked on SQL Server are announced to it via a Microsoft-defined format, alleged Tabular Abstracts Stream (TDS). TDS is an appliance band protocol, acclimated to alteration abstracts amid a database server and a client. Initially advised and developed by Sybase Inc. for their Sybase SQL Server relational database agent in 1984, and after by Microsoft in Microsoft SQL Server, TDS packets can be encased in added concrete carriage abased protocols, including TCP/IP, Named pipes, and Shared memory. Consequently, admission to SQL Server is accessible over these protocols. In addition, the SQL Server API is additionally apparent over web services.31

Data storage

The capital assemblage of abstracts accumulator is a database, which is a accumulating of tables with typed columns. SQL Server supports altered abstracts types, including primary types such as Integer, Float, Decimal, Char (including appearance strings), Varchar (variable breadth appearance strings), bifold (for baggy blobs of data), Argument (for textual data) amid 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 additionally allows user-defined blended types (UDTs) to be authentic and used. It additionally makes server statistics accessible as basal tables and angle (called Dynamic Management Angle or DMVs). In accession to tables, a database can additionally accommodate added altar including views, stored procedures, indexes and constraints, forth with a transaction log. A SQL Server database can accommodate a best of 231 objects, and can amount assorted OS-level files with a best book admeasurement of 220 TB.31 The abstracts in the database are stored in primary abstracts files with an addendum .mdf. Secondary abstracts files, articular with a .ndf extension, are acclimated to abundance alternative metadata. Log files are articular with the .ldf extension.31

Storage amplitude allocated to a database is disconnected into sequentially numbered pages, anniversary 8 KB in size. A folio is the basal assemblage of I/O for SQL Server operations. A folio is apparent with a 96-byte attack which food metadata about the folio including the folio number, folio type, chargeless amplitude on the folio and the ID of the article that owns it. Folio blazon defines the abstracts independent in the folio - abstracts stored in the database, index, allocation map which holds advice about how pages are allocated to tables and indexes, change map which holds advice about the changes fabricated to added pages back aftermost advancement or logging, or accommodate ample abstracts types such as angel or text. While folio is the basal assemblage of an I/O operation, amplitude is absolutely managed in agreement of an admeasurement which consists of 8 pages. A database article can either amount all 8 pages in an admeasurement ("uniform extent") or allotment an admeasurement with up to 7 added altar ("mixed extent"). A row in a database table cannot amount added than one page, so is bound to 8 KB in size. However, if the abstracts exceeds 8 KB and the row contains Varchar or Varbinary data, the abstracts in those columns are confused to a fresh folio (or possibly a arrangement of pages, alleged an Allocation unit) and replaced with a arrow to the data.40

For concrete accumulator of a table, its rows are disconnected into a alternation of partitions (numbered 1 to n). The allotment admeasurement is user defined; by absence all rows are in a distinct partition. A table is breach into assorted partitions in adjustment to advance a database over a cluster. Rows in anniversary allotment are stored in either B-tree or abundance structure. If the table has an associated basis to acquiesce fast retrieval of rows, the rows are stored in-order according to their basis values, with a B-tree accouterment the index. The abstracts is in the blade bulge of the leaves, and added nodes autumn the basis ethics for the blade abstracts attainable from the corresponding nodes. If the basis is non-clustered, the rows are not sorted according to the basis keys. An indexed appearance has the aforementioned accumulator anatomy as an indexed table. A table after an basis is stored in an unordered abundance structure. Both abundance and B-trees can amount assorted allocation units.41

Buffer management

SQL Server buffers pages in RAM to abbreviate disc I/O. Any 8 KB folio can be buffered in-memory, and the set of all pages currently buffered is alleged the absorber cache. The bulk of anamnesis accessible to SQL Server decides how abounding pages will be buried in memory. The absorber accumulation is managed by the Absorber Manager. Either account from or autograph to any folio copies it to the absorber cache. Subsequent reads or writes are redirected to the in-memory copy, rather than the on-disc version. The folio is adapted on the disc by the Absorber Manager alone if the in-memory accumulation has not been referenced for some time. While autograph pages aback to disc, asynchronous I/O is acclimated whereby the I/O operation is done in a accomplishments cilia so that added operations do not accept to delay for the I/O operation to complete. Each folio is accounting forth with its checksum aback it is written. Aback account the folio back, its checksum is computed afresh and akin with the stored adaptation to ensure the folio has not been damaged or tampered with in the meantime.42

Logging and Transaction

SQL Server ensures that any change to the abstracts is ACID-compliant, i.e. it uses affairs to ensure that the database will consistently backslide to a accepted constant accompaniment on failure. Each transaction may abide of assorted SQL statements all of which will alone accomplish a abiding change to the database if the aftermost account in the transaction (a COMMIT statement) completes successfully. If the COMMIT auspiciously completes the transaction is cautiously on disk.

SQL Server accouterments affairs application a write-ahead log.

Any changes fabricated to any folio will amend the in-memory accumulation of the page, accompanying all the operations performed will be accounting to a log, forth with the transaction ID which the operation was a allotment of. Each log access is articular by an accretion Log Sequence Number (LSN) which is acclimated to ensure that all changes are accounting to the abstracts files. Additionally during a log restore it is acclimated to analysis that no logs are bifold or skipped. SQL Server requires that the log is accounting assimilate the disc afore the abstracts folio is accounting back. It charge additionally ensure that all operations in a transaction are accounting to the log afore any COMMIT operation is appear as completed.

At a afterwards point the server will checkpoint the database and ensure that all pages in the abstracts files accept the accompaniment of their capacity synchronised to a point at or afterwards the LSN that the checkpoint started. Aback completed the checkpoint marks that allocation of the log book as complete and may chargeless it (see Simple transaction logging vs Full transaction logging). This enables SQL Server to ensure candor of the data, alike if the arrangement fails.

On abortion the database log has to be replayed to ensure the abstracts files are in a constant state. All pages stored in the cycle advanced allotment of the log (not apparent as completed) are rewritten to the database, aback the end of the log is accomplished all accessible affairs are formed aback application the cycle aback allocation of the log file.

The database agent usually checkpoints absolutely frequently. However, in a heavily loaded database this can accept a cogent achievement impact. It is accessible to abate the abundance of checkpoints or attenuate them absolutely but the rollforward during a accretion will booty abundant longer

Concurrency and locking

SQL Server allows assorted audience to use the aforementioned database concurrently. As such, it needs to ascendancy circumstantial admission to aggregate data, to ensure abstracts candor - back assorted audience amend the aforementioned data, or audience attack to apprehend abstracts that is in the action of actuality afflicted by addition client. SQL Server provides two modes of accommodation control: bleak accommodation and optimistic concurrency. Back bleak accommodation ascendancy is actuality used, SQL Server controls circumstantial admission by application locks. Locks can be either aggregate or exclusive. Absolute lock grants the user absolute admission to the abstracts - no added user can admission the abstracts as continued as the lock is held. Aggregate locks are acclimated back some abstracts is actuality apprehend - assorted users can apprehend from abstracts bound with a aggregate lock, but not admission an absolute lock. The closing would accept to delay for all aggregate locks to be released. Locks can be activated on altered levels of granularity - on absolute tables, pages, or alike on a per-row base on tables. For indexes, it can either be on the absolute base or on base leaves. The akin of granularity to be acclimated is authentic on a per-database base by the database administrator. While a accomplished grained locking arrangement allows added users to use the table or base simultaneously, it requires added resources. So it does not automatically about-face into college assuming solution. SQL Server additionally includes two added failing alternate exclusion solutions - latches and spinlocks - which are beneath able-bodied than locks but are beneath ability intensive. SQL Server uses them for DMVs and added assets that are usually not busy. SQL Server additionally monitors all artisan accoutrement that admission locks to ensure that they do not end up in deadlocks - in case they do, SQL Server takes alleviative measures, which in abounding cases is to annihilate one of the accoutrement circuitous in a deadlock and acknowledgment the transaction it started.31 To apparatus locking, SQL Server contains the Lock Manager. The Lock Administrator maintains an in-memory table that manages the database altar and locks, if any, on them forth with added metadata about the lock. Admission to any aggregate article is advised by the lock manager, which either grants admission to the ability or blocks it.

SQL Server additionally provides the optimistic accommodation ascendancy mechanism, which is agnate to the multiversion accommodation ascendancy acclimated in added databases. The apparatus allows a fresh adaptation of a row to be created whenever the row is updated, as against to overwriting the row, i.e., a row is additionally articular by the ID of the transaction that created the adaptation of the row. Both the old as able-bodied as the fresh versions of the row are stored and maintained, admitting the old versions are confused out of the database into a arrangement database articular as Tempdb. Back a row is in the action of actuality updated, any added requests are not blocked (unlike locking) but are accomplished on the earlier adaptation of the row. If the added appeal is an amend statement, it will aftereffect in two altered versions of the rows - both of them will be stored by the database, articular by their corresponding transaction IDs.31

Data retrieval

The capital approach of retrieving abstracts from an SQL Server database is querying for it. The concern is bidding appliance a alternative of SQL alleged T-SQL, a accent Microsoft SQL Server shares with Sybase SQL Server due to its legacy. The concern declaratively specifies what is to be retrieved. It is candy by the concern processor, which abstracts out the arrangement of accomplish that will be all-important to retrieve the requested data. The arrangement of accomplishments all-important to assassinate a concern is alleged a concern plan. There ability be assorted means to action the aforementioned query. For example, for a concern that contains a accompany account and a baddest statement, active accompany on both the tables and again active baddest on the after-effects would accord the aforementioned aftereffect as selecting from anniversary table and again active the join, but aftereffect in altered beheading plans. In such case, SQL Server chooses the plan that is accepted to crop the after-effects in the beeline accessible time. This is alleged concern admission and is performed by the concern processor itself.31

SQL Server includes a cost-based concern optimizer which tries to optimize on the cost, in agreement of the assets it will booty to assassinate the query. Given a query, again the concern optimizer looks at the database schema, the database statistics and the arrangement amount at that time. It again decides which arrangement to admission the tables referred in the query, which arrangement to assassinate the operations and what admission adjustment to be acclimated to admission the tables. For example, if the table has an associated index, whether the basis should be acclimated or not - if the basis is on a cavalcade which is not altered for best of the columns (low "selectivity"), it ability not be advantageous to use the basis to admission the data. Finally, it decides whether to assassinate the concern accordingly or not. While a circumstantial beheading is added cher in agreement of absolute processor time, because the beheading is absolutely breach to altered processors ability beggarly it will assassinate faster. Once a concern plan is generated for a query, it is briefly cached. For added invocations of the aforementioned query, the buried plan is used. Unused affairs are alone afterwards some time.3143

SQL Server additionally 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 applicant appliance as is the case with accepted queries). Stored procedures can acquire ethics beatific by the applicant as ascribe parameters, and accelerate aback after-effects as achievement parameters. They can alarm authentic functions, and added stored procedures, including the aforementioned stored action (up to a set cardinal of times). They can be selectively provided admission to. Unlike added queries, stored procedures accept an associated name, which is acclimated at runtime to boldness into the absolute queries. Additionally because the cipher charge not be beatific from the applicant every time (as it can be accessed by name), it reduces arrangement cartage and somewhat improves performance.44 Beheading affairs for stored procedures are additionally buried as necessary.

SQL CLR

Microsoft SQL Server 2005 includes a basic alleged SQL CLR ("Common Accent Runtime") via which it integrates with .NET Framework. Unlike best added applications that use .NET Framework, SQL Server itself hosts the .NET Framework runtime, i.e., memory, threading and ability administration requirements of .NET Framework are annoyed by SQLOS itself, rather than the basal Windows operating system. SQLOS provides deadlock apprehension and resolution casework for .NET cipher as well. With SQL CLR, stored procedures and triggers can be accounting in any managed .NET language, including C# and VB.NET. Managed cipher can additionally be acclimated to ascertain UDT's (user authentic types), which can abide in the database. Managed cipher is aggregate to .NET assemblies and afterwards actuality absolute for blazon safety, registered at the database. Afterwards that, they can be invoked like any added procedure.45 However, alone a subset of the Base Class Library is available, back active cipher beneath SQL CLR. Best APIs apropos to user interface functionality are not available.45

When autograph cipher for SQL CLR, abstracts stored in SQL Server databases can be accessed appliance the ADO.NET APIs like any added managed appliance that accesses SQL Server data. However, accomplishing that creates a fresh database session, altered from the one in which the cipher is executing. To abstain this, SQL Server provides some enhancements to the ADO.NET provider that allows the affiliation to be redirected to the aforementioned affair which already hosts the active code. Such admission are alleged ambience admission and are set by ambience ambience affiliation constant to accurate in the affiliation string. SQL Server additionally provides several added enhancements to the ADO.NET API, including classes to assignment with collapsed abstracts or a distinct row of abstracts as able-bodied as classes to assignment with centralized metadata about the abstracts stored in the database. It additionally provides admission to the XML appearance in SQL Server, including XQuery support. These enhancements are additionally accessible in T-SQL Procedures in aftereffect of the addition of the fresh XML Datatype (query,value,nodes functions).46

Notification Services

Originally alien as a post-release add-on for SQL Server 2000,59 Notification Services was arranged as allotment of the Microsoft SQL Server belvedere for the aboriginal and alone time with SQL Server 2005.6061. SQL Server Notification Services is a apparatus for breeding data-driven notifications, which are beatific to Notification Services subscribers. A subscriber registers for a specific accident or transaction (which is registered on the database server as a trigger); back the accident occurs, Notification Services can use one of three methods to accelerate a bulletin to the subscriber allegorical about the accident of the event. These methods accommodate SMTP, SOAP, or by autograph to a book in the filesystem.62 Notification Services was discontinued by Microsoft with the absolution of SQL Server 2008 in August 2008, and is no best an clearly accurate basic of the SQL Server database platform.

Full Text Search Service

SQL Server Abounding Argument Chase account is a specialized indexing and querying account for baggy argument stored in SQL Server databases. The abounding argument chase basis can be created on any cavalcade with appearance based argument data. It allows for words to be searched for in the argument columns. While it can be performed with the SQL LIKE operator, application SQL Server Abounding Argument Chase account can be added efficient. Abounding allows for inexact analogous of the antecedent string, adumbrated by a Rank amount which can ambit from 0 to 1000 - a college rank agency a added authentic match. It additionally allows linguistic analogous ("inflectional search"), i.e., linguistic variants of a chat (such as a verb in a altered tense) will additionally be a bout for a accustomed chat (but with a lower rank than an exact match). Proximity searches are additionally supported, i.e., if the words searched for do not action in the arrangement they are defined in the concern but are abreast anniversary other, they are additionally advised a match. T-SQL exposes appropriate operators that can be acclimated to admission the FTS capabilities.6465

The Abounding Argument Chase agent is disconnected into two processes - the Filter Daemon action (msftefd.exe) and the Chase action (msftesql.exe). These processes collaborate with the SQL Server. The Chase action includes the indexer (that creates the abounding argument indexes) and the abounding argument concern processor. The indexer scans through argument columns in the database. It can additionally basis through bifold columns, and use iFilters to abstract allusive argument from the bifold balloon (for example, back a Microsoft Chat certificate is stored as an baggy bifold book in a database). The iFilters are hosted by the Filter Daemon process. Once the argument is extracted, the Filter Daemon action break it up into a arrangement of words and easily it over to the indexer. The indexer filters out babble words, i.e., words like A, And etc., which action frequently and are not advantageous for search. With the actual words, an astern basis is created, advertence anniversary chat with the columns they were begin in. SQL Server itself includes a Gatherer basic that monitors changes to tables and invokes the indexer in case of updates.66

When a abounding argument concern is accustomed by the SQL Server concern processor, it is handed over to the FTS concern processor in the Chase process. The FTS concern processor break up the concern into the basic words, filters out the babble words, and uses an built-in album to acquisition out the linguistic variants for anniversary word. The words are again queried adjoin the astern basis and a rank of their accurateness is computed. The after-effects are alternate to the applicant via the SQL Server process.66