12 July 2010

Why SQL Server is better than any other RDBMS applications?

Pinal Dave has a question on his blog: "Why SQL Server is better than any other RDBMS applications?" Here's my two cents.

I learned RDBMS on SQL Server 2000 back in 2002; picked up a book and read it until the pages came out. It was an easy-to-use, developer-friendly relational database, which tied nicely to classic ASP. I wrote tons of stored procedures from the start, and avoided using ad hoc queries — and never had any security issues.

For a university’s online directory that needed quick response and tons of features, I wrote the entire search engine in a SQL Server stored procedure, with additional user-defined functions. At the time, the school’s data was in flat files stored on OpenVMS. I used a nightly DTS to pull the data and ran cleanup procedures to store it on SQL Server. All of this with great ease. And lets not forget: There’s not an equivalent of DTS/SSIS on Oracle. Going back to SQL Server 2000, this has been a powerful feature of the database, making developers’ lives easier.

For the same directory project, I needed to provide users with the option to leave multiple fields blank and allow searches on any combination of values they provided. The handy “coalesce” function made this a snap. I posted to SQL Server forums and got great help on how to accomplish this. The final SELECT statement looks beastly, but it’s quick and efficient. This was long before Oracle supported the “coalesce” function.

Also, I constantly used the IF EXISTS (SELECT *…) in my stored procedures and user-defined functions. Believe it or not, Oracle still doesn’t have a direct equivalent of this easy feature. If you wanted to insert a row but wanted to check that it didn’t already exist, you’d simply do an IF EXISTS. Easy and intuitive. This is much harder in Oracle.

In addition, the ease of using stored procedures with web apps makes SQL Server a web developer’s best friend. It took us over 2 weeks to get Oracle working with ASP.NET; SQL Server worked with it like a breeze. And the syntax was easy too. If you’ve connected Oracle stored procedures to ASP.NET apps, it’s not a trivial task. We had to install a 200Mb data provider to talk to Oracle. Not my idea of easy.

Moreover, SQL Server installs and runs smoothly with the Visual Studio development suite. SQL Server comes with the Management Studio tool built-in; you often buy extra utilities to get the same functionality out of other RDBMS.

With Oracle, you have to set up TNS files and ensure they have the correct settings on development and production. With SQL Server, you don’t have to worry about any of this.

Reporting tools? Built into SQL Server. As far back as 2002, you could purchase an inexpensive ($5, if I remember correctly) add-on from Microsoft to enable reporting capabilities. Today, the SSRS tool is robust, easy to use, and also developer-friendly. For years I was trying to convince my boss to go with SSRS; she finally went to a conference and saw how powerful it was and she was sold. Instead of a third-party tool that kept crashing daily, the university went with a product that users, admins, and developers love.

No comments: