LOG With a Base: Make the Calculation Explicit

I write LOG with a base when the consumer needs a specific logarithm. Base two and base ten produce different answers. Omitting the optional base requests the natural logarithm, so that choice belongs in the explanation.

Show three meanings together

The example reads one, eight and one thousand as float values. It places base-two, base-ten and natural results side by side. A final typed NULL row keeps missing-input behavior visible.

I cast the three computed outputs to decimal(12,6) for comparison. The original input remains in the result. That makes it easier to check each expected tuple. The function’s numeric return type is still float before the explicit display conversion.

WITH Inputs AS
(
 SELECT CaseId, CAST(InputValue AS float) AS InputValue
 FROM (VALUES (1,1),(2,8),(3,1000),(4,CAST(NULL AS int))) v(CaseId,InputValue)
)
SELECT CaseId, InputValue,
       CAST(LOG(InputValue,2) AS decimal(12,6)) AS BaseTwo,
       CAST(LOG(InputValue,10) AS decimal(12,6)) AS BaseTen,
       CAST(LOG(InputValue) AS decimal(12,6)) AS NaturalLog
FROM Inputs
ORDER BY CaseId;

Read exact powers first

Eight is two raised to the third power. Its base-two logarithm is therefore three. One thousand is ten raised to the third power, so its base-ten logarithm is also three.

The other columns differ because their bases differ. This is why I’d avoid a generic column name such as Score for all three calculations. A consumer comparing the columns needs their mathematical meaning. A different base can alter interpretation even when every input is positive.

Understand the default

With no base argument, LOG uses the natural base e. The expected natural results for eight and one thousand round to 2.079442 and 6.907755. Neither is the exponent in the familiar base-two or base-ten examples.

I’d include the base in exported field descriptions as well as SQL aliases. A chart can outlive the query that produced it. Without that description, a later reader may infer the wrong transformation. The optional argument shouldn’t make the intended convention optional.

Choose valid inputs

One has logarithm zero for each base used here. The final missing input expects NULL results. These are distinct cases: a known positive input can yield zero, while an unknown input remains unknown.

For ordinary real logarithms, the input must be positive. A chosen base must also be valid, positive and different from one. I’d validate the source domain before applying this calculation. The example deliberately avoids error-producing zero or negative inputs.

Treat the numeric display carefully

The optional integer base is available in SQL Server 2012 and later. LOG returns float, so a decimal cast here defines displayed precision. It doesn’t promise exact binary arithmetic for every possible argument.

I’d compare the explicitly rounded outputs in this small example. A production calculation may need a different scale or tolerance. That decision depends on the consuming formula. Decimal formatting alone isn’t evidence that an approximate mathematical result has become exact.

Keep transformation and policy separate

This query performs no updates and creates no objects. The complete result has four ordered rows and three computed columns. It isolates base selection without mixing in a larger model.

I’d choose a logarithmic transformation only when its meaning fits the analytical task. It can compress a range, but that doesn’t justify hiding missing values or invalid domains. Document the chosen base, retain input checks, and explain how the transformed value will be interpreted.

Name the base explicitly, and keep domain checks apart from display rounding.

A logarithm is not one function, it is a family selected by its base.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.