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

What is more efficient to use in SQL Server: pow(x,1/2) or sqrt(x)? 开发者_JS百科Which one cost less, and which one is faster?

---

Mathematically: SQRT is just a specialized form of POWER, using 1/2 as the exponent

But in SQL Server, the implementation is different. POWER is able to take any floating point as the 2nd argument, so detecting special cases and optimizing differently for each special case (p2=1=>identity and p2=0.5=>sqrt) would make POWER slower than it needs to be.

If you need the Square Root, use SQRT. POWER is demonstrably about 15% slower.

Note: make sure you're using POWER not POW and use 0.5 not 1/2 (literally) since 1/2 = 0

Comparison tests (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
*/

---

I'd like to see the source code that says SQRT uses POWER internally. SQRT is usually calculated 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 that said it isn't likely to matter. At best, it's the kind of micro-optimization that will be swamped by poor decisions about normalization, indexing, clustering, poorly written queries, etc.

---

If you want a square root, I'd suggest always using SQRT(x), because POWER(x,y) is dependent upon the precision 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 & 2008 R2)

---

SQL SQRT function is used to find out the square root of any number. You can Use SELECT statement to find out square root of any number as follows:

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

You are seeing float value here because internally SQL will manipulate square root in float data type.

You can use SQRT function to find out square root of various records as well. To understand SQRT function in more detail consider, an Table_employee, table which is having the following records:

   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 based on the above table you want to calculate square root of all the dialy_typing_pages, then you can do so by 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 |
    +------+--------------------------+

Example of POWER Function in SQL :

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

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