A Review of New T-SQL Features | Microsoft SQL Server 2000 Administrators Companion

3 4

Numerous additions and enhancements were made to T-SQL in Microsoft SQL Server 7, including new stored procedures, system tables, functions, data types, statements, and options for existing statements. These remain the same in SQL Server 2000, so we will review them here (in case you are not yet familiar with them from SQL Server 7). There are far too many features to describe them all in detail here, however, so we'll look at just a few examples in each category.

MORE INFO
For a complete list of features, refer to the topic "New Features in Transact-SQL" in SQL Server Books Online. To display this topic, on the Contents tab, click Transact-SQL Reference, and then click New Features In Transact-SQL.

System Stored Procedures

System stored procedures are provided by SQL Server to perform administrative and other tasks that involve updating system tables and to retrieve information from system tables. System stored procedures are installed with SQL Server; their names begin with sp_ (for "stored procedure") or xp_ (for "extended stored procedure"). These procedures are stored in the master database and are owned by the system administrator, but many of them can be executed from any user-defined database to retrieve information from the system tables in that particular database. When you execute a system stored procedure, it operates on the system tables in the current database.

MORE INFO
See the topic "Extended Stored Procedures" in Books Online for more information about this type of procedure.

Many system stored procedures were added in SQL Server 7, and they are now available in SQL Server 2000 as well. Table 13-1 describes a few of these system stored procedures that you might find helpful.

Table 13-1. Procedures introduced in SQL Server 7 and included in SQL Server 2000

System stored procedure	Description
sp_cycle_errorlog	Closes the current error log file, renames it errorlog.1 (and, if necessary, renames the old errorlog.1 as errorlog.2, and so on), and starts a new error log file
sp_helpfile	Returns the physical names and attributes of the files associated with the current database
sp_helpfilegroup	Returns the names and attributes of the filegroups associated with the current database
sp_helprole	Returns information about the roles in the current database
sp_help_alert	Reports information about the alerts defined for the server
sp_start_job	Instructs the SQL Server Agent to begin execution of a job

Not only do some of these stored procedures provide immediate information, but they can also be used to save important information about your user databases for later use. For example, the procedures that return information about a user database might prove useful when they are run as T-SQL scripts and the output is saved to a file. You could run and save the output from sp_helpfile, sp_helpfilegroup, and sp_helpdb (this latter stored procedure is an oldie) in a particular database, in case you ever need to rebuild that database and want to know how the files, filegroups, and database options were originally created and configured. A listing of the remainder of the new system stored procedures (introduced in SQL Server 7) can be found in the "New Features in Transact-SQL" topic in Books Online.

System Tables

System tables are used to store SQL Server configuration information and definitions of objects, users, and permissions for all databases. Each user database has its own system tables, which hold information for that database. System tables that hold server-level configuration information are found in only the master database. You should use system stored procedures to access system tables rather than access the tables directly. A list of the new system tables first featured in SQL Server 7 can be found in the "New Features in Transact-SQL" topic in Books Online. Some interesting new system tables include the following:

backupfile This table resides in the msdb database. It records a row of information for every log backup or database file backup. This information includes the file ID, the filegroup that the file belongs to, and the physical drive letter for the file.
restorehistory This table resides in the msdb database. It records a row of information for each restore operation, whether it be a file restore or a database restore. The information includes the date and time the restore occurred, the destination database, the point in time to which the data was recovered, and the type of restore.
sysfiles This table is a virtual table, which means it cannot be updated directly. It contains information about each database file, such as the physical and logical filenames, the size and maximum size for the file, and the growth increment, if any.

CAUTION
Always use system stored procedures to access system tables. The system stored procedures provide a layer of insulation that can keep you from altering data you should not alter. If you access system tables by hand, you run the risk of rendering your database useless by inadvertently altering important system information.

Functions

SQL Server's built-in functions provide a quick and easy way to accomplish certain tasks. Several new T-SQL functions were made available in SQL Server 7 and are included in SQL Server 2000. Knowing which functions are available to you can make SQL Server application programming a little easier. A complete list of the new functions can be found in the "New Transact-SQL Functions" topic in Books Online. Here are just a few new functions that you might find useful:

NEWID Creates a globally unique identifier (GUID) of type uniqueidentifier. You would use this function to assign a value to a column of that type. Usage: NEWID(). (No parameters are needed.)
YEAR Returns an integer that represents the year part of a date. Usage: YEAR(date). Example: the statement SELECT YEAR('07/11/01') returns the value 2001.
MONTH Returns an integer that represents the month part of a date. Usage: MONTH(date). Example: the statement SELECT MONTH('07/11/01') returns the value 7.
DAY Returns an integer that represents the day part of a date. Usage: DAY(date). Example: the statement SELECT DAY('07/11/01') returns the value 11.
FILE_NAME Returns the logical name of a file that corresponds to the given file ID number. Usage: FILE_NAME(file_id_number). Example: The statement SELECT FILE_NAME(4) returns the name of the file that has an ID of 4. If no file in the database has that file ID, NULL is returned.

Data Types

Several new data types were added with SQL Server 7, and a size extension was added to some existing data types. In addition to including these changes, three more new data types were added for SQL Server 2000. Most of these data types were discussed in Chapter 10. Here is a list of the data type changes in SQL Server 7 that are also included in SQL Server 2000:

A new cursor data type has been added for cursor variables. For more information about cursors, see the topic "Cursors" in Books Online.
Three new Unicode data types—nchar, nvarchar, and ntext—have been added. Unicode characters use 2 bytes per character and can support all international characters.
A new uniqueidentifier data type has been added for storing GUIDs.
The maximum length for character data and binary strings has been expanded to 8000 bytes. This length applies to the char, varchar, binary, and varbinary types.

And the three new data types for SQL Server 2000 are as follows:

bigint Stores an 8-byte integer.
sql_variant Allows values of different data types in the same column. The data value itself and data describing its value-its base data type, scale, precision, maximum size, and collation-are stored in a column of this type.
table Works in similar ways as a temporary table does; the declaration includes a column list and data types. This data type can be used to define a local variable or for the return value of a user-defined function.

Statements

SQL Server 7 included many new T-SQL statements and new options added to existing statements, which, again, are also part of SQL Server 2000. These statements correspond to some of the features introduced in SQL Server 7. For example, the ALTER DATABASE statement includes the following new options for files and filegroups: MODIFY FILE, ADD FILEGROUP, MODIFY FILEGROUP, REMOVE FILE, and REMOVE FILEGROUP. Also related to filegroups, the new statement DBCC CHECKFILEGROUP checks the allocation and structural integrity of all tables in a given filegroup.

SQL Server 7 and SQL Server 2000 include two additional DBCC statements: DBCC SHRINKFILE and DBCC SHRINKDATABASE. The former shrinks the size of a given data file, and the latter shrinks all data files in a given database, freeing up unused disk space.

SQL Server 7 and SQL Server 2000 have an improved backup-and-restore architecture. The new BACKUP statement allows full or partial database backups and log backups, and the new RESTORE statement allows full or partial database backups and log backups to be restored. These take the place of the DUMP and LOAD statements from previous versions of SQL Server. For a complete list of the new statements and options available for SQL Server 7 and SQL Server 2000, see the "New Features in Transact-SQL" topic in Books Online. See Chapters 32 and 33 for details about backing up and restoring databases.