Home » Microsoft » MB6-886 » Which type of object should you create?
You are a database developer on an instance of SQL Server 2008.
You need to provide a custom function that will perform complex calculations on groups of data in one of your tables. You want users to be able to reference the function directly from a query as if they were using a built-in scalar function. Which type of object should you create?
A. a CLR user-defined type
B. a Transact-SQL user-defined function that returns a scalar value
C. a CLR user-defined aggregate
D. a Transact-SQL user-defined stored procedure
Correct Answer: C
Explanation/Reference:
CLR user-defined aggregates allow you to create custom aggregate functions that users can call like other built-in aggregate functions, such as SUM, MAX, or COUNT. In a .NET language, you create the code necessary to implement the desired aggregation functionality and compile the class into an assembly. You should note that the class must meet specific requirements and implement specific methods to be able to use it for custom aggregation. After you create the assembly, you can register it in SQL Server using the CREATE ASSEMBLY statement. Then, you can use the CREATE AGGREGATE statement to create the custom aggregate function with the name users will use to access the custom aggregate function.
All of the other options are incorrect because to implement a custom aggregation, you must use a CLR user-defined aggregate.
CLR user-defined types allow you to create custom data types using a .NET Framework language, and then use these types from SQL Server. You can leverage the functionality of the .NET language that is not supported in SQL Server. You can also create a CLR user-defined type and then create a CLR user-defined aggregate that performs aggregations on a group of the specified type.
