El Diagnóstico de Comunicación Interna

No system would function without some kind of standard set of functions that can be used to simplify queries. The aggregates and functions in Microsoft SQL Server are no different than their counterparts in other, more refined programming languages.

We’ll spend a moment looking at some of the more useful aggregates and functions. You should also look each one up in Microsoft SQL Server Books Online to reinforce what we discuss here.

NOTE: Try keeping a weekly task list that includes a reminder to look up a new function or

feature in Microsoft SQL Server Books Online. This will help you stay on top of the many functions out there and how they can make programming easier.

These functions support nesting and are data type-sensitive where needed. They can return values to a query or to a local variable for manipulation in other parts of a script or stored procedure. For the syntax of each function, check Microsoft SQL Server Books Online. SUM()

The SUM() function returns the total of a column’s values. In the examples, I used the SUM() function to add the quantity column for each title sold. Depending how you use this function, it will work on an entire table or on a group of records.

Keep in mind that the columns must be numeric for this function to work properly. You can use the CONVERT() function to change numbers stored as text to the proper data type nested inside

this function as well. MAX()

This function returns the maximum value for a column in a table or set of rows returned in a query. In the examples, I used this function to return the greatest date value in the Sales table for each title listed. This was accomplished through the grouping of the rows by title and applying the aggregate function for each row. This function works with text as well as with dates and numbers.

Previous Table of Contents Next

Products | Contact Us | About Us | Privacy | Ad Info | Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.

All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles. Microsoft SQL Server Black Book

(Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

MIN()

MIN() is the opposite of MAX() in every regard. The same rules and features apply to this function as to the MAX() function. Remember that the lowest value returned for text will depend on sort order and case sensitivity.

AVG()

Many reports depend on averages to keep track of trends over time. The AVG() function calculates the average value of a column in a table or in a set of rows, depending on how it is used. Only numeric values can be passed to the AVG() function. You can use the CONVERT() function nested inside of the AVG() function to return the numeric value of a number stored as text to get around the numeric restriction of this function.

COUNT()

This very common function returns the count of rows in the query that match a particular WHERE clause. The number of rows in a table can be returned to a variable for testing purposes very effectively. COUNT() returns the number of non-NULL values indicated by the WHERE clause in any query. You can pass the asterisk to the COUNT(*) function to get a row count from a query.

COUNT() is useful in triggers and stored procedures for determining not only the existence of rows but the exact number of rows you are dealing with. The return value for this function is numeric.

CONVERT() http://www.itknowledge.com/reference/standard/1576101495/ch05/158-160.html (1 of 4) [1/27/2000 6:16:11 PM] Go! Keyword --- Go!

I use this function frequently in SQL code to ensure that I have the correct data types to perform an operation. This versatile function converts one data type to another so that a number of operations can be performed. You just specify the data type to which you wish the existing column or expression to be converted. Microsoft SQL Server will attempt to convert like data types to the proper type automatically when it can. If Microsoft SQL Server cannot process a function or query with the data passed to it, the server returns an error message that suggests the use of the CONVERT() function to solve the problem. See Calculated Values later in this chapter for an example of the CONVERT () function.

GETDATE()

The GETDATE() function returns the current date and time from the server’s system clock. This function can be used to automatically insert date values into columns or to find out the current date for comparisons in WHERE clauses. Audit trails and tracking tables benefit greatly from this function and the CURRENT_USER function.

DATEDIFF()

You can use this function to compare and return the difference between date items such as days, weeks, minutes, and hours. When this function is used in a WHERE clause, you can return records that meet a range of dates, or that meet certain time-span intervals. Make sure you specify the arguments to this

function in the correct order, or the value returned might be the opposite of what you expected.

DATEPART()

This function returns a value equal to the part of a date that you specify. If, for instance, you need to know the day of the week of a particular date, you can use this function to quickly pull that data out of a column or variable.

SOUNDEX()

This function converts a string to a four-digit code that represents the string. If two strings are very close to the same spelling, they will have the same

SOUNDEX() value returned. This function can be used to find a list of potential matches for a string in a set of rows.

I think of this as the “sounds like” function. If the strings sound alike, the number returned by this function will be the same. This can be useful for finding data when the user does not know the exact spelling. SOUNDEX() is critical to creating a full-featured Find for database records.

SUBSTRING()

The SUBSTRING() function is used many times throughout this book. Any string manipulation can be accomplished with this function in conjunction with a few string operators and some other basic string functions.

Another useful string function you should take a moment to look up in Microsoft SQL Server Books Online is the STUFF() function. STUFF() and SUBSTRING() in combination are useful for building strings on the fly in your SQL code. See Calculated Values later in this chapter for more on SUBSTRING() and its uses.

UPPER()

UPPER() converts the string passed to the function into all uppercase characters. You can use this function to maintain the data integrity of text columns in your tables without the user or client intervening. Some client software always assumes this function to be bound to a control with very little overhead, so I seldom use this for much more than making sure I pass

uppercase characters where needed.

CHARINDEX()

This function can be used to search for a match for a string in a column. If you want the character offset of a string within a string, this function returns the corresponding numbered offset of the start of the match. If you use this function to search an entire table, it will return a result set with a non-zero value for each row that contains a string.

CHARINDEX() does not allow wildcard characters. If you need to search for a string containing wildcards, use the PATINDEX() function instead.

RTRIM()

RTRIM() removes any trailing blanks from a string or column. In some situations, this helps keep formatting and reporting working the way your applications expect. RTRIM() is most useful in text reports generated from raw SQL.