You can perform computations with data from numeric columns or on numeric constants in a select list.
The bitwise operators are a Transact-SQL extension that you can use with integers. These operators convert each integer operand into its binary representation, then evaluates the operands column by column. A value of 1 corresponds to true; a value of 0 corresponds to false.
Table 2-1 shows the bitwise operators.
Operator |
Meaning |
|---|---|
& |
Bitwise and (two operands) |
| |
Bitwise or (two operands) |
^ |
Bitwise exclusive or (two operands) |
~ |
Bitwise not (one operand) |
See Chapter 4, “Expressions, Identifiers, and Wildcard Characters,” in the Reference Manual: Building Blocks.
Table 2-2 shows the available arithmetic operators.
Operator |
Operation |
|---|---|
+ |
Addition |
- |
Subtraction |
/ |
Division |
* |
Multiplication |
% |
Modulo |
With the exception of the modulo operator, you can use any arithmetic operator on any numeric column (bigint, int, smallint, tinyint, unsigned bigint, unsigned int, unsigned smallint, numeric, decimal, float, or money).
A modulo operator, which can be used on all integer type columns, finds the remainder after a division involving two numbers. For example, using integers: 21 % 11 = 10 because 21 divided by 11 equals 1, with a remainder of 10. You can obtain a non-integer result with numeric or decimal datatypes: 1.2 % 0.07 = 0.01 because 1.2 / 0.07 = 17 * 0.07 + 0.01. You receive similar results from float and real datatype calculations: 1.2e0 % 0.07 = 0.010000.
You can perform certain arithmetic operations on date/time columns using the date functions. See Chapter 16, “Using the Built-In Functions in Queries,” for information. You can use all of these operators in the select list with column names and numeric constants in any combination. For example, to see what a projected sales increase of 100 percent for all the books in the titles table looks like, enter:
select title_id, total_sales, total_sales * 2 from titles
Here are the results:
title_id total_sales -------- ----------- --------- BU1032 4095 8190 BU1111 3876 7752 BU2075 18722 37444 BU7832 4095 8190 MC2222 2032 4064 MC3021 22246 44492 MC3026 NULL NULL PC1035 8780 17560 PC8888 4095 8190 PC9999 NULL NULL PS1372 375 750 PS2091 2045 4090 PS2106 111 222 PS3333 4072 8144 PS7777 3336 6672 TC3218 375 750 TC4203 15096 30192 TC7777 4095 8190 (18 rows affected)
Notice the null values in the total_sales column and the computed column. Null values have no explicitly assigned values. When you perform any arithmetic operation on a null value, the result is NULL. You can give the computed column a heading, “proj_sales” for example, by entering:
select title_id, total_sales, proj_sales = total_sales * 2 from titles
title_id total_sales proj_sales --------- ----------- ----------- BU1032 4095 8190 ....
Try adding character strings such as “Current sales =” and “Projected sales are” to the select statement. The column from which the computed column is generated does not have to appear in the select list. The total_sales column, for example, is shown in these sample queries only for comparison of its values with the values from the total_sales * 2 column. To see only the computed values, enter:
select title_id, total_sales * 2 from titles
Arithmetic operators also work directly with the data values in specified columns, when no constants are involved. For example:
select title_id, total_sales * price from titles
title_id -------- ---------- BU1032 81,859.05 BU1111 46,318.20 BU2075 55,978.78 BU7832 81,859.05 MC2222 40,619.68 MC3021 66,515.54 MC3026 NULL PC1035 201,501.00 PC8888 81,900.00 PC9999 NULL PS1372 8,096.25 PS2091 22,392.75 PS2106 777.00 PS3333 81,399.28 PS7777 26,654.64 TC3218 7,856.25 TC4203 180,397.20 TC7777 61,384.05 (18 rows affected)
Computed columns can also come from more than one table. The joining and subqueries chapters in this manual include information on multi-table queries.
As an example of a join, this query multiplies the number of copies of a psychology book sold by an outlet (the qty column from the salesdetail table) by the price of the book (the price column from the titles table).
select salesdetail.title_id, stor_id, qty * price from titles, salesdetail where titles.title_id = salesdetail.title_id and titles.title_id = "PS2106"
title_id stor_id ---------------- ----------- ------ PS2106 8042 210.00 PS2106 8042 350.00 PS2106 8042 217.00 (3 rows affected)
