Functions

Manipulation of data provides useful and meaningful results. This may involve performing calculations and mathematical operations, converting data, parsing values, combining values, and aggregating a range of values.

Deterministic Functions examples

AVG ()             --Computes the average ex. SELECT AVG(Score)
CAST()            --Same with Convert    ex. Select CAST('123' AS INT)
CONVERT()
DATEADD()   --Ads a specific number of date unit intervals to a date/time value.
DATEDIFF()   --Use on age calculation example (Select DATEDIFF(month,'9-9-1989','10-17-1991')
ASCII()
CHAR()
SUBSTRING()

Non deterministic Functions

GETDATE()
@@ERROR
@@SERVICENAME
CURSORSTATUS()
RAND()

AGGREGATE FUNCTIONS

The essence of reporting is typically to distill a population of data into a value or values representing a trend or summary. This is what aggregation is all about. Aggregate functions answer the questions asked by the consumers of data:

     1. What are the total sales of chicken gizzard by-products for last month?
     2. What is the average price paid for food condiments by male Brazilians betweent the ages of 19 and 24?
 
Aggregate functions return a scalar value(a sing value) applying a specific aggregate operation. Examples
    Select AVG(15)
    SELECT SUM(15)
    SELECT MIN(15)
    SELECT MAX(15)

   THE AVG() FUNCTION

    Function returns the average for a range of numeric values, for all non-null values. For example, a table contains the following gymnastics meet scores:

   Gymnast        Event            Scores
    Sara               Vault              9.25
    Cassie            Vault              8.75
    Delaney          Vault              9.25
    
    The following query is executed with these values:

    SELECT AVG(Score)
    The result would be   9.083

   The Count() Function

   The Count() function returns an integer value for the number of non-null values in the column range. For instance, if the gymnastics data in the previous example were in a table called GymEvent and I wanted to know the number of events that Sara received a score on, I could execute the following query:
  SELECT COUNT(Score) FROM GymEvent where Gymnast = 'Sara'

  The MIN() and MAX() Functions

   The Min() Function returns the smallest(minimum) non-null value for a column range while Max() function returns the largest (maximum) value.

  The SUM() Function

  The SUM() function is one of the most commonly used aggregates and is fairly self-explanatory.

Configuration Variables
 These aren't really functions, but they can be used in much the same way as system functions. Following are examples:
   The @@ERROR Variable
   This variable contains the last error numbers for the current connection. The value for @@ERROR is 0 when a statement is executed with no errors. Errors are raised by the database engine when standard error conditions occur. All the standard error numbers and messages are viewable from the sys.messages system view and can be queried using the following script:
   SELECT * FROM sys.messages

   Examples: 
  1.  Select 5/0
       Select @@ERROR
  2.  Select 5/0
       Select * from master.dbo.sysmessages where error = @@ERROR

SQL Server is installed by default with languages in addition to U.S. English. Each language-specific error messages has a language identifier(mslangid) that corresponds to a language in the syslanguages table.

Note: mslangid is not installed in SQL SERVER 2005

The @@SERVICENAME Variable
  This is the name of the Windows service used to execute and maintain the current instance of SQL Server. This will typically return the value MSSQLSERVER for the default instance of SQL Server.

The @@TOTAL_ERRORS Variable
 This is the total number of errors that have occurred since the current connection was opened.

 The @@TOTAL_READ Variable
  This is a count of the total disk read operations that have occurred since the current connection was opened. This variable is interesting to the DBA to see disk read activity over time.

  The @@VERSION Variable
   This variable contains the complete version information for the current instance of SQL SERVER.
   SELECT @@VERSION

ERROR FUNCTIONS

Ex.
     --Try to do something
     BEGIN TRY
        SELECT 5/0
     END TRY
     -- If it causes an error, do this
     BEGIN CATCH
         PRINT ERROR_MESSAGE()
     END CATCH