One of SQL Server 2005's best selling points is how significantly it increases developers' productivity. With efficient features such as the SQLCLR, Web Services, and Encryption, you'd expect SQL Server 2005 to be more productive than SQL Server 2000 and Oracle 10g R2, but how can you really tell? Well, the SQL Server team provides proof in recent developer-productivity studies that compare SQL Server 2005's efficiency with that of SQL Server 2000 and Oracle 10g R2.The studies show that certain SQL Server 2005 features—specifically support for native encryption, the ability to create triggers in C#, and Reporting Services—helped the developer complete tasks faster than developers who used SQL Server 2000 and Oracle 10g R2.

Testing Productivity


The SQL Server team commissioned the development company 3 Leaf to conduct the productivity studies. Microsoft provided 3 Leaf with a baseline application specification that contained a simple database of all the content for an example newspaper Web site called Contoso Times and a set of enhancements to add into the Web site (Custom and Ad-Hoc Reporting, Middle-Tier Caching, Offline Smart Client Application, Paid Articles and Subscriptions, and Targeted Advertising). Because Oracle 10g R2's release date was late in the study calendar, the Oracle comparison measured the differences for only the Custom and Ad-Hoc Reporting and Offline Smart Client Application enhancements.

3 Leaf rebuilt the Web site in three environments—ASP.NET 2.0 and SQL Server 2005, ASP.NET 2.0 and SQL Server 2000, and Oracle JDeveloper and Oracle 10g R2—and implemented the enhancements without help from Microsoft or the SQL Server team. A 3 Leaf developer was assigned to each environment rebuild. All had approximately the same amount of experience in their respective environments.

During the study, the developers kept daily diaries of their procedures, problems, and successes.The full 63-page diary is the basis of the study's final whitepapers, which are posted on MSDN's Web site. (To read the entire study, see the Microsoft articles "Comparing Enterprise Development Productivity: SQL Server 2005 versus SQL Server 2000" at http://www.microsoft.com/sql/prodinfo/compare/devprod2000.mspx and "Comparing Enterprise Development Productivity: SQL Server 2005 versus Oracle Database 10g" at http://www.microsoft.com/sql/prodinfo/compare/oracle/devprodoracle.mspx.) As Table 1 shows, the development time for the SQL Server 2005 developer is considerably less than the SQL Server 2000 and Oracle 10g R2 developers' times for each enhancement and for the overall total. On average, the SQL Server 2005 developer completed the tasks 34.4 hours faster than the SQL Server 2000 developer and 47.3 hours faster than the Oracle 10g R2 developer. For all tested enhancements, the SQL Server 2005 developer's total time to complete the tasks was 173 hours faster than the SQL Server 2000 developer and 94.5 hours faster than the Oracle 10g R2 developer.

3 Keys to Efficiency


In the developers' diary, I discovered that the difference in the time to complete each enhancement was affected most by three key SQL Server 2005 features: support for native encryption, the ability to create triggers in C#, and Reporting Services. The support for native encryption in the database played a huge role in all five enhancements. For example, in the Paid Articles and Subscriptions enhancement, the SQL Server 2005 developer was able to store encrypted personal data directly in the database by using asymmetric encryption and the RSA encryption algorithm with 512-bit encryption.

To set up this encryption correctly, the SQL Server 2005 developer ran the following system stored procedure:

CREATE MASTER KEY ENCRYPTION
  BY PASSWORD = 'password'

Once he initialized the procedure, he started the encryption by running the following statement:

CREATE ASYMMETRIC KEY
  Asym_Contoso
WITH ALGORITHM = RSA_512

The developer needed to run this statement only once to generate the keys in the database.Then, he could leverage the asymmetric encryption and insert data into the database by calling the following Asym_Contoso key:

EncryptByAsymKey(AsymKey_ID
  ('Asym_Contoso'),@FirstName)

To retrieve and decrypt the data, he called:

CAST(DecryptByAsymKey(AsymKey_ID
  ('Asym_Contoso'), @FirstName)
  AS VARCHAR)

To achieve the same functionality, the SQL Server 2000 developer had to build a custom encryption class in Visual Studio that had to process all the data that needed to be encrypted.

When building the Targeted Advertising enhancement, the SQL Server 2005 developer benefited from the ability to create triggers in C#.This feature let him encapsulate both the encryption calls and the fulltext search and matching algorithms within the database. For the same task, the SQL Server 2000 developer had to extract the data to another tier, in which he had to decrypt the data before performing a match between an advertisement and a specific piece of content.This SQL Server 2000 procedure was more difficult to implement than the SQL Server 2005 procedure and slowed the system's performance.

The final key feature that aided SQL Server 2005's productivity was Reporting Services—specifically Report Builder. While building the Custom and Ad-Hoc Reporting enhancement, the SQL Server 2000 and Oracle developers had to build a custom solution while the SQL Server 2005 developer simply deployed Report Builder, which provides the Ad-Hoc reporting functionality in a shrink-wrapped product.

As you can see from the results, SQL Server 2005 has delivered on its promise of efficiency in building applications. So if you haven't done so already, install SQL Server 2005 and start building applications with it. Maybe you'll get to go home early!