Chittibabu

System functions perform operations and return information about values, objects, and settings in an instance of SQL Server. The large majority of system functions are nondeterministic.

Contents [hide] 1 CASE Function 2 COALESCE Function 3 ISNULL Function 4 NULLIF Function 5 GETANSINULL Function 6 CAST and CONVERT Function 7 @@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() Function 8 ISDATE Function 9 ISNUMERIC Function 10 CURRENT_TIMESTAMP Function 11 DATALENGTH Function 12 @@TRANCOUNT Function 13 XACT_STATE Function 14 @@ERROR Function 15 @@ROWCOUNT Function 16 APP_NAME() Function 17 UPDATE() Function 18 COLUMNS_UPDATED() Function 19 ERROR_LINE Function 20 ERROR_MESSAGE Function 21 ERROR_NUMBER Function 22 ERROR_PROCEDURE Function 23 ERROR_SEVERITY Function 24 ERROR_STATE Function 25 Additional System Functions Function 26 More SQL Server Functions

CASE Function

CASE is typically classified as a system function; however, it could also be considered as a statement. There are two general uses of the CASE function. The first one is used to replace occurrences of one value with other values, as specified by the programmer. Syntax for this flavor of CASE is as follows:



For instance, the following query attempts to specify the salary level for each job title within Adventure Works DW database:

Results (abbreviated):



The other variation of CASE, which is sometimes referred to as the searched CASE, evaluates a Boolean expression and returns different values accordingly. For instance, we could use the searched CASE to categorize the top internet customers within Adventure Works DW database as follows:



Results:

COALESCE Function

The COALESCE function returns the first value from a supplied list that is not NULL. COALESCE is a powerful tool if you are returning numerous values to a user and want to substitute occurrences of NULL with values from a different column or with an expression. The syntax is:
All expressions must have compatible data types. For instance, you can't coalesce DATETIME and INTEGER, but you could coalesce VARCHAR(20) and CHAR(20).
For example, the following query will examine the parent account key column for each account within DimAccount table; if the value is NULL the query will return "none" as the parent account:
Results (abbreviated):

COALESCE function is equivalent to the CASE function if it checks each expression for NULL values and returns the non-null expression, as in:

ISNULL Function

The ISNULL deterministic function is similar to COALESCE, but accepts only two parameters. The first parameter will be checked, and if NULL value is found, it will be replaced with the second parameter. Furthermore, ISNULL requires that both parameters have the same (not just compatible) data type. For example, we can return 'none' if the parent account of the given account is NULL:



Results would be identical to those results obtained with the COALESCE function.

NULLIF Function

The NULLIF deterministic function returns a NULL value if the two parameters it accepts are equivalent. NULLIF can be thought of as an opposite of ISNULL; for instance, we could substitute a NULL for number of employees if we find that number of employees for a particular reseller is 10:



Results (abbreviated):

GETANSINULL Function

The GETANSINULL function provides a quick way of checking whether column nullability is determined according to the ANSI 92 standard. The function returns 1 if ANSI null standard is used for column nullability, otherwise zero is returned. This function takes a single argument of database name. If database name isn't specified the setting is returned for the current database. For example:



Results:

CAST and CONVERT Function

The CAST and CONVERT functions are very similar: Both translate a value from one data type to another. Although their performance is also similar, their syntax and potential usage is slightly different. The syntax is as follows:



The expression must already have a data type that is translatable into the new_data_type. For instance, you can't convert an alphanumeric string into an integer.
Note: CONVERT has an optional parameter: style. This parameter is allowed only for cases when working with date and time values. SQL Server supports numerous formats for presenting date and time values; the style parameter is used to specify such format.
For example, suppose you want to retrieve dates from the dimTime table without returning the time portion. Either CAST or CONVERT function can be used as follows:
Both return the same results:
If you needed to return a date value in which month, day, and year are separated by dashes you could use the CONVERT function with the style 110, as follows:



Results:

@@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() Function

The @@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() functions deal with IDENTITIY values or globally unique identifiers. SQL Server 2005 also supports the NEWSEQUENTIALID() function for default constraints of columns with the UNIQUEIDENTIFIER data type.
The NEWID() function could be used if you want to provide a default value for a column with the UNIQUEIDENTIFIER data type. This function can also be used to generate a new GUID in Transact-SQL. Columns with UNIQUEIDENTIFIER data type are often used as primary keys. Values generated by NEWID() function are impossible to predict and are not ordered, which can cause performance issues. For example, the following script creates a table and populates using NEWID() function for UNIQUEIDENTIFIER data type column. Note that resulting GUIDS are NOT sequential:



Results:



Fortunately SQL Server 2005 supports the NEWSEQUENTIALID() function that generates a sequential GUIDS which tend to perform better than unordered GUID. The NEWSEQUENTIALID() function can only be used in a DEFAULT constraint expression. Since values generated by NEWSEQUENTIALID() are ordered one can predict the value that will be generated next; therefore this function shouldn't be exposed to the users if data security is of concern. For example, the following script creates a table and populates it with sequential values for UNIQUEIDENTIFIER data type column. Note that resulting GUIDS are sequential:


Results:
The IDENTITY function has limited use; in rare cases, when you use SELECT INTO syntax you can supply identity values for the newly created table using the IDENTITY function. For instance, suppose we want to add an identity column to the sales table (within a temporary table). We could use the following statement to copy all rows from sales table into #new_sales and add an identity column, all in one shot:
The other three IDENTITY-related functions deserve more attention. You will often need to populate multiple related tables, perhaps within a single transaction. For instance, you could be populating the order and order_details tables in one transaction. If the order table has an identity column, you'll have to look up the identity value just inserted into the order table before you can add a related record in order_details. The @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() functions help you look up the last identity value inserted, but their behavior is slightly different from each other, as follows:

• @@IDENTITY returns the last IDENTITY value inserted on the current connection. Suppose that you have an INSERT trigger on the order table that populates the audit_trail table, which also has an IDENTITY column. In such a case, the @@IDENTITY function will return the last identity value inserted, which would be the identity inserted in the audit_trail table instead of the identity value added to the order table. Therefore, if you try populating order_details with a value returned by the @@IDENTITY function, your data integrity will be compromised.
   
• The IDENT_CURRENT() function accepts a table name as the parameter and returns the last identity value generated in that table by any connection. If you were trying to populate order_details with the last identity value inserted into the order table, then IDENT_CURRENT('order') could work if you were the only user of the system; however, another user might have added a row to the order table a few milliseconds after you added the row to the same table. Therefore, using IDENT_CURRENT() in a multi-user system might also compromise your data integrity.
   
• The SCOPE_IDENTITY() function takes no parameters and returns the last identity value inserted within the current scope. So if an INSERT statement populating the order table executes a trigger and adds a row to the audit_trail table, SCOPE_IDENTITY() will return the last value added to the order table, whereas @@IDENTITY will return the last value added to audit_trail.

ISDATE Function

The ISDATE function determines whether the parameter passed is of a date and time data type. This function returns a BIT value, as in the following example:
Results:

ISNUMERIC Function

The ISNUMERIC deterministic function determines whether the parameter passed is of a numeric data type. This function returns a BIT value, as in the following example:
Results:

CURRENT_TIMESTAMP Function

The CURRENT_TIMESTAMP function works exactly the same way as GETDATE: It returns current date and time. For example:


Results:

DATALENGTH Function

The DATALENGTH deterministic function is similar to the LEN function, which returns the length of a particular string expression. DATALENGTH returns the number of bytes used to represent an expression of any data type. For example, the following query returns the data length for currency which is stored as NCHAR(3) data type:
Results:

@@TRANCOUNT Function

The @@TRANCOUNT function returns the number of open transactions on a particular connection. You can check the value of @@TRANCOUNT to troubleshoot blocking issues. @@TRANCOUNT can also be used for error handling; if @@TRANCOUNT returns anything other than 0, something must have gone wrong and you have uncommitted transactions on the current connection. This function does not take any parameters.

XACT_STATE Function

The XACT_STATE function is new with SQL Server 2005. It is similar to @@TRANCOUNT since it determines whether there are any uncommitted transactions on the current connection. The XACT_STATE function does not accept any parameters. Unlike @@TRANCOUNT the XACT_STATE function can also determine if the uncommitted transaction has been classified as an uncomittable transaction. This function can return one of the following values:

• 1 The session has an active transaction. The transaction can be committed.
   
• 0 The session has NO active transactions.
   
• (-1) The session has an active transaction; an error has occurred which classifies the active transaction as uncommittable. The transaction cannot be committed; neither can the session request rolling back to a savepoint. Rather the entire transaction must be rolled back. After the transaction has been rolled back the session can initiate a new transaction.

You can effectively use XACT_STATE function for error handling, as shown below:

@@ERROR Function

The @@ERROR function returns the number of the last error encountered on the current connection. If there are no errors, @@ERROR returns 0. The @@ERROR function is used for error handling. With SQL Server 2000 and previous releases checking @@ERROR was the only way to diagnose and troubleshoot errors. SQL Server 2005 introduces TRY / CATCH syntax for error handling as well as several new functions: ERROR_LINE, ERROR_NUMBER, ERROR_SEVERITY, ERROR_STATE, and ERROR_MESSAGE. Note that @@ERROR returns the error number returned by the last executed statement, so it's important to catch the error immediately after it occurs. For example, the following query catches the error because it checks for error immediately after the statement that encountered the error:
Results:


However, the next example does NOT catch the error because it checks the @@ERROR function value too late, after a statement that completes successfully:


Results:

@@ROWCOUNT Function

The @@ROWCOUNT function returns the number of rows affected by the last query. This function can be used effectively to find out whether the number of rows modified is the same as what you intended to modify. If your query was supposed to update 15 rows but @@ROWCOUNT returns 10 then something must have gone wrong. This function is often used for error handling. Much like with @@ERROR it's important to get @@ROWCOUNT value immediately after the statement you want to examine. For example, the following query erroneously reports that total number of affected rows is one, even though your main query returned 10 rows, as desired:


The ROWCOUNT_BIG() function does the same thing as @@ROWCOUNT, but returns a BIGINT data type instead of an INT data type.
Note: It's easy to confuse the functionality of ROWCOUNT and @@ROWCOUNT. The former advises SQL Server to affect only a specified number of rows (similar to the TOP keyword); the latter simply counts the number of rows affected, as shown here:
Results:

APP_NAME() Function

The APP_NAME() function returns a string with the name of the application that initiated the database connection. APP_NAME() can be helpful if you're troubleshooting a connection and want to know which app initiated the offending process. For example:
Results:

UPDATE() Function

The UPDATE() function is only available within INSERT or UPDATE triggers and determines whether a value of a single column has been modified. You can use this function to execute certain logic within a trigger only if the value of a particular column has changed. The function accepts column name as the only parameter. For example, the following query rolls back the transaction within trigger if the value of AccountCodeAlternateKey column has been changed:

COLUMNS_UPDATED() Function

The COLUMNS_UPDATED() function works similarly to UPDATE() except it checks for multiple columns being updated by the same statement. Like UPDATE() function the COLUMNS_UPDATED() is only available within insert and update triggers. This function returns a bit pattern with VARBINARY data type showing which columns have been affected by the INSERT or UPDATE statement that invoked the trigger. The function does not accept any parameters. For example, the following query checks whether columns 2, 3 and 4 have been modified:

ERROR_LINE Function

The ERROR_LINE function returns the line number at which the error occurred which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the line number where the error occurred:


Results:

ERROR_MESSAGE Function

The ERROR_MESSAGE function returns the text of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error text:



Results:

ERROR_NUMBER Function

The ERROR_NUMBER function returns the number of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error number:


Results:

ERROR_PROCEDURE Function

The ERROR_PROCEDURE function returns the name of the stored procedure or trigger that encountered the error. This function does not accept any parameters and can be effectively called from CATCH block. For example, the following query creates a stored procedure that intentionally causes divide by zero error. Next the procedure is executed and the name of the erroneous stored procedure is returned:



Results:

ERROR_SEVERITY Function

The ERROR_SEVERITY function returns the severity of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error severity:
Results:

ERROR_STATE Function

The ERROR_STATE function returns the state of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error state:
Results:

Additional System Functions Function

The following table provides a quick reference for what the rest of the system functions do:

System Function	Parameters	Description and Example
STATS_DATE	Table_id, index_id	Determining the last time statistics were updated. Example: view sourceprint? 1.SELECT TOP 1 STATS_DATE(object_id, index_id)  FROM sys.indexes Results: view sourceprint? 1.-----------------------  2.2005-10-14 01:36:26.140
COLLATIONPROPERTY	Collation_name, property	Determining the value of a certain collation property. Example: view sourceprint? 1.SELECT  COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'codepage') Results: view sourceprint? 1.----  1252
FN_HELPCOLLATIONS	None	Returns the list of supported collations. Example: view sourceprint? 1.SELECT * FROM FN_HELPCOLLATIONS()
FN_SERVERSHAREDDRIVES	None	Returns the list of shared drives in clustered servers. Example: view sourceprint? 1.SELECT * FROM FN_SERVERSHAREDDRIVES()
FN_VIRTUALFILESTATS	Database_id, file_id	Determining I/O stats for a certain database or log file. Example: view sourceprint? 1.SELECT * FROM FN_VIRTUALFILESTATS(10, 1)
FORMATMESSAGE	Message number, parameter value	Constructing an error message using an existing message in sysmessages table. Example: view sourceprint? 1.sp_addmessage 50002, 16,  'terrible error on %s'  2.GO  3.DECLARE @var1 VARCHAR(100)  4.SELECT @var1 =  FORMATMESSAGE(50002, 'Table1')  5.SELECT @var1 Results: view sourceprint? 1.--------------------------  terrible error on Table1
SERVERPROPERTY	Property name	Returns a server property value. The following example returns the edition of the current instance: view sourceprint? 1.SELECT SERVERPROPERTY('edition') Results: view sourceprint? 1.-----------------  Developer Edition
SESSIONPROPERTY	Option	Getting a session value for various session options configured using SET statements. The following query returns ANSI_NULLS setting: view sourceprint? 1.SELECT SESSIONPROPERTY('ANSI_NULLS') Results: view sourceprint? 1.--  1
PARSENAME	Object_name, object_piece	Returning server, owner, database or name portion of the specified object. Object piece can take the following values: 1 - object name 2 - schema name 3 - database name 4 - server name Example: view sourceprint? 1.SELECT PARSENAME('dbo.DimAccount', 2) Results: view sourceprint? 1.-----  Dbo