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.

Table 26.3. String Functions
Function Name Returns
ASCII( char ) The ASCII code for the leftmost character in char .
CHAR( int ) The ASCII character represented by int (an ASCII code).
CHARINDEX( char_pattern , char , [ int_start ]) Starting location of char_pattern within char , optionally starting search at int_start .
DIFFERENCE( char1 , char2 ) The difference between the two character expressions. Used for a phonetic match.
LEFT( char , int ) int characters from left of char .
LEN( char ) Number of characters in char , excluding trailing blanks.
LOWER( char ) char in lowercase.
LTRIM( char ) char without leading spaces.
NCHAR( int ) The character for a given Unicode value.
PATINDEX( char_pattern , char ) Starting position of char_pattern in char , or if the pattern is not found.
REPLACE( char1 , char2 , char3 ) String with all occurrences of char2 replaced with char3 in char1 .
QUOTENAME( char , [ char_quote ]) char as a valid quoted identifier. Adds the characters [ and ] at the beginning and end of char (this default can be changed to ' or " , specified as char_quote ). Returns a Unicode string.
REPLICATE( char , int ) Repeat of char expression int number of times.
REVERSE( char ) Reverse of char .
RIGHT( char , int ) int characters from right of char .
RTRIM( char ) char without trailing spaces.
SOUNDEX( char ) A four-character string used for comparison of a phonetic match.
SPACE( int ) A string of int spaces.
STR( float , [ length , [ decimal ]]) float as a character string, with length of length and decimal numbers of decimals. Default length is 10 and default number of decimals is 0.
STUFF( char1 , start , length , char2 ) A string with length number of characters from char1 replaced with char2 , starting at start .
SUBSTRING( char , start , length ) A string of length number of characters from char , from start position.
UNICODE( char ) The Unicode code for the leftmost character in char .
UPPER( char ) char in uppercase.

You can use the operator + to concatenate strings.

The following example uses SUBSTRING and string concatenation to present each author's first letter of the first name and then the last name:

 SELECT SUBSTRING(au_fname,1,1) + '. ' + au_lname FROM authors 

Mathematical Functions

The mathematical functions in Table 26.4 perform calculations based on the input values and return a numeric value. No new mathematical functions were introduced in version 7.0.

Table 26.4. Mathematical Functions
Function Name Returns
ABS(numeric) The absolute (positive) value of numeric.
ACOS( float ) The arc cosine for float .
ASIN(float) The arc sine for float .
ATAN( float ) The arc tangent for float .
ATAN2( float1 , float2 ) The arc tangent whose tangent is between float1 and float2 .
CEILING( numeric ) The smallest integer value that is higher than or equal to numeric .
COS( float ) The trigonometric cosine of float .
COT( float ) The trigonometric cotangent of float .
DEGREES( numeric ) The number of degrees for a given angle, numeric , given in radians.
EXP( float ) The exponential value of float .
FLOOR( numeric ) The largest integer value that is lower than or equal to numeric .
LOG( float ) The natural logarithm of float .
LOG10(float) The base-10 logarithm of float .
PI() The constant pi.
POWER( numeric1 , numeric2 ) The value of numeric1 to the specified power, given in numeric2 .
RADIANS( numeric ) Radians of numeric , given in degrees.
RAND([ seed ]) A random value between 0 and 1. seed can be specified as the starting value.
ROUND( numeric , length , func ) Number rounded to the specified numeric to specified length . If func is specified and not 0, numeric is rounded down to length .
SIGN( numeric ) 1 if numeric is positive, 0 if numeric is 0, and “1 if numeric is negative.
SIN( float ) The trigonometric sine of float .
SQUARE( float ) The square of float .
SQRT( float ) The square root of float .
TAN( float ) The trigonometric tangent of float .

The operators + , - , * , / , and % (modulo) are also available for numeric expressions.

Date Functions

The date functions perform operations such as formatting and subtracting. The expression given is a datetime datatype.

Some of the functions take a datepart as argument. The datepart specifies on what part of the datetime datatype you want to operate . Table 26.5 provides the codes for the datepart.

Table 26.5. Available Codes for Datepart
Datepart Abbreviation Possible 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 “53
weekday dw 1 “7
hour hh 0 “23
minute mi 0 “59
second ss 0 “59
millisecond ms 0 “999

The date- and time- related functions are listed in Table 26.6.

Table 26.6. Date-and Time-Related Functions
Function Name Returns New in SQL Server 2000
DATEADD( datepart , int , date ) Date expression as a result of adding int datepart s to date .  
DATEDIFF( datepart , date1 , date2 ) The number of datepart s between date1 and date2 .  
DATENAME( datepart , date ) The datepart of date returned as a character string. For month and day of week, the actual name is returned.  
DATEPART( datepart , date ) The datepart of date returned as an integer.  
DAY( date ) The day-of-month part as an integer.  
GETDATE() The current date and time.  
GETUTCDATE() The current date and time as Universal Time Coordinate Time (Greenwich Mean Time). Yes
MONTH( date ) The month as an integer.  
YEAR( date ) The year as an integer.  

You can use the operators + and - directly on datetime expressions in version 7.0 and later. The implied datepart is days. In this example, you use the + operator to add one day to the current date:

 SELECT GETDATE(), GETDATE() + 1  go ----------------------- ---------------------- 1998-03-28 16:08:33     1998-03-29 16:08:33 

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( expression AS datatype ) 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 ]) AS column_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:

 SELECT title_id, ISNULL(CONVERT(CHAR(10),price), 'Not Priced')  FROM titles 

Security Functions

The security functions, listed in Table 26.9, are useful for retrieving information about users and roles.

Table 26.9. Security Functions
Function Name Returns New in SQL Server 2000
HAS_DBACCESS( database ) Information about whether current user has access to database. Yes
IS_MEMBER( group role ] 1 if the user is a member of specified NT group or SQL Server role ; otherwise, 0.  
IS_SRVROLEMEMBER ( role [, login ]) 1 if the user's login ID is a member of the specified server role ; otherwise, 0. An explicit login name can be specified.  
SUSER_ID( login_name ) The loginid of the specified login_name . Included for backward compatibility; use SUSER_SID instead.  
SUSER_NAME([ login_id ]) The login name of login_id . Included for backward compatibility; use SUSER_SNAME instead.  
SUSER_SID([ login ]) Security identification number (SID) for login .  
SUSER_SNAME([ login_id ]) The login name of login_id .  
USER_ID([ username ]) The user ID for username .  
USER_NAME([ user_id ]) The username for current user or user_id .  

The following example returns the current user's database username, login name, and whether the user is a member of the db_owner role:

 select substring (user_name(), 1, 20) AS 'user_name',         substring (suser_sname(),1, 30) AS 'login_name',        case IS_MEMBER('db_owner')           when 1 then 'Yes'           else 'No'           end AS 'Is_dbOwner' go user_name            login_name                     Is_dbOwner -------------------- ------------------------------ ---------- dbo                  RRANKINSA20P\rrankins          Yes 

Text and Image Functions

These scalar functions, listed in Table 26.10, perform an operation on a text or image input value or column and return information about the value.

Table 26.10. Text and Image Functions
Function Name Returns
TEXTPTR ( column_name ) The text-pointer for a text or image column as a varbinary(16) value. Pointer can be used in READTEXT , WRITETEXT , and UPDATETEXT statements.
TEXTVALID( ' table . column ', ptr ) 1 if text or image pointer ptr is valid for table.column , 0 if pointer is invalid.

The following example shows how to use the TEXTPTR function to retrieve a text pointer and use it in the READTEXT command:

 USE pubs  GO DECLARE @ptr varbinary(16) SELECT @ptr = TEXTPTR(pr_info)    FROM pub_info    WHERE pub_id = '0877' READTEXT pub_info.pr_info @ptr 0 65 GO pr_info ----------------------------------------------------------------- This is sample text data for Binnet & Hardley, publisher 0877 in 

Rowset Functions

The rowset functions listed in Table 26.11 return an object that can be used in place of a table reference in a Transact-SQL statement.

Table 26.11. Rowset Functions
Function Name Returns New in SQL Server 2000
CONTAINSTABLE ( table , column * } , ' contains_conditions ' [, top_n_by_rank ]) 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.  
FREETEXTTABLE ( table , { column * } , ' freetext_ string ' [ , top_n_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. Yes
OPENQUERY ( linked_server , 'query' ) Resultset from specified pass-through query on linked_server .  
OPENROWSET ( provider_name , {' datasource ';' user_id '; ' password '' provider_string '} , {[ catalog .][ schema. ] object ' query '} ) Remote data result from specified connection to OLE DB data source.  
OPENXML( docid , rowpattern , flag ) [WITH ( schemaDeclaration tablename )] 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:

 select p.pub_id, t.title     from publishers p    join OPENROWSET('SQLOLEDB','RRANKINSA20P';'sa';'',                      'SELECT * FROM pubs.dbo.titles ORDER BY pub_id') AS t      on p.pub_id = t.pub_id go pub_id title ------ ------------------------------------------------------------------------ 0736   You Can Combat Computer Stress! 0736   Is Anger the Enemy? 0736   Life Without Fear 0736   Prolonged Data Deprivation: Four Case Studies 0736   Emotional Security: A New Algorithm 0877   Silicon Valley Gastronomic Treats 0877   The Gourmet Microwave 0877   The Psychology of Computer Cooking 0877   Computer Phobic AND Non-Phobic Individuals: Behavior Variations 0877   Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 0877   Fifty Years in Buckingham Palace Kitchens 0877   Sushi, Anyone? 1389   The Busy Executive's Database Guide 1389   Cooking with Computers: Surreptitious Balance Sheets 1389   Straight Talk About Computers 1389   But Is It User Friendly? 1389   Secrets of Silicon Valley 1389   Net Etiquette 

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."

Niladic Functions

The niladic group of functions, listed in Table 26.12, is basically a set of system functions. The reason for grouping them separately is that they are used without parentheses after the function name. They are defined in the ANSI SQL-92 standard.

You often find niladic functions used as defaults in CREATE TABLE and ALTER TABLE .

NOTE

Niladic functions are basically aliases to SQL Server system functions. If you use them for default values in tables and run sp_help for the table, edit the table, or script the table in Enterprise Manager, you will notice that they are translated to the corresponding system function.

Table 26.12. Niladic Functions
Function Name Returns Corresponding System Function
CURRENT_TIMESTAMP Current date and time GETDATE()
CURRENT_USER The user's username USER_NAME()
SESSION_USER The user's username USER_NAME()
SYSTEM_USER The user's login name SUSER_NAME()
USER The user's username USER_NAME()

In Listing 26.10, you create a table with three columns with defaults for the current datetime, the user's login name, and the username. The INSERT statement inserts default values for all columns, and the SELECT statements retrieve the row inserted.

Listing 26.10 Using Niladic Functions with the INSERT Statement
 CREATE TABLE my_defaults    (the_datetime DATETIME DEFAULT CURRENT_TIMESTAMP,     users_login CHAR(128) DEFAULT SYSTEM_USER,     users_name CHAR(128) DEFAULT CURRENT_USER,) INSERT my_defaults DEFAULT VALUES SELECT * FROM my_defaults go the_datetime                users_login          users_name --------------------------- -------------------- ------------------ 1998-03-29 19:09:52.377     sa                   dbo 

Aggregate Functions

The aggregate functions differ from those in the other groups. Aggregate functions perform an aggregation for a column over a set of rows.

Table 26.13 lists the aggregate functions available in SQL Server.

Table 26.13. Aggregate Functions
Function Name Returns New in SQL Server 2000
AVG([ALL DISTINCT] expression ) The average of all values given in expression .  
BINARY_CHECKSUM (* expression [ , ... n ] The binary checksum computed over a row or the list of expressions. Can be used to detect changes to a row. Yes
CHECKSUM(* expression [ , ... n ] The checksum value computed over a row or the list of expressions. Intended for use in building hash indices. Yes
CHECKSUM_AGG [ALL DISTINCT] expression ) The checksum of the values in a group expression, ignoring nulls. Yes
COUNT([ALL DISTINCT] expression *) The number of non- NULL values in expression . NULL s are counted if * is specified.  
COUNT_BIG([ALL DISTINCT] expression *) The number of non- NULL values in expression as a bigint. NULL s are counted if * is specified. Yes
GROUPING ( column_name ) 1 when a row is added to GROUP BY resultset by CUBE or ROLLUP operator. 0 when not.  
MAX([ALL DISTINCT] expression ) The maximum value in expression .  
VARP( expression ) The statistical variance for the population for all values in the given expression .  
STDEVP( expression ) The statistical standard deviation for the population for all values in the given expression .  
MIN([ALL DISTINCT] expression ) The minimum value in expression .  
SUM([ALL DISTINCT] expression ) The sum of all values in expression .  
VAR( expression ) The statistical variance of all values in the given expression .  
STDEV( expression ) The statistical standard deviation of all values in the given expression .  

If you add the keyword DISTINCT , only distinct values will be aggregated. The default is ALL . You should note that NULL values are not included in the aggregates, except for COUNT(*) , which counts the number of rows returned from the relational expression.

Perhaps you want to count the number of rows, prices, and distinct prices in the title table:

 SELECT COUNT(*) AS Total, COUNT(price) AS Prices,         COUNT(DISTINCT price) AS "Distinct prices"    FROM titles go Total       Prices      Distinct prices ----------- ----------- --------------- 18          16          11 

Apparently, two books are not priced yet, or the price is not known ( NULL ), and there are a total of five duplicate prices.

Now, you want to perform some real aggregation over the prices:

 SELECT MAX(price) AS 'Max', MIN(price) AS 'Min', AVG(price) AS 'Average'     FROM titles go Max                   Min                   Average --------------------- --------------------- --------------------- 22.9500               2.9900                14.7662 Warning: Null value is eliminated by an aggregate or other SET operation. 

Note that even though NULL usually sorts low, the minimum price is 2.99 because NULL is excluded from the aggregate. (The warning message displayed indicates that NULL values were encountered and excluded from the aggregate result.) The average price also does not include the nulls and is calculated only on the 16 rows that contain NULL . To include the rows containing NULL values, use the ISNULL() function to substitute a value for NULL and notice the difference in the average price:

 SELECT MAX(price) AS 'Max', MIN(price) AS 'Min', AVG(isnull(price, 
 SELECT MAX(price) AS 'Max', MIN(price) AS 'Min', AVG(isnull(price, $0)) AS 'Average' FROM titles go Max Min Average --------------------- --------------------- --------------------- 22.9500 2.9900 13.1255 Warning: Null value is eliminated by an aggregate or other SET operation. 
)) AS 'Average' FROM titles go Max Min Average --------------------- --------------------- --------------------- 22.9500 2.9900 13.1255 Warning: Null value is eliminated by an aggregate or other SET operation.

Aggregate functions are often used in conjunction with GROUP BY . The following example retrieves the average price for each book category:

 SELECT type, AVG(price) AS Average     FROM titles    GROUP BY type go type         Average ------------ ---------- business     13.7300 mod_cook     11.4900 popular_comp 21.4750 psychology   13.5040 trad_cook    15.9633 UNDECIDED    NULL 

The following example shows the use of CHECKSUM_AGG to detect changes in the publishers table:

 select checksum_agg(binary_checksum(*)) as old_checksum     from publishers update publishers set state = 'MA'    where state = 'CA' select checksum_agg(binary_checksum(*)) as new_checksum    from publishers go old_checksum ------------  -1096815739 new_checksum ------------  -1096873083 
Using COMPUTE and COMPUTE BY

The COMPUTE and COMPUTE BY keywords enable you to return both detail and summary information in a single SELECT statement.

The compute clause reports overall aggregate values for a resultset, as shown in the following example:

 /*list titles and prices, show overall max price*/  select title, price    from titles    compute max(price) go title                                                            price ---------------------------------------------------------------- ------------- The Busy Executive's Database Guide                              19.9900 Cooking with Computers: Surreptitious Balance Sheets             11.9500 You Can Combat Computer Stress!                                  2.9900 Straight Talk About Computers                                    19.9900 Silicon Valley Gastronomic Treats                                19.9900 The Gourmet Microwave                                            2.9900 The Psychology of Computer Cooking                               NULL But Is It User Friendly?                                         22.9500 Secrets of Silicon Valley                                        20.0000 Net Etiquette                                                    NULL Computer Phobic AND Non-Phobic Individuals: Behavior Variations  21.5900 Is Anger the Enemy?                                              10.9500 Life Without Fear                                                7.0000 Prolonged Data Deprivation: Four Case Studies                    19.9900 Emotional Security: A New Algorithm                              7.9900 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean  20.9500 Fifty Years in Buckingham Palace Kitchens                        11.9500 Sushi, Anyone?                                                   14.9900                                                                  max                                                                  =============                                                                  22.9500 

NOTE

The way the computed aggregate is displayed is dependent on the query tool you are using. Query Analyzer will line up the computed aggregate under the column that is being aggregated, with a header indicating the type of aggregate. Not all query tools format the result from the COMPUTE clause as nicely .

The COMPUTE BY clause can be used in conjunction with an ORDER BY displaying subtotals within a resultset. You can also include the COMPUTE clause to display a grand total as well, as shown in the following example:

 /* display type, title and price,  ** and show the maximum price for each type */ select type, title, price    from titles    order by type    compute max(price) by type    compute max(price) go type         title                                                  price ------------ ------------------------------------------------------ ---------- business     The Busy Executive's Database Guide                    19.9900 business     Cooking with Computers: Surreptitious Balance Sheets   11.9500 business     You Can Combat Computer Stress!                        2.9900 business     Straight Talk About Computers                          19.9900                                                                     max                                                                     ==========                                                                     19.9900 type         title                                                  price ------------ ------------------------------------------------------ ---------- mod_cook     Silicon Valley Gastronomic Treats                      19.9900 mod_cook     The Gourmet Microwave                                  2.9900                                                                     max                                                                     ==========                                                                     19.9900 type         title                                                  price ------------ ------------------------------------------------------ ---------- popular_comp But Is It User Friendly?                               22.9500 popular_comp Secrets of Silicon Valley                              20.0000 popular_comp Net Etiquette                                          NULL                                                                     max                                                                     ==========                                                                     22.9500 type         title                                                  price ------------ ------------------------------------------------------ ---------- psychology   Computer Phobic AND Non-Phobic Individuals             21.5900 psychology   Is Anger the Enemy?                                    10.9500 psychology   Life Without Fear                                      7.0000 psychology   Prolonged Data Deprivation: Four Case Studies          19.9900 psychology   Emotional Security: A New Algorithm                    7.9900                                                                     max                                                                     ==========                                                                     21.5900 type         title                                                  price ------------ ------------------------------------------------------ ---------- trad_cook    Onions, Leeks, and Garlic                              20.9500 trad_cook    Fifty Years in Buckingham Palace Kitchens              11.9500 trad_cook    Sushi, Anyone?                                         14.9900                                                                     max                                                                     ==========                                                                     20.9500 type         title                                                  price ------------ ------------------------------------------------------ ----------- UNDECIDED    The Psychology of Computer Cooking                     NULL                                                                     max                                                                     ==========                                                                     max                                                                     ==========                                                                     22.9500 

Important point: compute by columns must match, in order, the columns specified in the order by clause. You can specify only a subset of the order by columns. Consider a query containing this order by clause:

 order by a, b, c 

The only allowed compute by clauses are the following:

  • compute by a, b, c

  • compute by a, b

  • compute by a

Expressions in a compute or compute by clause must match exactly the corresponding expression in the select list:

 select type, price, price*2 as 'double price'     from titles    where type = 'business'    compute sum(price), sum(price*2) go type         price                 double price ------------ --------------------- --------------------- business     19.9900               39.9800 business     11.9500               23.9000 business     2.9900                5.9800 business     19.9900               39.9800              sum              =====================              54.9200                                    sum                                    =====================                                    109.8400 

NOTE

Many organizations do not use compute for a variety of reasons. First, because it is not ANSI-standard SQL, organizations avoid writing important queries that might need to be modified dramatically to be ported to other database environments. Second, any decent reporting tool will provide all of the totaling and subtotaling directly within the report definition, with much more flexibility about the actual handling of the computed values. Third, not all database APIs or middle- tier database solutions can handle compute results properly.

If you plan to use compute , make sure you have spent the necessary time testing how your application reads and presents computed values.



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

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