I have not yet downloaded the new CTP, but the KC User group meeting the other night as well as recent decision that I had to make about whether or not to purchase LiteSpeed has inspired me to do some research into SQL Server 2008.
Apparently Microsoft is still a little sensitive from being 2 years late on the last version because Dave Campbell spent the first 5 minutes of his Tech Ed presentation explaining how they have re-engineered their process to ensure that the next version will be delivered on schedule. For example, they will no longer be checking in features into the CTP codebase unless they are fully completed so early CTP’s will only include a few fully functional features rather than most of the envisioned features in a half-baked state.
Well, whenever we do get the next version, here are a few features that caught my eye.
- Merge statements (aka upserts) – a nice efficient syntax for loading data into a data warehouse that will allow you to specify either an insert or update based on whether or not a record exists.
MERGE INTO FactTable F
USING TransactionTable T ON T.OrderID = F.OrderID
WHEN MATCHED THEN UPDATE
SET F.Quantity = T.Quantity
- Table Valued Parameters – allows you to pass in a result-set as a parameter to a sproc of udf.
- Resource Governor – helps prevent runaway queries that can bring down your server. This is especially helpful if you work someplace that allows ad hoc querying against transactional systems.
- Declarative Management Framework – the Tech Ed webcast showed a demo where a create table statement was prevented from running in Query Analyzer because it violated a policy that had been set up regarding ownership and schemas. Another demo showed a database in SMS with a warning icon because the shrink DB setting had been enabled that violated a configuration policy.
- Dynamic Development – a rather vague term, but one example was given that described tagging columns with metadata tags such as ‘sensitive’ that would then allow you to do queries to track these categories and also apply policies such as only allowing users to query these columns through a secure connection.
- New Data Types – The filestream data type allows for a best-of-breed approach to managing blob data that would blend the efficiency of the file system with the manageability of the database (backup, restore, querying). Location and Spatial data types allow for built in support for geo-mapping features.
- Change Data Capture – a good way to appease the auditors by tracking and exposing changes though a relational interface.
- Encryption – Also an auditor pleaser. It is supposed to be configurable without any application changes required.
- Performance Enhancements and Tracking – The biggest gains appear to be in start join optimization and data compression, which takes advantage of the fact that SQL Servers are rarely CPU bound. There are also supposed to be a plethora of performance monitoring tools, which we currently rely on Quest tools to provide us.