| ID |
Description |
SQL Server 2000 |
SQL Server 2005 |
| 1 |
Average - Returns the average of the values in the select list ignoring the NULL values. |
SELECT AVG(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO |
SELECT AVG(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 2 |
BINARY_CHECKSUM - The checksum as a binary value for a single row or for particular columns in a table. |
SELECT TitleID, BINARY_CHECKSUM(*)
FROM Pubs.dbo.titles GO |
SELECT EmployeeID, BINARY_CHECKSUM(*)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 3 |
CHECKSUM - The checksum as a integer value for a single row or for particular columns in a table. |
SELECT TitleID, CHECKSUM(*)
FROM Pubs.dbo.titles GO |
SELECT EmployeeID, CHECKSUM(*)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 4 |
CHECKSUM_AGG - Returns the checksum of the values in a table as an integer. |
SELECT CHECKSUM_AGG(*)
FROM Pubs.dbo.titles GO |
SELECT CHECKSUM_AGG(*)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 5 |
COUNT - Returns the number of items in the select list as an integer data type including NULL and duplicate values. |
SELECT COUNT(*)
FROM Pubs.dbo.titles
GO |
SELECT COUNT(*)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 6 |
COUNT_BIG - Returns the number of items in the select list as a big integer data type including NULL and duplicate values. |
SELECT COUNT_BIG(*)
FROM Pubs.dbo.titles GO |
SELECT COUNT_BIG(*)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 7 |
DISTINCT - Not include duplicate values in the SELECT list. |
SELECT DISTINCT(Titles)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO |
SELECT DISTINCT(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 8 |
GROUPING - The GROUPING aggregate is always used with a GROUP BY and either the ROLLUP or CUBE function to calculate the group's value. |
SELECT Royalty, SUM(Advance) 'Total Advance', GROUPING(Royalty) 'GRP_Royalty'
FROM Pubs.dbo.Titles
GROUP BY royalty WITH ROLLUP
GO |
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM AdventureWorks.Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO |
| 9 |
MAX - The highest value in the SELECT list. |
SELECT MAX(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO |
SELECT MAX(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 10 |
MIN - The lowest value in the SELECT list. |
SELECT MIN(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO |
SELECT MIN(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 11 |
SUM - The sum of all the values in the SELECT list which are numeric data types ignoring the NULL values. |
SELECT SUM(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO |
SELECT SUM(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 12 |
STDEV - The standard deviation for all of the values in the SELECT list. |
SELECT STDEV(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO |
SELECT STDEV(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 13 |
STDEVP - The standard deviation for the population for all values in the SELECT list. |
SELECT STDEVP(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO |
SELECT STDEVP(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 14 |
VAR - The variance of the population for all values in the SELECT list. |
SELECT VAR(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO |
SELECT VAR(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO |
| 15 |
VARP - The variance of the population for all values in the SELECT list. |
SELECT VARP(YTD_Sales)
FROM Pubs.dbo.titles WHERE type <> 'business'
GO |
SELECT VARP(VacationHours)
FROM AdventureWorks.HumanResources.Employee;
GO |
