{"id":6370,"date":"2013-09-09T00:00:22","date_gmt":"2013-09-09T00:00:22","guid":{"rendered":"http:\/\/craftydba.com\/?p=6370"},"modified":"2016-04-21T20:53:57","modified_gmt":"2016-04-21T20:53:57","slug":"math-functions-sqrt","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6370","title":{"rendered":"Math Functions – SQRT()"},"content":{"rendered":"

I am going to knuckle down and write another short article or tidbit on Transaction SQL math functions<\/a>. Most, if not all, of these functions have been in the product before the release of SQL Server 2005. <\/p>\n

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.<\/p>\n

I will be exploring the SQRT()<\/a> function today. <\/p>\n

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<\/a>. The square root of number X is a number Y such that Y * Y = X.<\/p>\n

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.<\/p>\n

\r\n--\r\n--  The SQUARE ROOT Function\r\n--\r\n\r\n-- Various numbers as input\r\nSELECT \r\n    SQRT(109.2025) AS POWER_HALF1, \r\n    POWER(109.2025, 0.5) AS POWER_HALF2, \r\n    SQRT(NULL) AS UNKNOWN\r\nGO\r\n<\/pre>\n<\/p>\n
The output from the test is listed below.<\/p>\n
\r\noutput: \r\n\r\nPOWER_HALF1    POWER_HALF2    UNKNOWN\r\n-------------  -------------  ---------\r\n10.45          10.4500        NULL\r\n\r\n<\/pre>\n<\/p>\n
Lets go back and review the definition of square root.  <\/p>\n
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.  <\/p>\n
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<\/a>.<\/p>\n
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.<\/p>\n
\r\n--\r\n--  The SQUARE ROOT function\r\n--\r\n\r\n\/*\r\n  In math, a square root has both a \r\n  positive & negative solution\r\n*\/\r\n\r\nSELECT \r\n    SQUARE(5) AS POS_SQR, \r\n    SQUARE(-5) AS NEG_SQR, \r\n    SQRT(25) AS POS_SQRT,\r\n    SQRT(0) AS ZERO_SQRT\r\nGO\r\n\r\n-- A negative square root results in a error\r\nSELECT \r\n    SQRT(-25) AS NEG_SQRT\r\nGO\r\n<\/pre>\n<\/p>\n
The output from the test is listed below.<\/p>\n
\r\noutput: \r\n\r\nPOS_SQR   NEG_SQR   POS_SQRT  ZERO_SQRT\r\n--------- --------- --------- ---------\r\n25        25        5         0\r\n\r\nNEG_SQRT\r\n----------------------\r\nMsg 3623, Level 16, State 1, Line 1\r\nAn invalid floating point operation occurred.\r\n\r\n<\/span><\/pre>\n<\/p>\n
In summary, the SQRT function takes numbers greater than or equal to zero as input and returns the positive root as output.  <\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":5430,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[31,15,913,922,29],"class_list":["post-6370","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-database-developer","tag-john-f-miner-iii","tag-math-function","tag-sqrt","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6370","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6370"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6370\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/5430"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6370"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6370"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6370"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}