There are multiple ways to obtain single values in SQL Server 2005. Each method is evaluated to help you make better choices between the different options.
SQL Server 2005 provides several functions that return single values. These functions can help you obtain part of a value, a value in a different format, random values, environment data, and so forth. There are several function categories, and an example of each category follows .
Aggregate functions help you to obtain summarized values, and they include the COUNT , SUM , AVG , MAX , and MIN functions. These functions are used on grouped expressions to obtain aggregated values.
Suppose that you need to obtain the number of orders from a sales table. You can obtain the value by using the following sentence . The sentences in this section can be accessed from \Ch07\Samples03.sql in the sample files.
SELECT COUNT(*) NumOfElements FROM Sales.SalesOrderHeader
You can use more than one aggregate function on a single statement, as shown in the following sentence.
SELECT COUNT(*) NumOfElements,max(OrderDate) LastSaleDate FROM Sales.SalesOrderHeader
Suppose that you need to obtain the number of elements and the date of the last sale for every territory. To obtain this information, you can combine aggregate functions with the GROUP BY clause, as shown below.
SELECT ST.TerritoryId,ST.Name, COUNT(*) NumOfElements,max(OrderDate) LastSaleDate FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesTerritory ST ON SOH.TerritoryId=ST.TerritoryID GROUP BY ST.TerritoryId,ST.Name
The following figure presents the result.
Configuration functions help you to determine the execution environment. Multiple functions, shown in Table 7-1, can be used to obtain different information about the environment.
Returns the number of the first day of the week. You can change this value by using the SET DATEFIRST statement.
Returns the last timestamp value used on the current database. This value changes on every connection because each change on each table has a timestamp value that is guaranteed to be unique.
@@LANGID , @@LANGUAGE
Returns the default language for the actual connection. You can change these settings by using the SET LANGUAGE statement.
Shows the actual lock timeout value. Special values are 1: waits until the lock has been released and 0: does not wait for any time period. Other values indicate waiting time in milliseconds .
Specifies the nested level. If a stored procedure calls another stored procedure, the nested level is two inside the called stored procedure, and so on.
Represents the identifier of the actual connection.
|More Info|| |
You can obtain a detailed explanation of each of these functions, plus others that are not explained in this book, by searching the SQL Server Books Online topic, Configuration Functions.
Cursor functions are useful for managing cursors . The function @@cursor_rows , for example, returns the number of qualifying rows opened on a connection. When @@cursor_rows id = 1, this means that it is a dynamic cursor. On a dynamic cursor, the number of rows can change by time, and therefore this function always returns 1. @@cursor_rows will return 0 if there are no opened cursors. It will return a positive number with the number of qualifying rows if the cursor is already opened and populated . It will return a negative number if the cursors are still populating asynchronously. The CURSOR_STATUS function will help you determine the status of the cursor.
The most frequently used function when using cursors is @@FETCH_STATUS . This function will help you to determine when you are at the end of the cursor. A value of 0 means that the last FETCH statement was successful. A value of 1 means that the last FETCH statement failed, most likely because the row was beyond the result set. A value of 2 means that the row to be fetched is missing.
Most code needs to perform date and time transformations. You can easily perform these transformations by using the date and time system functions . The YEAR , MONTH , and DAY functions receive a date and return the year, month, or day of the provided date. Observe the following code:
This code will return the year 2006. The MONTH function applied to this date will return 2, and the DAY function applied to the same date will return 3.
The GETDATE and GETUTCDATE functions help you to determine the current date (including the time). The GETDATE function obtains the date and time for the current server time zone (configured in the regional settings), and the GETUTCDATE function obtains the UTC time (Coordinated Universal Time of Greenwich).
The DATEPART and DATENAME functions help you to obtain sections of the time (such as hours or minutes), as well as the names of the day or month.
The DATEADD and DATEDIFF functions are helpful when you need to perform operations with dates. For example, you can use these functions to calculate the age of your customers based on the date of birth stored in your database.
Several mathematical functions exist to help you perform complex mathematical operations. The ABS function returns the absolute value of a number. The SQUARE function returns the square root of a number. Other functions include POWER , EXP , FLOOR , and COS .
Metadata functions are used to discover information about databases and database objects. There are twenty-nine different metadata functions. The DATABASEPROPERTY and DATABASEPROPERTYEX functions help you to obtain metadata about databases such as the recovery model, collation, or ANSI defaults. The following example demonstrates some of the uses of these functions.
SELECT DATABASEPROPERTY('master','IsAnsiNullDefault') SELECT DATABASEPROPERTYEX('master','collation')
Other functions are related to columns , such as the COL_LENGTH , COL_NAME , and INDEX_COL functions. These are used to discover configuration information related to columns. There are also metadata functions related to files, file groups, objects, types, and full text services.
|More Info|| |
You can find detailed information about these and other functions by using SQL Server Books Online.
Security functions return information about logins, users, and permissions. There are multiple security function improvements to be found in SQL Server 2005. Some functions, such as CURRENT_USER, IS_MEMBER , and IS_SRVROLEMEMBER , help you to determine the members of a group or role, as shown below.
SELECT IS_SRVROLEMEMBER('sysadmin') SELECT IS_SRVROLEMEMBER('sysadmin',current_user)
Other SQL Server 2005 functions help you to discover permissions on different principals. The HAS_PERMS_BY_NAME function receives information about principals, classes, and permissions. With these values, it returns an integer with a NULL value if the query fails, it returns 0 when the user does not have permission, and it returns 1 when the user has permission. The following statement uses the HAS_PERMS_BY_NAME function.
SELECT HAS_PERMS_BY_NAME(NULL,NULL,'CREATE ANY DATABASE')
The first parameter represents the securablein this case, the default value of the server itself is indicated by NULLso you can use NULL. The second parameter represents the securable class, and it is NULL for the same reason.
Other security functions are SCHEMA_NAME , SESSION_USER , USER_NAME , and USER_ID , as well as others.
String functions help you to manipule strings (char, varchar, nchar, or nvarchar types). The LEFT , RIGHT , and SUBSTRING functions help you to manipulate strings to obtain certain parts of them.
Review the following statement:
SELECT LEFT('My Word',2),RIGHT('My Word',4),SUBSTRING('My Word',4,4)
This statement returns the words My , Word , and Word . The first clause returns the two first characters beginning at the left of the word. The second clause returns the four last characters beginning at the right of the word. The last clause returns four characters beginning at the fourth character (W, in this case).
Other functions, such as LOWER , REVERSE , UPPER , STUFF , and REPLICATE , are used to obtain additional information about strings. More information about these functions can be found on SQL Server Books Online.
Several system functions exist to obtain information about objects, server-wide values, and settings of a particular SQL Server instance. Some of these functions can help you to catch errors or identify values, collations, and transformations.
The CAST and CONVERT functions can help you with explicit conversions between datatypes, as shown below.
SELECT CAST('1234' AS int), CONVERT(datetime,'20060101',112)
This sentence returns the number 1234 as well as the date January 1, 2006, 00:00:00.000 hours. The value 112 represents the style of the string value; in this case, 112 indicates a format that includes four digits for years . You can obtain more information about the CAST and CONVERT functions on SQL Server Books Online.
The ERROR_LINE , ERROR_MESSAGE , ERROR_NUMBER , ERROR_PROCEDURE , ERROR_SEVERITY , and ERROR_STATE functions return information about the last error. In SQL Server 2005, you can use a TRY CATCH statement to catch errors. You can use these functions inside the CATCH statement to determine what error occurred in the system, the error number, procedure, severity, and so on.
DECLARE @i INT SET @i=0 BEGIN TRY SELECT 10/@i END TRY BEGIN CATCH SELECT Line=ERROR_LINE(), Message=ERROR_MESSAGE(), ErrNumber=ERROR_NUMBER(), [Procedure]=ERROR_PROCEDURE(), Severity=ERROR_SEVERITY(), State=ERROR_STATE() end catch
Another way to specify aliases is to use the syntax above ( Alias = ColumnName ). This syntax is only valid for use in the SELECT clause; you cannot use this syntax on the FROM clause.
The NEWID() function returns a new unique identifier. This identifier can be used as a default value for columns on your database.
CREATE TABLE Messages (idMessage uniqueidentifier NOT NULL default NEWID(), Messagebody varchar(max), CONSTRAINT idMessages PRIMARY KEY NONCLUSTERED (idMessage))
The @@IDENTITY , SCOPE_IDENTITY , IDENT_CURRENT , IDENT_INCR , and IDENT_SEED functions are helpful for identifying metadata identities and the recent values of these identities. Consider the following example:
CREATE TABLE Table1 (id int identity(1,1), othercolumns int DEFAULT 0) GO CREATE TABLE Table2 (id int identity(1,1), othercolumns int DEFAULT 0) GO INSERT INTRO Table1 DEFAULT VALUES GO CREATE TRIGGER tr_table1_insert ON Table1 FOR INSERT AS BEGIN INSERT INTO table2 DEFAULT VALUES END GO INSERT INTO table1 DEFAULT VALUES SELECT @@IDENTITY,IDENT_CURRENT('Table1'),SCOPE_IDENTITY()
The last query returns the values 1, 2, and 2. The @@IDENTITY value represents the last identity value inserted on the current connection (the value is 1 because the last identity value inserted on table2 was 1). The IDENT_CURRENT function represents the actual last identity value on Table1, but if another connection changes the value, IDENT_CURRENT returns the value as changed by this other connection. The SCOPE_IDENTITY() function returns the last identity value inserted in the current scope (the trigger is out of this scope). All three are useful functions, but understanding the exact meaning of each one will help you to use them properly.
Other system functions include @@ROWCOUNT , ISNULL , ISDATE , and SESSION_USER .
System statistical functions return statistical information about the system. For example, @@CPU_BUSY returns the time that SQL Server 2005 has spent working since it was last started.
Be careful when using the @@CPU_BUSY and @@IO_BUSY functions. If your server is running for a long time, these functions can receive an arithmetic overflow warning. In this situation, these variables will have no meaning.
The following statement returns the use of the central processing unit (CPU) in milliseconds.
SELECT @@CPU_BUSY * CAST(@@TIMETICKS AS float)/1000 [Milliseconds CPU]
The @@TOTAL_READ and @@TOTAL_WRITE functions return the number of disk (not cache) reads and writes since SQL Server 2005 was last started. Other statistical functions include @@CONNECTIONS , @PACK_SENT , @@PACK_RECEIVED , and @@IDLE .
SQL Server 2005 provides four ranking functions: RANK , DENSE_RANK , NTILE , and R OW_NUMBER . The ROW_NUMBER functions return the row number in a particular order or inside a particular partition. The next statement provides an example.
SELECT AddressLine1,City,PostalCode, ROW_NUMBER() OVER(ORDER BY Postalcode,AddressLine1), ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY Postalcode,AddressLine1) FROM Person.Address ORDER BY PostalCode,AddressLine1
The first ROW_NUMBER() function will return values from 1 to the total number of rows affected by the query. The second ROW_NUMBER() function will return values from 1 up to the number of rows having the same postal code.
The RANK and DENSE_RANK functions return the rank of every row within the partition specified. RANK will return the same ranking value for all elements that have the same value within the ORDER BY clause. Review the following statement:
SELECT AddressLine1,City,PostalCode, RANK() OVER(ORDER BY PostalCode), DENSE_RANK() OVER(ORDER BY PostalCode) FROM Person.Address ORDER BY PostalCode,AddressLine1
Ranking functions ( RANK , DENSE_RANK ) return the position of the row within the partition. In the previous example, it will return a different value for each postal code (the postal code is the field specified in the ORDER BY clause). The difference between RANK and DENSE_RANK is that DENSE_RANK returns consecutive values for each group. However, RANK returns 1 for all records in the first group of postal codes, then the ordinal position of the first record of the second group for all the records in the second group, then the ordinal position of the first record of the third group for all the records in the third group, and so on.
The NTILE function divides the information into a specified number of groups.
SELECT AddressLine1,City,PostalCode, NTILE(10) OVER(ORDER BY PostalCode,AddressLine1) FROM Person.Address ORDER BY PostalCode,AddressLine1
In the statement above, NTILE divides all the records into ten groups.
User-defined functions (UDFs) in SQL Server 2005 are similar to functions in programming lan-guagesthey accept parameters and return values. These values can be both scalar and table values. Only scalar functions will be discussed in this chapter; in-line and table functions will be covered in Chapter 9: Retrieving Data Using Programmable Objects.
UDFs help you to hide development complexity. These functions have access to every table (depending on permissions) and view, but they cannot call stored procedures or perform UPDATE or INSERT statements.
Scalar UDFs are helpful when organizing data. Suppose that you need descriptions for merchandise status and that these descriptions will rarely if ever be updated. You can use a UDF instead of a table to provide the descriptions, as shown in the following example. The code in this section can be accessed from \Ch07\Samples04.sql in the sample files.
CREATE FUNCTION States(@idState tinyint) RETURNS nvarchar(15) AS BEGIN RETURN (CASE @idState WHEN 0 THEN N'Stock Pending' WHEN 1 THEN N'Prepared' WHEN 2 THEN N'On truck' WHEN 3 THEN N'In warehouse' WHEN 4 THEN N'Finished' ELSE N'UNKNOWN' END) END GO SELECT dbo.states(0),dbo.States(1),dbo.States(100)
You can use this function to return the description of the state. The SELECT statement in the above example will return Stock Pending, Prepared, and Unknown. UDFs that do not access tables do not use many resources, and they can help you to envelope complexity and avoid hard-coding information throughout your application.
You can also use UDFs to access tables. By doing so, you can isolate certain types of complex business rules that are related to data. Review the following sample:
CREATE FUNCTION MostRecentSaleByCustomer(@CustomerID int) RETURNS INT AS BEGIN DECLARE @SalesOrderId int SELECT TOP 1 @SalesOrderId=SalesOrderId FROM Sales.SalesOrderHeader WHERE CustomerID=@CustomerID ORDER BY OrderDate Desc RETURN @SalesOrderId END GO SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderId=dbo.MostRecentSaleByCustomer(676)
This function returns the most recent sale for a particular customer. Using GROUP BY functions is an alternative but cumbersome way to obtain this information for a set of customers; the complexity is reduced dramatically by using a UDF.
In the previous example, a UDF was used for only one row, so there were no performance issues involved with the use of this function. However, be careful when using UDFs. If the UDF performs a simple search on a table, the Query Optimizer will not be able to optimize the call because the search is hidden within the UDF; consequently, every row will execute the scalar function. When using UDFs inside queries returning multiples rows, it is recommended that you avoid using scalar UDFs to access other tables. If you do use scalar UDFs, be sure that the queries will not affect too many records.
The use of stored procedures is a good strategy when you need to encapsulate data-related business logic. Stored procedures can be used not only to retrieve scalar values, but also to return result sets with multiple rows. This chapter focuses only on how to use stored procedures to retrieve scalar values.
The most natural way to return scalar values from a stored procedure is to use output parameters. Most datatypes can be returned as output parameters including the new xml type, varchar(max) types, and text types. Review the following stored procedure. The code in this section can be accessed from \Ch07\Samples05.sql in the sample files.
CREATE PROC ReturnAVowel (@WhatVowel tinyint,@Vowel char(1) output) AS BEGIN SELECT @Vowel= CASE @WhatVowel WHEN 1 THEN 'A' WHEN 2 THEN 'E' WHEN 3 THEN 'I' WHEN 4 THEN 'O' WHEN 5 THEN 'U' ELSE NULL END END
Suppose that you execute this stored procedure as shown in the following statement.
DECLARE @Vowel char(1) EXEC ReturnAVowel 1,@Vowel SELECT 'Vowel'=@Vowel
The result is not the letter A . The result is the NULL value because you need to use the OUTPUT keyword when executing the stored procedure, as shown in the following statement.
DECLARE @Vowel char(1) EXEC ReturnAVowel 1,@Vowel OUT SELECT 'Vowel'=@Vowel
In this example, the result is A , as expected. Note that the example uses the OUT clause. Both OUT and OUTPUT are valid clauses.
A stored procedure is able to return more that one value. Review the following statement:
CREATE PROC ReturnAVowelAndItsAsciiCode (@WhatVowel tinyint,@Vowel char(1) OUTPUT,@Asciicode tinyint OUTPUT) AS BEGIN SELECT @Vowel= CASE @WhatVowel WHEN 1 THEN 'A' WHEN 2 THEN 'E' WHEN 3 THEN 'I' WHEN 4 THEN 'O' WHEN 5 THEN 'U' ELSE NULL END SET @AsciiCode=ASCII(@Vowel) END
Now, this statement is used to execute the stored procedure:
DECLARE @Vowel char(1), @AsciiCode tinyint EXEC ReturnAVowelAndItsAsciiCode 1,@Vowel OUTPUT,@AsciiCode OUTPUT SELECT 'Vowel'=@Vowel,'Ascii code '=@AsciiCode
All stored procedures can return integer values by using the RETURN clause. However, do not use the RETURN clause to return values between applications or as mechanisms for returning scalar values. If you decide to use the RETURN statement, its purpose should be to return flow-related data, such as logical failures or status. It should never be used to return other kinds of data.