Tagged: 5 things SQL Server should drop
I was tagged by Paul Randal (blog | twitter) 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:
- 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.
- 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?
- 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.
- 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.
- Enterprise-only features that are more useful for Standard customers
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.
I'm going to tag Mike Walsh (blog | twitter), Denis Gobo (blog | twitter) and Adam Machanic (blog | twitter) on this one.
I think it's odd that SQL Server has a shared tempdb across all databases within an instance: 1) If this represents a security threat is more of an academic discussion, but as a principle I find it strange. 2) At the same time, if you enable TDE in one of your databases, then your tempdb will also be encrypted, which in turn will affect all other datbases on the instance in question.
Each databases should have it's own TempDB.
My 0.02 cents.
Would you be able to provide 2 or 3 specific items for bullet 3 that frustrated you? We are definitely working on this, but would like some insight if there are specific items that we may not be looking at, but is important for you guys.
#2 would be amazing in what it would allow. Especially if the requirement for using an aggregate was removed.
Wow…I mean wow, I would have picked the same items, almost exactly. Expecially #5…too many small shops get priced out of items that could make significant improvements…
Great points, Aaron. I don't know how many times I am doing a prod to dev restore into a new environment that hasn't been scripted yet and I type ROLLBACK_IMMEDIATE.. nope.. it's a space…
I absolutely agree with #5. There should be a separate SKU that includes things like Online index operations, Resource governor, Data Compression, etc. for the relational engine, and drops Analysis Services. They could call it SQL Server Professional Edition.
My vote goes for a-la-carte feature set…
Nice – I really like your #5.