Reading Single Values

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.

Using System-Supplied Scalar Functions

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

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.

image from book

Configuration Functions

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.

Table 7-1: Configuration Functions




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.


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

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.

Date and Time Functions

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:

 SELECT YEAR('2006/02/03') 

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.

Mathematical Functions

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

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

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.


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

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.

System Functions

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

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 .

Ranking Functions

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.

Designing and Using Scalar UDFs

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.

Designing UDFs that Do Not Access Tables or Views

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.

Designing UDFs that Access Tables or Views

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.

Designing and Using Stored Procedures to Retrieve Scalar Values

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.

Using Output Parameters

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.

Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step
Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step
Year: 2006
Pages: 130 © 2008-2017.
If you may any questions please contact us: