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 cost less, and which one is faster?

Answer 1

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
*/

Answer 2

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.