An RDBMS provides native (built-in) functionality in the form of functions that perform a predefined calculation or task. Generally, you provide a reference to values or information that the function processes within the confines of its defined job. You can use SQL Server functions in the following:
A SELECT statement's field list to return a value
A SQL WHERE clause search condition (SELECT, INSERT, DELETE, or UPDATE) to limit the returned rows
A CHECK constraint or trigger to check for specified values when inserting data
A DEFAULT constraint when inserting data
A SQL Server function is either deterministic or nondeterministic. Deterministic functions always return the same result. DateAdd is a deterministic value because it returns the same result for the fixed set of input values. Nondeterministic functions return different results each time they are called, depending on the input values. For instance, GetDate returns the current date, which will always be a different value every time it's called (because the result also includes a time value). Only deterministic functions can be invoked in views and computed columns.
There are several types of SQL Server functions. In this next section, we review Date and Time, Mathematical, System, and String functions. This list isn't comprehensive by any means, but it does include the functions you'll probably use most often.
Aggregate functions perform operations on groups of records. You can't limit the set based on criteria. The function processes the calculation on all the values in a single column. These functions are actually Transact-SQL functions and as such aren't truly native to SQL Server. You'll find more information on these functions in the "Using Transact-SQL in SQL Server" section later in this chapter.
Date and Time functions use date arithmetic or serial values in their calculations. SQL Server supports dates from January 1, 1753, through December 31, 9999. Oddly enough, December 30, 1899, is considered the anchor date (instead of January 1, 1900, as you might expect). An anchor date has a serial value of 0. Several Date functions accept an argument that denotes a specific interval. Table 8.2 lists these constants.
yyyy, yy, year
Returns a valid 4-digit year value
Returns a value between 1 and 365, representing the date's position within the year—between January 1 and December 31
mm, m, month
Returns the month as a value between 1 and 12
qq, q, quarter
Returns a value 1 through 4, denoting the quarter of the year
dd, d, day
Denotes day as a value between 1 and 31
Denotes position of day within the week; a value between 1 and 7
Returns a value between 1 and 52, which represents the week of the year that the date falls within
Denotes hours as a value between 0 and 23
mi, n, minute
Denotes minutes as a value between 0 and 59
ss, s, second
Denotes seconds as a value between 0 and 59
GetDate() returns the current date and time value. The following statement returns the current date in a column named CurrentDate.
SELECT GetDate() As CurrentDate FROM table
DatePart() returns a number of date components, and datecode is one of many options that define the time or date component. For instance, if the date field or variable contains August 10, 2002, the following returns the value 10.
SELECT DatePart(dd, date) As DayValue FROM table
The DateAdd() function adds an interval to a specific date. For instance, if the date column contained the dates August 10, 2002, and September 10, 2002, the following statement returns September 10, 2002, and October 10, 2002—adding exactly one month to each date.
SELECT DateAdd(mm, 1, date) FROM table
The DateDiff() function returns the difference between date and datestring. That difference can be presented in any date component, as specified by datecode. For instance, if the date column contains the dates August 10, 2002, and September 10, 2002, and datestring is November 4, 2002, the following statement returns the values 86 and 55.
SELECT DateDiff(d, date, '11/4/2002') FROM table
Day() returns a value between 1 and 31 that represents the day of the month of the specified date. The following statement returns 10 if the date in question is 8/10/2002.
SELECT Day(date) As DayValue FROM table
Month() returns a value between 1 and 12, a value represents a month between January and December. The following statement returns 8 if the date in question is 8/10/2002.
SELECT Month(date) As DayValue FROM table
Year() extracts the year value as a four-digit value from the specified date. The following statement returns 2002 if the date in question is 8/10/2002.
SELECT Year(date) As DayValue FROM table
Mathematical functions perform mathematical operations on numeric data and return the results. The user supplies the operands in the form of arguments, and the function processes those values within the context of its purpose.
Abs() returns the absolute, or positive, value of a value or expression. The following statement returns the values 1, 2, and 3 if value contains the values –1, –2, and –3.
SELECT Abs(value) As AbsValue FROM table
Ceiling() returns the smallest integer greater than or equal to the given numeric expression. The following statement returns the value 2 if value is 1.21 or 1.63 and returns –1 if value is –1.21 or –1.63:
SELECT Ceiling(value) As GreaterThan FROM table
Floor() returns the largest integer less than or equal to the given numeric expression. The following statement returns 1 when value is 1.21 and 1.63 and returns –2 when value is –1.21 and –1.63.
SELECT Floor(value) As LessThan FROM table
Rand() returns a random float value from 0 to 1. The following statement returns a series of random values, depending on the seed value.
SELECT Rand(seed) As RandomValue FROM table
A query returns the same results if the seed value doesn't change.
Round() returns a value, rounded to the specified length, but it's flexible. You can use Round() to round or truncate a value. You can apply the following rules to round values:
If length is negative and larger than or equal to the number of digits to the left of the decimal point, Round() returns 0. The following statements return 0.
SELECT Round(1.23, -1) AS RoundValue FROM table SELECT Round(1.23, -2 AS RoundValue FROM table SELECT Round(12.23, -3) AS RoundValue FROM table
Round() returns a rounded value when length is a negative number. The following statements return the values 10.00 and 100.00, respectively.
SELECT Round(12.23, -1) AS RoundValue FROM table SELECT Round(123.45, -2) AS RoundValue FROM table
Use the function argument (the default is 0) to truncate a value. When function is any value other than 0, the value is truncated. Generally, the length value needs to be 0 when truncating. The following statements return the values 12, 12.20, and 12.23, respectively.
SELECT Round(12.23, 0, 1) AS TruncatedValue FROM table SELECT Round(12.23, 1, 1) AS TruncatedValue FROM table SELECT Round(12.23, 2, 1) AS TruncatedValue FROM table
Expect normal rounding behaviors when length is a positive value and function is 0 (or omitted). The following statements return the values 120, 100, and 0, respectively.
SELECT Round(123, -1) AS RoundedValue FROM table SELECT Round(123, -2) AS RoundedValue FROM table SELECT Round(123, -3) AS RoundedValue FROM table
System functions operate or report on various system level options and objects by indirectly accessing information from system tables. Some functions are preceded with two at signs (@@). The following System functions return a corresponding identification value or name:
The DB_ID and DB_NAME functions return a database identification value or name. The following statement returns the value 6 when Northwind is the current (active) database.
SELECT DB_ID() As DBID
The following statement returns Northwind when that database is current.
SELECT DB_NAME() As DBName
These two functions return the host identification value or name, which will be unique to your system. Try the following to return the host identification value.
SELECT HOST_ID() As HostID
The following returns the host name, which should be the same as the current instance of SQL Server.
SELECT HOST_NAME() AS HostName
These two functions return an object's identification value or name. To return a known object's identification value, use the following syntax:
SELECT OBJECT_ID('sysfilegroups') AS ObjectID
When you know the identification value and you want to know the object's name, use the following syntax:
SELECT OBJECT_NAME(24) AS ObjectName
Use these functions to return the user identification value and name. The following statement returns the user identification value.
SELECT USER_ID() AS UserID
This statement returns the user name.
SELECT USER_NAME() AS UserName
Use @@Identify after inserting data to return the last-inserted identify value. For instance, the following statement inserts new values into table and then returns the latest identify value from that operation.
INSERT INTO Employees (lastname, firstname, ssnumber) VALUES ('Smith', 'Janice', '555-55-5555') SELECT @@IDENTITY As 'Identity'
Meta Data functions are a type of System function; they return information about the database and database objects. There are several Meta Data functions, and all are nondeterministic. We'll review just a few of them in this section.
This function returns the length of a specific column. The following statement returns the value 4—the length of the OrderID column in the Northwind Orders table.
USE Northwind SELECT COL_LENGTH('Orders',' OrderID')
This function returns a column's name. The following statement returns the column name EmployeeID.
USE Northwind SELECT COL_NAME(OBJECT_ID ('Employees'),1)
Rowset functions return an object that can be used in a Transact-SQL statement in place of a table reference. They're all nondeterministic.
The OpenDataSource() function provides connection information without using a linked server name. The providername argument is the registered name of the PRODIG of the OLE DB provider. Use any of the keywords in Table 8.3 as the initializestring argument.
OLE DB Property
Valid Values and Description
Name of the data source (connection).
Location of the database (connection).
The provider-specific connect-string.
Time-out value after which the connection attempt fails.
User's identification value.
Password to get past security.
The name of the initial or default catalog when connecting to the data source.
Use this function in the following form:
SELECT *FROM OPENDATASOURCE('SQLOLEDB','Data Source=servername; UserID=userid;Password=password'). database
String functions perform various operations on character and binary string values. Most can be used only with Char, NChar, VarChar, and NVarChar datatypes. When passing a literal string to the function, you must usually enclose the string in single-quotation delimiters in the form 'string'.
Substring() retrieves a portion of a string. For example, the following statement returns the string 'bcde'—it begins to extract characters at the second letter in the specified string and extracts four characters.
SELECT Substring('abcdef', 2, 4)
These two functions search for similar sounds in a character string. Soundex() converts a character string to a 4-digit code. The function ignores vowels and terminates when encountering a non- alphabetic character. Both strings in the following statement return the value S25.
SELECT Soundex('Susan'), Soundex('Suzanne')
The two strings, 'boy' and 'toy', return the values B000 and T000. (Those are zeros, not upper- case O's.)
SELECT Soundex('boy'), Soundex('toy')
The Difference() function compares the results of running the Soundex() function on the specified strings and then returns a value from 0 through 4, 4 being the best match. The two sets of strings from the Soundex() example return the values 4 and 3, respectively—implying that Susan and Suzanne are more similar than boy and toy:
SELECT Difference('Susan', 'Suzanne') SELECT Difference('boy', 'toy')
The Stuff() function replaces characters in one string with another, beginning at a specified position within the target string and replacing existing characters with a specified number of characters from the substring. The following statement replaces the third character with the letter o to return the word block.
SELECT Stuff('black', 3, 1, 'o')
This section is by no means comprehensive. You can find more information on SQL Server functions at http:// msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_9rar.asp or in Books Online, which comes with SQL Server 2000.