email article printer friendly get the code more resources

Write .NET Code in SQL Server
Write procedural code and create user-defined types and aggregates with .NET inside SQL Server's next version, code-named Yukon.
by Bob Beauchemin

Posted November 7, 2003

Technology Toolbox: VB.NET, C#, SQL Server "Yukon"

All versions of SQL Server prior to SQL Server "Yukon" provide two ways to write procedural code that executes inside the database. The usual way is to use Transact SQL (T-SQL), a proprietary implementation of a portion of the SQL standard known as persistent stored modules (SQL/PSM). An alternative is to write extended stored procedures in compiled languages such as C++; extended stored procedures employ native calls using the Open Data Services API. Yukon lets you use any .NET-compatible language to program SQL procedural code without needing to know T-SQL. This enhancement offers certain advantages, but using T-SQL code is still optimal in many cases. I'll explore the implementation of .NET language support in Yukon and discuss trade-offs to consider in choosing among the available alternatives.

T-SQL code is highly integrated with the rest of SQL Server. It executes using "native" data types, so data returned from the storage engine doesn't need to undergo expensive type conversions prior to T-SQL's use. This makes T-SQL code highly efficient. On the other hand, it isn't compiled prior to use. Each query or line of T-SQL code runs under an interpreter, making it less than ideal for complex number crunching or other manipulations that benefit from compilation. Since SQL Server 7, stored procedures and user-defined functions (UDFs) aren't precompiled or stored in an intermediate parsed-tree format prior to use, as in earlier versions.

Extended stored procedures are useful for accessing OS-level services such as the file system and the Internet, number crunching, or storing state such as global timers—but they're difficult to write and debug. Database calls that use ODBC or OLE DB APIs require conversion of data types between the database and ODBC/OLE DB and back again. Although extended stored procedures can "join" the existing session for the purpose of being in the same transaction, doing database calls through OLE DB or ODBC requires connecting back to the database. The concept of the "current connection" isn't available when you program with extended stored procedures.

Although a rogue extended stored procedure can cause SQL Server to fail by calling the process exit() function, well-meaning but buggy code cannot cause it to fail by branching to location zero, for example. The reason is that SQL Server wraps the execution of extended stored procedures with structured exception handling. However, pointers to incorrect memory locations can cause an extended stored procedure to scribble on SQL Server's internal buffers, because the extended stored procedure manages memory allocation and access, and it can access memory that SQL Server uses. Use of extended stored procedures is rare, for these reasons.