By Greg DeMichillie [bio]
Posted: Oct. 31, 2005

For many years, database developers have used Microsoft's Transact SQL (T-SQL) language to create stored procedures and other application code that runs within SQL Server. With Visual Studio (VS) 2005, Microsoft is offering them the ability to write this server-side code with the .NET Framework and languages such as C# and VB.NET, leaving developers to ask: when and why should I use .NET as opposed to T-SQL?

Using .NET to create stored procedures and other database code offers several benefits:

Similar APIs. For a developer already familiar with building database client applications, .NET offers a more consistent set of APIs than those offered by T-SQL.

Possibly better performance. For many applications, overall performance is determined by the amount of data being processed. But for some applications, particularly those that are compute-intensive, .NET may offer better performance. For example, a geographic information system not only performs database queries but must also perform intensive calculations on the data. Using .NET for such an application could result in better performance than T-SQL.

Modern programming language. T-SQL is a special-purpose language designed specifically for manipulating databases and lacks many of the features of general-purpose languages such as C#—most notably, support for object-oriented development, structured exception handling, and arrays.

Access to more libraries. Developers using .NET have access to a large set of libraries of reusable code written by Microsoft and third parties.

Safer than extended stored procedures. For many years, SQL Server has supported a specialized version of stored procedures written in the C and C++ programming languages, as opposed to T-SQL. These extended stored procedures give developers a way of writing high-performance code that is used from within SQL Server, but they come with a serious drawback: they lack any safety constraints. A poorly written extended stored procedure could accidentally crash the server or write over important data. Using .NET gives developers many of the same benefits as extended stored procedures (higher performance, more capable programming language), but with the added safety of the .NET Framework and Common Language Runtime to ensure that the code doesn't cause any damage.

However, there are drawbacks to using .NET to create database code:

New language. For database administrators and developers already familiar with T-SQL, the value of added language capabilities is offset by the need to learn a new language. Many of these developers are likely to stick with T-SQL.

More complex deployment. Although VS 2005 can automatically deploy .NET assemblies, there are some scenarios in which the developer must manually copy assemblies to the server and register them.

Scalability limitations. Client-side or middle-tier code can often be "scaled out" for greater performance by deploying copies of the code on multiple computers. Database-side code can also be scaled out in this way, but the process is more complex because the database must be copied along with the code, and the database copies must then be kept synchronized with one another.