Redigera

Dela via


Unary operators - Negative (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Returns the negative of the value of a numeric expression (a unary operator). Unary operators perform an operation on only one expression of any one of the data types of the numeric data type category.

Operator Meaning
+ (Unary positive) Numeric value is positive.
- (Unary negative) Numeric value is negative.
~ (Bitwise NOT) Returns the ones' complement of the number.

The + (positive) and - (negative) operators can be used on any expression of any one of the data types of the numeric data type category. The ~ (bitwise NOT) operator can be used only on expressions of any one of the data types of the integer data type category.

Transact-SQL syntax conventions

Syntax

- numeric_expression

Arguments

numeric_expression

Any valid expression of any one of the data types of the numeric data type category, except the date and time category.

Return types

Returns the data type of numeric_expression, except that an unsigned tinyint expression is promoted to a signed smallint result.

Examples

A. Set a variable to a negative value

The following example sets a variable to a negative value.

USE tempdb;
GO

DECLARE @MyNumber DECIMAL(10, 2);
SET @MyNumber = -123.45;

SELECT @MyNumber AS NegativeValue;
GO

Here's the result set.

NegativeValue
--------------
-123.45

B. Change a variable to a negative value

The following example changes a variable to a negative value.

USE tempdb;
GO

DECLARE @Num1 INT;
SET @Num1 = 5;

SELECT @Num1 AS VariableValue,
    -@Num1 AS NegativeValue;
GO

Here's the result set.

VariableValue NegativeValue
------------- -------------
5             -5

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

C. Return the negative of a positive constant

The following example returns the negative of a positive constant.

USE ssawPDW;
GO

SELECT TOP (1) - 17 FROM DimEmployee;

Here's the result set.

-17

Notice the same result returned as if the unary negative is applied to a value with unary Unary operators - Positive applied.

USE ssawPDW;
GO

SELECT TOP (1) - (+ 17)
FROM DimEmployee;

Here's the result set.

-17

D. Return the positive of a negative constant

The following example returns the positive of a negative constant.

USE ssawPDW;
GO

SELECT TOP (1) - (- 17)
FROM DimEmployee;

Here's the result set.

-17

E. Return the negative of a column

The unary negative reverses the numeric operator of a column's values. As result, the negative values are returned from positive values, and positive values are returned from negative values.

The following example returns the negative of the BaseRate value for each employee in the DimEmployee table.

USE ssawPDW;
GO

SELECT - BaseRate
FROM DimEmployee;