SQL Server Data System and Application logic

In many new enterprise system designs implemented using SQLServer, system designers need to make key decisions on the positioning of data structures and management application logic. SQLServer has its own programming language (Transact-SQL, TSQL). developers can use it to manage data access, code transaction logic, and transaction control. Use TSQL,

In many new enterprise system designs implemented using SQL Server, system designers need to make key decisions on the positioning of data structures and management application logic. SQL Server has its own programming language (Transact-SQL, TSQL). developers can use it to manage data access, code transaction logic, and transaction control. Use TSQL,

In many new enterprise system designs implemented using SQL Server, system designers need to make key decisions on the positioning of data structures and management application logic. SQL Server has its own programming language (Transact-SQL, TSQL). developers can use it to manage data access, code transaction logic, and transaction control.
With TSQL, developers can create a stored procedure and encapsulate data access using a reusable, pre-compiled, and licensed code block. Each table in the database has a special set of stored procedures called triggers. When a specific database event (such as Insert, Delete, or Update) occurs in the underlying database, the trigger is "triggered. With triggers, developers can write event-based transaction logic, so that the Insert, Delete, and Update events of a given table can drive the changes of other tables.
With this flexibility, why don't we use TSQL to write more transaction logic as much as possible?
Use TSQL to develop logical storage of applications
TSQL can be used not only as a logical warehouse for a single application, but also as a logical warehouse for an application group that accesses the same data-there are several logical reasons. By centrally processing and managing data in SQL server, you can configure such a security system-that is, applications cannot access the underlying database before they pass transaction rules.
This is a common database example for most two-tier customers-server applications. The system delivers all transaction logic and data access to the backend server and the rich presentation logic to the client. The customer manages the view of the transaction process and data, but does not process other transactions except the display locally. If we put all the transaction logic in the central warehouse, this system has the potential to reduce management costs, but this will reduce the testability cost.
I recently contacted a customer who spent several hundred months (working by one person for one month) and thousands of dollars to design an extremely complex application that uses TSQL to manage all application logic. Although the system works well with 10 to 15 users, it is very slow if there are 20 users. By adding a processor to SQL server, the system can be used by 60 users at the same time. However, there is still a long distance between the design goals of 100 users, which makes the company's plan to open the application on the Internet unable to be implemented. Since stored procedures and triggers can only operate on local data, the company cannot split the application into multiple SQL servers to improve testability. As a result, the company had to modify it on a large scale.
Use the. NET class in application logic
The problems found by the above company after twists and turns, most system designers will re-recognize at the system design stage-the application logic is included in a group. the n-layer system of the NET class can increase the flexibility and testability of the application. Because TSQL is a language that focuses on data management, it is not flexible enough, but we can still use TSQL to compile complex transaction logic.
If developers use the. NET Framework, they can make their own language choices when developing core transactions. This flexibility allows you to perfectly match application requirements with development languages or resources. In addition, the objects that block these transaction processes can run on multiple machines and share the same underlying database server. SQL server can handle a large number of concurrent requests, regardless of the TSQL transaction logic.
Row operation and set operations)
In the planning system stage, a guiding ideology for determining whether to use row operations or set operations is: If TSQL is used, set operations are used, and if. NET is used, row operations are performed. Providing a large amount of data through network connections will affect the overall performance of applications, so it makes sense to use the server to process them whenever possible. However, from the perspective of memory and processing capabilities, SQL Server pointers (cursor) are very expensive objects, therefore, creating a pointer to traverse all the records in the set and process these records in sequence does not make much sense. ()
When you need to execute row-based processing that involves complex program logic or operations that consume a lot of CPU, you should query these rows from the server and process them in the middle layer.
If you want to use an example to see how to encapsulate the data access logic into an intermediate layer object, download the data from MSDN to access the application module. This is a code-provided and reusable Data Access Subsystem. You can use it to write data access objects for your own databases or feature applications.
By creating reusable.. NET application framework to handle most application logic and use the TSQL-based stored procedure as the security restriction and mechanism for server-side set operations. Then you can create and own both TSQL and. NET.