Use the SET Option to configure the SQL Server Client

Generally, when a client is used to connect to the SQL Server database, the client connection is initialized by default using Server-related configuration. However, in some cases, the database administrator wants the client to manage its own settings. In addition, these client settings are often different from those of the server. In this case, the client must be able to connect to the database server without modifying the server settings.

To implement similar functions, you need to use the SET option in the SQL Server database. To meet the above requirements, the database administrator can SET ANSI_DEFAULTS ON and then SET the client configuration through SQL _COPT_SS_PRESERVE_CURSORS. In this way, you can achieve different client and server configurations, and improve the flexibility of client configuration.

However, it should be noted that using these SET options will lead to different configurations between the client and the server, that is, some connection parameters and runtime environments may be messy. Therefore, you must also consider this consistency when using the SET option. That is to say, pay attention to the following details when using the SET option,

1. time when the SET option takes effect.

When you use the SET Option to SET relevant running parameters and environment, pay attention to the effective time. This is different from the default server parameters. The default parameter always takes effect unless the database administrator changes its settings later. However, when you use the SET Option to SET relevant parameters, the effective time is especially special. Generally, the SET option can be divided into the SET option during analysis and the SET option during execution. The validity period of this option varies with their classification. For example, the analysis option takes effect when the option in the text is analyzed during the analysis, regardless of whether it is controlled by flow statements. The execution type selection takes effect during the code execution of the specified options. For example, you can SET a SET option in a batch processing program. If the execution of the batch processing program fails for some reason before the SET statement is executed, this option will not take effect, that is, the database system has not SET this option. If the SET statement fails to be executed, this option takes effect.

Therefore, if the SET option is used with some batch processing programs, pay special attention to the location of the SET option. Whether the SET type is effective depends on the execution of other statements in the batch processing program. For example, there are eight statements in a batch processing program, and the SET type selection is the fifth one. If the execution of the batch processing program gets stuck in the third statement, the SET Option statement of the fifth statement does not work at all. For this reason, I suggest the database administrator to place the SET Option statements in front of the batch processing program to initialize the relevant runtime environment of the batch processing program. Unless otherwise necessary, do not place them in the middle.

2. Pay attention to the scope of the SET option.

In fact, the parameters SET by the SET option and the default parameters of the database server are like the relationship between a local variable and a global variable. The parameter set in the former is a local variable, which is only valid within a specific range; if it is out of this range, it will become invalid. Global variables are usually valid unless they are assigned a value again in a certain field. Therefore, the database administrator must understand the scope of the SET Model Selection and be prepared to reference these parameters later. Specifically, these SET options are often used in combination with different functions. Such as batch processing programs and triggers. As the scope of use varies, the scope of use varies.

1. valid during the session. Some SET options are dedicated for client connection. For example, the SET type selected by the author at the beginning is used to SET the connection parameters of the client. These parameters may differ greatly from the default parameters of the server. What is the scope of these parameters? Generally, the SET option for a dedicated session is valid before the reset or termination of the user's session on the server. That is, the SET option takes effect during client connection initialization. These options take effect as long as the user's session is not terminated. Unless the user terminates the current session or the server forcibly resets relevant parameters, the SET option takes effect as the session is enabled and becomes invalid as the session ends. In addition, the SET option SET for a user is only valid for the user's session and does not affect the communication between other users and the database.

2. Support the MARS connection to maintain a SET of default SET Option values. When performing batch processing under this connection, the default SET option value will be copied to the request environment. After the batch processing is completed, the environment will copy back to the default settings of the session. In this way, multiple batches executed simultaneously under the same connection will run in an independent SET Option environment. This is an official explanation. What does it mean? That is to say, multiple batch processing jobs may be executed during the same connection. If the SET option is not SET in these batch processing jobs, the default settings of the server are used by default. However, if the SET option is used to change the default setting in the first batch, the SET option is used in the first batch running environment. In this way, you can set a relatively independent runtime environment for each batch processing program under the same connection according to the actual needs of the enterprise. If multiple batches are executed at the same time and the execution environment of the batches is modified under connections supporting MARS, the default connection environment is determined by the final batch processing. That is to say, the final connection parameter depends on the SET option in the last running skin handler. Simply put, unless the current connection is interrupted or the server is forced to reset, the relevant parameters in the current connection will be changed by the SET option in the batch processing program. The value is changed multiple times. The final parameter is determined by the SET option in the last batch processing program.

3. You can also use the SET option in the trigger or process. What is the life cycle of the SET option when used with them? This should be done in different situations. First, it depends on whether the SET option is used multiple times in the trigger or process content. For example, in a trigger, a parameter may be SET using the SET option at the beginning. After a function is executed, the default value is restored and the remaining functions are executed. In this case, the effective duration is before the parameter in the trigger is reset. That is to say, you can enable an option in a trigger or internal trigger before closing it. It is valid only before the option is enabled or disabled. Second, if a SET option is enabled at the beginning of the trigger and is not disabled, the option will not always take effect. Generally, the SET type selection will become invalid after the current trigger or process is executed. To this end, the SET option is like a local variable in the memory or process, which takes effect only within the trigger or process.

4. If process A calls another process B, but if process A uses the SET Option to change some parameters, will this change affect the running environment of process B? The answer is uncertainty. This mainly depends on whether the SET type selection is reset in process B. That is to say, unless the display is reset in process B, the SET option value from all higher-level code is valid in lower-level code. Simply put, if process A calls process B, the SET option value in process A is equally valid in process B. Unless the SET type is reset as shown in process B. In fact, this principle is very simple, as if A variable is defined in process A, and then process B is called. In this case, when process B uses the variable defined in process A, its value is the modified value in process. Of course, in process B, you can also modify the variable value defined in process A. For example, you can reset the value of this variable. However, when process A ends, the variable will become invalid.

It can be seen that this SET option is very similar to the local variables and global variables during program development. When using these SET options, the database administrator should pay attention to a basic principle, that is, the function scope is usually only valid in the current session or process. When enabling a SET option, the database administrator needs to consider the problem that the change is only for a specific session or a specific job, or for all users? If it is the former, you can SET it through the SET option. But if it is the latter, it is best to change it through the server settings. Otherwise, you need to SET the SET type for each session or job, which means the workload is relatively large. I have studied program development before. Therefore, I prefer to use the relationship between global variables and local variables to learn about this function when I select this SET. Global variables are valid throughout the runtime environment, while local variables are valid only within a specific range. That's simple. After understanding the scope of the SET option, you can help the database administrator determine whether to use the SET Option and when to use the SET option, to improve the flexibility of client connection and user running environment.

1. Describes how to delete SQL Server logs.
2. Detailed Description: query data between different SQL Server databases
3. Several Key Techniques for SQL Server Cluster