Ms SQL Server 2000 administrator manual series-13. T-SQL and SQL query Analyzer

13. T-SQL and SQL query Analyzer
What is SQL?
What is T-SQL?
Introduce new features of T-SQL
How to Use T-SQL
Summary
In this chapter, we will introduce some basic concepts about the Structured Query Language <Structured Query Language, SQL> and transact-SQL <t-SQL> and their differences. This chapter explains the Data Definition Language (DDL) and data operation language, and contains examples. We will also briefly introduce the new features of T-SQL in Microsoft SQL Server 2000. You will learn how to use different SQL Server utilities to create and manage database objects, including command column T-SQL and SQL query analyzer. You can also learn about creating T-SQL instructions.
What is SQL?
　
SQL is a database query and programming language used to access data and query, update, and manage relational database systems. The US National Bureau of Standards (ANSI) and the International Organization for Standardization (ISO) have developed SQL standards. ANSI is an American industrial and commercial group organization that develops business and communication standards in the United States. ANSI is also a member of ISO and International Electrotechnical Commission (IEC. ANSI releases American standards corresponding to international standards organizations. In 1992, ISO and IEC released the international standard for SQL, known as SQL-92. The standard that ANSI subsequently releases is the ANSI SQL-92. ANSI SQL-92 is sometimes called ansi SQL. Although there are some differences in the SQL versions used by different relational databases, most of them follow the ansi SQL standard. SQL Server uses an extended set of ANSI SQL-92, called a T-SQL, which complies with the ANSI SQL-92 standards.
The SQL language includes two main programming languages: Data Definition Language (DDL) and data operation language (DML ). The following describes the two languages.
DDL
　
DDL is used to define and manage objects, such as databases, data tables, and views. (Chapter 1 describes what a view table is ). DDL statements usually include the CREATE, alter, and drop commands for each object. For example, statements such as create table, alter table, and drop table can be used to create a new data table, modify its attributes (such as adding or deleting data rows), and delete a data table, next we will introduce them one by one.
Create table statement
　
Use DDL to create an example data table named mermer_data in the mydb database. We will use this data table as an example later in this chapter. As mentioned above, the create table statement can be used to create a data table. This example data table is defined as four data rows, as shown below:
Use mydb
Create Table customer_data
(Customer_id smallint,
First_name char (20 ),
Last_name char (20 ),
Phone char (10 ))
Go
This statement can generate a customer_data data table, which will remain empty until the data is filled in. For details about how to create a database data table, see Chapter 10th.
Alter table statement
　
Alter table statement is used to change the definition and attributes of a data table. In the following example, we use alter table to add the middle_initial data row to an existing customer_data table.
Alter table customer_data
Add middle_initial char (1)
Go
Currently, the definition of a data table includes five data rows, instead of the previous four data rows. For more information about using alter table, see Chapter 15th.
Drop TABLE statement
　
Drop table statements are used to delete data table definitions and permissions for all data, indexes, triggers, condition constraints, and data tables. To delete our customer_data data table, use the following command:
Drop table customer_data
Go
For details about the drop TABLE statement, see Chapter 15th.
DML
　
DML uses statements such as insert, select, update, and delete to operate data contained in database objects.
Insert statement
　
The insert statement is used to insert a column of data into a data table or view table. For example, if you want to add a new customer to the customer_data data table, you can use an insert statement similar to the following:
Insert into customer_data
(Customer_id, first_name, last_name, phone)
Values (777, "Frankie", "Stein", "4895873900 ")
Note the list of data row names in the second row in the SQL statement. The order of Data row names in the list determines which data row the data value will be placed in. For example, the first data value is placed in the customer_id of the first data row listed in the list, and the second data value is placed in the second data row. When creating a data table, we define that the order of values entered by data rows is the same as the current order, so we do not have to specify the field name. We can use the following insert statement instead:
Insert into customer_data
Values (777, "Frankie", "Stein", "4895873900 ")
________________________________________
Note:
If this form of insert statement is used, but the order of the inserted values is different from that when the data table is created, the values are placed in the wrong data row. If the data type does not match the definition, an error message is returned.
________________________________________
Select statement
　
The SELECT statement is used to retrieve data in a data table. The data to be retrieved is determined by the WHERE clause in the listed data rows and statements. For example, to retrieve the data of the customer_id and first_name data rows from the previously created customer_data data table, and retrieve only the data with the first_name data row value of Frankie in each column, the following SELECT statement can be used:
Select customer_id, first_name from customer_data
Where first_name = "Frankie"
If one column meets the criteria in the SELECT statement, the result is as follows:
Customer_id first_name
-------------------------
777 Frankie
Update statement
　
The update statement is used to update or change values in one or more columns. For example, if a customer named Frankie Stein wants to change his last name to Franklin in the record, the following update statement can be used:
Update customer_data
Set first_name = "Franklin"
Where last_name = "Stein" and customer_id = 777
We add the customer_id project in the WHERE clause to determine that other customers with the same name as Stein will not be affected-only customers with the same customer_id as 777 will change their surnames.
________________________________________
Description
When using the update statement, you must ensure that the WHERE clause provides sufficient filtering conditions so that you do not inadvertently change data that should not be changed.
________________________________________
Delete statement
　
The delete statement is used to delete data in one or more columns of a data table. You can also delete all data columns in the data table. To delete all columns from the customer_data table, you can use the following statement:
Delete from customer_data
Or
Delete customer_data
The from keyword before the data table name is optional in the delete statement. In addition, the two statements are identical.
To delete a column with a value less than 100 of the customer_id data row from the customer_data table, use the following statement:
Delete from customer_data
Where customer_id & lt; 100
Now we have quickly browsed the DDL and DML statements provided by SQL. Next we will introduce T-SQL.
What is T-SQL?
　
T-SQL is an enhanced version of the standard SQL programming language, which is the main language for applications to communicate with SQL Server. The T-SQL provides DDL and DML functionality for standard SQL, coupled with extended functions, system pre-stored programs, and program design structures (such as if and while) to make program design more flexible. The functionality of T-SQL continues to grow with the new version of SQL Server, let's take a look at the latest addition of T-SQL features.
Introduce new features of T-SQL
　
In Microsoft SQL Server 7.0, T-SQL has many new features, including new pre-stored programs, system data tables, functions, data types, statements, and options in existing statements. These are all retained in SQL Server 2000, so we should review them here (especially when you are not very familiar with the functionality of the T-SQL in SQL Server 7.0, this review is very important ). There are too many new features to be discussed. Here we will only give a few examples for each category.
________________________________________
Related information
For a complete list of these functions, see the topic "new and enhanced functions of transact-SQL" in SQL Server online books.
________________________________________
System pre-stored program
　
The system pre-stored program is provided by SQL Server to perform management and other work, including updating system data tables and retrieving data from system data tables. The system pre-stored program is installed with SQL Server. The name starts with SP _ (system pre-stored program) or XP _ (extended pre-stored program. These pre-stored programs are stored in the master database, and the permissions are owned by the system administrator. However, many programs can be executed from user-defined databases to retrieve information from system data tables in specific databases. When you execute a system pre-stored program, it is to expand the job in the system data table of the current database.
________________________________________
Related information
For more information about this type of program, see the "extended pre-stored program" topic in online books.
________________________________________
Many new system pre-stored programs have been added to SQL Server 7 and can be used in SQL Server 2000. Table 13-1 lists some system pre-stored programs that may be helpful to you.
Table 13-1 procedures introduced ....
System pre-stored program description
Sp_cycle_errorlog closes the current error log file and renames it errorlog.1 (and, if necessary, rename the old errorlog.1 as errorlog.2, and so on ), and start a new error log file.
Sp_helpfile returns the names and attributes of files related to the current database.
Sp_helpfilegroup returns the names and attributes of the archive groups related to the current database.
Sp_helprole returns the role information related to the current database.
Sp_help_alert Reports Server-defined alerts.
Sp_start_job indicates that the SQL Server Agent starts a job.
Not only these pre-stored programs provide immediate information, but they are quite useful when using your user database. For example, a program that provides user information can be quite helpful when a T-SQL directive is executed and output results are stored in a file. You can store the execution results of sp_helpfile, sp_helpfilegroup, and sp_helpdb in a specific database, after all, when you need to recreate the database, it is necessary to understand the original settings of the archive, archive group, and database options at the beginning. The list of system pre-stored programs added and retained in SQL Server 7.0 in SQL Server 2000 can be found in the topic "online series" "new and enhanced functions of transact-SQL.
System data table
　
The system data table is used to store the set information of all databases in SQL Server, as well as the definition of the access permissions of objects, users and users. Each user database has its own system data table, which stores the database information. The server-level setting information is available only in the system data table of the master database. You should use the system pre-stored program to access the system data table instead of directly accessing the system data table. You can find the list of System Data Tables added to the new SQL Server 7.0 in the topic "new and enhanced functions of transact-SQL" in online books. Some interesting new system data tables are listed below:
• The backupfile data table is stored in the MSDB database. It records all information related to file backup or database file backup. This information includes the archive ID, the archive group to which the archive belongs, and the name of the archive's existing physical disk or data segment.
　
• Restorehistory: The data table is stored in the MSDB database. It contains data columns corresponding to information related to each restoration job, whether it is file restoration or database restoration. The information includes the restoration date and time, the target database, the data recovery time, And the restoration type.
　
• Sysfiles: a virtual data table that cannot be directly updated or modified. It contains information related to each database file, such as the file name, file size, maximum file size, and database growth size of the physical device and logic (if the database grows ).
　
________________________________________
Note:
Always use the system pre-stored program to access the system data table. The system pre-stored program provides a layer of isolation to ensure that you do not change the data that cannot be changed. If you manually access the system data table, you may have the risk of changing important system information.
________________________________________
Function
　
SQL Server built-in functions provide a fast and simple way to complete some specific work. SQL Server 7.0 adds several useful functions, which are also included in SQL Server 2000. To understand which functions are available, the design of SQL server applications can be easier. In online books, you can find a complete list of newly added functions in the topic "new and enhanced functions of transact-SQL. Here we only introduce some functions that may be helpful to you:
• Newid creates a guid for the uniqueidentifier data type ). You need to use this function to assign a value to a data row of this data type, using newid (). (This function does not require independent variables)
　
• Year returns the complete integer of the specified date year. The usage is year (date ). For example, the following statement: Select Year ('2014/1/01') returns a value of 07/11.
　
• Month returns the integer of the specified date month. The usage is month (date ). For example, the statement 'select month' ('2014/1/01') will return a value of 7.
　
• Return the integer of the specified date part by day. The usage is day (date ). For example, the following statement states that select Day ('2014/1/01') will return a value of 11.
　
• File_name refers to the name of the logical file returned by the file ID. The usage is file_name (file_id_number ). For example, the declarative select file_name (4) logical name of the file with the file ID 4. If no file with this identifier is found in the database, null is returned.
　
Data Type
　
Several new data types are added to SQL Server 7, and some existing data types have extended sizes. In addition, SQL Server 2000 adds three data types, which are not discussed in Chapter 10th. Here, only SQL Server 7 has changed and the data types that are also included in SQL Server 2000 are listed:
• A new cursor data type is added to the Data Pointer variable. For information about pointers, see the topic "cursors" in online books.
　
• Three new Unicode data types-nchar, nvarchar, and ntext are added. Each UNICODE character uses two bytes and supports all international characters.
　
• A new uniqueidentifier data type is added to store the guid ).
　
• The maximum length of character data and binary strings can be 8,000 bytes. This length is applicable to Char, varchar, binary, varbinary, and other types.
　
SQL Server 2000 adds the following data types:
• Bigint stores the Integers of the eight-digit group length.
　
• SQL _variant allows values of different data types to be stored in the same data row. This type of data row stores and describes the value of the data itself-its basic type, decimal number of words, accuracy, maximum size, and collation ).
　
• The operation of a table is similar to that of a temporary data table. Its Declaration includes a list of data rows and data types. This data type can be used to define the return value of a region variable or a user-defined function.
　
Declarative
　
SQL Server 7.0 includes many new T-SQL statements and new options with existing statements. Similarly, these are also stored in s ql 2000. These statements correspond to some new functions of SQL Server 7.0. For example, the alter database statement contains the following new options for the archive and archive groups: Modify file, add filegroup, modify filegroup, remove file, and remove filegroup. In particular, the new DBCC checkfilegroup statement checks the distribution and structure integrity of all data tables in the archive group.
SQL Server 7.0 and SQL Server 2000 include two additional DBCC statements, DBCC shrinkfile and DBCC shrinkdatabase. The former can reduce the size of data files, and the latter can reduce all data files in the database and release unused disk space.
SQL Server 7 and SQL Server 2000 support an Improved Backup and Restore structure. The new backup statement allows full or partial database backup and file backup. The new restore statement allows you to use full or partial database backup and record file backup for restoration. These replace the dump and load statements in earlier versions of SQL Server. For a complete list of new statements and options for SQL Server 7.0 and SQL Server 2000, see the topic "new and enhanced functions of transact-SQL" in online books.
How to Use T-SQL
　
In addition to using T-SQL to design your applications (this topic is beyond the scope of this book, you can use one of the three SQL server tools-iSQL, osql, or query analyzer-to execute a T-SQL statement, or to create and execute a T-SQL directive.
ISQL Utility
　
The iSQL utility communicates with SQL Server through DB-library, which enables you to execute T-SQL statements, pre-stored programs, and script codes. Because DB-library retains the functions of SQL Server 6.5, iSQL applications may not support some new functions of SQL Server 2000. For example, iSQL cannot retrieve Unicode ntext data.
Osql utilities
　
Osql is a new tool added to SQL Server 7.0 and reserved to SQL Server 2000. It is mainly used to replace iSQL. These two utilities are basically not quite different, but osql uses open database connectivity (ODBC) to communicate with SQL Server, rather than DB-library, in addition, osql supports all SQL Server 2000 functions. Osql is similar to other functions of iSQL, and SQL Server 2000 supports both. However, you should use osql to replace iSQL to avoid the problems mentioned earlier.
To execute osql from the command prompt character window (MS-DOS Prompt window), you can use the following appropriate parameters to execute the osql. exe program:
Osql-u username-P password-s servername
When osql is connected to SQL Server, the following number is displayed:
1>
Enter the T-SQL statement under this prompt character,
1> sp_helpdb master
2> go
This statement lists information about the master database. The keyword go is not a T-SQL statement, it is a command that iSQL, osql, and query analyzer can recognize, mainly used as an end tag of A T-SQL Statement (as a batch operation ). The interactive query results are displayed in the command prompt character window.
If an error is entered in osql, you can use the reset osql command to return to 1> the prompt character,
1> sp_helpbd
2> Reset
1> sp_helpdb
2> go
To stop osql, enter quit or exit. You can also press Ctrl + C to end a command or interrupt the query without exiting the osql utility.
The osql utility can recognize not only-u,-P, and-S parameters. For more information about available osql parameters and osql, see the osql utility topic in the online guide.
Query Analyzer
　
Query analyzer allows you to execute T-SQL statements or instructions in Gui interfaces and output results to a window. You can also use this tool to perform some indexing and query analysis work. Some prefer to use query analyzer to execute statements rather than MS-DOS prompt character windows. To perform query analyzer, follow these steps:
1. Start query analyzer using one of the following three methods:
O enter "isqlw" after the command prompt 』.
　
O Enable Enterprise Manager and select SQL query analyzer from the tool menu.
　
O start/ASSEMBLY/Microsoft SQL Server/query Analyzer
　
The "connect to SQL Server" dialog box appears (unless you are online to the server), as shown in Figure 13-1.

　
 
Figure 13-1 online to SQL Server dialog box
2. Select the name of the server you want to bring online from the SQL Server drop-down menu. Enter your login information. If SQL server is not started yet and you want it to automatically start the service, check the check box under the options. Click OK. SQL query analyzer appears, as shown in Figure 13-2.
　
 
Figure 13-2 SQL query Analyzer
3. Enter any T-SQL statement or call the Deposit Program in the query window, as shown in 13-3. Note that the query window has been placed to the maximum, occupying the entire SQL query analyzer window.
　
 
Figure 13-3 call a stored procedure in the query window of query Analyzer
4. to execute this statement, click the execute query button on the tool column (green to the right arrow) or press Ctrl + E at the same time. The result is displayed in the result pane, as shown in Figure 13-4.
　
 
Figure 13-4 query results displayed in query Analyzer
5. to load and execute your pre-created T-SQL directive in query analyzer, click the button (yellow data clip) on the toolbar ), or you can browse and find your file from the file/open the old file. The script code is displayed in the upper pane. Click the execute query button to execute the instruction code.
________________________________________
Description
SQL query analyzer has many other options, including some new features of SQL Server 2000. For more details, see chapter 35th.
________________________________________
T-SQL script
　
Creating your own script is a pretty good method when you need to execute several T-SQL statements or pre-stored programs multiple times. Script is a simple file containing the T-SQL to be executed, either a statement or a series of continuous statements. It is recommended that you set the file extension to. SQL when creating a T-SQL directive so that you can quickly identify them.
The following program code is a simple script code example. This script calls several system pre-stored programs to output various information about the mydb database and its archives, archive groups, and data tables (customer_data and product_info. Assume that the script code has been pre-stored in the file named my_db. SQL.
Use mydb
Go
Sp_helpdb mydb
Go
Sp_helpfilegroup
Go
Sp_spaceused customer_data
Go
Sp_spaceused product_info
Go
You can use the-I option and-O option of osql to execute this command code through the command column. -I option: enter the name of the script file to be entered.-O option: Enter the file name of the file to which the execution result is output. In this example, the file name is mydb_info.out. We can also use the-e option to make the original T-SQL declarative display in the output file, making the results clearer. For example, to execute the above command code as a system administrator, enter the following program code after the command prompt character:
Osql-USA-p-I mydb_info. SQL-O mydb_info.out-e
Check the output file to check whether the script is correctly executed as expected. Using the T-SQL executable in this way allows you to store results in output files for later viewing. This method meets your expectations when you want to compare the output results of the script execution before and after database changes. In addition, the script code is helpful when you need to execute some statements more than once.
Another method that allows you to run the command code without having to input a bunch of osql commands every time is to use the osql command to create a. CMD file. In this example, we can name a mydb_info.cmd file, which contains the osql command we just typed. If the input and output files are not in the same directory as the. CMD files, make sure that their paths are specified. You can run mydb_info.cmd directly under the command prompt character or press the mydb_info.cmd file name twice in Microsoft Windows Explorer.
You can also execute a T-SQL directive in query analyzer. To execute our script code my_dbinfo. SQL, select enable old file from the File menu, browse and select the file to be executed. After the script is enabled, the code is displayed in the upper pane. Click the execute query button or press Ctrl + e to execute these statements. The execution result of each statement appears in sequence, as shown in 13-5. Note that the top two result data tables are from the first two sp_helpdb pre-stored programs in the script code.

　
 
Figure 13-5 execution result of the script in query Analyzer
Summary
　
In this chapter, you have learned the basics of SQL and T-SQL, and have seen some simple examples of DDL and DML statements. We also introduced different methods for executing T-SQL statements-including iSQL, osql, SQL query analyzer, and T-SQL instructions. More information about using the T-SQL will be found in the next two chapters and Chapter 25th.