Tuesday, 4 December 2007

8 Reasons to use Sql 2008

I managed to attend this seminar today - and wow! I was expecting it to be just very simple updates but there are some really good features!!!

The seminar was run by Andrew Fryer (you can catch his blog here: http://blogs.technet.com/andrew/)

Some of my favourite highlights:-

Geo-spatial Support: Basically it has added a geometry as a data type and its great! We (Waterstons) did a vehicle tracking system and I honestly think this would have saved us a week worth of work plus a performance benefit of 20%! This allows you to do things like intersection checks (is an object within a polygon). It also integrated very nicely with Virtual Earth.

T-Sql IntelliSense: This has been long awaited... but I think only offers minor benefit since you cant tell what you are selecting from until you write the from statement!

Resource Governor: This lets you write a function which basically says how much resource a user, table, database etc gets - very much like resource pools in VMWare you can set aside resources for production databases and test databases.

Transparent Data Encryption: This basically lets you encrypt a database transparently. The application using the database is not affected - the only issues is if you try and transfer the database to another instance/server. (you can see more about this on Andrews blog: http://blogs.technet.com/andrew/archive/2007/11/29/sql-server-2008-transparent-data-encryption.aspx)

Backup Compression: I love this - basically by adding the statement "with compression" you can now reduce backup sizes quite a bit - but it also makes the backup quicker!! (it took about 22 seconds for a 160mb backup and it took 8 (I think!) and it was compressed to 50mb!). Restores where also a bit quicker. Again you can see more on this at Andrews Blog (http://blogs.technet.com/andrew/archive/2007/11/30/sql-server-2008-backup-compression.aspx)

Policy's: This wasn't one of the 8 covered but I thought it was very cool - basically you can think of this as group policy but for databases - it covered lots including things like naming standards.

New reporting tool: This was an office like application which will hopefully lower the entry lever required for creating reports. This is a massive bonus and I can think of loads of clients who would benefit from this! However they still ha vent fixed the annoying not being able to put data in a header yet!!!! grr Crystal can do it!

Analysis Services MDX Query Optimizer - Block Computation and other improvements: Very cool stuff - should just transparently improve the performance of analysis services!

Multiple inserts: Andrews demo sql allowed the insert of multiple values like:-

insert into tblName
values ('Ross'), ('fred'), ('james')

This is really useful!

Change data capture: Basically archiving but at a low level so there will be some overhead (he estimated 20%) but loads less than using triggers etc!

Merge Statement: I have left my favorite until last! This would have saved me so much time - basically it lets you either insert/update/delete with one statement!!! you can see more about this here: http://richardsbraindump.blogspot.com/2007/07/what-new-in-sql-2008-katmai.html (point 3 - also note there is a Delete statement which does the delete part!)

Overall Im really impressed by the changes - I cant wait to get it installed. If you want to see more info about these then it may be worth looking here to: http://portal.sqltrainer.com/2007/11/sql-server-2008-ctp-5-new-features.html.

1 comment:

andy said...

I've recently been on an Oracle to SQL Server 2008 conversion course and, Iike you, was impressed with what I saw.

Of course, Microsoft are still playing catch up with Oracle on a lot of features - the 'Merge' statement you mention (an implementation of an upsert) has been in Oracle DB's since 2004's 10g :)

What impressed me most about the 2008 SQL Server toolset was the Activity monitor and controls - the ability to throttle back selected processes (and users) to a set level of processor occupancy looks cool...