SQL Other commands

4.7.1 BACKUP
The backup command is used to back up database content or its transaction log to storage media (floppy disk, hard disk, tape). The dump command is used to perform this function when the previous version of SQL Server 7.0 is in use, and the dump command is no longer used from SQL Server 2000. For more information on the backup command, see the "Data Backup and Recovery" section.
4.7.2 CHECKPOINT
The syntax is as follows:
CHECKPOINT
The CHECKPOINT command is used to force a data page, or log page, that has been changed in the current working database, from being forced to the hard drive from the data buffer cache.
4.7.3 DBCC
DBCC Database Base Consistency Checker, Database consistency checker) commands are used to validate the integrity of databases, find errors, analyze system usage, and so on.
The DBCC command must be followed by a child command, and the system knows what to do. The DBCC CHECKALLOC command checks the allocation and usage of all data pages in the current database.
For more information on the DBCC command, see Appendix.

4.7.4 DECLARE
The syntax is as follows:
DECLARE {{@local_variable data_type}
| {@cursor_variable_name cursor}
| {table_type_definition}
} [, .... N]]
The DECLARE command declares one or more local variables, cursor variables, or table variables. After the declare command is declared, all variables are given an initial value of NULL. You need to assign a value to a variable with a select or SET command. Variable types can be system-defined or user-defined types, but not text, NTEXT, or image types. The CURSOR named variable is a local cursor variable.
Example 4-16
DECLARE @x float @y datetime
Select @x = Pi @y = getdate
Print @x
Print @y
The results of the operation are as follows:
3.14159
June 4:32pm
Note:If the variable is a character type, it should be indicated in the DATA_TYPE expression
Its maximum length, otherwise the system considers its length to be 1.
Example 4-17
DECLARE @x char @y char 10
Select @x = ' 123 ' @y = ' data_type '
Print @x
Print @y
The results of the operation are as follows
1
Data_type

4.7.5 EXECUTE
The Execute command is used to execute a stored procedure, which is described in the "Stored Procedures" section for its specific use.
4.7.6 KILL
The KILL command is used to terminate the execution of a procedure, as you can see in the "Stored Procedures" section.
4.7.7 PRINT
The syntax is as follows:
PRINT ' any ASCII text ' | @local_variable | @ @FUNCTION | String_Expression
The PRINT command returns a user-customized message to the client that displays a string (up to 255 characters), a local variable, or a global variable. If the value of a variable is not a string, you must convert it to a string using the data type conversion function. Where String_Expression is an expression that can return a string. An expression can be longer than 8,000 characters, but more than 8000 characters will not be displayed.
Example 4-18:
DECLARE @x char @y char 10
Select @x = ' sql ' @y = ' Server '
print ' technology '
Print @x + @y

The results of the operation are as follows:
Technology
SQL Server

4.7.8 RAISERROR
The RAISERROR command returns the user-specified information when the SQL Server system returns an error message. See the "Stored Procedures" section for its specific use.
4.7.9 READTEXT
The READTEXT command syntax is as follows
READTEXT {table.column text_pointer offset size} [HOLDLOCK]
The READTEXT command is used to read data from a column with a data type of text, NTEXT, or image. Command reads the size character from the offset offset+1 a character, and if size is 0, the 4KB data is read. Where Text_pointer is a pointer to the first database page that stores text, which can be obtained using the TEXTPTR () function. See "4.8.7 TEXT and Image Functions" for the use of the TEXTPTR () function. The HOLDLOCK option locks the data that is read until the end of the transfer, during which time other users can read only data and cannot change the data.
Example 4-19:
Use Pangu
DECLARE @t_pointer varbinary (16)
Select @t_pointer = textptr (D_intro)
From department
where D_name = ' technology Department '
ReadText Department.d_intro @t_pointer 0 42
The results of the operation are as follows:
D_intro
--------------------------------------------------------------------
Carry out technical research and development, provide the latest technology dynamic information
(1 row (s) affected)

Note:If the data is in Chinese characters, the offset value should be O or another even number, if the odd number, there will be garbled

4.7.10 RESTORE
The RESTORE command is used to back up the database or its transaction log backup files from storage media to the SQL Server system. The previous version of SQL Server 7.0 uses the load command to perform this function, and the load command is no longer used from SQL Server 2000. For more information on the Restore command, see the "Data Backup and Recovery" section
4.7.11 SELECT
The SELECT command can be used to assign values to variables whose syntax is as follows:
SELECT {@local_variable = expression} [... n]
The SELECT command can assign values to multiple variables at once. When an expression is expression as a column name, the SELECT command can use its query function to return multiple values at a time, and the last value it returns is saved in the variable. If the Select command does not return a value, the value of the variable is still its original value. When an expression expression is a subquery, the variable is set to NULL if the subquery does not return a value.
Example 4-20:
Use Pangu
DECLARE @x char (30)
Select @x = ' CPU '
Select @x = D_name
From department
where dept_id = 9999
Select @x
The results of the operation are as follows:
-----------------------
Cpu
Example 4-21:
Use Pangu
DECLARE @x Char 30
Select @x = ' Main Board '
Select @x = (select D_name
From department
where dept_id=9999)
Select @x
The results of the operation are as follows
-----------------------
Null

4.7.12 SET
There are two uses of the SET command:
1 to assign a value to a local variable
The syntax is as follows:
SET {{{@local_variable = expression} | {@cursor_variable =
{@cursor_variable cursor_name
| {CURSOR
[Forward_only | SCROLL]
[STATIC | Keyset | DYNAMIC | Fast_forward]
[Read_Only | Scroll_locks | Optimistic]
[Type_warning]
For select_statement
[For {READ only
| UPDATE [of column_name [... n]]}}
Parameters such as cursor are described in the "Cursors" chapter.
After the declare command is declared, all variables are given an initial value of NULL. You need to assign a value to a variable with the SET command. Unlike the Select command, however, the SET command can assign values to only one variable at a time. However, because the set command is more powerful and more rigorous, SQL Server recommends using the SET command to assign a value to a variable.

Example 4-22
DECLARE @x char (30)
Set @x = ' This is a set command. '
Select @x
The results of the operation are as follows
------------------------------
This is a SET command.
Example 4-23
Use Pangu
DECLARE @department_num int @x char (10)
Set @department_num = SELECT COUNT (*)
From department)
Set @x = ' Number of departments '
Print @x
Select @department_num
The results of the operation are as follows
Number of departments
-----------
7

2 to set the SQL Server's processing options when the user executes the SQL command
There are several ways to set this up:

• SET option on
• SET option off
• SET option value

For more information on such set commands, see the Appendix.
The syntax is as follows
SHUTDOWN [WITH NoWait]
The SHUTDOWN command stops SQL Server execution, and when the nowait parameter is used, the SHUTDOWN command stops SQL Server immediately, exiting SQL Server after terminating all user procedures and having a rollback for each current transaction. When the nowait parameter is not used, the SHUTDOWN command performs the following steps:
(1) Terminate any user login to SQL Server.
(2) Wait for a Transact-SQL command or stored procedure that has not yet completed execution complete.
(3) Execute the checkpoint command in each database
(4) Stop execution of SQL Server
Example 4-24
Shutdown with NoWait
The results of the operation are as follows:
------------------------------
Changed language setting to us_english.
Server shut down by request.
SQL Server is terminating the this process.
There are several ways to stop SQL Server execution, which we'll introduce in the Enterprise Manager Enterprisemanager section.

4.7.14 WRITETEXT
The syntax is as follows:
WRITETEXT {table.column Text_pointer} [with LOG] {data}
The WRITETEXT command is used to read data in a column with a data type of text, NTEXT, or image. Where Text_pointer is a pointer to the first database page that stores text, which can be obtained using TEXTPTR (). See "4.8.7 TEXT and Image Functions" for the use of the TEXTPTR function. The woth log option is used to record the data being written. Data can be text or a variable with a maximum length of 120KB. Note: The WRITETEXT command does not work on the view.

It is recommended that you use the UPDATETEXT command to modify the text, NTEXT, and image type data. Because the WRITETEXT command replaces the data in the data column completely, the UPDATETEXT command can change only a portion of the data column. The use of the UPDATETEXT command is described in the "Database Updates" section.
Example 4-25
DECLARE @t_pointer binary (16)
Use Pangu
Select @t_pointer = Textptr D_intro
From department
where D_name = ' technology Department '
WRITETEXT Department.d_intro @t_pointer ' carry out technical research and development to provide the latest technology dynamic information '

4.7.15 use
The syntax is as follows
Use {databasename}
Use command to change the currently used database to the specified database. The user must be a user member of the target database or the target database has a guest user account, and the use command is used to successfully switch to the target database.