T-SQL Code or Managed Code?

When writing stored procedures, triggers, and user-defined functions (UDFs), one decision that database programmers will now have to make is whether to use traditional T-SQL or a .NET language such as Visual Basic .NET (VB.NET) or C#. The answer depends upon the particular situation.

T-SQL is best used in situations where the code will mostly perform data access with little or no procedural logic. Managed code is best suited for CPU-intensive functions and procedures that feature complex logic or when you want to leverage the .NET Framework's Base Class Library.

Code placement is another important factor to consider. Both T-SQL and in-process managed code can be run on the server.This functionality places code and data close together, and allows you to take advantage of the processing power of the server machine. On the other hand, you might wish to avoid placing processor-intensive tasks on your database server. Most client machines today are powerful, and you might wish to take advantage of this processing power by placing as much code as possible on the client.Although T-SQL code can't run on a client machine, the SQL Server in-process provider was designed to be as similar as possible to client-side managed ADO.NET, enhancing the portability of code between server and client.