I was tagged by Paul Randal last night in his latest blog post, entitled, "What 5 things should SQL Server get rid of?" His top 5 pretty much coincide with my top 5, so I'll have to dig a little deeper. In no particular order:

1. Syntax inconsistencies

This isn't really a specific thing that Microsoft should get rid of, but rather an attitude and overall approach to SQL Server's long-term development.  Every time they add a feature or option to SQL Server, it seems to be implemented in a different way.  In some places we use NO_WAIT and in others we use NOWAIT (see Connect #520245).  With Change Data Capture and Change Tracking, some of it is implemented with stored procedures, and other parts are implemented with DDL.  And even the DDL is implemented inconsistently compared to other ALTER DATABASE statements; see Connect #312102.

3. Restrictions on PIVOT

When you are constructing a PIVOT statement, you need to know all of the possible column values as you write the statement.  This kind of defeats the purpose of PIVOT, in my mind, at least for reporting purposes.  Without using dynamic SQL, shouldn't it be possible to dynamically add the next month or year when the calendar flips over?

3. The current setup program

During setup you are offered all kinds of bad defaults (Paul already touched on a couple of those), and the process is not very intuitive.  This past weekend I helped a colleague get set up on SQL Server and he was totally lost several times during installation.  I realize it's a complex server application but there has got to be a better routine to get SQL Server installed and running the way you want it to run.

4. TIMESTAMP as a data type

ROWVERSION is the way to go, as TIMESTAMP trips too many people up, thinking that it has anything to do with date and time.  It also does not match up with the ANSI standard.  And unfortunately, even in SQL Server 2008 R2, sys.types still shows TIMESTAMP as the base type when you use ROWVERSION, and scripting the table yields the bad name as well.

5. Enterprise-only features

Features like the Resource Governor and Data Compression are great for Enterprise Edition customers.  However, I've argued for a long time that Standard Edition customers could benefit more from them than Enterprise Edition customers.  Why?  Simply put, the folks who can afford Enterprise Edition are not doing it for these features, and they typically have the hardware capacity to get by without them anyway.  The Standard Edition folks are already on a budget, but they're getting hurt the most because their sub-optimal hardware is being crushed due to not being able to take advantage of these features.  I think there should be a more a-la-carte feature set… not necessarily every single feature is priced separately, but more along the lines of picking between three premium packages on a new car – pick any two Enterprise features that are turned on.