SQL Server R2 system function Learning

/******************************************* * Aggregation function *******************************************/ SELECT AVG(ID) asAverage fromSchool asS--returns the average of each value in a group *  SELECT binary_checksum(ID) fromSchool asS--returns a binary checksum value computed from a row or set of expressions in a table  SELECT CHECKSUM(ID) fromSchool asS--returns the checksum value computed from a row or set of expressions in a table  SELECT Checksum_agg(ID) fromSchool asS--returns the checksum value of each value in a group  SELECT COUNT(ID) fromSchool asS--returns the number of items in a group *  SELECT Count_big(ID) fromSchool asS--returns the number of items in a group  SELECTP.province,p.provinceid,GROUPING(P.provinceid) fromProvinces asPINNER JOINCities asC onP.provinceid=C.provinceidGROUP  byP.province,p.provinceid withCUBE--Rollup--Returns 1 (aggregation) or 0 (non-aggregation) for an expression in the group by list
 SELECTP.province,p.provinceid,grouping_id(P.provinceid) fromProvinces asPINNER JOINCities asC onP.provinceid=C.provinceidGROUP  byP.province,p.provinceid withCUBE--Rollup
--Returns an integer that represents the group by level of the parameter
  SELECT MAX(ID) fromSchool asS--returns the maximum value of an expression *  SELECT MIN(ID) fromSchool asS--returns the minimum value of an expression *  SELECT STDEV(ID) fromSchool asS--returns the overall standard deviation of all values in a given expression   SELECT STDEVP(ID) fromSchool asS--returns the overall standard deviation of all values in a given expression  SELECT SUM(ID) fromSchool asS--returns the and or only non-repeating values of all values in an expression and *  SELECT VAR(ID) fromSchool asS--returns the variance of all values in a given expression *  SELECT VARP(ID) fromSchool asS--returns the population variance of all values in a given expression    /******************************************* * Configuration function *******************************************/  SELECT Connectionproperty('Net_transport') as 'Net Transport',connectionproperty('Protocol_type') as 'Protocol Type'  --returns the property value of the Connection property *  SELECT @ @DATEFIRST --returns the current value of the set Datefirst parameter, expressed as the first day of the week specified  SELECT @ @DBTS  --returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database *  SELECT @ @LANGID  --returns the local speech identifier for the current action language  SELECT @ @LANGUAGE --returns the name of the currently used language *  SELECT @ @LOCK_TIMEOUT --returns the current lock timeout setting (in milliseconds) for the current session and returns 1 * without setting  SELECT @ @MAX_CONNECTIONS --returns the maximum number of simultaneous user connections allowed *  SELECT @ @MAX_PRECISION --returns the level of precision that the decimal and numeric data types function by following the current settings in the server *  SELECT @ @NESTLEVEL --returns the nesting level of the current stored procedure execution (initial value is 0) *  SELECT @ @OPTIONS --returns information about the current SET option *  SELECT @ @REMSERVER --returns the name that remote SQL Server displays in the logon record *  SELECT @ @SERVERNAME --returns the name of the local SQL Server *  SELECT @ @SERVICENAME --returns the name of the registry key currently used to run SQL Server *  SELECT @ @SPID --returns the server process identifier for the current user process  SELECT @ @TEXTSIZE --returns the current value of the TEXTSIZE option in the SET statement *  SELECT @ @VERSION --returns the date, version, and processor type of the currently installed SQL Server *

SQL Server R2 system function Learning