THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Some of my reasons for upgrading to SQL Server 2008

So Aaron posted My reasons for upgrading to SQL Server 2008

 

I thought it was an interesting list, not as interesting as the one by Jason Massie (10 Reasons to Upgrade to SQL Server 2008) but still interesting :-) I would like to give you my list.

 

Date data type

We have data that goes back to 1896 so we cannot use smalldatetime instead of datetime. When your table has billions of rows and grows by millions of rows a day this will save a lot of storage. The time data type can also be beneficial if you need to find all customers that placed orders between 4 and 5 PM in the last 5 years.

 

Partition-Aware Seek Operation also known as skip scan

I still have to do some more testing with this but here is what it basically does:  the query processor performs a first-level seek operation to find every partition that contains rows that meet the condition This identifies the partitions to be accessed. Within each partition identified, the processor then performs a second-level seek into the clustered index. More about this is explained here: http://msdn.microsoft.com/en-us/library/ms345599.aspx

 

Row and Page Level compression

I have tested this and for some tables performance is better and for some it is worse, you will need to test to see if it makes sense to turn this on or not. If you have lots of repeated values on the page then it should benefit you. I will have a blog post on this before the economy recovers (I just bought myself some time here)

 

Transact-SQL Row Constructors

If you have ever had to allow people to upload files, parse these files on a web server and import this data you will appreciate Row Constructors. No longer do you need to call a proc for every row in the file, after that checking that the rowcount is the same in the table as in the file. You will be able to insert a ton of rows in one shot, this will make the process much faster and it is also part of one transaction, they all go in or they don’t. BULK INSERT is of course better but most likely you only have 1 port open between the web server and the SQL server (and if you don’t then you should)

 

C# in SSIS

This might not seem as a big deal but switching between C# and VB can be a pain in the neck. If you are a developer who does 50% SQL development and 50% C# windows forms/web forms/MVC development then you don’t have to switch modes in your head every time you go from SQL to web. If you are doing VB then of course this doesn’t apply to you.

 

Table-Valued Parameters

Table-Valued Parameters are nice because you don’t have to create temp tables anymore before calling the proc so that you can read from the temp table after the proc has executed. I always felt ‘dirty’ doing that and it felt like a hack.

 

Other new T-SQL things.

Not as important but still nice are the MERGE (UPSERT) statement, hierarchyid Data Type and Compound Operators. I am sure some people will love the geometry and geography spatial data types, I have no use for them right now

 

Other new SQL Server things

A couple of other things which I find interesting are:

On partitioned tables, you can configure locks to escalate to the partitions instead of to the whole table by using the LOCK_ESCALATION option of ALTER TABLE.

The FORCESEEK table hint.

Resource Governor

SQL Server Extended Events

IntelliSense

 

There you have it, I will be upgrading on some machines from 2000 to 2008 so this list will be even bigger for those instances. Actually these will be new machines as well.

Published Wednesday, November 19, 2008 11:59 AM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Bart Czernicki said:

For the environment I work on...which is BI

Like you mentioned the Date type is nice.  

The compression that was available in SQL Server 2005 SP2 Enterprise was really nice (varbinary)...overall-wise this works great for BI.

The other big thing is the new storage algorithm for SSAS using the Bloom data strucutre...makes our queries run 30% faster just by upgrading

SSRS new Report Builder 2.0 with visualizations!

Database backup compression.  This was a huge pain in the butt, because BI databases are so big.  It eliminates an extra compression step.

November 19, 2008 10:56 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement