SQL Server CEILING()

SQL Server CEILING() Function

The SQL Server CEILING() function returns the smallest integer greater than or equal to a given numeric expression. This function is useful for rounding up numbers to the nearest integer.

Syntax

SELECT CEILING(number);

The CEILING() function takes a single argument:

  • number: The numeric expression to be rounded up.

Example SQL Server CEILING() Function Queries

Let's look at some examples of SQL Server CEILING() function queries:

1. Basic CEILING() Example

SELECT CEILING(4.2) AS result;

This query returns the smallest integer greater than or equal to 4.2. The result will be:

result
------
5

2. CEILING() with a Negative Number

SELECT CEILING(-4.7) AS result;

This query returns the smallest integer greater than or equal to -4.7. The result will be:

result
------
-4

3. CEILING() with a Column

SELECT price, CEILING(price) AS ceiling_price
FROM products;

This query returns the smallest integer greater than or equal to the price column for each product in the products table. The result will show the original price and its corresponding ceiling_price.

4. CEILING() with a Variable

DECLARE @myNumber FLOAT;
SET @myNumber = 123.456;
SELECT CEILING(@myNumber) AS result;

This query uses a variable to store a numeric value and then returns its ceiling value. The result will be:

result
------
124

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the CEILING() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    price DECIMAL(10, 2)
);

In this example, we create a table named example_table with columns for id and price.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, price) VALUES (1, 4.2);
INSERT INTO example_table (id, price) VALUES (2, -4.7);
INSERT INTO example_table (id, price) VALUES (3, 123.456);

Here, we insert data into the example_table.

Step 3: Using the CEILING() Function

This step involves using the CEILING() function to return the ceiling value of the price column.

SELECT id, price, CEILING(price) AS ceiling_price
FROM example_table;

This query retrieves the id, price, and the ceiling value of the price column for each row in the example_table. The result will be:

id  price    ceiling_price
--- -------- -------------
1   4.2      5
2   -4.7     -4
3   123.456  124

Conclusion

The SQL Server CEILING() function is a powerful tool for returning the smallest integer greater than or equal to a given numeric expression. Understanding how to use the CEILING() function and its syntax is essential for effective numeric calculations and data processing in SQL Server.