Avoid procedural row processing if set-oriented operations can do it
Set-oriented operations can be very powerful. Sometimes it can be tricky to get them right, but once they are there, the database engine has a lot of opportunities to understand what you want to do based on the SQL statement that you provide, and it can perform deep, sophisticated optimisations on your behalf.

So in general it's a good thing to process rows using set-oriented statements such as UPDATE, INSERT and DELETE.

Good examples of this are:

• Avoid row-by-row scans and updates. If at all possible, it's much better to try to write a more sophisticated UPDATE statement.
• Avoid custom aggregation of values by explicitly opening a SqlDataReader and iterating over the values. Either use the built-in aggregation functions (SUM, AVG, MIN, MAX, etc.) or create user-defined aggregates.
• There are of course some scenarios where row-by-row processing using procedural logic makes sense. It's mostly a matter of making sure that you don't end up doing row-by-row processing for something that could be expressed in a single SQL statement.

More on Connections
Choosing between regular and context connections
If you're connecting to a remote server, you'll always be using regular connections. On the other hand, if you need to connect to the same server you're running a function or stored procedure on, in most cases you'll want to use the context connection. As I mentioned above, there are several reasons for this, such as running in the same transaction space, and not having to reauthenticate.

Additionally, using the context connection will typically result in better performance and less resource utilization. The context connection is an in-process-only connection, so it can talk to the server "directly", meaning that it doesn't need to go through the network protocol and transport layer to send SQL statements and receive results. It doesn't need to go through the authentication process either.

There are some cases where you may need to open a separate regular connection to the same server. For example, there are certain restrictions in using the context connection described in the Restrictions for the context connection section.