SQL Server 2008 R2 vs. 2005

Before I began to write this article, I had planned to cover all possible new features in SQL Server 2008 R2 comparing it to the 2005 edition. This appeared to be impossible because there are so many additions and enhancements to this edition that writing about each of them would fill a book. After I excluded the features that are not included in the edition of the servers we are running in our data centers, those that are disabled for the security or performance reasons, and those that require administrative rights at the instance level, I was still left with a significant amount of information to cover. So here are the additions that in my opinion will be useful to the most users.

New Features in Management Studio

The Management Studio also comes with new features and functionality with the release of SQL Server 2008 R2. The most useful features for developers and administrators are IntelliSense and Regions in Query Editor.

IntelliSense in the Query Editor

One of the most exciting features added to SQL Server 2008 R2 is the IntelliSense support in Query Editor of Management Studio. To be able to use this feature you need to be connected to SQL Server 2008 R2. It is not backward compatible with SQL Server 2005 edition. Unless you are one of those developers or DBAs who like to memorize all objects, their parameters, and T-SQL statements, or enjoy spending hour in books online looking for this information, this feature is a must. Besides completing a word and identifying an incorrect syntax, IntelliSence will list database objects and parameters required by functions and stored procedures as you type.

Query Editor Regions

Regions helps with organizing T-SQL code by grouping certain region of code. Regions can be expanded or collapsed by clicking the ‘-‘ or ‘+’ signs next to them which helps navigating and examining the code. A region is automatically created for batches ending with a GO statement, and for each BEGIN / END block.

Going Beyond Relational

There are more and more applications that require storing beyond relational data types. SQL Server 2008 R2 provides the functionality to store, search, and retrieve the beyond relational data types such as XML, Full-Text, Geometry and Geography types, Hierarchies, and Media.

HierarchyID

Object-Oriented class inheritance, organizational charts, Family trees and Bills of Material are all examples of hierarchy which is hard to model with the traditional relational databases. The problem becomes more complicated when you need to implement functionality such as navigating and manipulating the tree or defining many-to-many relationships. Microsoft introduced a new data type called HierarchyID where the entire hierarchy can be stored. Although HierarchyID is a CLR data type, it does not require CLR to be enabled on the instance.

Geospatial Data Types

SQL Server 2008 R2 introduces new GEOGRAPHY and GEOMETRY data types that allow storing geospatial data in the form of points, lines, and polygons in the tables. These spatial types enable users to perform SQL queries and operations on spatial data within SQL Server 2008 R2 directly. You can also index those columns that store spatial data types. The geospatial data types and functions in SQL Server 2008 R2 are based on standards established by the Open Geospatial Consortium. Geospatial data can be imported into SQL server from the following formats: WKT, WKB and GML (Geospatial Markup Language). Geography and Geometry data types are supported by many major GIS (Geography Information Systems) such as ESRI ArcGIS, Mapinfo, Autodesk Map 3D 2007, Manifold, and more.

Working with Date and Time

Until Microsoft SQL Server 2008 R2, the ability to store date and time was limited. The only options SQL Server 2005 offered were the datetime or smalldatetime data types. The problem with those data type is that they do not allow storing only date or only time. Moreover, they do not account for time difference between physical locations (time zones). The results of those limitations are inconsistent data, storage and coding overhead.

SQL Server 2008 R2 introduces 4 new date and time data types; date, time, datetime2, and datetimeoffset.

datetime2 occupies 6-8 bytes and is able to store dates and times between January 1, 0001 and December 31, 9999 with an accuracy of 100 nanoseconds, whereas datetime takes up fixed 8-bytes and stores date/time in range of January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds. The new datetimeoffset date type is similar to datetime2, but in addition it stores time zone offset in the range between -14:00 through +14:00.

The range of the date data type is between 0001-01-01 and 9999-12-31, whereas its predecessor datetime could only store the date range between 01-01-1753 through 9999-12-31. The range of time data type is between 00:00:00 and 23:59:59.9999999.

Upgrading to SQL Server 2008 R2

Mainstream support for SQL Server 2000 ended in April, 2008. The mainstream support for SQL Server 2005 will come to an end in April of 2011. If you are considering deploying your web application to SQL Server, we strongly recommend deploying it to 2008 R2 version. If you are currently using SQL 2005, SQL 2008, or even SQL 2000 versions, our advice would be to upgrade to 2008 R2 as soon as you can. The new SQL Server is fully backward compatible with its predecessors while providing better and faster performance for your applications. The steps to upgrade SQL Server are detailed in this community forum post.

Dmitri Gropen
Technical Support