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:
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
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:
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".
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.
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.
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:
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):
One question under the table valued types and performance (gosh the launch seems soooo long ago now) .
There used to be a performance hit for dynamic parameters entered into stored procedures with one version or the other can't remember if 2000 or 2005) due to the way that the query cache operated with regards to query plans.
Is that still the case or is 2008 optimised to cater for that issue?
This one's beyond me, but I pinged one of the SQL Server MVPs in this area and here's his response:
I'm not quite sure what the person means by "dynamic parameters". I think they might be referring to a problem known as "parameter sniffing" -- and that's as much an issue today as it ever has been (for background see: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx).
With regard to the table types, parameter sniffing is not an issue per se, as there are no statistics on table types -- so you should get the same plan whether your table has one row or one million, and whether you have 1% cardinality or 100% on a given column. The issue then becomes whether the default plan is adequate for all possible inputs, and that can be tough to analyze.
Tony, if that's not what you' were looking for, I'd recommend hitting the MSDN SQL Server Forums - they speak better DB than I!