I am going to knuckle down and write another short article or tidbit on Transaction SQL math functions. Most, if not all, of these functions have been in the product before the release of SQL Server 2005.
I am very proud of my daughter making the advance math class in sixth grade. I am dedicating these blogs to her hard class work and love of the subject.
I will be exploring the SQRT() function today.
This function takes an expression of type float or of a type that can be implicitly converted to float. The output of the function is the mathematical square root. The square root of number X is a number Y such that Y * Y = X.
The example below tests the function with two different inputs: floating point number and a NULL value. It is interesting to note that the POWER() function can be used in place of the SQRT() function.
|
1 2 3 4 5 6 7 8 9 10 |
-- -- The SQUARE ROOT Function -- -- Various numbers as input SELECT SQRT(109.2025) AS POWER_HALF1, POWER(109.2025, 0.5) AS POWER_HALF2, SQRT(NULL) AS UNKNOWN GO |
The output from the test is listed below.
|
1 2 3 4 5 |
output: POWER_HALF1 POWER_HALF2 UNKNOWN ------------- ------------- --------- 10.45 10.4500 NULL |
Lets go back and review the definition of square root.
The square root of number X is a number Y such that Y * Y = X. If we set Y to -5, we end up with 25. On the other hand, if we set Y to 5, we end up with 25 again. Every positive number has two square roots. We always refer to the positive root as the result. So does the TSQL SQRT() function.
Last but not least, we can never have a number Y such that Y * Y that results in a negative result such as -X. To have a more comprehensive set of numbers, an imaginary unit i was introduced. It is defined as i * i = -1. This is a whole area of study called complex numbers.
The code snippet below demonstrates how the square root has both a negative and positive (root) solution. In addition, the TSQL function does not handle negative numbers that depend upon the imaginary unit. It generates an 3623 error message when passed a value less than zero.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- -- The SQUARE ROOT function -- /* In math, a square root has both a positive & negative solution */ SELECT SQUARE(5) AS POS_SQR, SQUARE(-5) AS NEG_SQR, SQRT(25) AS POS_SQRT, SQRT(0) AS ZERO_SQRT GO -- A negative square root results in a error SELECT SQRT(-25) AS NEG_SQRT GO |
The output from the test is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
output: POS_SQR NEG_SQR POS_SQRT ZERO_SQRT --------- --------- --------- --------- 25 25 5 0 NEG_SQRT ---------------------- Msg 3623, Level 16, State 1, Line 1 An invalid floating point operation occurred. </span> |
In summary, the SQRT function takes numbers greater than or equal to zero as input and returns the positive root as output.
