Native Functions

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

  • Any expression

  • A CHECK constraint or trigger to check for specified values when inserting data

  • A DEFAULT constraint when inserting data

Note 

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.

Note 

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

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.

Table 8.2: INTERVAL OPTIONS FOR SOME DATE FUNCTIONS

Option

Description

yyyy, yy, year

Returns a valid 4-digit year value

y

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

dw

Denotes position of day within the week; a value between 1 and 7

ww, wk

Returns a value between 1 and 52, which represents the week of the year that the date falls within

hh, hour

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()

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(datecode,date)

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

DateAdd(datecode, interval, date)

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

DateDiff(datecode, date, datestring)

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(date)

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(date)

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(date)

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

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(value)

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(value)

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(value)

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(seed)

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(value,length,function)

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

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:

DB_ID() and DB_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

HOST_ID() and HOST_NAME()

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

OBJECT_ID('name') and OBJECT_NAME(value)

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

USER_ID() and USER_NAME()

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

@@Identify

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

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.

COL_LENGTH('table','column')

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')

COL_NAME('tableid','columnid')

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

Rowset functions return an object that can be used in a Transact-SQL statement in place of a table reference. They're all nondeterministic.

OpenDataSource(providername,initalizestring)

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.

Table 8.3: OPENDATASOURCE KEYWORDS

Keyword

OLE DB Property

Valid Values and Description

Data Source

DBPROP_INIT_DATASOURCE

Name of the data source (connection).
Providers interpret this in different ways. For a SQL Server OLE DB provider, this indicates the name of the server. For a Jet OLE DB provider, this indicates the full path of the MDB file or XLS file.

Location

DBPROP_INIT_LOCATION

Location of the database (connection).

Extended Properties

DBPROP_INIT_PROVIDERSTRING

The provider-specific connect-string.

Connect Timeout

DBPROP_INIT_TIMEOUT

Time-out value after which the connection attempt fails.

User ID

DBPROP_AUTH_USERID

User's identification value.

Password

DBPROP_AUTH_PASSWORD

Password to get past security.

Catalog

DBPROP_INIT_CATALOG

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

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(searchstring, begin, length)

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)

Soundex() and Difference()

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')

Stuff(string, begin, length, substring)

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')
Note 

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.



Mastering Dreamweaver MX Databases
Mastering Dreamweaver MX Databases
ISBN: 078214148X
EAN: 2147483647
Year: 2002
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net