Functions execute a section of code that performs an operation that returns a desired value. For the function to perform its operation, you must usually supply the required data as a list in which each element is called a parameter. You can use functions with columns of data and other storage structures of Transact-SQL. You can also use functions in the Select or Where clause of Select statements, in expressions, and, for selected functions such as system and niladic functions, in constraint defined in tables or views.
If you've worked with another dialect of SQL, then you're probably familiar with the handful of basic functions. Unfortunately, the set of functions that are used across different vendors' dialects of SQL is extremely small. The remaining functions may be comparable across server database SQL dialects, though they aren't identical.
Some of the basic Transact-SQL functions are shown in Table 8.1.
Function Operation AVG Average SUM Sum MIN Minimum value MAX Maximum value COUNT Count
These, and other functions in a SELECT clause, are used as if they are column identifiers. The objects or arguments of a function must be enclosed in parentheses. If the function requires more than a single argument, the arguments are separated by a comma (,).
The syntax for the use of functions in the SELECT clause of a SELECT statement is as follows:
SELECT function (column_1 or *),...function (column_n)
FROM table
Note that NULL values aren't used for computation of AVERAGE, SUM, MIN, MAX. If all elements of a set are NULL, the function return is NULL. COUNT, when used with an asterisk (*), determines the number of rows in a column, even if it contains NULL values.
select avg(pay_rate)
from pays
SELECT COUNT(column_name)
FROM table_name
For example, the following SELECT statement returns the number of rows in the Employees table.
select count(*)
from employees
If a WHERE clause is used in your SELECT statement, the COUNT function applies to only the rows that match the criteria specified in the WHERE clause. For example, the following COUNT statement returns the number of employees in the Sales department.
select count(*)
from employees
where department='Sales'
You can improve the performance of the COUNT function by specifying a column name to count, and making sure that the column you specify is both indexed and not NULL. By doing so, SQL Server can use its optimization techniques to return the count of the rows more quickly.
SELECT MAX(column_name)
FROM table_name
In the following example, MAX is used to return the maximum, or greatest number of hours_worked for all rows of the Pays table.
select max(hours_worked)
from pays
select min(hours_worked)
from pays
In the next example, MIN is used to return the lowest rate of pay for employees in the Field Service department. Both the Pay and Employees tables must be referenced in the SELECT statement because the Department column is in the Employees table while the Rate column is in the Pays table. The corresponding badge numbers in each table are used to combine the appropriate rows.
select min(rate)
from employees,pays
where employees.badge=pays.badge
and department='Field Service'
SUM([DISTINCT] <expression>)
In the following example, the result would be the sum of the hours_worked for all rows of the Pays table is displayed:
select sum (hours_worked)
from pays
Rows that contain a NULL value in the column referenced by the SUM function are automatically skipped in the calculation.
You can use multiple functions within a single statement. The following example returns the average, minimum, and maximum of the hours_worked column in the Pays table:
select avg(hours_worked), min(hours_worked), max(hours_worked)
from pays
In a more-complicated example that follows, a SELECT statement is used to return the maximum and average rate, minimum hours_worked, and the count of all rows of the Employees table.
select max(rate),min(hours_worked),avg(rate),count(*)
from employees,pays
where employees.badge=pays.badge
SELECT COUNT(DISTINCT column_name)
FROM table_name
In the following example, the keyword DISTINCT is used with the COUNT function in a SELECT statement to display the number of different departments in the Employees table:
select count(distinct department)
from employees
There are several things to be aware of when using the CUBE and ROLLUP operators. First, a GROUP BY column list can be no more than 900 bytes. Second, there is a maximum of 10 columns. Next, columns or expressions must be specified in the GROUP BY clause; GROUP BY ALL can't be used. Finally, these operators are disabled when trace flag 204 is on.
Book sales are a perfect example. A query that returns a book title and the number of books ordered for each invoice in a database would return a row for each invoice. If the cube operator was applied to this query, it would only return a row for each title and the total quantity ordered for that title.
ASCII PATINDEX SPACE
CHAR REPLICATE STR
CHARINDEX REVERSE STUFF
DIFFERENCE RIGHT SUBSTRING
LOWER RTRIM UPPER
LTRIM RTRIM +
String functions are usually used on CHAR, VARCHAR, BINARY, and VARBINARY datatypes as well as datatypes that implicitly convert to CHAR or VARCHAR. For example, you can use the PATINDEX function on CHAR, VARCHAR, and TEXT datatypes.
You can nest string functions so that the results returned by an inner function are available for the operation performed by the outer function. If you use constants with string functions, you should enclose them in quotation marks. String functions are usually used in SELECT or WHERE clauses.
You should ensure that the result returned by a nested function is compatible as input to the function in which it's embedded. In other words, if your function is expecting a string variable, be sure that the nested function returns a string, not a numeric value. Check your functions and datatypes carefully to determine if they're compatible. Otherwise, a the set of functions can't function correctly.
ASCII(<char_expr>)
Remember that ASCII only returns the code associated with the leftmost character. If you need to have the ASCII value associated with the remaining portion of the string, you'll need to write a function that can walk down the string and return each value in succession.
CHAR(<integer_expr>)
In the following example, the ASCII and CHAR functions are used to convert a character to the decimal ASCII value, and the decimal ASCII value to a character:
select ascii('Able'),char(65)
----------- -
65 A
The syntax for use of the SOUNDEX function is as follows:
SOUNDEX(<char_expr>)
SOUNDEX ignores all vowels unless they're the first letter of a string. In the following example, SOUNDEX is used to return evaluation values for a series of strings.
select soundex ('a'),soundex ('aaa'),soundex ('b'),soundex ('red'),
soundex ('read')
----- ----- ----- ----- -----
A000 A000 B000 R300 R300
select soundex ('right'),soundex ('write')
----- -----
R230 W630
DIFFERENCE(<char_expr1>, <char_expr2>)
In the following example, the difference between "the" and "teh" is four, a value that is considered a good match. If you were using DIFFERENCE along with SOUNDEX in a program such as a spell checker, "teh" can be treated as a misspelling of "the."
select difference(soundex('the'),soundex('teh'))
-----------
4
The value that is returned by the DIFFERENCE function is fixed according to the design of the DIFFERENCE function. You must decide how you use the value returned. In the example, a value of 4 means that the two character strings, the and teh, are as alike as they can be using the soundex scale of values.
If you're looking for a misspelling for the name of a department stored in the Department column of a table such as Employees, a value of 3 or less may be a different department or a misspelling of a department.
LOWER(<char_expr>)
UPPER(<char_expr>)
In the following example, UPPER and LOWER are used to convert a mixed-case string to all-uppercase and all-lowercase:
select upper('Bob Smith1234*&^'),lower('Bob Smith1234*&^')
---------------- ----------------
BOB SMITH1234*&^ bob smith1234*&^
LTRIM(<char_expr>)
In the following example, LTRIM is used to remove leading spaces from a string:
select ltrim(' middle ')
--------------
middle
In this example, the return value of ('____middle____') still contains trailing spaces. You need to use the next function, RTRIM, to remove trailing spaces.
RTRIM(<char_expr>)
In many cases, you'll want to work with the string without any leading or trailing spaces. Remember that you can nest these functions, so you can use the syntax as indicated in the following example:
select RTRIM(LTRIM(' middle ')
This example returns only the word "middle" with no spaces surrounding it.
CHARINDEX(<'char_expr'>, <expression>)
In the following example, CHARINDEX returns the starting character position of the word "Service" in a row of the Department column of the table Employees. An uppercase S—the first letter in Service—is the seventh character in the Field Service department.
select charindex('Service',department)
from employees
where name='Stan Humphries'
-----------
7
CHARINDEX can be used with other functions. The value returned by CHARINDEX can be used with other functions to extract parts of strings from within other strings. For example, CHARINDEX could be used within the string expression in the second argument of SUBSTRING.
Wild-card characters can be used in the substring as long as the percent sign (%) precedes and follows the substring. The syntax PATINDEX is as follows:
PATINDEX('%substring%', <column_name>)
In the following example, PATINDEX returns the character position for the first character of the substring within the string of characters stored in the department for the employee Stan Humphries. Stan Humphries is a member of the Field Service department.
select patindex('%erv%',department)
from employees
where name='Stan Humphries'
-----------
8
REPLICATE(character_expression, integer_expression)
In the following example, REPLICATE returns a string of identical characters and also returns two iterations of the same sequence of two characters:
select replicate ('a',5),replicate('12',2)
----- ----
aaaaa 1212
REVERSE(character_string)
In the following example, the example would return the two constant strings that are enclosed in quotation marks, but their contents would be reversed:
select reverse('12345678910'),reverse('John Smith')
----------- ----------
01987654321 htimS nhoJ
In the following example, the result is a table column displayed without REVERSE, the same column is displayed in a different order using REVERSE, and the same string that is the name of the column of the Employees table is processed as a constant because it's enclosed in parentheses:
select name,reverse(name),reverse('name')
from employees
where name='Bob Smith'
name
-------------------- -------------------- ----
Bob Smith htimS boB eman
RIGHT (character_expression, integer_expression)
The following example shows two identical strings, one that is displayed with a RIGHT function and also without a function. The second parameter of the RIGHT function 4 specifies to return from four characters from the end of the string to the rightmost character of the string.
select '12345678', right ('12345678',4)
-------- ----
12345678 5678
(1 row(s) affected)
You can't use a function such as the RIGHT function on TEXT or IMAGE datatypes. You must use the specialized set of string handling functions with TEXT and IMAGE datatypes. These special functions are discussed later in this chapter in the section entitled "Using TEXT and IMAGE Functions."
SPACE(<integer_expr>)
In the following example, SPACE returns multiple spaces between two string constants:
select 'begin',space(15),'end'
----- --------------- ---
begin end
STR(<float_expr>[, <length>[, <decimal>]])
You should ensure that both the length and decimal arguments are non-negative values. If you don't specify a length, the default length is 10. The value returned is rounded to an integer by default. The specified length should be at least equal to or greater than the part of the number before the decimal point plus the number's sign. If <float_expr> exceeds the specified length, the string returns ** for the specified length.
In the following example, a series of constant numbers is converted to strings. The first number is completely converted because the second argument (the length) specifies the correct size of the resultant string, five numeric digits, the minus sign (–), and the decimal place (.). When the same constant value is converted using a length of six, the least-significant digit is truncated.
The third constant is correctly displayed using a length of six because it's a positive number. The same constant can't be displayed with a length of two, so two asterisks (**) are displayed instead.
select str(-165.87,7,2)
go
select str(-165.87,6,2)
go
select str(165.87,6,2)
go
select str(165.87,2,2)
go
-------
-165.87
------
-165.9
------
165.87
--
**
STUFF(character_string_1,starting_position,length,character_string_2)
In the following example, the string abcdef is inserted into the first string beginning at the second character position. The string "abcdef" is inserted after the number of characters specified by the length argument are deleted from the first string:
select stuff('123456',2,4,'abcdef')
---------
1abcdef56
If the starting position or length is negative, or if the starting position is larger than the first character_string, STUFF displays a NULL string. In the following example, a NULL is the result of the code shown because the starting position is a negative value:
select stuff('wxyz',-2,3,'abcdef')
(null)
If the length to delete is longer than the length of the first character string, the first character string is deleted to only the first character. In the following example, only the first character of the first character string remains after the second character string is inserted:
select stuff('123',2,3,'abc')
----
1abc
Like several other string functions, you can't use SUBSTRING with Text or Image datatypes. The SUBSTRING syntax is as follows:
SUBSTRING(character_string, starting__position,length)
In the following example, multiple SUBSTRINGs are used along with the SPACE function to separate the first name from the last name, each of which is stored in a single column of the Employees table.
select substring(name,1,3),space(4),substring(name,5,5)
from employees
where badge=1234
--- ---- -----
Bob Smith
Unlike earlier examples, the following example uses a function in several SQL statements. Multiple functions are often used in stored procedures or other batch objects. See Chapter 13, "Managing Stored Procedures and Using Flow-Control Statements" for more information about the use of local variables and the SELECT statement in the following example. Like the previous example, the first name is separated from the last name with multiple spaces added between the names, all of which is done by using multiple functions.
declare @x int
select @x=charindex(' ',(select name from employees where name='Bob Smith'))
select @x=@x-1
select substring(name,1,@x), right(name,@x+2)
from employees
where badge=1234
--------------------
Bob Smith
(d)+ (Concatenation)
The concatenation operator symbol (+) concatenates two or more character or binary strings, column names, or a combination of strings and columns. Concatenation is used to add one string to the end of another string. You should enclose character strings within single quotation marks. The syntax of the concatenation operator is as follows:
<expression> + <expression>
Conversion functions are used to concatenate datatypes that could not be concatenated without a change in datatype. CONVERT is one of the functions that you can use for datatype conversion. In the following example, a string constant is concatenated with the current date returned using the GETDATE date function. GETDATE is nested within CONVERT to convert it to a datatype, in this case VARCHAR, that is compatible with the string constant.
select 'The converted date is ' + convert(varchar(12), getdate())
----------------------------------
The converted date is Jul 11 1994
(1 row(s) affected)
Two query processing options can be used to control the execution of statements that include arithmetic functions. The keyword for each of the two arithmetic operations is preceded by the SET keyword. You can use the ARITHABORT option to terminate a query when a function finds an error. ARITHIGNORE returns NULL when a function finds an error. If you set both ARITHABORT and ARITHIGNORE, no warning messages are returned.
There are numerous mathematical functions available in Transact-SQL (see Table 8.2).
Function Parameters Return ACOS (float_expression) Angle in radians whose cosine is a FLOAT value. ASIN (float_expression) Angle in radians whose sine is a FLOAT value. ATAN (float_expression) Angle in radians whose tangent is a FLOAT value. ATAN2 (float_expr1,float_expr2) Angle in radians whose tangent is float_expr1/floatexpr2. COS (float_expression) Trigonometric cosine of angle in radians. COT (float_expression) Trigonometric cotangent of angle in radians. SIN (float_expression) Trigonometric sine of angle in radians. TAN (float_expression) Trigonometric tangent of expression in radians. DEGREES (numeric_expression) Degrees converted from radians returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL and FLOAT. RADIANS (numeric_expression) Radians converted from degrees returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. CEILING (numeric_expression) Smallest INTEGER >= expr returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. FLOOR (numeric_expression) Largest INTEGER <= expr returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. EXP (float_expression) Exponential value of expression. LOG (float_expression) Natural log of expression. LOG10 (float_expression) Base 10 log of expression. PI() Value is 3.1415926535897936. POWER (numeric_expression,y) Value of expression to power of y returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. ABS (numeric_expression) Absolute value of expression returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. RAND ([integer_expression]) Random flat number between zero and one using optional int as seed. ROUND (numeric_expr,integer_expr) Rounded value to precision of integer_expr returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. SIGN (numeric_expression) One, zero, or –1 returned as the same datatype as expression. Datatypes can be INTEGER, MONEY, REAL, and FLOAT. SQRT (float_expression) Square root of expression.
The following example shows the use of ABSOLUTE, RANDOM, SIGN, PI, and RANDOM within an expression:
Select abs(5*-15),rand(),sign(-51.23),pi(),round((10*rand()),0)
----- ------------------ ---------- ---------------------- ----
75 0.3434553056428724 -1.0 3.141592653589793 8.0
(1 row(s) affected)
In another example of the use of mathematical functions, FLOOR and CEILING are used to return the largest and smallest integer values that are less than or equal to, or greater than or equal to, the specified value.
select floor(81),ceiling(81),floor(81.45),
ceiling(81.45),floor($81.45),ceiling(-81.45)
-------- -------- ------------- ------------- ----------- -------
81 81 81.0 82.0 81.00 -81.0
(1 row(s) affected)
ROUND always returns a value, even if the length is invalid. If you specify that the length is positive and longer than the digits after the decimal point in ROUND, a zero is added after the least-significant digit in the returned value. If you specify that the length is negative and greater than or equal to the digits before the decimal point, 0.00 is returned by ROUND.
The following example shows the effects of using ROUND functions on various values. In the first example, the decimal number is rounded to two decimal places. The second number is displayed as 0.00 because the length is negative.
select round(81.4545,2), round(81.45,-2)
------------------------ ---------------
81.45 0.0
(1 row(s) affected)
In the following example, the first number is rounded down to three decimal places while the second number is rounded up to a whole number because it's more than half the value of the least-significant digit.
select round(81.9994,3),round(81.9996,3)
------------------------ ------------------------
81.999 82.0
(1 row(s) affected)
SET TEXTSIZE n
Use n to specify the number of bytes to be displayed. You must specify the value of n in the function SET TEXTSIZE as an INTEGER. If you specify n as zero (0), the default length in bytes (up to 4K bytes) is displayed. The current setting for TEXTSIZE is stored in the global variable @@TEXTSIZE.
In the following example, the TEXTSIZE default is first used to display a table column defined as the datatype TEXT. SET TEXTSIZE is defined to two (2), and as a result only two bytes of the table-column test are displayed. Finally, TEXTSIZE is reset to the default of 4K using a value of zero (0).
select * from imagetext_table
image1 ... text1
-------------------------- ... ----------------------------------
0x31323334353637383961637a782b3d5c ... 12345678aczx+=
(1 row(s) affected)
set textsize 2
go
select text1 from imagetext_table
go
set textsize 0
go
select * from imagetext_table
go
text1
------------- ... --------------------------------------
12
(1 row(s) affected)
image1 ... text1
-------------------------- ... ----------------------------------
0x31323334353637383961637a782b3d5c ... 12345678aczx+=
(1 row(s) affected)
TEXTPTR(column_name)
READTEXT [[<database.>]<owner.>]<table_name.><column_name>
<text_pointer> <offset> <size>
In the following example, TEXTPTR retrieves the point to the first page of text for the one-and-only row of the table. The pointer is stored in a local variable @v. READTEXT is then used to extract a substring starting at the third byte—using an offset to skip past the first two bytes and retrieve the specified four bytes.
declare @v varbinary(16)
select @v=textptr(text1) from imagetext_table
readtext imagetext_table.text1 @v 2 4
(1 row(s) affected)
text1
--------------------------------------...----------------------------
3456
TEXTVALID('table_name.column_name', text_pointer)
In the following example, TEXTVALID determines the validity of a pointer to a data column stored as the datatype text. Recall that the output of one function can be used as the input to another function, as is the following example:
select textvalid('imagetext_table.text1',(select textptr(text1)
from imagetext_table))
go
-----------
1
(1 row(s) affected)
In the next example, a SELECT statement that contains a WHERE clause returns a table row. As a result, TEXTVALID returns a zero—an invalid value because no row column was located.
select textvalid('imagetext_table.text1',(select textptr(text1)
from imagetext_table where text1 like '5'))
-----------
0
(1 row(s) affected)
See the Chapter 5 section entitled Numeric integer Datatypes
Use a conversion function if you're unsure whether SQL Server will perform implicit conversions for you or if you're using other datatypes that aren't implicitly converted.
The CONVERT syntax is as follows:
CONVERT(<datatype> [(<length>)], <expression> [, <style>])
You can use CONVERT in SELECT and WHERE clauses or anywhere an expression can be used in a Transact-SQL statement. If you omit a length specification, it defaults to value of 30. Any unrecognized values that appear in DATATIME-to-SMALLDATETIME conversions aren't used. Not surprisingly, any conversions of BIT datatypes convert non-zero values to one (1) in keeping with the usual storage of BIT datatypes.
Integer values that you convert to MONEY or SMALLMONEY datatypes are processed as monetary units for the defined country (such as dollars for the United States). If you convert CHAR or VARCHAR datatypes to INTEGER datatypes such as INT or SMALLINT, the values must be numeric digits or a plus (+) or minus (–) sign.
Conversions that you attempt to make to a datatype of a different size can truncate the converted value and display + after the value to denote that truncation has occurred. Conversions that you attempt to a datatype with a different number of decimal places can also result in truncation. Conversions that you specify as TEXT datatypes to CHAR and VARCHAR datatypes can be up to only 255 characters, the maximum length for CHAR and VARCHAR datatypes. The default of 30 characters is used if an explicit length is supplied.
The conversion of data stored as IMAGE datatypes to BINARY and VARBINARY datatypes can also be up to only 255 characters with a default of 30 characters. In the following example, a numeric constant is converted to a CHAR datatype, a decimal constant is converted to an INT datatype, and a decimal constant is converted to a BIT datatype:
select convert(char(4),1234),convert(int,12.345),convert(bit,87453.34)
---- ----------- ---
1234 12 1
(1 row(s) affected)
In the next example of using CONVERT, several table columns are converted from an INT datatype to a CHAR datatype. The attempted conversion of the same table column to a VARCHAR datatype of an inadequate length results in truncation of each column value.
select badge,convert(char(4),badge),convert(varchar(2),badge)
from employees
badge
----------- ---- --
3211 3211 *
6732 6732 *
4411 4411 *
...
You can use the style argument of the CONVERT function to display the date and time in different formats. You can also use the style argument as part of a CONVERT function when you convert dates and times to CHAR or VARCHAR datatypes. Table 8.3 shows the different style numbers that can be used with CONVERT.
| Without | With Century (yy) | Century (yyyy) | Standard Display |
|---|---|---|---|
| - | 0 or 100 | default | mon dd yyyy hh:miAM(orPM) |
| 1 | 101 | USA | mm/dd/yy |
| 2 | 102 | ANSI | yy.mm.dd |
| 3 | 103 | English/French | dd/mm/yy |
| 4 | 104 | German | dd.mm.yy |
| 5 | 105 | Italian | dd-mm-yy |
| 6 | 106 | dd mon yy | |
| 7 | 107 | mon dd, yy | |
| 8 | 108 | hh:mi:ss | |
| 9 | 109 | mon dd yyyy hh:mi:sssAM (orPM) | |
| 10 | 110 | USA | mm-dd-yy |
| 11 | 111 | Japan | yy/mm/dd |
| 12 | 112 | ISO | yymmdd |
| 13 | 113 | Europe | dd mon yyyy hh:mi:ss:mmm (24h) |
| 14 | 114 | - | hh:mi:ss::mmm (24h) |
In the following example, the current date and time are implicitly displayed using GETDATE, and GETDATE appears within CONVERT using different style numbers.
select getdate(),convert(char(12),getdate(),3),convert(char(24),
getdate(),109)
--------------------------- ------------ ------------------------
Jul 12 1994 1:34PM 12/07/94 Jul 12 1994 1:34:49:440
(1 row(s) affected)
select convert(char(24),getdate(),114),convert(char(24),getdate(),112)
------------------------ ------------------------
13:36:45:223 19940712
(1 row(s) affected)
DATENAME(<date part>, <date>)
DATEPART(<date_part>, <date>)
GETDATE()
DATEADD (<date part>, <number>, <date>)
DATEDIFF(<date part>, <date1>, <date2>)
Table 8.4 shows the values used as arguments for the date parts with the date functions.
Date Part Abbreviation Values Year yy 1753-9999 Quarter qq 1-4 Month mm 1-12 Day of Year dy 1-366 Day dd 1-31 Week wk 1-54 Weekday dw 1-7 (Sun-Sat) Hour hh 0-23 Minute mi 0-59 Second ss 0-59 Millisecond ms 0-999
The following examples show the use of several of the date functions. In the first example, the columns of a table that are defined as DATETIME and SMALLDATETIME datatypes are displayed without any functions.
select * from date_table
date1 date2
--------------------------- ---------------------------
Jan 1 1753 12:00AM Jan 1 1900 12:00AM
(1 row(s) affected)
In the following example, the keyword year is used with DATENAME to return the year with the century from a DATETIME value:
select datename(year,date1) from date_table
1753
(1 row(s) affected)
In the following example, hour is used with DATENAME to return the hour from a DATETIME datatype value:
select datename(hour,date1) from date_table
------------------------------
0
(1 row(s) affected)
In the following example, month is used with DATENAME to return the number of the month from a DATETIME datatype value:
select datepart(month,date1) from date_table
-----------
1
(1 row(s) affected)
In the following example, GETDATE function is used in a SELECT statement to display the current date and time:
select now=getdate()
now
---------------------------
May 19 1994 2:00PM
(1 row(s) affected)
In the following example, GETDATE is nested within DATEPART to display only the current day as part of a SELECT statement:
select datepart(day,getdate())
-----------
19
(1 row(s) affected)
In the following example, GETDATE is nested within DATENAME to display only the name of the current month as part of a SELECT statement:
select datename(month,getdate())
------------------------------
May
(1 row(s) affected)
In the following example, the current date and the date stored in a datetime column are first displayed for reference. DATEDIFF is then used to display the number of days between the two datetime values.
select getdate()
---------------------------
May 19 1994 2:12PM
(1 row(s) affected)
select date1 from date_table
date1
---------------------------
Jan 1 1753 12:00AM
(1 row(s) affected)
select new=datediff(day,date1,getdate())
from date_table
new
-----------
88161
(1 row(s) affected)
You can use a system function, like other functions, in the SELECT and WHERE clauses of a SELECT statement as well as in expressions. If you omit the optional parameter with some system functions (see Table 8.5), information about your computer system and the current, user, database, is returned.
| Function | Parameter(s) | Information Returned |
|---|---|---|
| HOST_NAME() | The name of the server computer | |
| HOST_ID() | The ID number of the server computer | |
| SUSER_ID | (['login-name']) | The login number of the user |
| SUSER_NAME | ([server_user_id]) | The login name of the user |
| USER_ID | (['user_name']) | The database ID number of the user |
| USER_NAME | ([user_id]) | The database username of the user |
| DB_NAME | (['database_id']) | The name of the database |
| DB_ID | (['database_name']) | The ID number of the database |
| GETANSINULL | (['database_name']) | Returns 1 for ANSI nullability, 0 if ANSI nullability not defined |
| OBJECT_ID | ('object_name') | The number of a database object |
| OBJECT_NAME | (object_id) | The name of a database object |
| INDEX_COL | ('table_name', index_id, | The name of the index key_id) column |
| COL_LENGTH | ('table_name', | The defined length of a 'column_name') column |
| COL_NAME | (table_id, column_id) | The name of the column |
| DATALENGTH | ('expression') | The actual length of an expression of a datatype |
| IDENT_INCR | ('table_or_view') | The increment (returned as numeric(@@MAXPRECISION,0)) for a column with the identity property |
| IDENT_SEED | ('table_or_view') | The seed value (returned as numeric(@@MAXPRECISION,0)) for a column with the identity property |
| STATS_DATE | (table_id, index_id) | The date that the statistics for the index (index_id) were last updated |
| COALESCE | (expression1, expression2,... expressionN) | Returns the first non-null expression |
| ISNULL | (expression, value) | Substitutes value for each NULL entry |
| NULLIF | (expression1, expression2) | Returns a NULL when expression1 is NULL when expression1 is equivalent to expression2 |
In the following example, system function the HOST_ID is used to return the name of the Windows NT server system to which a user is connected.
select host_name ()
------------------------------
NT1
(1 row(s) affected)
In the following example, multiple system functions are used to return information about the Windows NT server system, the current database and the current user.
select host_name (),host_id (),db_name (), db_id (), suser_name ()
---------- -------- ---------- --------------------------
NT1 0000005e employees 6 sa
(1 row(s) affected)
You may not have reason to use any of the system functions. You may only need to use the system functions if you're performing some administrative operation with the database. Several of the system functions require that you have access to the system tables in order to return useful information. Access to these will depend on the security of your login ID.
You would not usually use the system functions in the SELECT clause of a SELECT statement that displays the information on your monitor. Rather, system functions, like other functions, can be used within other functions, and the information returned is recorded in local variables or a temporary or permanent table. System functions provide information that is usually used for advanced programming or administrative operations. Administrative operations can be performed within stored procedures as well as in an interactive session.
For example, the system function STATS_DATE returns the date the last time that statistics were updated for an index on a table. The database administrator must periodically update the statistics for a table so that the query optimizer has valid information to use to decide whether or not to use an index for the retrieval of rows from a table. Microsoft SQL Sever does not automatically update the table statistics used by the query optimizer.
For example the following SELECT statement returns the statistics update date for two indexes on the table company.
select 'Index' = i.name,
'Statistics Update Date' = stats_date(i.id, i.indid)
from sysobjects o, sysindexes i
where o.name = 'company' and o.id = i.id
Index Statistics Update Date
------------------------------ ---------------------------
badge_index Sep 18 1995 3:24PM
department_index Sep 18 1995 3:27PM
(2 row(s) affected)
You can use a system function such as STATS_DATE, as shown in the previous example, to determine if it's time to update the statistics for the indexes of a table so the query optimizer will work properly.
You can also combine the system function STATS_DATE, with the functions GETDATE, and DATEDIFF functions to return the update statistics date, the current date, and the difference between the two dates. Using these three functions and a conditional statement in a procedure, you can run the procedure periodically to determine if the statistics for a table index have been updated within some period of time, for example a week.
If the difference between the STATS_DATE and the GETDATE is more than seven days, an UPDATE STATISTICS command should be issued (perhaps within the procedure as well). Other system functions can also be used to determine if a needed system operations on the database or their objects need to be performed.
For more information, see the section entitled Using Date Functions earlier in this chapter.
See Chapter 13, "Managing Stored Procedures and Using Flow-Control Statements" for more information
ISNULL(expression,value)
The expression is usually a column name that may contain a NULL value. The value specifies a string or number to be displayed when a NULL is found. In the following example, the ISNULL function is used to return the character string 'No entry' when a NULL is encountered.
select ISNULL(y, 'No entry') from nulltable
y
-----------
No entry
(1 row(s) affected)
The NULLIF function returns a NULL if the two expressions are identical, otherwise the second expression is returned. The NULLIF function is usually used with the CASE statement. In the following example, a NULL is returned for identical strings while the first parameter is returned when the strings don't match.
select nullif ('same','same'),space (2),nullif ('same','different')
---- -- ----
(null) same
(1 row(s) affected)
The space function is used in the example of the NULLIF function to provide a visual separation between the values returned by the use of the function twice in the SELECT statement.
You can also use COALESCE with more than two parameters. COALESCE returns the first non-null expression in the list of parameters when no NULL is used. If no non-null values are present, when COALESCE is used with more than two parameters, the function returns a NULL.
The COALESCE function is designed for use in a CASE statement which is discussed in the chapter on stored procedures. Please consult the chapter on stored procedures for additional information on the COALESCE function.
See the Chapter 13 section entitled Using CASE Expressions
USER
CURRENT_USER
SESSION_USER
SYSTEM_USER
CURRENT_TIMESTAMP
APP_NAME
The niladic functions USER, CURRENT_USER, and SESSION_USER all return the database username of the user executing an INSERT or UPDATE statement. The function SYSTEM_USER returns the user's login ID. CURRENT_TIMESTAMP returns the current date and time in the same form as the GETDATE function. APP_NAME returns the program name for the current session if one has been set.
Niladic functions can be used outside of the DEFAULT CONSTRAINT of a CREATE or ALTER TABLE statement. For example, you cannot use the niladic functions in the SELECT clause of a SELECT statement.
See the Chapter 11 section entitled "Managing and Using Rules and Defaults"
For information about selected aspects of the topics mentioned in this chapter, review the following chapters:
