In addition to the built-in scalar functions, SQL Server 2000 now provides a set of system-wide table-valued functions that can be invoked from any database. Usually, when you invoke a user -defined table-valued function that is not local to the current database context, you have to fully qualify the function name with the database name. This is not required for system-wide table-valued functions. A special syntax is used to invoke system-wide table-valued funcions. You must precede the function name with two colons (::), as shown in the following example:
SELECT * FROM ::fn_virtualfilestats(5, 1) go DbId FileId TimeStamp NumberReads NumberWrites BytesRead BytesWritten IoStallMS ---- ------ --------- ----------- ------------ --------- ------------ --------- 5 1 88725180 36 2 294912 16384 460
The fn_virtualfilestats function returns IO statistics for a database file. It is passed the database ID and a file ID as parameters. The previous example returned the IO stats for the datafile of the pubs database.
SQL Server 2000 provides a number of documented and undocumented table-valued functions. The following is a list of the documented functions:
The trace- related functions perform actions that previously could only be performed via extended stored procedures. These functions will be discussed in more detail in Chapter 7, "Using the SQL Server Profiler."
The majority of the undocumented system-wide table-valued functions are used within SQL Server replication and are not intended for end-user execution.