SQL Server Functions

With version 7.0, Microsoft added more than 30 functions to an already large number of functions. More were added in SQL Server 2000 as well to support new features or capabilities, or to provide additional functionality.

Some of the SQL Server functions provide shortcuts to obtain information that could be retrieved in other ways. For instance, one function will obtain an object ID (OBJECT_ID()) if you know the object's name, but looking it up in the sysobjects table could also work.

Other functions, such as some of the mathematical functions, are more essential. (Okay, it could be argued that you can calculate the square root, for instance, using T-SQL code, but it is not efficient.)

Most functions have the following structure:

FUNCTION_NAME([parameter1 [, parameter2 [, ...]])

The parameters might be an expression (such as a column name or another function), a constant, or a special code (such as a formatting code).

A function returns a value. The datatype for the value depends on the function you are using. Take a look at the available functions grouped by category.

String Functions

The string functions allow you to perform concatenation, parsing manipulation, and so on with strings.

TIP

Excessive use of string functions against a column might indicate that the column should be split into several columns. For example, if you find yourself frequently parsing out first name and last name from a name column, perhaps you should split the name into two columns.

Table 26.3 lists the available string functions. They can be used against any string expression.

Metadata Functions

The metadata functions, listed in Table 26.7, are useful for retrieving information such as column names, table names, index keys, and so on. Basically, many of the functions are shortcuts for querying the system tables.

TIP

It is better to use the system functions than to directly query the system tables. If the system tables change in forthcoming releases of SQL Server (as they did with version 7.0), your applications and scripts will still work if you use the system functions. You can also use the information schema views for retrieving system-table?related information. The views are ANSI standard and are independent of the system tables, and all have the object owner INFORMATION_SCHEMA.

Table 26.7. Metadata Functions

Function Name

Returns

New in SQL Server 2000

COL_LENGTH(table,column)

The length of column in table.

COL_NAME(table_id,column_id)

The name of column_id in table_id.

COLUMNPROPERTY(id,column,property)

Information about a column in a table, given the table id. Returns information for a parameter, given in column, for a stored procedure. The property parameter defines the type of information to be returned.

DATABASEPROPERTY (database_name,property)

Setting of specified property for database_name. Included for backward compatibility, use DATABASEPROPERTYEX instead.

DATABASEPROPERTYEX (database_name,property)

Current setting of specified property for database_name as a sql_variant.

Yes

DB_ID([db_name])

The database ID of db_name or the current database.

DB_NAME([db_id])

The database name of db_id or the name of the current database.

FILE_ID(filename)

The ID for filename.

FILE_NAME(file_id)

The filename for file_id.

FILEGROUP_ID(filegroupname)

The ID for filegroupname.

FILEGROUP_NAME(filegroup_id)

The filegroup name for filegroup_id.

FILEGROUPPROPERTY (filegroup_name,property)

The value of property for filegroup_name.

FILEPROPERTY (filename, property)

The value of property for filename.

FULLTEXTCATALOGPROPERTY (catalog_name, property)

The value of property for full-text catalog catalog_name.

FULLTEXTSERVICEPROPERTY (property)

Information about property for full-text service-level.

INDEX_COL(table,index_id,key_id)

The column name for the specified table, index_id, and key_id.

INDEXKEY_PROPERTY (table_ID,index_ID,key_ID,property)

The property information about index key key_ID for index_ID on table_ID.

Yes

INDEXPROPERTY(table_ID,index,property)

The property info for index index on table_ID.

OBJECT_ID(object_name)

The ID for object_name.

OBJECT_NAME(object_id)

The database object name for object_id.

OBJECTPROPERTY (object_id,property)

Information for object_id. property defines the type of information to be returned.

SQL_VARIANT_PROPERTY (expression,property)

The property information about sql_variant expression, such as base datatype, as a sql_variant.

Yes

TYPEPROPERTY(datatype,property)

Information defined in property for datatype.

System Functions

The system functions, listed in Table 26.8, are useful for retrieving information about values, options, and settings within the SQL Server.

Table 26.8. System Functions

Function Name

Returns

New in SQL Server 2000

APP_NAME()

The name of the application that executes the function.

CAST(expressionASdatatype)

The CAST function is a synonym for the CONVERT function and converts expression to datatype.

COALESCE(expr1, [expr2,,,])

The first non-null expression in the list.

COLLATIONPROPERTY(collation_name,property)

The specified property of the collation_name. property can be CodePAgeLCID, ComparisonStyle.

Yes

CONVERT(datatype[(length)],expression,style)

Converts expression to datatype. For conversion of datetime or float expressions, style defines the formatting.

DATALENGTH(expression)

The storage area of expression, including trailing blanks for character information.

GETANSINULL([db_name])

The default nullability option of db_name for the current database.

GETCHECKSUM(col_name)

A checksum value for the values in col_name.

HOST_ID()

The process ID of the client application's process.

HOST_NAME()

The client's workstation name.

IDENT_CURRENT('tablename')

The last identity value generated for tablename by any session and within any scope.

Yes

IDENT_INCR(table)

The identity increment for the identity column in table.

IDENT_SEED(table)

The identity seed for the identity column in table.

IDENTITY(datatype[,seed,increment]) AScolumn_name

Used only in SELECT INTO to create identity column in new table.

ISDATE(char)

1 if char is in a valid datetime format; otherwise, 0.

ISNULL(expression,value)

value if expression is NULL.

ISNUMERIC(char)

1 if char can be converted to a numeric value; otherwise, 0.

NEWID()

A generated global unique identifier.

NULLIF(expr1,expr2)

Null if expr1 equals expr2.

PARSENAME(object_name,object_part)

Name of object_part (specified as an int) of object_name.

PERMISSIONS(object_id[,column])

A bitmap indicating permissions on object_id and optionally column.

ROWCOUNT_BIG()

Number of rows affected by previous statement executed in session as a bigint.

Yes

SCOPE_IDENTITY()

The last identity value inserted into an IDENTITY column within current scope (for example, stored procedure, trigger, function, or batch).

Yes

SERVER_PROPERTY('property')

The property information about the server as a sql_variant.

Yes

SESSIONPROPERTY(option)

The SET options of a session as a sql_variant.

Yes

STATS_DATE(table_id,index_id)

Date when the distribution page was updated for index_id on table_id.

TRIGGER_NESTLEVEL ([tr_object_id])

Nesting level of specified or current trigger.

The following example returns the title ID and price for all books. If the price is not set (NULL), it returns a price of 0:

SELECT title_id, ISNULL(price, 0) FROM titles

Let us expand the example. You want to display the string 'Not Priced' for those that contain NULL values. You have to convert the price to a character value before replacing NULL with your text string:

Table 26.11. Rowset Functions

A table of 0 or more rows for columns containing string data using precise or fuzzy matches to a single word or phrases, the proximity of words to one another, or weighted matches, as specified in contains_conditions. Result can be limited to the top n matching rows ordered by rank.

A table of 0 or more rows that match the meaning of the text in freetext_string. table is a table marked for full-text querying. column must be columns that contain string data. Result can be limited to the top n matching rows ordered by rank.

OPENDATASOURCE (provider_name,init_string)

The connection information used as the first part (servername) of a four-part fully qualified object name. Can be used in place of a linked server name. Should only reference OLE DB data sources.

Resultset from an XML document specified with docid. Data is returned in edge table format unless SchemaDeclaration or TableName is specified.

Yes

The following example uses the OPENROWSET function and the Microsoft OLE DB Provider for SQL Server to titles records from the titles table in the pubs database on a remote server named RRANKINSA20P. Notice how the result from the function can be used just like a table, even in a JOIN clause:

To learn more about the CONTAINSTABLE and FREETEXTTABLE functions, see Chapter 44, "SQL Server Full-Text Search Services." For more information on using XML documents and the OPENXML function, see Chapter 41, "Using XML in SQL Server 2000."