I have noticed that a fair number of developers are still using SQL Server 2005.
Is there any compelling reason not to use SQL Server 2008?
Is it simply that your employer sees no benefit in upgrading?
I am planning to start a new project soon, and I am wondering which version would be better to use.
Depends on where you host it. If you have your pick, use 2008. I'd like to move up to 2008 for the resource governor on roles.
There's just not a compelling reason to upgrade unless you need the additional functionality. If you have the choice I'd go with 2008, since you may end up needing the new stuff.
I recently upgraded to 2008 and everything went smoothly.
Really the only benefits I have seen are the new data types and an improved Reporting Services.
In the past, I have seen much better performance improvements when upgrading SQL Server. I have seen complicated query times cut by large orders of magnitude. I saw none of this from the 2008 upgrade.
The only downside I have seen is with the management tools. I have seen several machines where SQL Server Management Studio failed to install or failed to install completely. The guy that sits beside me still cannot run an Integration Services package despite repeated uninstalls and reinstalls.
Personally I would rather start the project on 2008 if I had the choice. It is stable and there are some benefits.
The counter question being "Is there any compelling reason to use SQL Server 2008?" of course.
Upgrading for upgrading's sake is a short road to ruin. Even SQL Server 2000 installs are not that uncommon to find in the wild still. SQL Server is a pretty mature, robust product from 2000 onwards (the only ones I've had experience with).
And while 2005 is better than 2000 and 2008 better than 2005, there is nothing wrong with 2000.
Moral of all this is: If they have an existing database server, stick with it. If they don't, install 2008.
It's not just the employer, it's also the customers. It's not easy to get them to purchase the upgrades just because the devs want to play with new features.
For most organisations their data is abosolutely critical, apps come and go, languages come and go, people come and go, but the data is constant and growing. In that environment why take any unecessary risk? SQL Server has been a good stable platform for at least a decade now, unless there's a reason to move why do it?
There are actually a couple reason's for not upgrading:
Cost vs Benefit. Forking over the license fee to get what are non-essential improvements is sometimes difficult to swallow. Yes Intellisense for Sql is awesome, but still non-essential.
Your database makes use of full text indexing. This radically changed in SQL 2008. I've had a fair amount of conversion work to do beyond just "upgrading" to fix queries that were based on full text indexing.
The database makes use of replication. The replication model would have to be torn down to do the upgrade, then rebuilt. That could cause issues.
You make use of SSIS packages... SSIS has had so many problems that I know DB's who cringe when they have to make a change, much less make sure everything copasetic in a new database server.
Basically, if you use ANY of the advanced features then you probably want to think twice before upgrading.
That said, for any new project I would gladly go 2008.
If you're doing a new build there's no reason not to use SQL Server 2008 unless you have a lot of infrastructure standardised on 2005 with significant inertia to change. There are a few useful new features but the most compelling reason is that you get an extra three years' lifespan on the application before you will have to move to another version.
If you have a lot of SQL Server 2005 based infrastructure and there is a significant cost to change, SQL Server 2008 probably doesn't offer a compelling reason to change in itself unless you absolutely must have a specific feature (spatial indexing, for example).
Thus, as we often see with databases, infrastructure maintenance and cost of change will keep an application or even a whole organisation on an old version unless there is a compelling reason to change. Often the reason has to be really compelling, like a version going to end-of-life or out of standard support. If you look around you'll find there's plenty of SQL Server 2000 code and even 7.0 or 6.5 based code out there.
If you think lots of people keep old versions of SQL Server beyond its use-by date, take a look at Oracle customers.
To answer your question, SQL Server 2008. I work in a small software engineering firm and have worked on a large cross section of projects on various platforms inclusing Mobile, Desktop and Web and for a .NEt developer extensively used a large cross section of different DBMSes, relational and 'bigtableesque'.
But have you considered PostgreSQL? In my experience its the fastest, and most reliable RDBMS, and without exception, the projects here in the office that we've ported to Postrges, are running noticably faster.
If its an option, I'd give it a serious think.
I certainly agree with what most posters say: For a new install, pick the newest stable version (here: SQL 2008). In general, you can expect it to be a better product. That's the point of new versions, no?
For existing installations, things are different - but I don't entirely agree with the 'no reason to upgrade until you have to' policy.
Most databases have long life expectancies. SQL versions grow old and underfeatured, and eventually, they get harder to support because fewer people use them and professionals actually forget the specifics after years working with newer versions. Development is harder and more expensive. And in the end, official support ends, of course.
In the long run, one will have to upgrade eventually anyway. The question then becomes one of cost and benefit: When will an upgrade cost the least and benefit the most? Sooner? Or later?