History of SQL Server

In 1988, Microsoft released its first version of SQL Server.

It was designed for the OS/2 platform and was developed jointly by Microsoft and Sybase.

During the early 1990s, Microsoft began to develop a new version of SQL Server for the NT platform. While it was under development, Microsoft decided that SQL Server should be tightly coupled with the NT operating system.

In 1992, Microsoft assumed core responsibility for the future of SQL Server for NT. In 1993, Windows NT 3.1 and SQL Server 4.2 for NT were released. Microsoft's philosophy of combining a high-performance database with an easy-to-use interface proved to be very successful.

Microsoft quickly became the second most popular vendor of high-end relational database software.

In 1994, Microsoft and Sybase formally ended their partnership.

In 1995, Microsoft released version 6.0 of SQL Server. This release was a major rewrite of SQL Server's core technology. Version 6.0 substantially improved performance, provided built-in replication, and delivered centralized administration.

In 1996, Microsoft released version 6.5 of SQL Server. This version brought significant enhancements to the existing technology and provided several new features.

In 1997, Microsoft released version 6.5 Enterprise Edition. In 1998, Microsoft released version 7.0 of SQL Server, which was a complete rewrite of the database engine.

In 2000, Microsoft released SQL Server 2000. SQL Server version 2000 is Microsoft's most significant release of SQL Server to date. This version further builds upon the SQL Server 7.0 framework. According to the SQL Server development team, the changes to the database engine are designed to provide an architecture that will last for the next 10 years.

Rather than listing all the new features and enhancements found in 2000, I've decided to list my favorite changes. The remainder of this chapter is dedicated to discussing these new features found in version 2000.

SQL Server 2005

SQL Server 2005, released in November 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; 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 requests. When the data is access over web services, results are returned as XML.^[27]

For relational data, T-SQL has been augmented with error handling features and support for recursive queries. 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.

SQL Server 2008

The next version of SQL Server is SQL Server 2008, code-named "Katmai", slated to launch on February 27, 2008 and release (RTM) in Q3 2008. most recent CTP was made available on February 19, 2008. SQL Server 2008 aims^[33] 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 will also include 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.^[30]

Other new data types include specialized date and time types and a Spatial data type for location-dependent data.^[34] Better support for unstructured and semi-structured data is provided using the FILESTREAM^[35] data type has been added, which can be used to reference any file stored on the file system.^[36] 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 binary BLOB. Backing up and restoring the database backs up or restores the referenced files as well.^[37] SQL Server 2008 also natively supports hierarchical data, and included T-SQL constructs to directly deal with them, without using recursive queries.

SQL Server Release History

Version	Year	Release Name	Codename
4.21 (WinNT)	1993	SQL Server 4.21	-
6.0	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7.0	1999	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

SQL Server 2000 Vs SQL Server 2005 (Differences)

Differences in DBA

Feature	SQL Server 2000	SQL Server 2005
Security	Owner = Schema, hard to remove old users at times	Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.
Encryption	No options built in, expensive third party options with proprietary skills required to implement properly.	Encryption and key management build in.
High Availability	Clustering or Log Shipping require Enterprise Edition. Expensive hardware.	Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.
Scalability	Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.	4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.

Differences in Development

Feature	SQL Server 2000	SQL Server 2005
Server Programming Extensions	Limited to extended stored procedures, which are difficult to write and can impact the server stability.	The incorporation of the CLR into the relational engine allows managed code written in .NET languages to run. Different levels of security can protect the server from poorly written code.
T-SQL Error Handling	Limited to checking @@error, no much flexibility.	Addition of TRY/CATCH allows more mature error handling. More error_xx functions can gather additional information about errors.
T-SQL Language	SQL Language enhanced from previous versions providing strong data manipulation capabilities.	All the power of SQL Server 2000 with the addition of CTEs for complex, recursive problems, enhanced TOP capabilities, PIVOT/APPLY/Ranking functions, and ROW_NUMBER
Auditing	Limited support using triggers to audit changes.	Robust event handling with EVENT NOTIFICATIONS, the OUTPUT clauses, and DDL triggers.
Large Data Types	Limited to 8k for normal data without moving to TEXT datatypes. TEXT is hard to work with in programming environments.	Includes the new varchar(max) types that can store up to 2GB of data in a single column/row.
XML	Limited to transforming relational data into XML with SELECT statements, and some simple query work with transformed documents.	Native XML datatype, support for schemas and full XPATH/XQUERY querying of data.
ADO.NET	v1.1 of ADO.NET included enhancements for client development.	v2 has more features, including automatic failover for database mirroring, support for multiple active result sets (MARS), tracing of calls, statistics, new isolation levels and more.
Messaging	No messaging built into SQL Server.	Includes Service Broker, a full-featured asynchronous messaging system that has evolved from Microsoft Message Queue (MSMQ), which is integrated into Windows.
Reporting Services	An extremely powerful reporting environment, but a 1.0 product.	Numerous enhancements, run-time sorting, direct printing, viewer controls and an enhanced developer experience.
ETL	DTS is a very easy to use and intuitive tool. Limited capabilities for sources and transformations. Some constructs, such as loops, were very difficult to implement.	Integration Services is a true programming environment allowing almost any source of data to be used and many more types of transformations to occur. Very complex environment that is difficult for non-DBAs to use. Requires programming skills.
Full-Text Search	Workable solution, but limited in its capabilities. Cumbersome to work with in many situations.	More open architecture, allowing integration and plug-ins of third party extensions. Much more flexible in search capabilities.