News

Equal Footing is Key Feature of Microsoft SQL Server 2005

• By Stephen Swoyer
• May 11, 2005

The Common Language Runtime is at the heart of Microsoft’s .NET vision. It promises to co-opt the benefits of Java–it’s a run-time environment that features a just-in-time compiler and built-in management services–with the bonus that (unlike Java) it’s language-independent.

To some extent, CLR is also SQL Server-independent. Although Microsoft’s .NET vision pre-dates the arrival of SQL Server 2000, the software giant didn’t deliver the first release of its .NET framework until several years later. One upshot is that coding for SQL Server 2000 typically involves one of three approaches: learning that database’s Transact-SQL programming language; using ADO.NET clients or other bridging technologies to build middle-tier and front-end applications in Visual Basic .NET, C# and other languages; or using a third-party CLR assembly that executes out-of-process on SQL Server 2000 itself.

SQL Server 2005 alters this calculus. It drops the .NET CLR into the SQL Server RDBMS itself, placing it on equal footing with T-SQL. The ramifications of this move are far-reaching, but the big takeaway, as far as rank-and-file developers are concerned, is that it’s now possible to use Visual Basic .NET or C# to code stored procedures, database triggers and other programs directly in the database tier.

Outside the obvious ease-of-use benefits, there are other advantages to such integration, experts say. “The key difference is that SQL Server hosts the .NET runtime internally, so that any managed code within a database effectively treats the SQL Server process as if it were the operating system in terms of thread scheduling, garbage collection and so on,” says Graeme Malcolm, a SQL server programmer and principal technologist with technical training and content development specialist Content Master. “The point of this exercise is to allow you to develop database objects such as stored procedures, functions, triggers and types in managed code instead of T-SQL.”

There are risks, too, starting with conceptual incongruity. What works best in the world of procedural programming might not work well in the relational space. “T-SQL is a great language for data access. It is a set-based language and as such, operates on collections of data rather than [on] a single row at a time as do procedural languages such as C# and VB.NET,” says Adam Machanic, a database software engineer with a telecommunications and broadband services provider.

For this reason, Machanic, a SQL Server MVP, says T-SQL will continue to be the language of choice for most SQL Server pros. “[It] can do certain operations a lot faster than [C# or Visual Basic .NET]—such as filtering rows to return to a client.” He agrees that in-process CLR integration will almost certainly be favored by code jockeys new to SQL Server but suggests it might behoove these programmers to pick up a little T-SQL, too.

“There will always be a learning curve for procedural developers coming into the world of SQL. It takes time to learn to think in terms of sets and abstractions instead of doing row-by-row processing. But the rewards are quite great once you get there. And I don't believe that SQL CLR will ever be able to totally eliminate that advantage.”