SQL Server Data system and application logic

In many of the new enterprise system designs implemented with SQL Server, System architects need to make decisions that are critical to the positioning of data structures and management application logic. SQL Server has its own programming language (Transact-SQL, or TSQL) that developers can use to manage data access, code transaction logic, and transaction control.


using TSQL, developers can create a save process that encapsulates data access with a reusable, precompiled code block that has its own license settings during the save process. Each table in the database has a special set of save procedures called triggers. When a specific database event (such as INSERT, delete, or update) occurs on the underlying database, the trigger is "triggered". With triggers, developers can write event-based transaction logic so that the INSERT, delete, and update events for a given table can drive changes to other tables.

Since
has such flexibility, why don't we try to write more things logically with TSQL?


uses TSQL to develop application logical storage


TSQL not only serves as a logical repository for a single application, it can also be a logical repository for an application group that accesses the same data-for several logical reasons. By centralizing the data and managing the rules of data in SQL Server, you can configure the security system-that is, the application cannot access the underlying database until the transaction rules are passed.


This is a common database paradigm for most two-tier client-server applications. The system gives all the transaction logic and data access to the backend server and gives the rich presentation logic to the client. Customer manages transactional process and data view, but does not locally handle transactions other than the display. If you put all the business logic into the central warehouse, the system also has the potential to reduce the cost of management, but it will pay a lower price for testability.


I recently contacted a client who spent hundreds of months (one person working one months of work) and thousands of dollars to design a very complex application that uses TSQL to manage all application logic. Although the system is very sophisticated and works well for 10 to 15 users, it is very slow if you have 20 users. By adding a processor to SQL Server, the system can allow 60 of users to use it at the same time. But this is a long way from the design goal of 100 users, which makes it impossible for the company to open the application on the Internet. Because stored procedures and trigger can only manipulate local data, the company cannot decompose the application into multiple SQL Servers to improve testability. As a result, the company had to overhaul it on a large scale.


is used in application logic. NET class


the problem that the company above has found after a twist, most architects will realize in the system design phase that the application logic is contained in a set. NET class N-tier system can increase the flexibility and testability of the application. Since TSQL is a language that primarily aims to manage data, it is not flexible enough, but we can still write complex transaction logic with TSQL.


If the developer uses the. NET Framework, they can make their own language choices when developing core business processes. This flexibility gives you the most reasonable mix of application requirements and development language or resources. And if properly developed, 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 logic of processing TSQL transactions.


row operations (row operation) and set operations (set operations)

One of the guiding principles of
in planning system phases is to use a row or set operation: If you use a TSQL, use a set operation if you use a. NET is a row operation. Providing a large amount of data over a network connection can affect the overall performance of your application, so it makes sense to use the server to process them whenever possible.  But in terms of memory and processing power, the pointer to SQL Server (cursor) is a very expensive object, so creating a pointer to traverse all the records in the collection and process the records in turn does not make much sense. (www.3lian.com)


When you need to perform line based processing that includes complex program logic or CPU-intensive operations, you should query the rows from the server and process them in the middle tier.


If you want to use an example to see how to encapsulate data access logic into a middle-tier object, download the data Access Application module from MSDN. This is a reusable data access subsystem that provides code, and you can write your own database or attribute application's data access object based on it.


by creating reusable. NET application framework to handle most application logic and use a TSQL-based save process as a security restriction and mechanism for server-side set operations, you can create applications that have both the benefits of both TSQL and. NET.