Home » Microsoft » MB6-886 » Which mechanism should you use to implement this functionality?
You have a Research database. The database resides on a SQL Server 2008 database instance named SRV1.
You want users to be able to specify up to 10 string values. Using these specified values, you want to perform complex string comparisons and rank each of the strings with a rating value. Researchers need to store these string values and their corresponding ratings in the Scenario table in the Research database. You want to provide this functionality while optimizing performance and minimal development. Which mechanism should you use to implement this functionality?
A. an extended stored procedure
B. a CLR table-valued function
C. a Transact-SQL multi-statement table-valued function that uses the OVER clause
D. a CLR user-defined aggregate
Correct Answer: B
Explanation/Reference:
A CLR function is created in a .NET Framework language and then registered in the database so it can be accessed using Transact-SQL. You should consider using CLR functions or procedures when you need to perform CPU-intensive processing, or if the desired functionality can be implemented more efficiently using .NET Framework classes. The .NET Framework offers more robust string comparison and manipulation functionality that is not natively supported in SQL Server.
CLR functions can return a single scalar value or a result set. Because the function would need to return more than one value, it would be created as a CLR tablevalued function that returns a result set to the caller. The data could then be easily inserted into the Scenario table as required.
You should not use an extended stored procedure because this would require additional development effort.
You should not use a Transact-SQL multi-statement table-valued function that uses the OVER clause. Although you could use a Transact-SQL function to perform complex string comparisons, a CLR function would be a better choice because it offers more robust built-in methods that will optimize performance and reduce the coding complexity. The OVER clause specifies the order of the rowset or the partitioning order before an associated windowing function is applied.
You should not use a CLR user-defined aggregate. A user-defined aggregate would be used to implement a customized aggregate function. In this scenario, there were no aggregation requirements.