Tuesday, May 18, 2010

QuickTime

QuickTime is integrated with Mac OS X, but it was once an optional component at install for earlier versions of Mac OS. QuickTime for Microsoft Windows is downloadable, either as a standalone installation or bundled with iTunes.
Software development kits (SDKs) for QuickTime are available to the public with an Apple Developer Connection (ADC) subscription.
QuickTime is available free of charge for both Mac OS X and Windows operating systems. Some other free player applications that rely on the QuickTime framework provide features not available in the basic QuickTime Player. For example:
  • iTunes can export audio in WAV, AIFF, MP3, AAC, and Apple Lossless.
  • In Mac OS X, a simple AppleScript can be used to play a movie in full-screen mode.[4] However, since version 7.2 the QuickTime Player now also supports full-screen viewing in the non-pro version.[5]

[edit] QuickTime Pro

QuickTime Player is limited to only basic playback operations unless the user purchases a QuickTime Pro license key, which Apple sells for US$29.95. Apple's "Pro Applications" (e.g. Final Cut Studio, Logic Studio) come with a free QuickTime Pro license. Pro keys are specific to the major version of QuickTime for which they are purchased and unlocks additional features of the QuickTime Player application on Mac OS X or Windows (although most of these can be accessed simply by using players, video editors or miscellaneous utilities from other sources).[6] Use of the Pro key does not entail any additional downloads.
Features enabled by the Pro license include, but are not limited to:
  • Editing clips through the cut, copy and paste functions, merging separate audio and video tracks, and freely placing the video tracks on a virtual canvas with the options of cropping and rotation.
  • Saving and exporting (encoding) to any of the codecs supported by QuickTime. QuickTime 7 includes presets for exporting video to a video-capable iPod, Apple TV, and the iPhone.
  • Saving existing QuickTime movies from the web directly to a hard disk drive. This is often, but not always, either hidden or intentionally blocked in the standard mode. It should be noted that two options exist for saving movies from a web browser:
    • Save as source – This option will save the embedded video in its original format. (i.e., not limited to *.mov files.)
    • Save as QuickTime movie – This option will save the embedded video in a *.mov file format no matter what the original container is/was.
Mac OS X v10.6 "Snow Leopard", includes QuickTime X, the latest version of the player. This version lacks cut, copy and paste and will only export to 4 formats, but its limited export feature is free. Users do not have an option to upgrade to a pro version of QuickTime X, but those who have already purchased QuickTime 7 Pro and are upgrading to Snow Leopard from a previous version of Mac OS X will have QuickTime 7 stored in the Utilities folder. Otherwise, users will have to specify during installation that they want to install QuickTime 7 on their computers.

[edit] QuickTime framework

The QuickTime framework provides the following:
  • Encoding and transcoding video and audio from one format to another.
  • Decoding video and audio, then sending the decoded stream to the graphics or audio subsystem for playback. In Mac OS X, QuickTime sends video playback to the Quartz Extreme (OpenGL) Compositor.[7]
  • A “component” plug-in architecture for supporting additional 3rd-party codecs (such as DivX).
As of early 2008, the framework hides many of the older codecs listed below from the user although users must enable the option to "Show legacy encoders" in QuickTime Preferences to use them.[8] The framework supports the following file types and codecs natively:[9]
Audio Video Picture

[edit] QuickTime file format

QuickTime Movie
Filename extension .mov
.qt
Internet media type video/quicktime
Type code MooV
Uniform Type Identifier com.apple.quicktime-movie
Developed by Apple Inc.
Type of format Media container
Container for Audio, video, text
Extended to MPEG-4 Part 12
The QuickTime (.mov) file format functions as a multimedia container file that contains one or more tracks, each of which stores a particular type of data: audio, video, effects, or text (e.g. for subtitles). Each track either contains a digitally-encoded media stream (using a specific codec) or a data reference to the media stream located in another file. Tracks are maintained in a hierarchical data structure consisting of objects called atoms. An atom can be a parent to other atoms or it can contain media or edit data, but it cannot do both.[11]
The ability to contain abstract data references for the media data, and the separation of the media data from the media offsets and the track edit lists means that QuickTime is particularly suited for editing, as it is capable of importing and editing in place (without data copying). Other later-developed media container formats such as Microsoft's Advanced Systems Format or the open source Matroska and Ogg containers lack this abstraction, and require all media data to be rewritten after editing.
Other file formats that QuickTime supports natively (to varying degrees) include AIFF, WAV, DV-DIF, MP3, and MPEG program stream. With additional QuickTime Components, it can also support ASF, DivX Media Format, Flash Video, Matroska, Ogg, and many others.

[edit] QuickTime and MPEG-4

On February 11, 1998, the ISO approved the QuickTime file format as the basis of the MPEG-4 file format.[citation needed] The MPEG-4 file format specification was created on the basis of the QuickTime format specification published in 2001.[12] The MP4 (.mp4) file format was published in 2001 as the revision of the MPEG-4 Part 1: Systems specification published in 1999 (ISO/IEC 14496-1:2001).[13][14][15] In 2003, the first version of MP4 format was revised and replaced by MPEG-4 Part 14: MP4 file format (ISO/IEC 14496-14:2003).[16] The MP4 file format was generalized into the ISO Base Media File Format ISO/IEC 14496-12:2004, which defines a general structure for time-based media files. It in turn is used as the basis for other multimedia file formats (for example 3GP, Motion JPEG 2000).[17][18][19][20][21] A list of all registered extensions for ISO Base Media File Format is published on the official registration authority website www.mp4ra.org
 
. This registration authority for code-points in "MP4 Family" files is Apple Computer Inc. and it is named in Annex D (informative) in MPEG-4 Part 12.[20] By 2000, MPEG-4 formats became industry standards, first appearing with support in QuickTime 6 in 2002. Accordingly, the MPEG-4 container is designed to capture, edit, archive, and distribute media, unlike the simple file-as-stream approach of MPEG-1 and MPEG-2.[22]

[edit] Profile support

QuickTime 6 added limited support for MPEG-4; specifically encoding and decoding using Simple Profile (SP). Advanced Simple Profile (ASP) features, like B-frames, were unsupported (in contrast with, for example, encoders such as XviD or 3ivx). QuickTime 7 supports the H.264 encoder and decoder.[23]

[edit] Container benefits

Use Passthrough to change to the MP4 container without re-encoding the stream.
Because both the MOV and MP4 containers can use the same MPEG-4 codecs, they are mostly interchangeable in a QuickTime-only environment. However, MP4, being an international standard, has more support. This is especially true on hardware devices, such as the Sony PSP and various DVD players; on the software side, most DirectShow / Video for Windows codec packs[24][25] include an MP4 parser, but not one for MOV.
In QuickTime Pro's MPEG-4 Export dialog, an option called "Passthrough" allows a clean export to MP4 without affecting the audio or video streams. One recent discrepancy ushered in by QuickTime 7 is that the MOV file format now supports multichannel audio (used, for example, in the high-definition trailers on Apple's site[26]), while QuickTime's support for audio in the MP4 container is limited to stereo.[citation needed] Therefore multichannel audio must be re-encoded during MP4 export.[citation needed]

[edit] History

QuickTime platform compatibility
Macintosh
OS Latest version
System 6.0-System 6.0.5 2.0[citation needed]
System 6.0.7-System 7.0.1 2.5
 
(68K) System 7.1-8.1 4.0.3
 
(PPC) System 7.1.2-System 7.5.3 4.0.3
 
(PPC) System 7.5.5-8.5.1 5.0.5
 
Mac OS 8.6-9.2.2 6.0.3
 
Mac OS X v10.0.4 5.0 (bundled)
Mac OS X v10.1.5 6.3.1
 
Mac OS X v10.2.8 6.5.3
 
Mac OS X v10.3.9 7.5
 
Mac OS X v10.4.11 7.6.4
 
Mac OS X v10.5.8 7.6.6
 
(current)
Mac OS X v10.6.3 10.0
 
(current) / 7.6.6 (optional extra)
Windows
OS Latest version
Windows 3.1x/Windows NT 3.1-3.5 2.1.2
 
Windows NT 3.51 2.1.2
 
Windows 95 5.0.5
 
; 6.3 unofficially
Windows NT 4.0 6.1
 
; 6.5.2 unofficially
Windows 98/ME 6.5.2
 
Windows 2000 7.1.6
 
; 7.2 unofficially
Windows XP/Vista/7 7.6.6
 
(current)
Apple released the first version of QuickTime on December 2, 1991 as a multimedia add-on for System Software 6 and later. The lead developer of QuickTime, Bruce Leak, ran the first public demonstration at the May 1991 Worldwide Developers Conference, where he played Apple's famous 1984 TV commercial on a Mac, an astounding technological breakthrough at the time. Microsoft's competing technology—Video for Windows—employed several thousand lines of allegedly stolen Quicktime source code
 
and did not appear until November 1992.

[edit] QuickTime 1.x

The original video codecs included:
  • the Apple Video codec (also known as "Road Pizza"), suited to normal live-action video
  • the Animation codec, which used run-length encoding and better suited cartoon-type images with large areas of flat color
  • the Graphics codec, for 8-bit images, including ones that had undergone dithering
The first commercial project produced using QuickTime 1.0 was the CD-ROM From Alice to Ocean
 
. The first publicly visible use of QuickTime was Ben & Jerry's interactive factory tour (dubbed The Rik & Joe Show after its in-house developers). The Rik and Joe Show was demonstrated onstage at MacWorld in San Francisco when John Sculley announced QuickTime. Apple released QuickTime 1.5 for Mac OS in the latter part of 1992. This added the SuperMac-developed Cinepak vector-quantization video codec (initially known as Compact Video), which managed the previously unheard-of feat of playing back video at 320 x 240 resolution at 30 frames per second on a 25 MHz 68040 CPU. It also added text tracks, which allowed for things like captioning, lyrics, etc., at very little addition to the size of a movie.
In an effort to increase the adoption of QuickTime, Apple contracted an outside company, San Francisco Canyon Company, to port QuickTime to the Windows platform. Version 1.0 of QuickTime for Windows provided only a subset of the full QuickTime API, including only movie playback functions driven through the standard movie controller.
QuickTime 1.6.x came out the following year. Version 1.6.2 first incorporated the "QuickTime PowerPlug" which replaced some components with PowerPC-native code when running on PowerPC Macs.

[edit] QuickTime 2.x

Apple released QuickTime 2.0 for Mac OS in February 1994—the only version never released for free. It added support for music tracks, which contained the equivalent of MIDI data and which could drive a sound-synthesis engine built into QuickTime itself (using a limited set of instrument sounds licensed from Roland), or any external MIDI-compatible hardware, thereby producing sounds using only small amounts of movie data.
Following Bruce Leak's departure to Web TV the leadership of the QuickTime team was taken over by Peter Hoddie.
QuickTime 2.0 for Windows appeared in November 1994 under the leadership of Paul Charlton. As part of the development effort for cross-platform QuickTime, Charlton (as architect and technical lead), along with ace individual contributor Michael Kellner and a small highly effective team including Keith Gurganus, ported a subset of the Macintosh Toolbox to Intel and other platforms (notably, MIPS and SGI Unix variants) as the enabling infrastructure for the QuickTime Media Layer (QTML) which was first demonstrated at the Apple Worldwide Developers Conference (WWDC) in May 1996. The QTML later became the foundation for the Carbon API which allowed legacy Macintosh applications to run on the Darwin kernel in Mac OS X.
The next versions, 2.1 and 2.5, reverted to the previous model of giving QuickTime away for free. They improved the music support and added sprite tracks which allowed the creation of complex animations with the addition of little more than the static sprite images to the size of the movie. QuickTime 2.5 also fully integrated QuickTime VR 2.0.1 into QuickTime as a QuickTime extension. On January 16, 1997, Apple released the QuickTime MPEG Extension (PPC only) as an add-on to QuickTime 2.5, which added software MPEG-1 playback capabilities to QuickTime.

[edit] QuickTime 3.x

The release of QuickTime 3.0 for Mac OS on March 30, 1998 introduced the now-standard revenue model of releasing the software for free, but with additional features of the Apple-provided MoviePlayer application that end-users could only unlock by buying a QuickTime Pro license code. Since the "Pro" features were the same as the existing features in QuickTime 2.5, any previous user of QuickTime could continue to use an older version of the central MoviePlayer application for the remaining lifespan of Mac OS to 2002; indeed, since these additional features were limited to MoviePlayer, any other QuickTime-compatible application remained unaffected.
QuickTime 3.0 added support for graphics importer components that could read images from GIF, JPEG, TIFF and other file formats, and video output components which served primarily to export movie data via FireWire. Apple also licensed several third-party technologies for inclusion in QuickTime 3.0, including the Sorenson Video codec for advanced video compression, the QDesign Music codec for substantial audio compression, and the complete Roland Sound Canvas instrument set and GS Format extensions for improved playback of MIDI music files. It also added video effects which programmers could apply in real-time to video tracks. Some of these effects would even respond to mouse clicks by the user, as part of the new movie interaction support (known as wired movies).

[edit] QuickTime interactive

During the development cycle for QuickTime 3.0 part of the engineering team was working on a more advanced version of QuickTime to be known as QuickTime interactive or QTi. Although similar in concept to the wired movies feature released as part of QuickTime 3.0, QuickTime interactive was much more ambitious. It allowed any QuickTime movie to be a fully interactive and programmable container for media. A special track type was added that contained an interpreter for a custom programming language based on 68000 assembly language. This supported a comprehensive user interaction model for mouse and keyboard event handling based in part on the AML language from the Apple Media Tool.
The QuickTime interactive movie was to have been the playback format for the next generation of HyperCard authoring tool. Both the QuickTime interactive and the HyperCard 3.0 projects were canceled in order to concentrate engineering resources on streaming support for QuickTime 4.0, and the projects were never released to the public.

[edit] QuickTime 4.x

Apple released QuickTime 4.0 on June 8, 1999[27] for Mac OS 7.5.5 through 8.6 (later Mac OS 9) and Windows 95, Windows 98, and Windows NT. Three minor updates (versions 4.0.1, 4.0.2, and 4.0.3) followed.[28] It introduced features that most users now consider basic:[29]
  • Graphics exporter components, which could write some of the same formats that the previously-introduced importers could read. (GIF support was omitted, possibly because of the LZW patent.)
  • Support for the QDesign Music 2 and MPEG-1 Layer 3 audio (MP3)
  • QuickTime 4 was the first version to support streaming. It was accompanied by the release of the free QuickTime Streaming Server version 1.0.
On December 17, 1999, Apple provided QuickTime 4.1, this version's first major update.[27] Two minor versions (4.1.1 and 4.1.2) followed.[30] The most notable improvements in the 4.1.x family were:[31]

[edit] QuickTime 5.x

QuickTime 5 was one of the shortest-lived versions of QuickTime, released in April 2001 and superseded by QuickTime 6 a little over a year later. This version was the last to have greater capabilities under Mac OS 9 than under Mac OS X, and the last version of QuickTime to support Mac OS versions 7.5.5 through 8.5.1 on a PowerPC Mac and Windows 95. Version 5.0 was initially only released for Mac OS and Mac OS X on April 14, 2001, and version 5.0.1 followed shortly thereafter on April 23, 2001, supporting Mac OS, Mac OS X, and Windows.[32] Three more updates to QuickTime 5 (versions 5.0.2, 5.0.4, and 5.0.5) were released over its short lifespan.
QuickTime 5 delivered the following enhancements:[33]
  • MPEG-1 playback for Windows, and updated MPEG-1 Layer 3 audio support for all systems.
  • Sorenson Video 3 playback and export[34] (added with the 5.0.2 update).[35]
  • Realtime rendering of effects & transitions in DV files, including enhancements to DV rendering, multiprocessor support, and Altivec enhancements for PowerPC G4 systems.
  • Flash 4 playback and export.
  • A new QuickTime VR engine, adding support for cubic VR panoramas.

[edit] QuickTime 6.x

On July 15, 2002, Apple released QuickTime 6.0, providing the following features:[36]
  • MPEG-4 playback, import, and export, including MPEG-4 Part 2 video and AAC Audio.
  • Support for Flash 5, JPEG 2000, and improved Exif handling
  • Instant-on streaming playback
  • MPEG-2 playback (via the purchase of Apple's MPEG-2 Playback Component)
  • Scriptable ActiveX control
QuickTime 6 was initially available for Mac OS 8.6 – 9.x, Mac OS X (10.1.5 minimum), and Windows 98, Me, 2000, and XP. However, development of QuickTime 6 for Mac OS slowed considerably in early 2003, after the release of Mac OS X v10.2 in August 2002. QuickTime 6 for Mac OS continued on the 6.0.x path, eventually stopping with version 6.0.3.[37]
QuickTime 6.1 & 6.1.1 for Mac OS X v10.1 and Mac OS X v10.2 (released October 22, 2002)[38] and QuickTime 6.1 for Windows (released March 31, 2003)[39] offered ISO-Compliant MPEG-4 file creation and fixed the CAN-2003-0168
 
vulnerability. Apple released QuickTime 6.2 exclusively for Mac OS X on April 29, 2003 to provide support for iTunes 4, which allowed AAC encoding for songs in the iTunes library.[40] (iTunes was not available for Windows until October 2003.)
On June 3, 2003, Apple released QuickTime 6.3, delivering the following:[41]
  • Support for 3GPP, including 3G Text, video, and audio (AAC and AMR codecs)
  • Support for the .3gp, .amr, and .sdv file formats via separate component
QuickTime 6.4, released on October 16, 2003 for Mac OS X v10.2, Mac OS X v10.3, and Windows, added the following:[42]
  • Addition of the Apple Pixlet codec (only for Mac OS X v10.3 and later)
  • ColorSync support
  • Integrated 3GPP
On December 18, 2003, Apple released QuickTime 6.5, supporting the same systems as version 6.4. Versions 6.5.1 and 6.5.2 followed on April 28, 2004 and October 27, 2004. These versions would be the last to support Windows 98 and Me. The 6.5 family added the following features:[43]
  • 3GPP2 and AMC mobile multimedia formats
  • QCELP voice code
  • Apple Lossless (in version 6.5.1)
QuickTime 6.5.3 was released on October 12, 2005 for Mac OS X v10.2.8 after the release of QuickTime 7.0, fixing a number of security issues.

[edit] QuickTime 7.x

Initially released on April 29, 2005 in conjunction with Mac OS X v10.4 (for version 10.3.9 and 10.4.x), QuickTime 7.0 featured the following:[44][45]
After a couple of preview Windows releases,[47] Apple released 7.0.2 as the first stable release on September 7, 2005 for Windows 2000 and Windows XP. Version 7.0.4, released on January 10, 2006 was the first universal binary version. But it suffered numerous bugs, including a buffer overrun, which is more problematic to most users.
Apple dropped support for Windows 2000 with the release of QuickTime 7.2 on July 11, 2007.[48] The last version available for Windows 2000, 7.1.6, contains numerous security vulnerabilities.[49] References to this version have been removed from the QuickTime site, but it can be downloaded from Apple's support section.[50] Apple has not indicated that they will be providing any further security updates for older versions. QuickTime 7.2 is the first version for Windows Vista.
Apple dropped support for Flash content in QuickTime 7.3, breaking content that relied on Flash for interactivity, or animation tracks. Security concerns seem to be part of the decision.[51] Flash flv files can, however, still be played in QuickTime if the free Perian plugin is added.
In QuickTime 7.3, a processor that supports SSE is required. QuickTime 7.4 does not require SSE. Unlike versions 7.2 and 7.3, QuickTime 7.4 cannot be installed on Windows XP SP1 system (its setup program checks if Service Pack 2 is installed). QuickTime 7.5 was released on June 10, 2008. QuickTime 7.5.5 was released on September 9, 2008, which requires Mac OS X v10.4 or higher, dropping 10.3 support. QuickTime 7.6 was released on January 21, 2009.

[edit] QuickTime X

Acap.svg This section may require copy-editing.
QuickTime X (pronounced QuickTime Ten[52]) was initially demonstrated at WWDC on June 8, 2009, and shipped with Mac OS X v10.6.[2].
QuickTime X includes visual chapters,[53] conversion, and sharing for YouTube and MobileMe, video editing,[54] capture of video and audio streams, screen recording,[55] GPU acceleration, and live streaming.[56]
The reason for the jump in numbering from 7 to 10 (X) was to indicate a similar break with the previous versions of the product that Mac OS X indicated. QuickTime X is fundamentally different from previous versions, in that it is provided as a Cocoa (Objective-C) framework and breaks compatibility with the previous QuickTime 7 C-based APIs that were previously used. QuickTime X was completely rewritten to implement modern audio video codecs in 64-bit. QuickTime X is a combination of two technologies: QuickTime Kit Framework (QTKit) and QuickTime X Player. QTKit is used by QuickTime player to display media. QuickTime X does not implement all of the functionality of the previous QuickTime as well as some of the codecs. When QuickTime X attempts to operate with a 32-bit codec or perform an operation not supported by QuickTime X, it will start a 32-bit helper process to perform the requested operation. The website Ars Technica revealed that QuickTime X uses QuickTime 7.x via QTKit to run older codecs that have not made the transition to 64-bit.[57]
QuickTime 7 may still be required to support older formats on Snow Leopard such as QTVR, interactive QuickTime movies, and MIDI files. In such cases, a compatible version of QuickTime 7 is included on Snow Leopard installation disc and may be installed side-by-side with QuickTime X. Users who have a Pro license for QuickTime 7 can then activate their license.[58]
A Snow Leopard-compatible version of QuickTime 7 may also be downloaded from Apple Support website.[59]

[edit] Creating software that uses QuickTime

[edit] QuickTime X

QuickTime X provides the QTKit Framework on Mac OS 10.6 and greater.[60]

[edit] Previous versions

QuickTime consists of two major subsystems: the Movie Toolbox and the Image Compression Manager. The Movie Toolbox consists of a general API for handling time-based data, while the Image Compression Manager provides services for dealing with compressed raster data as produced by video and photo codecs.
Developers can use the QuickTime software development kit (SDK) to develop multimedia applications for Mac or Windows with the C programming language or with the Java programming language (see QuickTime for Java), or, under Windows, using COM/ActiveX from a language supporting this.
The COM/ActiveX option was introduced as part of QuickTime 7 for Windows and is intended for programmers who want to build standalone Windows applications using high-level QuickTime movie playback and control with some import, export, and editing capabilities. This is considerably easier than mastering the original QuickTime C API.[61]
QuickTime 7 for Mac introduced the QuickTime Kit (aka QTKit), a developer framework that is intended to replace previous APIs for Cocoa developers. This framework is for Mac only, and exists as Objective-C abstractions around a subset of the C interface. Mac OS X v10.5 extends QTKit to full 64-bit support. The QTKit allows multiplexing between Quicktime X and Quicktime 7 behind the scenes so that the user need not worry about which version of Quicktime they need to use.

[edit] Bugs and vulnerabilities

QuickTime 7.4 was found to disable Adobe's video compositing program, After Effects.[62][63][64] This was due to the DRM built into version 7.4 since it allowed movie rentals from iTunes. QuickTime 7.4.1 resolved this issue.[65]
Versions 4.0 through 7.3 contained a buffer overflow bug which could compromise the security of a PC using either the QuickTime Streaming Media client, or the QuickTime player itself.[66] The bug was fixed in version 7.3.1.
Some versions of QuickTime prior to version 7.5.5 are known to be vulnerable to a cross-site scripting problem.[citation needed]
QuickTime 7.6 has been found to disable Macintosh users' ability to play certain games such as Civilization IV and The Sims 2. There are fixes available from the publisher, Aspyr.[67]
Quicktime 7 lacks support for H.264 Sample Aspect Ratio [68]. Quicktime X does not have this limitation; however, many Apple products (such as iTunes and Apple TV) still use the older Quicktime 7 engine.
http://en.wikipedia.org/wiki/Quicktime
05/18/10
Posted by at No comments:
Labels: , ,

SQL Server 2003

Genesis

SQL Server Release History
Version Year Release Name Codename
1.0
(OS/2)		 1989 SQL Server 1.0
(16bit)		 -
1.1
(OS/2)		 1991 SQL Server 1.1
(16bit)		 -
4.21
(WinNT)		 1993 SQL Server 4.21 SQLNT
6.0 1995 SQL Server 6.0 SQL95
6.5 1996 SQL Server 6.5 Hydra
7.0 1998 SQL Server 7.0 Sphinx
- 1999 SQL Server 7.0
OLAP Tools		 Plato
8.0 2000 SQL Server 2000 Shiloh
8.0 2003 SQL Server 2000
64-bit Edition		 Liberty
9.0 2005 SQL Server 2005 Yukon
10.0 2008 SQL Server 2008 Katmai
10.5 2010 SQL Server 2008 R2 Kilimanjaro (aka KJ)
The code base for MS SQL Server (prior to version 7.0) originated in Sybase SQL Server, and was Microsoft's entry to the enterprise-level database market, competing against Oracle, 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, VMS, etc. Microsoft SQL Server 4.2 was shipped around 1992 (available bundled with Microsoft 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 eight 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 ETL tool (SQL Server Integration Services or SSIS), a Reporting Server, an OLAP and data mining server (Analysis Services), and several messaging technologies, specifically Service Broker and Notification Services.

[edit] 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 data. For this purpose, it defined an xml data type that could be used either as a data type in database columns or as literals 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;CLR Integration was the main features with this edition where one could write SQL code as Managed Code these are those code which are being executed by CLR(Common Language Runtime). 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 services using TDS packets encapsulated within SOAP (protocol) requests. When the data is accessed over web services, results are returned as XML.[1]
For relational data, T-SQL has been augmented with error handling features (try/catch) and support for recursive queries (Common Table Expressions). SQL Server 2005 has also been enhanced with new indexing algorithms and better error recovery systems. Data pages are checksummed 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 is easier. SQL CLR was introduced with SQL Server 2005 to let it integrate with the .NET Framework.[2]
SQL Server 2005 introduced "MARS" (Multiple Active Results Sets), a method of allowing usage of database connections for multiple purposes.[3]

[edit] SQL Server 2008

 This section may require cleanup to meet Wikipedia's quality standards. Please improve this section if you can. (April 2009)
The current version of SQL Server, SQL Server 2008,[4][5] was released (RTM) on August 6, 2008[6] 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 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 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 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.[5]
Other new data types include specialized date and time types and a Spatial data type for location-dependent data.[7] Better support for unstructured and semi-structured data is provided using the new FILE STREAM[8] data type, which can be used to reference any file stored on the file system.[9] 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 APIs 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.[10] SQL Server 2008 also natively supports hierarchical data, and includes T-SQL constructs to directly deal with them, without using recursive queries.[10]
The Full-Text Search functionality has been integrated with the database engine, which simplifies management and improves performance.[11]
Search Wikibooks Wikibooks has a book on the topic of
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.[12]
SQL Server includes better compression features, which also helps in improving scalability. It also includes Resource Governor that allows reserving resources for certain users or workflows. It also includes capabilities for transparent encryption of data as well as compression of backups.[8] 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.[13] 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.[14] 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.[7] The version of SQL Server Management Studio included with SQL Server 2008 supports IntelliSense for SQL queries against a SQL Server 2008 Database Engine.[15] 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.[16]

[edit] 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.[17] 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.[18]
SQL Server 2008 R2 boasts a number of new services,[19] 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 that manages multiple SQL Servers.

[edit] Editions

Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users. These editions are:[20][21]
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 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.
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.[22]
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[23]). The entire database is stored in a single .mdf 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.[24]
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.[25]
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-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.

[edit] Architecture

[edit] 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 pipes, and Shared memory. Consequently, access to SQL Server is available over these protocols. In addition, the SQL Server API is also exposed over web services.[21]

[edit] Data storage

The main unit of data storage is a database, which is a collection of tables with typed columns. SQL Server supports different data types, including primary types such as Integer, Float, Decimal, Char (including character strings), Varchar (variable length character strings), binary (for unstructured 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: SELECT Round(2.5, 0) 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 views, stored procedures, indexes and constraints, along with a transaction log. A SQL Server database can contain a maximum of 231 objects, and can span multiple OS-level files with a maximum file size of 220 TB.[21] The data in the database are stored in primary data files with an extension .mdf. Secondary data files, identified with an .ndf extension, are used to store optional metadata. Log files are identified with the .ldf extension.[21]
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.[26]
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. Rows in each partition are stored in either B-tree or heap structure. If the table has an associated 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 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.[27]

[edit] Buffer management

SQL Server buffers 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.[28]

[edit] Logging and Transaction

SQL Server ensures that any change to the data is ACID-compliant, i.e., it uses 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.[21]

[edit] 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: pessimistic concurrency and 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 deadlocks - 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.[21] 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 Tempdb. 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.[21]

[edit] Data retrieval

The main mode of retrieving data from an SQL Server database is querying for it. The query is expressed using a variant of SQL called T-SQL, a dialect 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 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 statement and a 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.[21]
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 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.[21][29]
SQL Server also 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 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 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.[30] Execution plans for stored procedures are also cached as necessary.

[edit] SQL CLR

Main article: 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 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 procedures and triggers can be written in any managed .NET language, including C# and VB.NET. Managed code can also be used to define UDT's (user defined types), which can persist in the database. Managed code is compiled to .NET assemblies and after being verified for type safety, registered at the database. After that, they can be invoked like any other procedure.[31] 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.[31]
When writing code for SQL CLR, data stored in SQL Server databases can be accessed using the ADO.NET APIs like any other 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 context connection parameter to true 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).[32]

[edit] Services

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

[edit] Service Broker

Used inside an instance, it is used to provide an asynchronous programming environment. For cross instance applications, Service Broker communicates The Service Broker, which runs as a part of the database engine, provides a reliable messaging and message queuing platform for SQL Server applications. over TCP/IP and allows the different components to be synchronized together, via exchange of messages.[33]

[edit] Replication Services

SQL Server Replication Services are used by SQL Server to replicate and synchronize database objects, either in 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. Replication follows a publisher/subscriber model, i.e., the changes are sent out by one database server ("publisher") and are received by others ("subscribers"). SQL Server supports three different types of replication:[34]
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 in near real time.[35]
Merge replication
Changes made at both the publisher and subscriber databases are tracked, and periodically the changes are synchronized bi-directionally between the publisher and the subscribers. If the same data has been modified differently in both the publisher and the subscriber databases, synchronization will result in a conflict which has to be resolved - either manually or by using pre-defined policies. rowguid needs to be configured on a column if merge replication is configured.[36]
Snapshot replication
Snapshot replication published a copy of the entire database (the then-snapshot of the data) and replicates out to the subscribers. Further changes to the snapshot are not tracked.[37]

[edit] Analysis Services

Main article: SQL Server Analysis Services
SQL Server Analysis Services adds OLAP and data mining capabilities for SQL Server databases. The OLAP engine supports MOLAP, ROLAP and HOLAP storage modes for data. Analysis Services supports the XML for Analysis standard as the underlying communication protocol. The cube data can be accessed using MDX queries.[38] Data 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 in data mining.[39]

[edit] Reporting Services

Main article: SQL Server Reporting Services
SQL Server Reporting Services is a report generation environment for data gathered from SQL Server databases. It is administered via a web interface. Reporting services features a web services interface to support the development of custom reporting applications. Reports are created as RDL files.[40]
Reports can be designed using recent versions of Microsoft Visual Studio (Visual Studio.NET 2003, 2005, and 2008)[41] with Business Intelligence Development Studio, installed or with the included Report Builder. Once created, RDL files can be rendered in a variety of formats[42] including Excel, PDF, CSV, XML, TIFF (and other image formats),[43] and HTML Web Archive.

[edit] Notification Services

Main article: SQL Server Notification Services
Originally introduced as a post-release add-on for SQL Server 2000,[44] Notification Services was bundled as part of the Microsoft SQL Server platform for the first and only time with SQL Server 2005.[45][46] with Sql Server 2005, 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 as 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. These methods include SMTP, SOAP, or by writing to a file in the filesystem.[47] Notification Services was discontinued by Microsoft with the release of SQL Server 2008 in August 2008, and is no longer an officially supported component of the SQL Server database platform.

[edit] Integration Services

Main article: SQL Server Integration Services
SQL Server Integration Services is used to integrate data from different data sources. It is used for the ETL capabilities for SQL Server for data warehousing needs. Integration Services includes GUI tools to build data extraction workflows integration various functionality such as extracting data from various sources, querying data, transforming data including aggregating, duplication and merging data, and then loading the transformed data onto other sources, or sending e-mails detailing the status of the operation as defined by the user.[48]

[edit] Full Text Search Service

Main article: SQL Server Full Text Search
The SQL Server Full Text Search service architecture
SQL Server Full Text Search service is a specialized indexing and querying service for unstructured text stored in SQL Server databases. The full text search index can be created on any column with character based text data. It allows for words to be searched for in the text columns. While it can be performed with the SQL LIKE operator, using SQL Server Full Text Search service can be more efficient. Full Text Search (FTS) allows 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 allows linguistic matching ("inflectional search"), i.e., linguistic variants of a word (such as a verb in a different tense) will also be a match for a given word (but with a lower rank than an exact match). Proximity searches are also supported, i.e., if the words searched for do not occur in the sequence they are specified in 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.[49][50]
The Full Text Search engine is divided into two processes - the Filter Daemon process (msftefd.exe) and the Search process (msftesql.exe). These processes interact with the SQL Server. The Search process includes the indexer (that creates the full text indexes) and the full text query processor. The indexer scans through text columns in the database. It can also index through binary columns, and use iFilters to extract meaningful text from the binary blob (for example, when a Microsoft Word document is stored as an unstructured binary file in a database). The iFilters are hosted by the Filter Daemon process. Once the text is extracted, the Filter Daemon process breaks it up into a sequence of words and hands it over 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 in. SQL Server itself includes a Gatherer component that monitors changes to tables and invokes the indexer in case of updates.[51]
When a full text query is received by the SQL Server query processor, it is handed over to the FTS query processor in the Search process. The FTS query processor breaks 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.[51]

[edit] Tools

[edit] 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 .sql 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, which is functionally equivalent and many of the command line parameters are identical.

[edit] 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.

[edit] 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.[52] 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).[53]
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.[54] It can be used to visually observe and analyze query plans and optimize the database performance, among others.[55] 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.[21]

[edit] Business Intelligence Development Studio

Business Intelligence Development Studio (BIDS) is the IDE from Microsoft used for developing data 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 customizes with the SQL Server services-specific extensions and project types, including tools, controls and projects for reports (using Reporting Services), Cubes and data mining structures (using Analysis Services).[56]

[edit] Programmability

[edit] T-SQL

Main article: 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.[57]

[edit] SQL Native Client

SQL Native Client is the native client side data access 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 ADO or OLE DB. The SQL Native Client can also be directly used, bypassing the generic data access layers.[58]

[edit] See also

Posted by at No comments:
Labels: , ,
Subscribe to: Posts (Atom)