SQL Server 2005 was released in November, 2005. I actually played with it much earlier than that. Overall, I am pretty happy with it. I think it is a great product, and will give Oracle’s namesake RDBMS a run for its money.
Previously, there were a lot of arguments against Sql Server for its perceived lack of features and sophistication. With this new release, you can almost see SQL Server 2005 is aimed squarely at those charges, and by extension, Oracle. Let’s just look at a few new or enhanced things:
1. Dynamic Management Views
Dynamic Management Views, or DMVs, is a new feature introduced to SQL Server 2005. You can use it to get server state information such as CPU, memory, IO, index usage, etc. These information will be helpful for troubleshooting, diagnosis, and performance tuning.
Oracle has had similar things for a while, except it is called Dynamic Performance Views. Those are usually views whose names start with v$, such as v$session, v$sysstat, etc.
Yes, v$ views are handy in Oracle and I expect DMVs in Sql Server to be equally useful.
2. The enhanced concept of schema
The concept of schema is not new to Sql Server. However, the implementation of it is quite different in SQL Server 2005. In SQL Server 2005, schema will be object owners, not users. That’s a departure from SQL Server 2000. However, schema in Sql Server and Oracle are still different. In my understanding, a schema in Oracle is like a database in Sql Server.
3. The introduction of synonym object
Oracle has had synonyms for a while. To SQL Server 2005, this is a new concept. The implementation is very similar to Oracle.
Conceptually, synonyms are similar to views, in the sense that they are all sitting on top of base objects. Synonyms provide alternative names for all SQL Server 2005 base objects, such as tables, views, UDFs, stored procedures, assemblies, etc. As such, if you reference them in your application, they can provide a layer of abstraction, in case the name or location of the base objects change.
4. Enhanced partitions implementation
Partition creation and management is greatly enhanced in SQL Server 2005. In Sql Server 2000, Microsoft had the concept of horizontal partition and vertical partition. The concept is somewhat awkward and so is its implementation.
With SQL Server 2005, partition management has been enhanced greatly. Not only can you do table partition, index partition is also possible. Plus, the way to implement them is also more consistent with industry standard. I think this eliminated one gap between Oracle and Sql Server.
Microsoft also totally revamped the command line tool. I’ve talked quite a bit of sqlcmd in my blog. I think it is a powerful utility for a lot of automation tasks. To compete with SQLPlus, sqlcmd introduced quite a few enhancements. The ed command will invoke default editor in both sqlcmd and SQLPlus.
6. More advanced query tuning options
Microsoft also introduced more query tuning options in this release. For starters, there is the newly introduced plan guides. Plan guides influence optimization of queries by attaching query hints to them, without changing the query itself.
Oracle has often touted its many ways to tune queries, guide execution path, and adding hints. I don’t necessarily think that is something to be proud of. I think once again, Microsoft is trying to close the gap (whether perceived or real) here.
All the above mentioned things are great stuff. (I haven’t even talked about SSIS yet.) But Microsoft has to maintain a good balance and do not over do it. Simplicity and ease of management is the great strength of Sql Server. It will be a mistake if Sql Server try to match against Oracle feature by feature, and vice versa for Oracle. After all, who wants to manage and tweak hundreds of parameters in init.ora file in Oracle? As I said, Sql Server has the reputation of easy management and “working out of the box”, and it is something that should be treated as a competitive edge. With the somewhat bloated Sql Server Management Studio, Business Intelligence Development Studio, the misleading Visual Studio program group in your program files if you do a full install, and the slowness of those tools, I actually wonder if Microsoft over-engineered things and made them more complicated than they need to be.
As mentioned in the beginning of this article, overall, I like SQL Server 2005. Combined with Windows 2003 64-bit, SQL Server 2005 will take market share away from Oracle for both low end and high end database management systems. Oracle’s DBMS has been plagued with bugs, over-complication, and pricing issues. I don’t think Oracle’s outlook is very bright in the near future.
Besides, what’s up with Larry Ellison? It seems that his only obsession is to surpass Bill Gates as the richest man in the world. Bill Gates, warts and all, at least contributed billions of dollars to world health and charity. Yeah, I think PR is not a small factor in Bill’s charitable contributions, but you still have to give the man some credit. The most noise I heard from Larry was his procurement of a MiG fighter, his yacht, his Japanese style villa, and his many egomaniac antics. What’s up with that?
Don’t get me wrong, though. I am not a big fan of Windows monopoly. And I don’t like Microsoft’s predatory practice based on its dominate position on desktop, especially in the late 90’s. But it does seem that Microsoft has backed away from that in recent years.