Many coders are confused about the codes such as select, insert, update, delete, sum (), convert () after they have just come into contact with the database or for a period of time. What are these? In fact, these are the SQL statements we often say. More formally, they are called T-SQL. So what is T-SQL? In Microsoft SQL Server 7.0, T-SQL has many new functions, including new stored procedures, system tables, functions, data types, statements and options in existing statements. These are all stored in SQL Server 2000, so we need to review them here first (especially if you are not familiar with the functions of T-SQL in SQL Server 7.0, this review is very important). Because there are too many new functions to discuss, here are only a few examples for each category. System stored procedures System stored procedures are provided by SQL Server to perform administrative and other tasks, including updating and retrieving data from system tables. System stored procedures are installed with SQL Server, and their names begin with sp_ (system stored procedures) or xp_ (extended stored procedures). These stored procedures are stored in the master database, and the permissions belong to the system administrator. However, many procedures can be run from a user-defined database to retrieve information from the system tables of a specific database. When you run a system stored procedure, it expands the operation in the system table of the current database. Sp_cycle_errorlog closes the current error log file and renames it to errorlog. 1 (if necessary, rename the old errorlog. 1 to errorlog.2, and so on), and starts a new error log file.

Sp_helpfile returns the names and properties of files related to the current database.

Sp_helpfilegroup returns the name and properties of the filegroup associated with the current database.

Sp_helprole returns role information related to the current database.

Sp_help_alert reports the alert information defined by the server.

Sp_start_job instructs SQL Server Agent to start the job.

These stored procedures are not the only ones that provide instant information, but they can be very useful when using your user database. For example, the process of providing user information is very useful when executing a T-SQL script and saving the output to 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 rebuild the database, it is necessary to know the original settings of files, filegroups and database options. Add and keep the list of system stored procedures in SQL Server 2000 in SQL Server 7.0. Function SQL Server's built-in functions provide a quick and simple way to accomplish some tasks. SQL Server 7.0 adds several useful functions, which are also included in SQL Server 2000. Knowing which functions are available can make the design of SQL Server applications easier. A complete list of new functions can be found in the topic "New and enhanced functions of Transact-SQL" in Books Online. Here are some features that might help you:

NEWID creates a globally uniqueidentifier (GUID) of the UniqueIdentifier data type. You need to use this function to assign values to columns of this data type through NEWID (). (This function does not require parameters. )

YEAR returns an integer for the year part of the specified date. The usage mode is year (date). For example, the following Select Year statement ('07/ 1 1/0 1') will return 200 1.

MONTH returns an integer for the month part of the specified date. Use month (date). For example, the following select month statement ('07/ 1 1/0 1') will return a value of 7.

DAY returns an integer of the date part of the specified date. The usage mode is day (date). For example, the following statement Select Day ('07/1 11') will return the value11.

FILE_NAME returns the logical file name of the file ID. The usage mode is file name (file identification number). For example, the statement SELECT FILE_NAME(4) is the logical name of the file with file ID 4. If a file with that ID cannot be found in the database, NULL is returned.