Applies to SQL Server 2019, 2022 and 2025.

The T-SQL SQUARE function does one small job: it multiplies a value by itself. I don’t reach for it often, but when I do it’s usually in a statistics query, a distance calculation or a quick sanity check on some reporting maths. It has one quirk worth knowing about before you rely on it, and that quirk is all about the data type it hands back.

What the T-SQL SQUARE function returns

SQUARE takes a single argument and returns its square. The argument is treated as float, and anything that can be implicitly converted to float (int, bigint, decimal, money and so on) is accepted. The important part is the return type: it’s always float, whatever you pass in.

SELECT SQUARE(7)                          AS IntInput,     -- 49, but as float
       SQUARE(CAST(1.5 AS decimal(4,1)))  AS DecimalInput, -- 2.25, as float
       SQUARE(-3)                         AS NegativeInput,-- 9
       SQUARE(NULL)                       AS NullInput;    -- NULL

On screen that looks harmless. 49 is 49. But the column type is float, and that changes how the value behaves once it goes into a table, a comparison or a calculation further down the line.

A working example: variance by hand

The place I’ve used SQUARE most is checking a statistic rather than trusting it blindly. Population variance is the average of the squared distances from the mean, so it’s easy to work out yourself and compare with the built-in VARP:

CREATE TABLE #Reading
(
    ReadingId int IDENTITY(1,1) PRIMARY KEY,
    ResponseMs int NOT NULL
);

INSERT INTO #Reading (ResponseMs)
VALUES (120), (135), (128), (410), (131), (126);

WITH MeanCalc AS
(
    SELECT AVG(CAST(ResponseMs AS float)) AS MeanMs
    FROM #Reading
)
SELECT AVG(SQUARE(r.ResponseMs - s.MeanMs))       AS VarianceByHand,
       VARP(r.ResponseMs)                         AS VarianceBuiltIn,
       SQRT(AVG(SQUARE(r.ResponseMs - s.MeanMs))) AS StdDevByHand,
       STDEVP(r.ResponseMs)                       AS StdDevBuiltIn
FROM #Reading AS r
CROSS JOIN MeanCalc AS s;

DROP TABLE #Reading;

The two pairs of columns agree, give or take the last decimal place or so of float rounding. Notice I cast ResponseMs to float before taking the average. Without that, AVG on an int column returns an int and silently throws away the fraction, so every deviation would be measured from the wrong mean. That’s an integer arithmetic problem rather than a SQUARE problem, but it turns up in exactly these queries.

The common mistake: assuming SQUARE keeps your data type

The mistake I see is treating SQUARE as if it were just x * x with a nicer name. It isn’t, because x * x keeps the type of x and SQUARE doesn’t. That cuts both ways.

First, float only holds about 15 significant digits exactly. Square a large whole number and the answer can be quietly wrong in the last digit:

DECLARE @n bigint = 123456789;

SELECT @n * @n                       AS ExactProduct,  -- bigint, exact
       SQUARE(@n)                    AS FloatSquare,   -- float, approximate
       @n * @n - CAST(SQUARE(@n) AS bigint) AS SquareGap; -- not 0

The exact answer is 15,241,578,750,190,521. That’s more than float can store exactly, so SQUARE gives you the nearest value float can hold and the SquareGap column isn’t zero. For money, counts or anything you’ll reconcile against another system, multiply the value by itself in an exact type instead.

Second, people swap SQUARE for POWER(x, 2) thinking they’re the same thing. They aren’t. POWER keeps the type of its first argument, so with an int it returns an int and can overflow where SQUARE wouldn’t:

SELECT SQUARE(50000);    -- 2500000000 (float)
SELECT POWER(50000, 2);  -- arithmetic overflow error: int can't hold 2.5 billion
SELECT POWER(CAST(50000 AS bigint), 2); -- 2500000000 (bigint)

POWER with a decimal input is a quieter trap: it returns decimal(38, s) with the same scale as the input, so POWER(1.5, 2) can’t give you 2.25 because a scale of 1 only has room for one decimal place. Pick the function for the type you want back, not for how the code reads.

Watch out for

• Float in the result. SQUARE always returns float, even for int or decimal input. Cast the result if a column or variable needs an exact type, and don’t use float squares for money.
• Large values. Above roughly 15 significant digits, float can’t store the answer exactly. Use @n * @n in bigint or decimal when you need every digit.
• POWER is different. POWER(x, 2) keeps the input type, so it can overflow an int or lose decimal places that SQUARE wouldn’t.
• Integer averages. When squaring deviations from a mean, make sure the mean itself isn’t an int average that has already lost its fraction.
• Comparisons. Avoid testing float squares for exact equality, such as WHERE SQUARE(Col) = 2. Compare against a range or work in an exact type.

Where it fits

SQUARE sits alongside the other mathematical functions such as SQRT, ABS and SIGN. I wrote about the T-SQL SIGN function recently, and it behaves the other way round: its return type follows the type of its input. For more T-SQL and day-to-day DBA notes, see my SQL Server DBA Handbook. Microsoft’s reference pages for SQUARE (Transact-SQL) and POWER (Transact-SQL) list the argument and return types in full.

This topic came up in the SQLServerCentral Question of the Day: Squared Values.

Clive Roberts DBA