I got this from my friend Shally, and it really worth reading ………..

When you write stored procedures, triggers, and user-defined functions, one decision you will now have to make is whether to use traditional Transact-SQL or a programming language that is compatible with the .NET Framework, such as Visual Basic .NET or C#. The answer to this question will depend on the particular situation that is involved; in some situations you’ll want to use Transact-SQL, while in other situations you will want to use managed code.

Transact-SQL is best for situations in which the code will primarily perform data access with little or no procedural logic. Programming languages that are compatible with the .NET Framework are best suited for computationally intensive functions and procedures that feature complex logic, or for situations in which you want to take advantage of the .NET Framework class library.

Code placement is also important. Both Transact-SQL and managed code run on the server. This places functionality and data close together, and allows you to take full advantage of a server’s processing power. This is beneficial because it decreases traffic between the data and middle-tier. By simply performing the computation next to the data, a system that is I/O-bound may find this method significantly beneficial. CLR functions also can take advantage of the SQL Server query processor, which can parallelize and optimize execution. On the other hand, you may wish to avoid placing processor intensive tasks on your database server. Most client computers today are very powerful, and you may wish to take advantage of this processing power by placing as much code as possible on the client. There is no “one size fits all” answer.


Happy Coding