What is better to use in SQL Server: sqrt or pow?

Question

What is more efficient to use in SQL Server: pow(x,1/2) or sqrt(x) ? Which one costs less and which one is faster?

Answer 1

Mathematically: SQRT is the only specialized form of POWER , using 1/2 as an indicator

But in SQL Server, the implementation is different. POWER can accept any floating point as the second argument, so detecting special cases and optimizing differently for each particular case (p2 = 1 => identity and p2 = 0.5 => sqrt) will make POWER slower than it should be.

If you need a square root, use SQRT. POWER clearly about 15% slower .

Note: make sure you use POWER not POW and use 0.5 not 1/2 (literally), as 1/2 = 0

Benchmarks (and timings from SQL Server 2005):

 declare @dummy float -- to hold the result without generating resultset declare @t1 datetime, @t2 datetime, @t3 datetime declare @a float set @a = rand()*1000000 declare @i int select @t1 = getdate() set @i = 0 while @i < 10000000 begin select @dummy= sqrt(@a) set @i = @i + 1 end select @t2 = getdate() set @i = 0 while @i < 10000000 begin select @dummy= power(@a, 0.5) set @i = @i + 1 end select @t3 = getdate() select Time_SQRT = datediff(ms, @t1, @t2), Time_POWER = datediff(ms, @t2, @t3) /* Time_SQRT Time_POWER ----------- ----------- 14540 16430 14333 17053 14073 16493 */ 

Answer 2

I would like to see the source code that says that SQRT uses POWER internally. SQRT is usually computed using Newton's iterative method; I thought POWER would be more likely to use something else (like natural log and exponential).

I agree with the comment, which said it hardly matters. In the best case, this is a kind of micro-optimization, which will depend on bad decisions about normalization, indexing, clustering, poorly written queries, etc.

Answer 3

If you need a square root, I would suggest always using SQRT (x), because POWER (x, y) depends on the accuracy of your input value:

 DECLARE @Foo DECIMAL(18,6) = 12 SELECT POWER(12, 0.5), POWER(12.0,0.5), POWER(@Foo, 0.5), SQRT(12) = 3 3.5 3.464102 3.464101615... 

(SQL Server 2008 and 2008 R2)

Answer 4

The SQL SQRT function is used to determine the square root of any number. You can use the SELECT statement to determine the square root of any number as follows:

  SQL> select SQRT(16); +----------+ | SQRT(16) | +----------+ | 4.000000 | +----------+ 

You see the float value here because internally SQL will manipulate the square root of the float data type.

You can use the SQRT function to find out the square root of different records. To better understand the SQRT function, consider the table Table_employee, which has the following entries:

  SQL> SELECT * FROM Table_employee; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | Ravi | 2007-01-24 | 250 | | 2 | Greg | 2007-05-27 | 220 | | 3 | Neha | 2007-05-06 | 170 | | 3 | Neha | 2007-04-06 | 100 | | 4 | Raj | 2007-04-06 | 220 | | 5 | Indi | 2007-06-06 | 300 | | 5 | Indi | 2007-02-06 | 350 | +------+------+------------+--------------------+ 

Now suppose that based on the table above you want to calculate the square root of all dialy_typing_pages, then you can do this using the following command:

  SQL> SELECT name, SQRT(daily_typing_pages) -> FROM Table_employee; +------+--------------------------+ | name | SQRT(daily_typing_pages) | +------+--------------------------+ | Ravi | 15.811388 | | Greg | 14.832397 | | Neha | 13.038405 | | Neha | 10.000000 | | Raj | 14.832397 | | Indi | 17.320508 | | Indi | 18.708287 | +------+--------------------------+ 

An example of the POWER function in SQL:

  SQL> select POWER(2,3); +------------+ | POWER(2,3) | +------------+ | 8.000000 | +------------+ SQL> select POWER(5,4); +------------+ | POWER(5,4) | +------------+ | 625.0000 | +------------+