In SQL Server, the T-SQL LOG() function is a mathematical function that returns the natural logarithm of the specified float expression.

You specify the float expression as an argument. You can also provide a second (optional) argument to set the base for the logarithm.

Syntax

The syntax goes like this:

LOG ( float_expression [, base ] )  

Where float_expression is an expression of type float or of a type that can be implicitly converted to float, and base is an optional integer argument that sets the base for the logarithm.

Example 1 – One Argument

Here’s a basic example to demonstrate how this function works using one argument.

SELECT LOG(10) Result;

Result:

+------------------+
| Result           |
|------------------|
| 2.30258509299405 |
+------------------+

And here’s another example using a different value.

SELECT LOG(0.5) Result;

Result:

+--------------------+
| Result             |
|--------------------|
| -0.693147180559945 |
+--------------------+

Example 2 – Negative Value

Here’s an example of passing in a negative value.

SELECT LOG(-10) Result;

Result:

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

This example returns an error because the logarithm can only be returned for positive numbers that aren’t 1.

Example 3 – Zero

Here’s an example of passing in zero as the argument (we get the same result as the previous example).

SELECT LOG(0) Result;

Result:

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

Example 4 – Passing in 1

If you pass in a value of 1, you end up with zero.

SELECT LOG(1) Result;

Result:

+----------+
| Result   |
|----------|
| 0        |
+----------+

Example 5 – Expressions

You can pass in expressions like this:

SELECT LOG(2+3) Result;

Result:

+-----------------+
| Result          |
|-----------------|
| 1.6094379124341 |
+-----------------+

That’s effectively the same as doing this:

SELECT LOG(5) Result;

Result:

+-----------------+
| Result          |
|-----------------|
| 1.6094379124341 |
+-----------------+

Example 6 – Two Arguments

Here’s an example to demonstrate how this function works using two arguments.

SELECT LOG(10, 100) Result;

Result:

+----------+
| Result   |
|----------|
| 0.5      |
+----------+

And using another example:

SELECT LOG(100, 10) Result;

Result:

+----------+
| Result   |
|----------|
| 2        |
+----------+

If you’re familiar with the MySQL version of LOG(), you’ll notice that the two-argument syntax uses the opposite order to the T-SQL version (i.e. the base comes first).

Example 7 – Using a Base of 1

If you use 1 as the base, you’ll get an error.

SELECT LOG(10, 1) Result;

Result:

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

Same if the first argument is equal to zero or less:

SELECT LOG(0, 10) Result;

Result:

Msg 3623, Level 16, State 1, Line 1
An invalid floating point operation occurred.

T-SQL also has the EXP() function that is the inverse of the LOG() function.

You can also use the LOG10() function, which returns the base-10 logarithm of the specified float expression. Using this function is the same as passing in 10 as the second argument to the LOG() function.