About generation of number sequences in SQL Server page 3

D. Iterative calculation of square root

Square root from A number is a solution of equation x * x = A. In the terms of previous point it is a root of equation g(x) = 0, where g(x) = x * x - a. The calculation of this numbers isn`t difficult. In  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL we may use SQRT(a) or POWER(a, 0.5) for it. Let`s exemplify the method, nevertheless, which is useful in the case when there is no standard function but the contracting mapping is known.

The iterative analytical algorithm for calculation of square root is well known from basic course of mathematics and you can see it here.

It may be written in the form of contracting mapping x = f(x), where


 It`s easy to get evidence in that the equation x = 1/2 * (x+a/x) is equivalent to equation x*x = a for x <> 0. The reader with mathematical education may try to prove that this transformation is really contracting, and therefore may be used for iteration process of finding equation`s root.

For illustration of this algorithm let`s note an example of SQL-code for calculation the square root from a = 3:

  1. WITH Square3(a,b,c) AS
  2. (
  3. SELECT 1, CAST(3 AS float(53)), CAST(3 AS float(53))
  5. SELECT a+1, 1./2.*(b+3/b), 1./6.*3*(c+3./c) FROM Square3 WHERE a < 7
  6. )
  7. SELECT iteration=a, Exact=sqrt(CAST(3 AS float(53))), Res1=b, Res2=c
  8. FROM Square3;

Here the [a] column is introduced for iteration`s number, the [b] and [c] columns calculate square root by two arithmetically equivalent methods. Iterations do not use built-in operations such as SQRT or POWER, but for control we outputted the value of square root which is calculated by using standard function in the [exact] column.

iteration Exact Res1 Res2
1 1.7320508075688772 3.0 3.0
2 1.7320508075688772 2.0 1.999992
3 1.7320508075688772 1.75 1.7499920000160001
4 1.7320508075688772 1.7321428571428572 1.7321358469571777
5 1.7320508075688772 1.7320508100147274 1.7320438814531474
6 1.7320508075688772 1.7320508075688772 1.7320438793794952
7 1.7320508075688772 1.7320508075688772 1.7320438793795034

It is evident that the 6th iteration calculations in the third column [Res1] had leaded to coincidence with value of built-in function SQRT(3) in [Exact] column in the FLOAT(53) precision limits. Calculations in the fourth column [Res2] had not. What`s the difference? It is not so obvious, but the reason in that the expression (1./6.) calculates with big mistake as operands are not casted to the 8-byte representation for real numbers (double precision). It affects on all calculations and we have only 5-6 valid significant digits in the result that is corresponds to the theory of calculations with single precision arithmetic.

Bookmark and Share
Pages 1 2 3
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.