Some new features in SQL Server 2008 - July CTP

Date and time

As many of you already know we finally have a separate date and time datatype in SQL Server 2008. This is not the only new option for date and time however, there a couple of other new datatypes like datetime2 and datetimeoffset.

• Date
  The data datatype takes 3 bytes and has a range from 0001-01-01 through 9999-12-31
• Time
  The time datatype takes 3 to 5 bytes depending on the fractional second precision. Time has a range from 00:00:00.0000000 through 23:59:59.9999999. The default precision is 7 meaning hh:mm:ss.nnnnnnn and depending on the precision you specify - with time(x) where x is a number from 0 to 7 - the storage size differs . From 0 to 2 time will take 3 bytes, from 3 to 4 it will take 4 bytes and up to 7 it will take 5 bytes.
• Datetime2
  Although this makes me shiver as it makes me think of varchar2 it is a great datatype. Datetime2 takes from 6 to 8 bytes and has a range of 0001-01-01 through 9999-12-31. It also implements the same fractional second precision option as the time datatype. From 0 to 2 datetime2 will take 6 bytes, from 3 to 4 it will take 7 bytes and up to 7 it will take 8 bytes.
• Datetimeoffset
  The datetimeoffset datatype takes from 8 to 10 bytes and has the same properties as datetime2 but the difference with the other datatypes is that datetimeoffset is timezone aware. The fractional second precision again defines the storage size, from 0 to 2 datetimeoffset will take 8 bytes, from 3 to 4 it will take 9 bytes and up to 7 it will take 10 bytes. There are also a couple of datetimeoffset related functions like SWITCHOFFSET and TODATETIMEOFFSET.

HierarchyID

Another new datatype is the hierarchyid datatype which represents the position in a tree hierarchy. I did not have the time to play with it yet but from what I have read it implements 2 special indexing methods, depth-first and breadth-first. It also has a couple of related function like GetAncestor, GetDescendant, IsDescendant, ...

Object Dependencies

The new management views sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, and sys.dm_sql_referenced_entities gives you a view of dependencies that is always up-to-date. It even allows you to see cross-server and cross-database dependencies.

Page corruption

The new management view sys.dm_db_mirroring_auto_page_repair contains information about automatic repair attempts on mirrored databases (page restores from the mirror database).

SQL Server Extended Events

Built in support for the ETW (Event Tracing for Windows) engine to help you trace, log and debug problems. For more information about using ETW with SQL Server check out this post.

I recommend you to play with SQL Server 2008 yourself since there is already a very long list of new things to learn in this CTP! There are still a couple of other features in the July CTP which I have not seen yet so I hope I will find some time to experiment with these too.