MSDN Follow-up: SQL Server 2008 For Developers

  • Article

Breaking news! Content now available

 

This is the first of three postings I'm writing to follow up on the MSDN Events sessions I delivered in Nashua, NH, and Stamford, CT, on the 1st and 2nd of this month.  There were a lot of great questions that I didn't have ready answers for, so I figured this would be a good way to get the information to all of you who attended.   If I didn't touch on your specific question in this post, please feel free to drop me an e-mail to follow up.   Here's what's covered in this article:

  1. Correction! CLR support *is* a server-based configuration.
  2. How is Daylight Savings Time (DST) accounted for in the new date types?
  3. How is FILESTREAM data treated in backups?
  4. How do you insert data into an hierarchyid column?
  5. Is there 3-D support when using spatial data types?
  6. The Spatial Results tab in SQL Server Management Studio doesn't provide a lot of context.  Is there a way to overlay a map, for instance?
  7. What are the performance implications of Table Value parameters?
  8. Is there an easy way to create a Table type based on an existing table?
  9. How does SQL Server's MERGE statement compare to Oracle's?
  10. What's the story on SQL Server 2008 for mobile devices?

Correction: Enabling CLR support is a server operation not a per-database operation.

Thanks to Rick Lemon for the correction here.  Indeed, the command

exec sp_configure 'clr enabled', 1

applies to the entire server.   Check out this MSDN article for more details.

How is Daylight Savings Time (DST) accounted for in the new date types?

There is no server-side daylight savings time support for the new datetimeoffset type; however, .NET 3.5 added some additional time types to support variations including DST (and even the change to DST that was initiated in 2007).  There's a number of articles that touch on handling DST; here's one that I thought was particularly detailed.

How is FILESTREAM data treated in backups?

By default, FILESTREAM data is backed up just like your relational data; however, if you want to exclude these types from your backup, you can create a partial backup that excludes one or more filegroups.  Filegroups are a construct that includes file system directories instead of the files themselves.  As such they are the data containers for the FILESTREAM columns you define in your tables (the association is made via the FILESTREAM_ON clause in the CREATE TABLE statement).  As an example, the AdventureWorks 2008 database we used was created with the following DDL:

CREATE DATABASE statement 

How do you insert data into an hierarchyid column?

The GetDescendant method is your friend.  You call GetDescendant on an existing node in your hierarchy and provide two parameters - the two children between which you want to insert the new node.  One or both of these parameters can be null, so you can control the ordering of the descendant nodes.  To get the the ball rolling, the top node in your hierarchy is set via the static method GetRoot.

You can also use a logical (string) representation which takes a form like "/2/1/3.4".  Strings of this format can be CAST into a hierarchyid type; likewise, ToString() on a hierarchyid column returns its logical representation.  For example, when using the AdventureWorks 2008 database the following query returns the string "/3/1/20".

Hierarchyid query 

Is there 3-D support when using spatial data types?

Not at this point, but there are a number of partners that extend the native spatial type support that you may want to investigate.  That said, a POINT can contain an elevation value (and a measure) in SQL Server 2008.

The Spatial Results tab in SQL Server Management Studio doesn't provide a lot of context. Is there a way to overlay a map, for instance?

Not natively, but I did find a nifty download, Geoquery 2008, which is a WPF application that mimics the UI of SQL Server Management Studio and allows you to overlay the points on a number of different map sources.  Here's a snapshot of the AdventureWorks 2008 Person.Address table. 

Geoquery 2008 

What are the performance implications of Table value parameters?

Table types are stored partially in memory and partially on disk, so in comparison to temporary tables, there can be performance improvement in accessing the data.  For example, a temporary table would require read locks, but locks aren't required on table types since they can't be accessed by any other process or user on the system.   On the other hand, table types can't be indexed (with the exception of declaring a primary key constraint) so for large amounts of data with joins performance can suffer.  Also since table types are stored and processed in memory,  heavy multi-user usage will limit scalability.

[N.B., Table types are also available in SQL Server 2005.  the new feature in SQL Server 2008 is the ability to pass these types into stored procedures.]

Is there an easy way to create a Table type based on an existing table?

While you can't create the type automatically from an existing table, there is a New User-Defined Table Type... menu option on the context menu for User-Defined Table Types in the Object Explorer of SQL Server Management Studio.  That option brings up a template, and an associated UI that can be used to fill out the template is also available.

 

foo

How does SQL Server's MERGE statement compare to Oracle's?

Oracle documents its MERGE here, and SQL Server's is here.  Both are implementations of the SQL/Foundation:2003 specification, in which the MERGE statement was introduced.  Oracle indicates that it supports the MERGE statement with the following exceptions:

  • Oracle does not support the optional AS keyword before a table alias
  • Oracle does not support the ability to rename columns of the table specified in the USING clause with a parenthesized list of column names following the table alias
  • Oracle does not support the <override clause>

SQL Server's implementation has been described as a superset of the SQL:2003 specification, but appears to not support the <override clause> either.  The other two aspects that Oracle doesn't support are, however, supported in SQL Server.  For more information on the formal specification of the MERGE statement, you can purchase the specification from ISO (note, the SQL:2008 specification supersedes SQL:2003) or download a late draft of SQL:2003.

What's the story on SQL Server 2008 for mobile devices?

SQL Server Compact Edition 3.5 SP1 was released along with SQL Server 2008 and includes the following features (not an exhaustive list):

  • 64-bit support
  • support for case-sensitive collations
  • ADO.NET Entity Framework support
  • merge replication support with SQL Server 2008
  • replication of the new data types in SQL Server 2008 (Date, Time, DateTime2, DateTimeOffset, Geography, and Geometry) to SQL Server Compact. The new data types in SQL Server 2008 are supported through mapped data types such as nchar, nvarchar, and image in SQL Server CE.