Take a test with both of them and come to the conclusion for yourself on the terms of what you will use. The code has less influence than the database where only you have access. And keep in mind that in the future with a slightly different basis from the current it may change.

Maybe you take a test and you can not tell the difference. Then you will know that performance does not matter and you should choose to do what is easiest to maintain. Concerns about performance rarely make sense if you are not seeing a big problem. And if the problem does exist, the way is to check on its own if another way helps significantly.

It is possible that the first code runs slightly faster for some SQL Server optimization but I would not count on a big difference, at least not in most cases.

There are controversies of which is more readable and easy to maintain. I think in the application code it is always more readable but there are people who say otherwise. What do you think you're going to do best to move in the future? Is there any reason to put it in the database?

Even those who prefer to by the database admit that it is not so simple to maintain code in the database. So it's one more reason to just choose this option if you really get a handsome gain, which is rarely the case.

See more on Programmers and no DBA.SE . Note that if you are programmer you will probably want to put the logic in the application and if you are DBA will try at all costs to put the logic in the database:)

    

Conclusion:

  

Give preference to writing queries on the application side instead of writing stored procedures.

Why:

The following are some factors that hang on the side of writing queries on the application side instead of writing on the database side.

Performance

For the database, there is no difference between executing queries on stored procedures or received from the client.

You will only be able to observe a minimum performance difference between one and another due to environment variations that are very difficult to control, and sometimes stored procedures will be slightly faster and sometimes the queries sent by the client will be slightly faster .

Where stored procedures can take advantage of heavy data-heavy processing, for two reasons: because you are closer to data and do not need to traffic them over the network, and because you can easily rely on disk usage physical and temporary tables so that you do not need a huge amount of RAM.

So if you do not plan to do heavy data-heavy processing on stored procedures, you can discard the performance factor of your decision making.

See, for example, results of this measurement .

Complexity

Each language introduced in your project adds complexity. It is common for a project to use multiple languages (SQL, C #, ASP and JavaScript, for example), but complexity is something to fight against and not to search for free. Each complexity must have a good reason to be added. What is the good reason to add the stored procedure language to the project?

In addition to the complexity of an additional language, there is the complexity of additional tools, such as code debugging. In addition to the tools for C # are much more evolved than the tools for Transact-SQL, for example.

Project structure, dependencies, tracking, versioning, deploy ...

All of these needs are more complex when it comes to database code than when it comes to standard application code. Luckily, table definitions vary much less than all the rest (business rules, queries, UI ...). So using database code for queries or business rules increases the code management, distribution, and application update challenges.

Scalability

When there is a need to process large volumes of data and choose stored procedures, another major problem usually arises: scaling the solution.

The demand for a lot of performance usually has no end - data volume only grows and yesterday's solution no longer meets today, and database servers are recognized for not allowing horizontal scheduling. You put more and more processors on the server but find that it costs a lot more than adding a new server - not all database servers support parallelism between physical machines (or "active cluster"), and those that support are expensive, far more expensive solutions than just buying one more simple server or allocating one more machine in the cloud.

I have seen projects migrating the processing to the database (stored procedures) and a few years later migrating back to the application side, and the two movements had the same demand: more performance.

Portability

The SQL language is quite standardized between banks, while the language for writing stored procedure is very specific in each of them. So it's easier to support more than one database by writing only SQL instead of writing stored procedures as well. By "easier" it means less costly and less risky, since each query only needs to be written once while the stored procedure needs to be rewritten for each database.

Of course this factor is only important if you want to support more than one database server, which is a fairly common requirement.

    

Depends on the System

The answers that have already been presented are very good, but I will contribute other aspects that may not exist in your project, but may be relevant to others.

  

It is good practice to traffic the text created in C # to the bank as in the 2nd   example? I've heard that it would not be a good practice.

This kind of recommendation not to run off the bench can be linked to the fact that there are traditionally professionals with different roles in the development of a system. The database worker can write more optimized queries while the developer traditionally does not specialize in database.

There are aspects that are tied to system requirements that can best define whether the choice is on one side or the other. For example, if the system needs to run on multiple databases, it is more costly to have to write the queries inside the database using the specific language of each one.

On the other hand,% s of the bank may have facilities that you will not find in the language you are using for access. If there is a requirement that you need to ensure the security of query execution, it may be more interesting to use the database object's security infrastructure.

Nowadays, with systems running in the cloud, it does not really matter to the user which database is being used and a project can easily be written to run on a specific database and thus benefit from all the features of it. I see such decisions with procedures banks, for example.

  

In issues of maintainability and legibility, which example is   better?

The maintenance and readability part will depend more on how you design the system than if the query is on the side of the program or the database.

In your example you are concatenating NoSQL on both sides. A more readable alternative would be to have two queries, each with its purpose and the program call one or the other according to the parameter instead of the parameter modify SQL . Another way to do this type of merge more cleanly would be to use a template engine at the top of the SQL command in the same way as SQL , for example.

  

Example 1 could affect the performance of the bank, due to the fact that   to execute the ifs? Which one to use?

If you are really judgmental, the example of the query written in the bank, you have two executions. The first is to interpret the command and mount the HTML and the second one actually executes the SQL . The second call is internal and her time is irrelevant. For the bank, SQL is dynamic and it will not have the chance to leave the query ready. You will have to run the preparation every time the command is run.

The query being sent from the program, although it represents only one execution, is also dynamic and the bank will not have the chance to leave it ready. Of course the optimization and caching mechanisms of today's banks already do a good job in this scenario, but it's good to know what's going on.

Normally, query execution times in SQL are of an order of magnitude much larger than the call times between the program and the bank, so you have to worry about the purpose of that query. If for example, you need to make a call to the program and with the answer you need to make other calls, maybe it's more interesting that everything is in SQL .

If, on the other hand, you need to split code execution to optimize the result, maybe letting everything inside procedure might limit you. That way this aspect should also be considered for every function you need to draw in the system.