tailieunhanh - Expert SQL Server 2008 Development- P5

Tham khảo tài liệu 'expert sql server 2008 development- p5', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | CHAPTER 7 SQLCLR ARCHITECTURE AND DESIGN CONSIDERATIONS server. For a further discussion of the correct placement of data and application logic refer back to Chapter 1. Examples of commonly cited situations in which SQLCLR is perhaps a better choice than TSQL include manipulation of string or XML data certain math functions that are provided by dedicated methods in the .NET Base Class Library and situations where procedural code is more efficient than setbased logic. In order to test the validity of these claims I decided to set up some simple test cases to compare the relative performance of T-SQL against SQLCLR which are described in the following sections. Creating a Simple Sieve for Prime Numbers For this test I created two simple procedures that return a list of all prime numbers up to a supplied maximum value one implemented in T-SQL and one using SQLCLR. The logic of these tests was made as simple as possible each is supplied with a maximum value that is decremented in a loop and in each iteration of the loop the modulo operator is used to determine the remainder when that value is divided by every lesser number. If the remainder of the division is 0 in other words we have found a factor we know that the current value is not a prime number. The loop therefore moves on to test the next possible value. If the inner loop tests every possible divisor and has not found any factors then we know the value must be a prime. Using this kind of simple sieve algorithm for finding prime numbers relies on basic mathematical functions and procedural logic which makes it a good test to compare the performance of T-SQL and SQLCLR. Here s the T-SQL implementation CREATE PROCEDURE ListPrimesTSQL @Limit int AS BEGIN Download at DECLARE -- @n is the number we re testing to see if it s a prime @n int @Limit --@m is all the possible numbers that could be a factor of @n @m int @Limit - 1 -- Loop descending through the candidate primes WHILE @n 1 BEGIN -- Loop .