Examine individual changes

'{{Infobox Software | name = Microsoft SQL Server | logo = [[Image:SQLServer2008Logo.png|250px]] | screenshot = | caption = | frequently updated = yes | programming language = [[C (programming language)|C]], [[C++]] | developer = [[Microsoft]] | latest release version = SQL Server 2008 R2 (10.50.1600.1) | latest release date = {{release date|2010|4|21}} | operating system = [[Microsoft Windows]] | genre = [[Relational database management system|RDBMS]] | status = Active | language = Multi-lingual | license = [[Microsoft EULA]] | website = [http://www.microsoft.com/sqlserver www.microsoft.com/sqlserver] }} '''Microsoft SQL Server''' is a [[relational model]] [[database server]] produced by [[Microsoft]]. Its primary [[query language]]s are [[Transact-SQL|T-SQL]] and [[SQL|ANSI SQL]]. ==History== ===Genesis=== {| style = "float:right; margin-left:1em;margin-right:0; font-size:90%" class="wikitable" |+ SQL Server Release History |- ! Version !! Year !! Release Name !! Codename |- |1.0<br/>([[OS/2]]) || 1989 || SQL Server 1.0<br/>(16bit) || - |- |1.1<br/>([[OS/2]]) || 1991 || SQL Server 1.1<br/>(16bit) || - |- | 4.21<br/>([[Windows NT|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<br/>[[OLAP]] Tools ||Plato |- | 8.0 || 2000 || SQL Server 2000 || Shiloh |- | 8.0 || 2003 || SQL Server 2000<br/>64-bit Edition || Liberty |- | 9.0 || 2005 || SQL Server 2005 ||Yukon |- | 10.0 || 2008 || SQL Server 2008 || Katmai |- |10.5 || 2010 || SQL Server 2008 R2 || Kilimanjaro (aka KJ) |} The [[code base]] for MS SQL Server (prior to version 7.0) originated in [[Adaptive Server Enterprise|Sybase SQL Server]], and was Microsoft's entry to the enterprise-level database market, competing against [[Oracle database|Oracle]], [[International Business Machines|IBM]], and, later, [[Sybase]]. Microsoft, Sybase and [[Ashton-Tate]] originally teamed up to create and market the first version named SQL Server 1.0 for [[OS/2]] (about 1989) which was essentially the same as Sybase SQL Server 3.0 on [[Unix]], [[Virtual Memory System|VMS]], etc. Microsoft SQL Server 4.2 was shipped around 1992 (available bundled with IBM [[OS/2]] version 1.3). Later Microsoft SQL Server 4.21 for Windows NT was released at the same time as Windows NT 3.1. Microsoft SQL Server v6.0 was the first version designed for NT, and did not include any direction from Sybase. About the time [[Windows NT]] was released, Sybase and Microsoft parted ways and each pursued their own design and marketing schemes. Microsoft negotiated exclusive rights to all versions of SQL Server written for Microsoft operating systems. Later, Sybase changed the name of its product to [[Adaptive Server Enterprise]] to avoid confusion with Microsoft SQL Server. Until 1994, Microsoft's SQL Server carried three Sybase copyright notices as an indication of its origin. Since parting ways, several revisions have been done independently. SQL Server 7.0 was a rewrite from the legacy Sybase code. It was succeeded by SQL Server 2000, which was the first edition to be launched in a variant for the [[IA-64]] architecture. In the ten years since release of Microsoft's previous SQL Server product (SQL Server 2000), advancements have been made in performance, the client IDE tools, and several complementary systems that are packaged with SQL Server 2005. These include: an [[Extract, transform, load|ETL]] tool (SQL Server Integration Services or [[SQL Server Integration Services|SSIS]]), a Reporting Server, an [[OLAP]] and [[data mining]] server ([[Microsoft Analysis Services|Analysis Services]]), and several messaging technologies, specifically Service Broker and Notification Services. ===SQL Server 2005=== [[SQL Server]] 2005 (codenamed Yukon), released in October 2005, is the successor to SQL Server 2000. It included native support for managing [[XML]] data, in addition to [[relational database|relational data]]. For this purpose, it defined an <code>xml</code> [[data type]] that could be used either as a data type in database columns or as [[Literal (computer science)|literal]]s in queries. XML columns can be associated with [[XSD]] schemas; XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database. Specialized indexing methods were made available for XML data. XML data is queried using [[XQuery]]; [[Common Language Runtime]] (CLR) integration was a main features with this edition, enabling one to write SQL code as Managed Code by the CLR. SQL Server 2005 added some extensions to the [[T-SQL]] language to allow embedding XQuery queries in T-SQL. In addition, it also defines a new extension to XQuery, called XML DML, that allows query-based modifications to XML data. SQL Server 2005 also allows a database server to be exposed over [[web service]]s using TDS packets encapsulated within [[SOAP (protocol)]] requests. When the data is accessed over web services, results are returned as XML.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms170809.aspx | title = Database Engine XML Enhancements | accessdate = 2007-12-03}}</ref> For relational data, [[T-SQL]] has been augmented with error handling features (try/catch) and support for recursive queries with CTEs (Common Table Expressions). SQL Server 2005 has also been enhanced with new indexing algorithms, syntax and better error recovery systems. Data pages are [[checksum]]med for better error resiliency, and optimistic concurrency support has been added for better performance. Permissions and access control have been made more granular and the query processor handles concurrent execution of queries in a more efficient way. Partitions on tables and indexes are supported natively, so scaling out a database onto a [[Cluster (computing)|cluster]] is easier. SQL CLR was introduced with SQL Server 2005 to let it integrate with the .NET Framework.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms170910.aspx | title = Database Engine Enhancements | accessdate = 2007-12-03}}</ref> SQL Server 2005 introduced "MARS" (Multiple Active Results Sets), a method of allowing usage of database connections for multiple purposes.<ref>[http://msdn.microsoft.com/en-us/library/ms345109(SQL.90).aspx Multiple Active Result Sets (MARS) in SQL Server 2005] (retrieved June 20, 2009)</ref> SQL Server 2005 introduced DMVs (Dynamic Management Views), which are specialized views and functions that return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.<ref>[http://msdn.microsoft.com/en-us/library/ms188754.aspx Dynamic Management Views and Functions] (retrieved June 06, 2010)</ref> SQL Server 2005 introduced Database Mirroring, but it was not fully supported until the first Service Pack release (SP1). In the initial realease (RTM) of SQL Server 2005, database mirroring was available, but unsupported. In order to implement database mirroring in the RTM version, you had to apply trace flag 1400 at startup.<ref>http://support.microsoft.com/kb/907741</ref> Database mirroring is a high availability option that provides redundancy and failover capabilities at the database level. Failover can be performed manually or can be configured for automatic failover. Automatic failover requires a witness partner and an operating mode of synchronous (also known as high-safety or full safety).<ref>http://msdn.microsoft.com/en-us/library/ms189590.aspx</ref> To date, the only book written specifically on the topic of database mirroring is Pro SQL Server 2008 Mirroring by [[Robert L Davis]] and Ken Simmons, published by Apress Inc., ISBN13: 978-1-4302-2423-5, ISBN10: 1-4302-2423-1.<ref>http://www.apress.com/book/view/9781430224235</ref> ===SQL Server 2008=== {{Cleanup-section|date=April 2009}} The current version of SQL Server, '''SQL Server 2008''',<ref name="2k8">{{cite web | url = http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx | title = Microsoft SQL Server 2008| accessdate=2007-04-06}}</ref><ref name=PaulFlessner>{{cite web|title=ChannelWeb: Next SQL Server stop: Katmai|url=http://www.crn.com/storage/170702999|accessdate=2005-11-05}}</ref> was released ([[Software release life cycle#RTM|RTM]]) on August 6, 2008<ref>Announced to the SQL Server Special Interest Group at the ESRI 2008 User's Conference on August 6, 2008 by Ed Katibah (Spatial Program Manager at Microsoft)</ref> and aims to make data management [[self-tuning]], self organizing, and self maintaining with the development of ''SQL Server Always On'' technologies, to provide near-zero downtime. SQL Server 2008 also includes support for [[structured data|structured]] and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as [[Binary large object|BLOBs]] (binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them. According to [[Paul Flessner]], senior Vice President, Server Applications, [[Microsoft|Microsoft Corp.]], SQL Server 2008 can be a data storage backend for ''different varieties of data: XML, email, time/calendar, file, document, spatial, etc'' as well as perform ''search, query, analysis, sharing, and synchronization'' across all data types.<ref name=PaulFlessner /> Other new data types include specialized date and time types and a ''Spatial'' data type for ___location-dependent data.<ref name="iWeek">{{cite web | url = http://www.informationweek.com/software/showArticle.jhtml?articleID=199500164&subSection=Development | title = Microsoft Gives Peek At Next Version Of SQL Server | accessdate=2007-05-11}}</ref> Better support for unstructured and semi-structured data is provided using the new ''FILESTREAM''<ref name="mary"/> data type, which can be used to reference any file stored on the file system.<ref>{{cite web | url = http://blogs.msdn.com/stevengu/archive/2007/11/13/guest-blogger-ted-kummert.aspx | title = Guest Blogger: Ted Kummert | accessdate = 2007-11-20}}</ref> Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via [[Win32]] file handling [[API]]s as well as via SQL Server using [[T-SQL]]; doing the latter accesses the file data as a BLOB. Backing up and restoring the database backs up or restores the referenced files as well.<ref name="kleinerman">{{cite web | url = http://channel9.msdn.com/Showpost.aspx?postid=387069 | title = SQL Server 2008 for developers | author = Christian Kleinerman | publisher = [[Channel9]] | accessdate = 2008-03-07}}</ref> SQL Server 2008 also natively supports hierarchical data, and includes [[T-SQL]] constructs to directly deal with them, without using recursive queries.<ref name="kleinerman"/> The [[SQL Server Full Text Search|Full-Text Search]] functionality has been integrated with the database engine, which simplifies management and improves performance.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/cc721269.aspx | title = SQL Server 2008 Full-Text Search: Internals and Enhancements | author = Fernando Azpeitia Lopez}}</ref> {{Wikibooks|Geospatial Data in SQL Server}} Spatial data will be stored in two types. A "Flat Earth" (GEOMETRY or planar) data type represents geospatial data which has been projected from its native, spherical, coordinate system into a plane. A "Round Earth" data type (GEOGRAPHY) uses an ellipsoidal model in which the Earth is defined as a single continuous entity which does not suffer from the singularities such as the international dateline, poles, or map projection zone "edges". Approximately 70 methods are available to represent spatial operations for the [[Open Geospatial Consortium]] Simple Features for SQL, Version 1.1.<ref>{{cite web | url =http://www.directionsmag.com/editorials.php?article_id=2477&trv=1 | title = Microsoft Shares Details on SQL Server 2008 Spatial Support by Directions Staff | accessdate = 2007-09-07}}</ref> SQL Server includes better compression features, which also helps in improving scalability <Ref> Features of SQL 2008 [http://technet.microsoft.com/en-us/library/cc721270%28SQL.100%29.aspx] </ref>. It enhanced the indexing algorithms and introduced the notion of filtered indexes. It also includes ''Resource Governor'' that allows reserving resources for certain users or workflows. It also includes capabilities for [[transparent encryption]] of data (TDE) as well as compression of backups.<ref name="mary">{{cite web | url = http://blogs.zdnet.com/microsoft/?p=937 | title = One more test build to go for SQL Server 2008 | accessdate = 2006-11-13}}</ref> SQL Server 2008 supports the [[ADO.NET Entity Framework]] and the reporting tools, replication, and data definition will be built around the [[Entity Data Model]].<ref>{{cite web | url = http://oakleafblog.blogspot.com/2007/05/sql-server-to-deliver-entity-data.html | title = SQL Server "Katmai" to Deliver Entity Data Platform and Support LINQ | accessdate=2007-05-12}}</ref> [[SQL Server Reporting Services]] will gain charting capabilities from the integration of the data visualization products from Dundas Data Visualization Inc., which was acquired by Microsoft.<ref>{{cite web | url = http://www.earthtimes.org/articles/show/news_press_release,115898.shtml | title = Microsoft Details Dynamic IT Strategy at Tech-Ed 2007 | accessdate=2007-06-04}}</ref> On the management side, SQL Server 2008 includes the ''Declarative Management Framework'' which allows configuring policies and constraints, on the entire database or certain tables, declaratively.<ref name="iWeek"/> The version of [[SQL Server Management Studio]] included with SQL Server 2008 supports [[IntelliSense]] for SQL queries against a SQL Server 2008 Database Engine.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms173434.aspx | title = SQL Server IntelliSense | accessdate = 2008-08-18}}</ref> SQL Server 2008 also makes the databases available via [[Windows PowerShell]] providers and management functionality available as [[Cmdlets]], so that the server and all the running instances can be managed from [[Windows PowerShell]].<ref>{{cite web | url = http://blogs.msdn.com/powershell/archive/2007/11/13/sql-server-support-for-powershell.aspx | title = SQL Server Support for PowerShell! | accessdate = 2007-12-03}}</ref> ===SQL Server 2008 R2=== SQL Server 2008 R2 (formerly codenamed ''SQL Server "Kilimanjaro"'') was announced at TechEd 2009, and was released to manufacturing on April 21, 2010.<ref>{{cite web | url = http://blogs.technet.com/sqlserverexperts/archive/2010/04/21/sql-server-2008-r2-launches.aspx|title=SQL SQL Server 2008 R2 Launches!|accessdate=2010-04-21}}</ref> SQL Server 2008 R2 adds certain features to SQL Server 2008 including [[master data management]] system branded as ''Master Data Services'', a centralized console to manage multiple SQL Server instances, and support for more than 64 logical processors.<ref>{{cite web | url = http://blogs.technet.com/dataplatforminsider/archive/2009/05/11/teched-2009-new-sql-server-innovations.aspx | publisher = MSDN Blogs | author = SQL Server Team | accessdate = 2009-05-12 | title = TechEd 2009 – new SQL Server Innovations }}</ref> SQL Server 2008 R2 boasts a number of new services,<ref>[http://www.networkworld.com/reviews/2010/050310-microsoft-sql-server-2008-test.html?ap1=rcb networkworld.com]</ref> including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, ReportBuilder 3.0, Reporting Services Add-in for SharePoint, a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility named UC (Utility Control POint), part of AMSM (Application and Multi-Server Management) that is used to manage multiple SQL Servers.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ee364757%28SQL.100%29.aspx|title=SQL SQL Server 2008 R2 Application and Multi-Server Management|accessdate=2010-06-06}}</ref> ==Editions== Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users. These editions are:<ref>{{cite web | url = http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx | title = SQL Server 2005 Features Comparison | accessdate = 2007-12-03}}</ref><ref name="storageengine"/> ; [[SQL Server Compact|SQL Server Compact Edition (SQL CE)]]: The compact edition is an embedded database engine. Unlike the other editions of SQL Server, the SQL CE engine is based on SQL Mobile (initially designed for use with hand-held devices) and does not share the same binaries. Due to its small size (1MB DLL footprint), it has a markedly reduced feature set compared to the other editions. For example, it supports a subset of the standard data types, does not support stored procedures or Views or multiple-statement batches (among other limitations). It is limited to 4GB maximum database size and cannot be run as a Windows service, Compact Edition must be hosted by the application using it. The 3.5 version includes considerable work that supports ADO.NET Synchronization Services. ; SQL Server Datacenter Edition ; SQL Server Developer Edition: SQL Server Developer Edition includes the same features as SQL Server Enterprise Edition, but is limited by the license to be only used as a development and test system, and not as production server. This edition is available to download by students free of charge as a part of [[Microsoft]]'s [[DreamSpark]] program. ; [[Windows Internal Database|SQL Server 2005 Embedded Edition (SSEE)]]: SQL Server 2005 Embedded Edition is a specially configured named instance of the SQL Server Express database engine which can be accessed only by certain Windows Services. ; SQL Server Enterprise Edition: SQL Server Enterprise Edition is the full-featured edition of SQL Server, including both the core database engine and add-on services, while including a range of tools for creating and managing a SQL Server cluster. ; SQL Server Evaluation Edition: SQL Server Evaluation Edition, also known as the ''Trial Edition'', has all the features of the Enterprise Edition, but is limited to 180 days, after which the tools will continue to run, but the server services will stop.<ref>{{cite web | url = http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx | title = SQL Server 2008 Trial Software | accessdate = 2009-03-26}}</ref> ; [[SQL Server Express|SQL Server Express Edition]]: 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 GB memory and 4 GB database files (10 GB database files from SQL Server Express 2008 R2<ref>{{cite web | url = http://blogs.msdn.com/sqlexpress/archive/2010/04/21/database-size-limit-increased-to-10gb-in-sql-server-2008-r2-express.aspx | title = SQL Server 2008 R2 Express Database Size Limit Increased to 10GB | accessdate = 2010-04-23}}</ref>). The entire database is stored in a single <code>.mdf</code> file, and thus making it suitable for [[XCOPY deployment]]. It is intended as a replacement for [[MSDE]]. Two additional editions provide a superset of features not in the original Express Edition. The first is '''SQL Server Express with Tools''', which includes [[SQL Server Management Studio]] Basic. '''SQL Server Express with Advanced Services''' adds full-text search capability and reporting services.<ref>{{cite web | url = http://blogs.msdn.com/sqlexpress/archive/2008/08/07/what-s-up-with-sql-server-2008-express-editions.aspx | title = What's up with SQL Server 2008 Express editions | accessdate = 2008-08-15}}</ref> ; SQL Server Fast Track: SQL Server Fast Track is specifically for enterprise-scale data warehousing storage and business intelligence processing, and runs on reference-architecture hardware that is optimized for Fast Track.<ref>{{cite web | url = http://www.microsoft.com/Sqlserver/2008/en/us/fasttrack.aspx | title = Microsoft SQL Server 2008: Fast Track Data Warehouse | accessdate = 2009-03-26}}</ref> ; SQL Server Standard Edition: SQL Server Standard edition includes the core database engine, along with the stand-alone services. It differs from Enterprise edition in that it supports fewer active instances (number of nodes in a cluster) and does not include some high-availability functions such as hot-add memory (allowing memory to be added while the server is still running), and parallel indexes. ; SQL Server Web Edition: SQL Server Web Edition is a low-[[Total cost of ownership|TCO]] option for Web hosting. ; SQL Server Workgroup Edition: SQL Server Workgroup Edition includes the core database functionality but does not include the additional services. ==Architecture== ===Protocol layer=== Protocol layer implements the external interface to SQL Server. All operations that can be invoked on SQL Server are communicated to it via a Microsoft-defined format, called [[Tabular Data Stream]] (TDS). TDS is an application layer protocol, used to transfer data between a database server and a client. Initially designed and developed by Sybase Inc. for their [[Sybase SQL Server]] relational database engine in 1984, and later by Microsoft in Microsoft SQL Server, TDS packets can be encased in other physical transport dependent protocols, including [[TCP/IP]], [[Named pipe]]s, and [[Shared memory]]. Consequently, access to SQL Server is available over these protocols. In addition, the SQL Server API is also exposed over [[web service]]s.<ref name="storageengine">{{cite book | isbn = 0-7356-2105-5 | publisher = [[Microsoft Press]] | title = Inside Microsoft SQL Server 2005: The Storage Engine | author = Kalen Delaney | accessdate = 2007-11-10}}</ref> ==Data storage== The main unit of [[Computer data storage|data storage]] is a [[database]], which is a collection of tables with [[type system|typed]] columns. SQL Server supports different data types, including [[primary type]]s such as ''Integer'', ''Float'', ''Decimal'', ''Char'' (including character strings), ''Varchar'' (variable length character strings), binary (for unstructured [[Binary large object|blobs]] of data), ''Text'' (for textual data) among others. The [[rounding]] of floats to integers uses either Symmetric Arithmetic Rounding or Symmetric Round Down (''Fix'') depending on arguments: <code>SELECT Round(2.5, 0)</code> gives 3. 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³¹ objects, and can span multiple OS-level files with a maximum file size of 2²⁰ [[Terabyte|TB]].<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 an <code>.ndf</code> extension, are used to store optional metadata. 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 KB in size. A ''page'' is the basic unit of I/O for SQL Server operations. A page is marked with a 96-byte header which stores metadata about the page including the page number, page type, free space on the page and the ID of the object that owns it. Page type defines the data contained in the page - data stored in the database, index, allocation map which holds information about how pages are allocated to tables and indexes, change map which holds information about the changes made to other pages since last backup or logging, or contain large data types such as image or text. While page is the basic unit of an I/O operation, space is actually managed in terms of an ''extent'' which consists of 8 pages. A database object can either span all 8 pages in an extent ("uniform extent") or share an extent with up to 7 more objects ("mixed extent"). A row in a database table cannot span more than one page, so is limited to 8 KB in size. However, if the data exceeds 8 KB and the row contains ''Varchar'' or ''Varbinary'' data, the data in those columns are moved to a new page (or possibly a sequence of pages, called an ''Allocation unit'') and replaced with a pointer to the data.<ref name="pages">{{cite web | url = http://msdn.microsoft.com/en-us/library/ms190969.aspx | title = Pages and Extents | accessdate = 2007-12-02}}</ref> 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|cluster]]. Rows in each partition are stored in either [[B-tree]] or [[heap (data structure)|heap]] structure. If the table has an associated [[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 an index is stored in an unordered heap structure. Both heaps and B-trees can span multiple allocation units.<ref name="table">{{cite web | url = http://msdn.microsoft.com/en-us/library/ms189051.aspx | title = Table and Index Organization | accessdate = 2007-12-02}}</ref> ===Buffer management=== SQL Server [[Data buffer|buffer]]s pages in [[RAM]] to minimize disc I/O. Any 8 KB page can be buffered in-memory, and the set of all pages currently buffered is called the buffer cache. The amount of memory available to SQL Server decides how many pages will be cached in memory. The buffer cache is managed by the ''Buffer Manager''. Either reading from or writing to any page copies it to the buffer cache. Subsequent reads or writes are redirected to the in-memory copy, rather than the on-disc version. The page is updated on the disc by the Buffer Manager only if the in-memory cache has not been referenced for some time. While writing pages back to disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/O operation to complete. Each page is written along with its [[checksum]] when it is written. When reading the page back, its checksum is computed again and matched with the stored version to ensure the page has not been damaged or tampered with in the meantime.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/aa337525.aspx | title = Buffer Management | accessdate = 2007-12-02}}</ref> ===Logging and Transaction=== SQL Server ensures that any change to the data is [[ACID]]-compliant, i.e., it uses [[transaction (database)|transactions]] to ensure that any operation either totally completes or is undone if fails, but never leaves the database in an intermediate state. Using transactions, a sequence of actions can be grouped together, with the guarantee that either all actions will succeed or none will. SQL Server implements transactions using a write-ahead log. Any changes made to any page will update the in-memory cache of the page, simultaneously all the operations performed will be written to a log, along with the transaction ID which the operation was a part of. Each log entry is identified by an increasing ''Log Sequence Number'' (LSN) which ensure that no event overwrites another. SQL Server ensures that the log will be written onto the disc before the actual page is written back. This enables SQL Server to ensure integrity of the data, even if the system fails. If both the log and the page were written before the failure, the entire data is on persistent storage and integrity is ensured. If only the log was written (the page was either not written or not written completely), then the actions can be read from the log and repeated to restore integrity. If the log wasn't written then integrity is also maintained although the database state remains unchanged as if the transaction never occurred. If it was only partially written, then the actions associated with the unfinished transaction are discarded. Since the log was only partially written, the page is guaranteed to have not been written, again ensuring data integrity. Removing the unfinished log entries effectively undoes the transaction. SQL Server ensures consistency between the log and the data every time an instance is restarted.<ref name="storageengine"/> ===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. 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 turn into higher performing solution. 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]]s - in case they do, SQL Server takes remedial measures, which in many cases is to kill one of the threads entangled in a deadlock and rollback 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"/> ==Data retrieval== The main mode of retrieving data from an SQL Server database is [[database query|querying]] for it. The query is expressed using a variant of [[SQL]] called [[T-SQL]], a dialect Microsoft SQL Server shares with [[Adaptive Server Enterprise|Sybase SQL Server]] due to its legacy. The query [[declarative programming language|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 data. The sequence of actions necessary to execute a query is called a query plan. There might be multiple ways to process the same query. For example, for a query that contains a [[join (SQL)|join]] statement and a [[select (SQL)|select]] statement, executing join on both the tables and then executing select on the results would give the same result as selecting from each table and then executing the join, but result in different execution plans. In such case, SQL Server chooses the plan that is supposed to yield the results in the shortest possible time. This is called [[query optimization]] and is performed by the query processor itself.<ref name="storageengine"/> SQL Server includes a cost-based query optimizer which tries to optimize on the cost, in terms of the resources it will take to execute the query. Given a query, the query optimizer looks at the [[database schema]], the database statistics and the system load at that time. It then decides which sequence to access the tables referred in 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 column which is not unique for most of the columns (low "selectivity"), it might not be worthwhile to use the index to access the data. Finally, it decides whether to execute the query [[concurrent computing|concurrently]] or not. While a concurrent execution is more costly in terms of total processor time, because the execution is actually split to different processors might mean it will execute faster. Once a query plan is generated for a query, it is temporarily cached. For further invocations of the same query, the cached plan is used. Unused plans are discarded after some time.<ref name="storageengine"/><ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms190623.aspx | title = Single SQL Statement Processing | accessdate = 2007-12-03}}</ref> SQL Server also allows [[stored procedure]]s to be defined. Stored procedures are parameterized T-SQL queries, that are stored in the server itself (and not issued by the client application as is the case with general queries). Stored procedures can accept values sent by the client as input parameters, and send back results as output parameters. They can call defined functions, and other stored procedures, including the same stored procedure (up to a set number of times). They can be [[access control|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 (as it can be accessed by name), it reduces network traffic and somewhat improves performance.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms191436.aspx | title = Stored Procedure Basics | accessdate = 2007-12-03}}</ref> Execution plans for stored procedures are also cached as necessary. ===SQL CLR=== {{main|SQL CLR}} 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 (.NET)|.NET 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">{{cite web | url = http://msdn.microsoft.com/en-us/library/ms131045.aspx | title = Overview of CLR integration | accessdate = 2007-12-03}}</ref> 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 [[ADO.NET]] provider that allows the connection to be redirected to the same session which already hosts the running code. Such connections are called context connections and are set by setting <code>context connection</code> parameter to <code>true</code> in the connection string. SQL Server also provides several other enhancements to the [[ADO.NET]] API, including classes to work with tabular data or a single row of data as well as classes to work with internal metadata about the data stored in the database. It also provides access to the XML features in SQL Server, including [[XQuery]] support. These enhancements are also available in T-SQL Procedures in consequence of the introduction of the new XML Datatype (query,value,nodes functions).<ref name="xmlsupport">{{cite web | url = http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx | title = XML Support in SQL Server | accessdate = 2008-09-05}}</ref> Serviços == == SQL Server também inclui uma variedade de serviços adicionais . Enquanto estas não são essenciais para o funcionamento do sistema de banco de dados, que fornecem serviços de valor acrescentado em cima do sistema de gerenciamento de banco de dados central . Estes serviços ou executar como parte de algum componente do SQL Server ou fora do processo , como [[ Windows Service] ] e apresenta os seus próprios [[ API ]] de controlar e interagir com eles. Service Broker === === Utilizado dentro de um exemplo, é utilizado para fornecer um ambiente de programação assíncrona. Para aplicações cross exemplo , o Service Broker comunica o Service Broker , que funciona como uma peça do motor de banco de dados, fornece um sistema de mensagens confiável e mensagem [[ filas ] plataforma ] para aplicações SQL Server. mais de [ [TCP / IP ]] e permite que os diferentes componentes a serem sincronizados , através de troca de mensagens. <ref> url {{citar web | = http://msdn.microsoft.com/en-us/library/ms166104 . aspx | title = Apresentação Service Broker accessdate = | 2007/12/03 }} </ ref > === === Replication Services SQL Server Replication Services são usados pelo SQL Server para replicar e sincronizar objetos de banco de dados , quer na totalidade ou um subconjunto dos objetos presentes , através de agentes de replicação , o que poderia ser outro banco de dados em servidores de rede, banco de dados ou cache no lado do cliente . Replicação segue um editor / assinante modelo , ou seja, as mudanças são enviados por um servidor de banco de dados (" editor ") e são recebidos por outros ( "assinantes" ). SQL Server suporta três diferentes tipos de replicação: <ref> url {{citar web | http://msdn.microsoft.com/en-us/library/ms152531.aspx = | título = Tipos de replicação accessdate | Resumo = 2007 - 12-03 }} </ ref > ; replicação de transação : cada transação feita no banco de dados editor ( banco de dados mestre ) é sincronizada para assinantes , que atualizam suas bases de dados com a transação. A replicação transacional sincroniza dados em tempo quase real . <ref> url {{citar web | http://msdn.microsoft.com/en-us/library/ms151176.aspx = | título = replicação transacional Resumo accessdate = | 2007-12 -03 }} </ ref > ; replicação de mesclagem : As alterações feitas , tanto a editora e bancos de dados de assinantes são monitorados e periodicamente as alterações serão sincronizadas bi- direcional entre o editor e assinantes. Se os mesmos dados foi modificado de forma diferente em ambos o editor e os bancos de dados de assinantes , a sincronização irá resultar em um conflito que tem de ser resolvido - manualmente ou através de políticas pré-definidas. [[ ROWGUID ]] precisa ser configurado em uma coluna , se a replicação de mesclagem está configurado. <ref> url {{citar web | http://msdn.microsoft.com/en-us/library/ms152746.aspx = | título = Merge Replication Resumo accessdate = | 2007/12/03 }} </ ref > ; replicação de instantâneo de replicação: Snapshot publicou uma cópia do banco de dados inteiro ( o então instantâneo dos dados ) e replica para fora para os assinantes. Outras alterações ao instantâneo não são rastreadas. <ref> url {{citar web | http://msdn.microsoft.com/en-us/library/ms151832.aspx = | título = Snapshot replicação Resumo accessdate = | 2007-12 -03 }} </ ref > === === Analysis Services {{ principal | SQL Server Analysis Services }} SQL Server Analysis Services adiciona [[ OLAP ] ] e [[ mineração de dados ]] recursos para bancos de dados SQL Server. O motor OLAP suporta [[ MOLAP ]], [[ ROLAP ] ] e [[ HOLAP ] modos de armazenamento ] de dados. O Analysis Services suporta o [[ XMLA | XML for Analysis padrão ] ], como o protocolo de comunicação subjacente. Os dados do cubo pode ser acessada usando [[ Multidimensional Expressions | MDX ]] consultas. <ref> url {{citar web | http://msdn.microsoft.com/en-us/library/ms174918.aspx = | título = Análise Services Architecture accessdate = | 2007/12/03 }} </ ref > funcionalidade de mineração de dados específico é exposto através da [[ Data Mining Extensions | DMX ] linguagem de consulta ]. O Analysis Services inclui vários algoritmos - [[ árvore de decisão de aprendizagem árvores | Decisão ]], algoritmo de agrupamento , [[ Naive Bayes ] ] algoritmo , análise de séries temporais , a seqüência de agrupamento algoritmo, linear e logística [[ regressão ] análise ], e [[ neural rede] ] s - para uso em mineração de dados. <ref> url {{citar web | http://msdn.microsoft.com/en-us/library/ms174949.aspx = | título = Data Mining Conceitos accessdate = | 2007 -12-03 }} </ ref > === === Reporting Services {{ principal | SQL Server Reporting Services }} SQL Server Reporting Services é um ambiente de geração de relatórios para os dados recolhidos a partir de bases de dados SQL Server. É administrado por um [[World Wide Web web | Interface ]]. serviços Reporting possui um [ serviços web [ ]] interface para apoiar o desenvolvimento de aplicações de relatórios personalizados. Os relatórios são criados como [[ Report Definition Language | RDL ]] arquivos. <ref> url {{citar web | http://msdn.microsoft.com/en-us/library/ms159106.aspx = | título = SQL Server Reporting Serviços accessdate = | 2007/12/03 }} </ ref > Os relatórios podem ser projetados usando versões recentes do [ [Microsoft Visual Studio ]] (Visual Studio.NET 2003 , 2005 e 2008) <ref> [https: / connect.microsoft.com/VisualStudio/feedback/details/532664/cannot / -Abrir- a- sql- relatórios - serviços - rptproj arquivo ? wsignin1.0 wa = ] < / ref> com [[ Business Intelligence Development Studio ]] , instalados ou incluídos com o Report Builder. Uma vez criada , os arquivos RDL pode ser processado em uma variedade de formatos <ref> [ http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx MSDN Library: Reporting Serviços Render Method] - Veja http://msdn.microsoft.com/en-us/library/ms155397.aspx [Device Information Settings] </ ref> , incluindo Excel, [[ PDF]] , os valores de [[ separados por vírgulas | CSV ]], [ [XML] ], [[ TIFF ]] (e outros formatos de imagem ), <ref> [ http://msdn.microsoft.com/en-us/library/ms155373.aspx Image Device Information Settings] - SSRS pode tornar [ [arquivo BMP | formato BMP ]], [ [Windows Metafile | EMF ]], [[ GIF ]], [ [JPEG] ], [[ Portable Network Graphics | png ]] e [[ TIFF ] ]. </ ref> e Arquivo da Web HTML. === === Notification Services {{ principal | SQL Server Notification Services }} Originalmente introduzido como uma pós-libertação add-on para o SQL Server 2000, <ref> url {{citar web | = http://www.codeproject.com/KB/dotnet/sqlns.aspx title = | Uma introdução ao SQL Server Notification Services accessdate = | 2008/11/14 }} </ ref > Notification Services foi incluído como parte da plataforma Microsoft SQL Server pela primeira vez e apenas com o SQL Server 2005. <ref> {{citar | url = http web : / / título blog.desktopalert.net/blogs/da_blog/archive/2008/02/16/25.aspx | = SQL Server Notification Services removido do SQL Server 2008 | accessdate = 2008/09/17 }} </ ref > <ref> url {{citar web | http://technet.microsoft.com/en-us/library/ms144231.aspx = | título = Funcionalidade descontinuados no SQL Server 2008 Reporting Services accessdate = | 2008/09/17 }} </ ref com o SQL Server 2005, SQL Server Notification Services é um mecanismo de geração de notificações por dados que são enviados para os assinantes de serviços de notificação . A registos assinante para um determinado evento ou transação (que é registrado no servidor de banco de dados como um gatilho ), quando o evento ocorre, Notification Services pode usar um dos três métodos para enviar uma mensagem para o assinante informando sobre a ocorrência do evento. Estes métodos incluem SMTP, SOAP, ou escrevendo para um arquivo no sistema de arquivos. <ref> {{citar | url = http://msdn.microsoft.com/en-us/library/ms166495.aspx web | title = Apresentação SQL Server Notification Services accessdate = | 2007/12/03 }} </ ref > Notification Services foi descontinuado pela Microsoft com o lançamento do SQL Server 2008 , em agosto de 2008, e não é mais um componente de apoio oficial da plataforma de banco de dados SQL Server. === === Integration Services {{ principal | SQL Server Integration Services }} SQL Server Integration Services é usado para integrar dados de diferentes fontes de dados . Ele é usado para o [ [ extrair, transformar carga | ETL recursos ]] para o SQL Server para [ data warehouse [| armazenamento de dados ]] necessidades. O Integration Services inclui ferramentas de GUI para construir a extração de dados [ fluxo de trabalho []] s funcionalidade de integração diferentes , tais como extração de dados de várias fontes, consultando os dados , transformando dados, incluindo a agregação , a duplicação e fusão de dados , e depois carregar os dados transformados em outras fontes, ou envio de [[ e- mails ]] detalhando o status da operação , tal como definido pelo usuário. <ref> url {{citar web | = http://msdn.microsoft.com/en-us/library/ms141263.aspx | title = Integration Services Resumo accessdate = | 2007/12/03 }} </ ref > === serviço Full Text Search === {{ principal | SQL Server Full Text Search }} [[Imagem : SQL Server FTS.svg thumb | | right | 250px | O SQL Server Full Text Search serviço arquitetura ]] SQL Server Full Text Search é um serviço de indexação e consulta de serviços especializados para textos não estruturados armazenados em bases de dados SQL Server. O índice de pesquisa de texto completo pode ser criado em qualquer coluna com dados de caracteres de texto. Ele permite que as palavras a serem procuradas nas colunas de texto. Embora possa ser executado com o SQL <code> LIKE operador < / code> , usando SQL Server Full Text Search serviço pode ser mais eficiente . Full Text Search ( FTS) prevê a correspondência inexata da seqüência de origem , indicado por um '' '' Rank valor que pode variar de 0 a 1000 - uma classificação mais elevada significa uma combinação mais exata. Ele também permite que correspondem à linguística (" busca de inflexão " ), ou seja , as variantes lingüísticas de uma palavra (como um verbo em uma tensa diferentes ) também será um jogo para uma determinada palavra (mas com uma classificação mais baixa do que uma correspondência exacta ). pesquisas de proximidade também são suportados , ou seja, se as palavras procurou por não ocorrer na seqüência em que são especificadas na consulta, mas são próximos uns dos outros , eles também são considerados uma partida. T -SQL expõe os operadores especiais que podem ser usados para acessar os recursos do STF . <ref> url {{citar web | http://msdn.microsoft.com/en-us/library/ms142545.aspx = | título = Introdução ao Full- Text Search accessdate = | 2007/12/03 }} </ ref > <ref> url {{citar web | http://msdn.microsoft.com/en-us/library/ms142559.aspx = | título = Consultando o SQL Server usando o Full- Text Search accessdate = | 2007/12/03 }} </ ref > O Full Text Search Engine é dividido em dois processos - o'' processo '' Filter Daemon (<code> msftefd.exe </ code> ) e '' Pesquisa '' do processo ( <code> msftesql.exe > </ code ). Estes processos interagem com o SQL Server . O processo de pesquisa inclui o indexador ( que cria os índices de texto completo) e processador de consulta de texto completo. O indexador varre colunas de texto no banco de dados . Pode índice também através de colunas de binário, e use [ iFilter []] s para extrair sentido do texto blob binário ( por exemplo, quando a [ [Microsoft Word ] ] documento é armazenado como um arquivo binário não estruturados em um banco de dados ). O iFilters são hospedados pelo processo de daemon de filtro . Uma vez que o texto é extraído , o Filtro de Daemon processo divide- se em uma seqüência de palavras e mãos -a para o indexador. O indexador filtra os ruídos '' palavras '', ou seja, palavras como '' A '', ''e'' etc , que ocorrem com freqüência e não são úteis para a pesquisa . Com as palavras restantes, um [[ invertido índice ]] é criada , associando cada palavra com as colunas que foram encontrados dentro do SQL Server já inclui um coletor '' '' componente que monitora alterações em tabelas e invoca o indexador , no caso de atualizações . name="fts"/> <ref Quando uma consulta de texto completo é recebida pelo processador de consultas SQL Server, que é entregue ao processador de consulta do STF no processo de pesquisa. O processador de consulta do STF divide a consulta nas palavras constituintes, filtra as palavras de ruído , e usa um inbuilt [[ dicionário ]] para encontrar as variantes lingüísticas para cada palavra. As palavras são então consultados contra o índice invertido e um posto de seu rigor é computada. Os resultados são retornados para o cliente através do processo do SQL Server. name="fts"> <ref url {{citar web | http://msdn.microsoft.com/en-us/library/ms142541.aspx = | título = Full- Text Search Architecture accessdate = | 2007/12/03 }} </ ref > ==Tools== ===SQLCMD=== SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt. It can also act as a [[scripting language]] to create and run a set of SQL statements as a script. Such scripts are stored as a <code>.sql</code> file, and are used either for management of databases or to create the database schema during the deployment of a database. SQLCMD was introduced with SQL Server 2005 and this continues with SQL Server 2008. Its predecessor for earlier versions was OSQL and ISQL, which is functionally equivalent as it pertains to TSQL execution, and many of the command line parameters are identical, although SQLCMD adds extra versatility. ===Visual Studio=== '''[[Microsoft Visual Studio]]''' includes native support for data programming with Microsoft SQL Server. It can be used to write and debug code to be executed by SQL CLR. It also includes a ''data 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 as well. ===SQL Server Management Studio=== '''[[SQL Server Management Studio]]''' is a [[GUI]] tool included with SQL Server 2005 and later for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical tools that work with objects and features of the server.<ref>[http://msdn.microsoft.com/en-us/library/ms174173.aspx MSDN: Introducing SQL Server Management Studio]</ref> SQL Server Management Studio replaces [[Enterprise Manager]] as 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 as ''SQL Server Management Studio Express'' (SSMSE).<ref>[http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en SQL Server Management Studio Express]</ref> A central feature of SQL Server Management Studio is the Object Explorer, which allows the user to browse, select, and act upon any of the objects within the server.<ref>[http://msdn.microsoft.com/en-us/library/ms173849.aspx MSDN: Using Object Explorer]</ref> It can be used to visually observe and analyze query plans and optimize the database performance, among others.<ref>[http://www.sqlmag.com/articles/index.cfm?articleid=46798& SQL Server 2005 Management Tools]</ref> SQL Server Management Studio can also be used to create a new database, alter any existing database schema by adding or modifying tables and indexes, or analyze performance. It includes the query windows which provide a GUI based interface to write and execute queries.<ref name="storageengine"/> ===Business Intelligence Development Studio=== '''[[Business Intelligence Development Studio]]''' ('''BIDS''') is the [[Integrated Development Environment|IDE]] from [[Microsoft]] used for developing data analysis and [[Business Intelligence]] solutions utilizing the Microsoft SQL Server [[SQL Server Analysis Services|Analysis Services]], [[SQL Server Reporting Services|Reporting Services]] and [[SQL Server Integration Services|Integration Services]]. It is based on the [[Microsoft Visual Studio]] development environment but customizes with the SQL Server services-specific extensions and project types, including tools, controls and projects for reports (using Reporting Services), [[OLAP cube|Cubes]] and [[data mining]] structures (using Analysis Services).<ref>{{cite web | url = http://msdn.microsoft.com/hi-in/library/ms173767.aspx | title = Introducing Business Intelligence Development Studio | accessdate = 2007-12-03}}</ref> ==Programmability== ===T-SQL=== {{main|T-SQL}} T-SQL (Transact-SQL) is the primary means 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, entering and editing data in the database as well as monitoring and managing the server itself. Client applications, both which consume data or manage the server, 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 allows 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 Server using T-SQL. Linked server allows operation to multiple server as one query.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms189826.aspx | title = Transact-SQL Reference | accessdate = 2007-12-03}}</ref> ===SQL Native Client=== SQL Native Client is the native client side data access [[library (computing)|library]] for Microsoft SQL Server, version 2005 onwards. It natively implements support for the SQL Server features including the [[Tabular Data Stream]] implementation, support for mirrored SQL Server databases, full support for all data types supported by SQL Server, asynchronous operations, query notifications, [[encryption]] support, as well as receiving multiple result sets in a single database session. SQL Native Client is used under the hood by SQL Server plug-ins for other data access technologies, including [[ActiveX Data Objects|ADO]] or [[OLE DB]]. The SQL Native Client can also be directly used, bypassing the generic data access layers.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms131456.aspx | title = Features of SQL Native Client | accessdate = 2007-12-03}}</ref> ==See also== * [[Comparison of relational database management systems]] * [[Comparison of object-relational database management systems|List of object-relational database management systems]] * [[Database management system]] * [[List of relational database management systems]] ==References== {{pagenumbers}} {{reflist|2}} {{refbegin}} * Lance Delano, Rajesh George et al. (2005). ''Wrox's SQL Server 2005 Express Edition Starter Kit (Programmer to Programmer)''. ''Microsoft Press''. ISBN 0764589237 * Delaney, Kalen, et al. (2007). ''Inside SQL Server 2005: Query Tuning and Optimization''. ''Microsoft Press''. ISBN 0735621969. * Ben-Gan, Itzik, et al. (2006). ''Inside Microsoft SQL Server 2005: T-SQL Programming''. ''Microsoft Press''. ISBN 0735621977. {{refend}} ==External links== * [http://www.mytechmantra.com/LearnSQLServer/Microsoft_SQL_Server_2008_R2.html New Features in SQL Server 2008 R2] * [http://www.mytechmantra.com/LearnSQLServer/SQL_Server_Versions_P1.html SQL Server Build List] * [http://www.networkworld.com/reviews/2010/050310-microsoft-sql-server-2008-test.html?ap1=rcb Review:SQL Server 2008 R2] * [http://www.microsoft.com/sql/default.mspx SQL Server Official Site] * [http://blogs.msdn.com/euanga/archive/2006/01/19/514479.aspx Longer version of the history from a dev team member] {{Databases}} {{MS DevTools}} {{Microsoft|close}} {{DEFAULTSORT:SQL Server}} [[Category:Database management systems]] [[Category:Microsoft server technology]] [[Category:Windows Server System]] [[Category:Microsoft database software]] [[Category:Relational database management systems]] [[ar:ميكروسوفت إس كيو إل سيرفر]] [[az:Microsoft SQL Server]] [[bg:Microsoft SQL Server]] [[ca:Microsoft SQL Server]] [[cs:Microsoft SQL Server]] [[da:Microsoft SQL Server]] [[de:Microsoft SQL Server]] [[el:Microsoft SQL Server]] [[es:Microsoft SQL Server]] [[fa:اس‌کیوال سرور مایکروسافت]] [[fr:Microsoft SQL Server]] [[ko:마이크로소프트 SQL 서버]] [[hr:Microsoft SQL Server]] [[id:Microsoft SQL Server]] [[it:Microsoft SQL Server]] [[he:Microsoft SQL Server]] [[mn:MS SQL сервер]] [[nl:Microsoft SQL Server]] [[ja:Microsoft SQL Server]] [[no:Microsoft SQL Server]] [[pl:Microsoft SQL Server]] [[pt:Microsoft SQL Server]] [[ro:Microsoft SQL Server]] [[ru:Microsoft SQL Server]] [[sk:Microsoft SQL Server]] [[sv:Microsoft SQL Server]] [[ta:மைக்ரோசாப்ட் சீக்குவல் வழங்கி]] [[th:Microsoft SQL Server]] [[tr:Microsoft SQL Server]] [[uk:Microsoft SQL Server]] [[zh:Microsoft SQL Server]]'