SQL Server 2005
SQL
Server 2005 (formerly codenamed "Yukon") was released in October
2005. 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; 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 Tabular Data Stream (TDS)
packets encapsulated within SOAP (protocol) requests. When the data is accessed
over web services, results are returned as XML.
Common
Language Runtime (CLR) integration was introduced with this version, enabling
one to write SQL code as Managed Code by the CLR. 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 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.
SQL
Server 2005 introduced "MARS" (Multiple Active Results Sets), a
method of allowing usage of database connections for multiple purposes.
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.
Service
Pack 1 (SP1) of SQL Server 2005 introduced Database Mirroring, 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).
SQL Server 2008
SQL
Server 2008 (formerly codenamed "Katmai") was released on August 6,
2008 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.
Other
new data types include specialized date and time types and a Spatial data type
for location-dependent data. Better support for unstructured and
semi-structured data is provided using the new FILESTREAM[15] data type, which
can be used to reference any file stored on the file system. 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. SQL Server 2008
also natively supports hierarchical data, and includes T-SQL constructs to
directly deal with them, without using recursive queries.
The
Full-text search functionality has been integrated with the database engine.
According to a Microsoft technical article, this simplifies management and
improves performance.
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.
SQL
Server includes better compression features, which also helps in improving
scalability. 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. 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.
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. 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. The
version of SQL Server Management Studio included with SQL Server 2008 supports
IntelliSense for SQL queries against a SQL Server 2008 Database Engine. 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.
SQL Server 2008 R2
SQL
Server 2008 R2 (10.50.1600.1, formerly codenamed "Kilimanjaro") was
announced at TechEd 2009, and was released to manufacturing on April 21, 2010. SQL
Server 2008 R2 adds certain features to SQL Server 2008 including a master data
management system branded as Master Data Services, a central management of
master data entities and hierarchies. Also Multi Server Management, a
centralized console to manage multiple SQL Server 2008 instances and services
including relational databases, Reporting Services, Analysis Services &
Integration Services.
SQL
Server 2008 R2 includes a number of new services, including PowerPivot for
Excel and SharePoint, Master Data Services, StreamInsight, Report Builder 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.
The
first SQL Server 2008 R2 service pack (10.50.2500, Service Pack 1) was released
on July 11, 2011.
The
second SQL Server 2008 R2 service pack (10.50.4000, Service Pack 2) was
released on July 26, 2012.
SQL Server 2012
At the
2011 Professional Association for SQL Server (PASS) summit on October 11,
Microsoft announced that the next major version of SQL Server (codenamed
"Denali"), would be SQL Server 2012. It was released to manufacturing
on March 6, 2012.
It was
announced to be the last version to natively support OLE DB and instead to
prefer ODBC for native connectivity.
SQL Server 2012's new
features and enhancements include AlwaysOn SQL Server Failover Cluster
Instances and Availability Groups which provides a set of options to improve
database availability, Contained Databases which simplify the moving of
databases between instances, new and modified Dynamic Management Views and
Functions, programmability enhancements including new spatial features,
metadata discovery, sequence objects and the THROW statement, performance
enhancements such as ColumnStore Indexes as well as improvements to OnLine and
partition level operations and security
enhancements including provisioning during setup, new permissions, improved
role management and default schema assignment for groups.
In Short:
SQL SERVER 2005
·
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.
·
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 introduced "MARS"
(Multiple Active Results Sets), a method of allowing usage of database
connections for multiple purposes.
·
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.
SQL SERVER 2008
·
It 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.
·
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.
·
The Full-text search functionality has been
integrated with the database engine. According to a Microsoft technical
article, this simplifies management and improves performance.
SQL SERVER 2012
·
It was announced to be the last version to
natively support OLE DB and instead to prefer ODBC for native connectivity.
·
It includes
AlwaysOn SQL Server Failover Cluster Instances and Availability Groups which
provides a set of options to improve database availability, Contained Databases
which simplify the moving of databases between instances, new and modified
Dynamic Management Views and Functions, programmability enhancements including
new spatial features, metadata discovery, sequence objects and the THROW
statement, performance enhancements such as ColumnStore Indexes as well as
improvements to OnLine and partition level operations and security enhancements including
provisioning during setup, new permissions, improved role management and
default schema assignment for groups.
Transact-SQL
(T-SQL) is Microsoft's and Sybase's proprietary extension to
SQL. SQL, often expanded to Structured Query Language, is a standardized
computer language that was originally developed by IBM for querying, altering
and defining relational databases, using declarative statements. T-SQL expands
on the SQL standard to include procedural programming, local variables, various
support functions for string processing, data processing, mathematics, etc. and
changes to the DELETE and UPDATE statements. These additional features make
Transact-SQL Turing complete.
Transact-SQL is central to using Microsoft SQL Server.
All applications that communicate with an instance of SQL Server do so by
sending Transact-SQL statements to the server, regardless of the user interface
of the application.
All The Best!!!
No comments:
Post a Comment