Data Summary

• Summarize data by using Transact -SQL.

Summarizing information to provide results that collect data in a meaningful manner applies functions against the data that can perform calculations, make decisions, and provide answers by performing data analysis on behalf of the user .

All functions are either deterministic or nondeterministic. Whether a function is deterministic or nondeterministic is called the determinism of the function.

• Deterministic functions always return the same result any time they are called with a specific set of input values.

• Nondeterministic functions may return different results each time they are called with a specific set of input values.

For example, the DATEADD built-in function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed.

Be careful with the use of appropriate functions because nondeterministic functions cannot be used in a lot of processes. An index cannot be created on a computed column if the expression references any nondeterministic functions, and a clustered index cannot be created on a view if the view references any nondeterministic functions.

Functions can be divided into categories that are each addressed separately over the remainder of this exam objective coverage.

Date Functions

In T-SQL, as with many other programming languages, date manipulation has been given special importance, by the many functions available. Dates are implemented as datetime and smalldatetime data types and need to be treated with special functions such as DATEDIFF . Dates cannot be added or subtracted using the regular operators. The date functions available in SQL Server enable you to change and manipulate dates easily; the dates must be in datetime or smalldatetime type. The next couple of sections cover the date functions found in SQL Server.

DATEADD

The DATEADD function adds a number to the part of the date you specify, such as month or day, and then returns the new datetime value. The syntax is

 DATEADD (  date_part, number, date  ) 

Where:

• Date_part determines which unit of measure of time. A listing of the possible values accepted is shown in Table 4.7.

• Number determines the number by which you want to increase the date_part .

• Date is a datetime or smalldatetime value.

The DATEADD function adds an interval to a date you specify. For example, if the shipping dates of all invoices in the backorders table slip three days, you can get the new dates with this statement:

 SELECT DATEADD(day, 3, shipdate) FROM backorders 

Table 4.7. Possible Values for DATE_PART

date_part name	Abbreviations
Year	yy, yyyy
Quarter	qq, q
Month	mm, m
Dayofyear	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
Minute	mi, n
Second	ss, s
Millisecond	ms

The DATEDIFF function is used in a similar manner. This function, however, calculates the amount of time in dateparts between the second and first of two dates you specify.

DATEDIFF

Included in the various date manipulation functions is DATEDIFF . As the name suggests, DATEDIFF is a function that returns the difference between two date parts specified. The syntax for DATEDIFF is

 DATEDIFF (  date_part, start_date, end_date  ) 

Where:

• date_part is the unit of time measure you want to find the difference between. The values this can take are shown earlier in Table 4.7.

• start_date is the beginning date for the calculation.

• end_date is the date being subtracted from start_date .

DATEDIFF subtracts the start date from the end date to produce the result. Look at the following example:

 USE pubs GO SELECT pubdate, DATEDIFF(year, pubdate, getdate()) AS 'years difference' FROM titles 

The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of date_part boundaries crossed between the first and second date. For example, the number of weeks between Monday, March 5, and Monday, March 12, is 1.

GETDATE

To get the current date, use the GETDATE function. This function can be useful when you are producing reports that need to be dated. The GETDATE function returns the current system date and can be used in a SELECT statement. The syntax for GETDATE is

 GETDATE() 

The following is an example of using GETDATE() to return the current date:

 SELECT Getdate() as "today's date" 

You can use GETDATE in designing a report to have the current date and time printed every time the report is produced. GETDATE is also useful for functions such as logging the time a transaction occurred on an account.

You can use GETDATE anywhere to return the current system date.

DATENAME

The DATENAME function can be used in determining the current date_part in the form of a string. You may, for instance, need to know specifically the day or month of a publication. The DATENAME function returns a character string representing the specified date part from the date. The syntax is

 DATENAME (  date_part, date  ) 

Where:

• date_part determines which unit of time measure needs to be found. The values it can take were shown in Table 4.7.

• date is an expression denoting the date being used.

Here's an example of using DATENAME to find the name of the current month:

 Select datename(mm,getdate()) as 'month' 

The DATEPART and DATENAME functions produce the specified part of a datetime value (the year, quarter, day, hour, and so on) as either an integer or a string. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used with either of these functions, the seconds and milliseconds returned are zero.

DATEPART

DATEPART is similar to DATENAME except that it returns an integer value indicating the date rather than a character string. The syntax for DATEPART is the same as for DATENAME .

The following is an example using DATEPART to find the current month and display it numerically :

 SELECT datepart(mm,getdate()) AS 'month' 

Many useful date functions give the developer flexibility in dealing with what is traditionally one of the more pesky data types: Dates . With history reflecting unfavorably on the storage of date information, it is important to consider this data type to be infinite in that dates need to remain unique as time progresses.

Numeric data types have always had useful functionality supplied with the programming environment in most languages. SQL Server is no exception with its adoption of a standard set of mathematical functions.

Using Mathematical Functions in T-SQL

A mathematical function performs a math operation on numeric expressions and returns the result of the operation. Math functions operate on any of the SQL Server numeric data types ( decimal , integer , float , real , money , smallmoney , smallint , and tinyint ). The precision of built-in operations on float data type data is six decimal places by default.

By default, a number passed to a mathematical function is interpreted as a decimal data type. The CAST or CONVERT functions can be used to change the data type to something else, such as a float . For example, the value returned by the FLOOR function has the data type of the input value. The input of this SELECT statement is a decimal, and FLOOR(123.34) returns 123 , which is a decimal value.

You can use the diverse set of math functions provided by T-SQL to fulfill many database needs. SQL Server provided numerous mathematical functions so that you can perform the most complex calculations possible. These numerous functions are provided in most programming language environments. A compiled list of most of the important math functions is listed in Table 4.8.

Table 4.8. Mathematical Functions in T-SQL

Function	Parameters	Result
ABS	(Numeric expression)	Returns the absolute value of a number.
ACOS	(Float expression)	Angle (in radians) whose cosine is the specified float expression.
ASIN	(Float expression)	Angle (in radians) whose sine is the specified float expression.
ATAN	(Float expression)	Angle (in radians) whose tangent is the specified float expression.
ATN2	(Float expression1, float expression2)	Returns the angle in radians whose tangent is between the two given float expressions (also called arctangent).
CEILING	(Numeric expression)	Returns the smallest integer greater than or equal to the numeric expression.
COS	(Float expression)	The cosine of the specified expression.
COT	(Float expression)	Cotangent of the specified expression.
DEGREES	(Numeric expression)	Degrees converted from radians of the numeric expression.
EXP	(Float expression)	Exponential value of the specified expression.
FLOOR	(Numeric expression)	Largest integer less than or equal to the specified numeric expression.
LOG	(Float expression)	Natural logarithm of the specified expression.
LOG10	(Float expression)	Base “ 10 logarithm of the specified expression.
PI	( )	Returns 3.1415926535897931.
POWER	(Numeric expression, y)	Value of numeric expression to the power of y.
RADIANS	(Numeric expression)	Radians converted from degrees of the numeric expression.
RAND	(seed )	Random float value between zero and one.
ROUND	(Numeric expression, length)	Numeric expression rounded off to the precision (length) of a number.
SIGN	(Numeric expression)	Returns the positive, negative, or zero sign of a numeric expression.
SIN	(Float expression)	Trigonometric sine of the specified angle.
SQRT	(Float expression)	Square root of the specified expression.
TAN	(Float expression)	Tangent of the specified expression.
SQUARE	(Float expression)	Square of the specified expression.

The following example shows you a query that uses some of the many mathematical functions in SQL Server. Notice that when a query is non-data retrieval, as this one is, a FROM clause does not have to be specified.

 SELECT pi() AS 'PI', abs(-44) AS 'Absolute', power(2,3) AS 'Power' 

Discussion of mathematical functions would be an involved endeavor for this medium. This book does not set out to make mathematical geniuses out of every SQL developer. To become fully versed in all mathematical functions would require a significant education in the science of mathematics.

Character functions interact against alphanumeric data in a similar fashion as numeric data interacts with mathematical functions.

Character Functions

SQL Server also provides a full array of character (string) functions. Character functions enable you to easily manipulate attributes that are character-specific. Most of the functions provided in table are normally used and therefore you should regularly practice using them. For example, if you want to not include the blank spaces before a value or after a value, you could use the LTRIM and RTRIM functions. Table 4.9 is a compilation of the various character functions included in SQL Server's version of SQL.

Table 4.9. Character Functions in T-SQL

Function	Parameters	Result
+	Expression1 + expression2	Concatenates a string; brings two strings together.
ASCII	String expression	Gets the ASCII code value of the string.
CHAR	Integer expression	Changes the ASCII integer into a character.
LEN	String expression	Identifies the length of an expression in characters .
LOWER	String expression	Converts uppercase to lowercase.
LTRIM	String expression	Removes leading blanks.
PATINDEX	Pattern, expression	Returns the position of the beginning of the occurrence of the pattern. If the pattern is not found, it returns a zero.
REPLICATE	String expression, integer expression	Repeats a character expression the number of times specified.
REVERSE	String expression	Returns the reverse of a string expression.
RTRIM	String expression	Removes trailing blanks.
SPACE	Integer expression	Returns a string of repeated spaces.
STUFF	String expression1, start, length, String expression2	Deletes a specified length of characters and inserts another set of characters at a specified starting point.
SUBSTRING	String expression, start, length	Returns part of a string expression.
UPPER	String expression	Makes all lowercase letters uppercase.

Sometimes data is entered into a database with leading or trailing spaces. To retrieve data free of these spaces, use the trim functions: LTRIM and RTRIM .

 SELECT RTRIM (LTRIM(' this is an example       '))+'TESTER' As 'example' 

Concatenation processes are often performed to generate names from a fully normalized database, as shown in Figure 4.5.

Figure 4.5. Concatenation to formulate names from multiple columns .

You can change the direction of text or make an expression uppercase or lowercase by using these three functions: REVERSE , UPPER , and LOWER . Look at the following example:

 SELECT UPPER('mustafa'), LOWER('MUNAWAR'), REVERSE('Mustafa') 

Concatenation of a string value to a NULL string results in NULL . A check should be performed for string content in situations where concatenated information could be partially or completely NULL .

The SUBSTRING Function

It may be that you want to return a part of a string rather than the whole string. The SUBSTRING function, which returns a part of a character or binary string, can be used for this. The syntax for SUBSTRING is

 SUBSTRING  (expression, start, length)  

Where:

• Expression is a character string, binary string, text, image, a column, or an expression that includes a column.

• Start is a number denoting the initial position of the sub-string.

• Length is a number denoting how long the sub-string is.

This example shows how SUBSTRING works:

 SELECT Au_fname +' ' + au_lname AS 'full name',    SUBSTRING (au_fname, 1,1) + SUBSTRING (au_lname, 1,1) AS 'initials'    FROM authors 

WARNING

Avoid Using SUBSTRING The SUBSTRING function, depending on how it is used, as in a WHERE clause, may perform a table scan where an index was supposed to function (if an index was implemented). When the SUBSTRING function does not include the first letter of the column being searched, a table scan is performed.

The next section looks at using DATALENGTH to count the number of bytes used to represent an expression.

DATALENGTH

You may need to know how many bytes long a string is. Of course, you could count the number of characters present in a string, but that would be a complete waste of time. You might also have problems differentiating standard one-byte strings and Unicode two-byte strings. For example, if you were creating an application with a first name column of a fixed length, you would need to observe previous tables with a first name column to get an estimate of the highest first name present to set the fixed length. An easy way to do this would be to use a function known as DATALENGTH in conjunction with MAX ; in this way, you would certainly save time and get results. The DATALENGTH function returns the number of bytes used in an expression supplied. The syntax for DATALENGTH is

 DATALENGTH  (expression  ) 

Where:

• Expression is the data you want to find the length of.

Filtering and formatting data can be carried further with elements of grouping and computing results. These features are defined further in Chapter 5, "Advanced Data Retrieval and Modification."

The final area of SQL Server functionality left to discuss is a series of functions that allow for a variety of system-level interactions.

System Functions

As you have seen in the previous two compilations, many functions are supplied to perform mathematical and character-manipulation operations. You can also use a third category of functions with the SELECT list known as system-specific functions. You can use system functions to retrieve special system or database information through T-SQL and the SELECT statement.

Table 4.10 shows a compilation of the numerous system functions available in T-SQL.

Table 4.10. Database/System Functions in T-SQL

System Function	Parameters	Description
COL_LENGTH	( table name, column name )	The length of a column.
COL_NAME	( table_id , Column_id )	The name of a column
DATALENGTH	(Expression)	The length of any expression in bytes.
DB_ID	( database_name )	The database's identification number.
DB_NAME	( database_ID )	The database's name.
GETANSINULL	( database_name )	Returns the default nullability for the database for this session.
HOST_ID	( )	The identification number for the workstation.
HOST_NAME	( )	The name of the workstation.
IDENT_INCR	(table or view)	Returns the increment value specified during the creation of an identity column in a table or view that has an identity column.
IDENT_SEED	(table or view)	The starting number for an identity column.
INDEX_COL	(table name, index_id , key_id )	The indexed column's name.
ISNULL	(expression, value)	Changes NULL values in the expression to a value specified.
NULLIF	(expression1, expression2)	This gives a NULL value only if the two expressions are equivalent.
OBJECT_ID	( object_name )	The database object identification number.
OBJECT_NAME	( Object_ID )	The database object name.
STATS_DATE	( Table_ID , Index_ID )	The date that the statistics for a particular index were last updated.
SUSER_ID	(login name)	This is only used for backward compatibility. Use SUSER_SID instead.
SUSER_SID	(login name)	The user's login identification number.
SUSER_NAME	( server_user_id )	This is only used for backward compatibility. Use SUSER_SNAME instead.
SUSER_SNAME	( server_user_id )	The user's login identification name.
USER_ID	( user_name )	The user's database identification number.
USER_NAME	( user_ID )	The user's database username.

ISNULL can be useful when you want to convert all NULL values to a particular value. For example, look at the following query. This query converts all NULL values into zeros:

 SELECT ISNULL (price, 0.0000), price FROM titles 

System functions, information schema views, or the system stored procedures can be used to gain access to system information without querying the system tables directly. System tables can change significantly between versions of SQL Server.

SQL Server provides system stored procedures or information schema views for obtaining information about the properties of data, such as the type of data in a column (numeric, text, and so on) or the length of a column. This type of information is called meta data and is maintained by SQL Server for all server and database objects. Meta data can be used to find out information about the structure of data, the contents of a server, or information that specifies the design of objects.

Although it is possible to obtain data through querying any of the system tables directly, the system tables may not provide the information required in the future. It is recommended that system stored procedures, system functions, and information schema views be used because the contents of the system tables may change in future releases.

Information Schema Views

Information schema views provide a method independent of the system tables to view meta data. These views enable applications to work properly even though significant changes may have been made to the system tables and more changes may be made in the future. An application that uses the views rather than a direct query against the system tables should function in the same manner in the future as it does in the current SQL Server release. The information schema views included in SQL Server conform to the SQL-92 Standard definition for the INFORMATION_SCHEMA .

Names used in the SQL-92 standard for these views are different from those used by SQL Server, though the names from SQL Server can equivalently be mapped to those of the standard. The following list shows the SQL-92 names and the SQL Server equivalents:

• A SQL-92 'Catalog' is a SQL Server 'Database'.

• 'Schema' in SQL-92 is an 'Owner' in SQL Server.

• 'Object' is the same in both SQL-92 and in SQL Server.

• A 'Domain' in SQL-92 is a user-defined data type in SQL Server.

When retrieving meta data from the information schema views, you must use a qualified name that includes the INFORMATION_SCHEMA in the position where you usually specify the user name. For example:

 SELECT * FROM Northwind.INFORMATION_SCHEMA.TABLES 

For more information on the variety of meta data that can be obtained through the use of information schema views, use the Index tab of SQL Server Books Online. When you type information schema , the index shows links to all the appropriate views.

Many system stored procedures can also be used to find information about server and database objects. With many of the procedures, however, you can also perform actions against the server, whereas information schema views are used solely to obtain meta data.

System Stored Procedures

Many administrative and informational activities in SQL Server can be accomplished through the use of SQL Server's many system stored procedures. System stored procedures are available to perform a variety of activities from obtaining information about server settings and objects to managing processes on the server to performing maintenance activities and much more.

It is not possible to cover all the procedures in this book, and SQL Server Books Online has full definitions and examples for these procedures. At various points throughout the book, references will be made to those procedures you are likely to find on the exam and others that will serve useful purposes in the future.

REVIEW BREAK: Data Summary

Summarizing information requires that a variety of functions be applied against the data to produce useful information. Summarizing data will provide the end user with the best information source possible to meet the needs of a business.

EXAM TIP

Exam Processes Typical situations that are tested on the exam are:

• Date conversion using appropriate date functions

• String concatenation, including building and parsing functions

• System application design, where system functions are used to programmatically control or manipulate the software functionality

To master the use of functions, you need to work with as many different situations as possible. Each individual problem requires the use of the set of functions necessary to accomplish the task. With such a diverse set of functions available, SQL Server can be used in a multitude of different situations. To recognize the diversity, you will need experience in an equal number of diverse situations.