Aggregates
Aggregate functions, or Aggregates, perform a calculation on a set of values and return a single value. Traditionally Microsoft® SQL Server supported only built-in aggregate functions, such as SUM or MAX, that operate on a set of input scalar values and generate a single aggregate value from that set. SQL Server integration with the .NET Framework common language runtime (CLR) now allows developers to create custom aggregate functions in managed code and make these functions accessible to Transact-SQL or other managed code.
To create a new aggregate:
- select the Database | New Object... main menu item;
- select Aggregate in the Create New Object dialog;
- define aggregate properties using the appropriate tabs of Aggregate Editor.
Hint: To create a new aggregate, you can also right-click the Aggregates node or any object within this node in the DB Explorer tree and select the New Aggregate item from the context menu.
To create a new aggregate with the same properties as one of existing aggregates has:
- select the Database | Duplicate Object... main menu item;
- follow the instructions of Duplicate Object Wizard.
Alternatively, you can right-click a aggregate in the DB Explorer tree and select the Duplicate Aggregate <aggregate_name>... context menu item.
Duplicate Object Wizard allows you to select the database to create a new aggregate in, and to edit the result SQL statement for creating the aggregate.
To edit an existing aggregate:
- select the aggregate for editing in the DB Explorer tree (type the first letters of the aggregate name for quick search);
- right-click the object and select the Edit Aggregate <aggregate_name> context menu item, or simply double-click the aggregate;
- edit aggregate properties using the appropriate tabs of Aggregate Editor.
To change the name of an aggregate:
- select the aggregate to rename in the DB Explorer tree;
- right-click the aggregate alias and select the Rename Aggregate <aggregate_name>... item from the context menu;
- edit the aggregate name using the Rename Object... dialog.
To drop an aggregate:
- select the aggregate to drop in the DB Explorer tree;
- right-click the object and select the Drop Aggregate <aggregate_name>... context menu item;
- confirm dropping in the dialog window.
Note: If more convenient, you can also use the following shortcuts:
Ctrl+N to create a new aggregate;
Ctrl+O to edit the selected aggregate;
Ctrl+R to rename the aggregate;
Shift+Del to drop the object from the database.
See also:
Work with Database and Server Objects