Time to take a break from BI based ramblings, and more importantly for my sanity, get the hell away from Cognos Data Mangler for a bit.
Recently I attended a SQL2008 skills update session to find out what’s new in SQL Server 2008. The answer is – from a manageability point of view – quite a lot. From a BI point of view, there’s two real stand out features.
First is the addition of the MERGE statement. This will be old hat to Oracle* users, but it allows you to do all your insert / updates in a single SQL statement as opposed to on a row by row basis. This delivers major performance advantages and will speed any ETL that delivers both new and changed rows to a table (in Oracle I managed to get a 20-fold improvement over row by row update / inserts). The only downside is this hasn’t been catered for in SSIS, so there’s scope out there for a custom component to take advantage of this.
Second is Change Data Capture (CDC). This captures in a mirror table – with metadata – any rows that are added or changed in a table on which CDC is enabled. Probably less relevant these days as most well designed databases will have update timestamps or versions, but a useful way of tracking changes in data – which ETL processes can then pick up on.
Otherwise there’s not much in SSIS, a new Office 2007 style interface for Reports, and things in SSAS which I don’t have a full handle on yet, but nothing groundbreaking. They have plugged some performance holes, but 2008 looks to be the administartion release, as 2005 was the BI release of the platform. Microsoft want to go head to head with Oracle, and assuming they can shake the perception that SQL Server isn’t scalable (which is is) then Oracle should start worrying. Because it’s usable and doesn’t require an expensive DBA and huge costs per feature.
As an aside, for geekery, the new spatial data type is incredibly cool…