SQL 2005+

SQL Server Arithmetic Operators

The thirty-second part of the SQL Server Programming Fundamentals tutorial describes the basic arithmetic operators provided by Transact-SQL (T-SQL). These operators allow simple mathematical operations within queries, procedures and other statements.

Arithmetic Operators

So far in the SQL Server Programming Fundamentals tutorial we have looked at the basic tasks of creating a database schema, adding and maintaining data, performing queries and creating simple stored procedures. These tasks have been looked at individually and have been introduced in stages. The previous articles have introduced concepts and techniques that are essential to developers that use SQL Server but have included little actual programming elements.

In the next group of articles in the series we will examine some of the operators, functions and code structures that allow you to program directly within a SQL Server environment. These can be used within queries or update statements, or directly from SQL Server Management Studio. They can also be included in stored procedures, where they can become extremely powerful.

In this article we will investigate the use of arithmetic operators in various scenarios. SQL Server provides five such operators, including additional, subtraction, multiplication and division.

Using Arithmetic Operators

Some of the examples in this article use data from the JoBS database. This is a database that has been generated and populated with data throughout the course of this tutorial. If you do not have an up-to-date copy of the database you can create one by downloading and executing the script provided via the link at the top of this page.

Basic Operators

The first four arithmetic operators are available in almost all programming languages. These are addition (+), subtraction (-), multiplication (*) and division (/). Using a new query window in SQL Server Management Studio we can demonstrate their use with the PRINT command. This command simple outputs information to the results pane. Execute the following to see the results:

PRINT 10 + 4    -- 14
PRINT 10 - 4    -- 6
PRINT 10 * 4    -- 40
PRINT 10 / 4    -- 2

The last of the four results is worthy of comment. In this case, SQL Server has assumed that the values being used are integers and so the result is also an integer. This has caused the value to be rounded downwards from 2.5 to 2. It is important to understand that all arithmetic operation results are limited by the data types that are being used. We will investigate conversion between data types in a later article in this tutorial. For now, we can modify the final PRINT statement to include a value with a decimal point for a different result:

PRINT 10.0 / 4    -- 2.500000

The fifth numeric operator is modulus (%). This returns the remainder value for an integer division. For example, if you divide ten by six, the integer part of the result would be one and the remainder would be four:

PRINT 10 % 6    -- 4

Operators and Queries

The arithmetic operators can be used within queries wherever the result that they give would be valid. This includes operations within the selected column list and the WHERE clause. The operators can be used with literal values, column values and variables. As with other functions that we have examined earlier in the tutorial, when you calculate the value of a column it will lose the column name. You can create an alias for the new column using the AS clause.

Try executing the query below. This query lists all of the engineers from the JoBS database and includes a column that gives their daily pay. This value is calculated for each engineer by multiplying their hourly rate by eight.

SELECT
    EngineerName,
    HourlyRate,
    OvertimeRate,
    HourlyRate * 8 AS DailyRate
FROM
    Engineers