T-SQL Math, System and Metadata Functions: ROUND, NEWID, SCOPE_IDENTITY, @@ROWCOUNT, OBJECT_ID and Catalog Views

Version note: The SQL queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10) with the series' Bookstore database; the PostgreSQL comparisons were run on PostgreSQL 18.7.

Guess First: Rounding 9.99 to One Decimal Place

We round a discount of 9.99 TRY, typed as DECIMAL(3,2), to one decimal place. Is the result 10.0?

SELECT ROUND(CAST(9.99 AS DECIMAL(3,2)), 1) AS overflow;

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type numeric.

An error. The result of ROUND has the same type as its input, DECIMAL(3,2): one digit before the decimal point. 10.00 does not fit that type. Price columns are usually defined wide, so you rarely see this in production; but it can show up in a narrow intermediate calculation, a staging column or a CHECK. This post is the last part of the T-SQL function catalog: math functions and integer division, generating random values, getting the id of a newly inserted row correctly, system functions such as @@ROWCOUNT, and exploring the schema with queries. The examples use the database of Bookstore, the online bookstore used as the shared example across this site's series.

ROUND, CEILING, FLOOR and Other Math Functions

SELECT ROUND(2.345, 2) AS r2, ROUND(2.345, 2, 1) AS truncated, ROUND(-2.5, 0) AS neg_half, ROUND(2.5, 0) AS pos_half,
       ROUND(1234.5, -2) AS to_hundreds, ROUND(CAST(2.5 AS FLOAT), 0) AS float_half;

r2     truncated  neg_half  pos_half  to_hundreds  float_half
-----  ---------  --------  --------  -----------  ----------
2.350  2.340      -3.0      3.0       1200.0       3.0

ROUND(x, n) rounds halves away from zero: 2.5 → 3, -2.5 → -3. With a third argument other than 0 it does not round but truncates: 2.345 → 2.340. A negative n rounds to the left of the decimal point; 1234.5 rounded to hundreds is 1200. The scale of the result does not change: 2.345 rounded shows as 2.350, not 2.35. In PostgreSQL round(2.5::float8) returns 2; on float it rounds halves to even (banker's rounding), while on numeric it rounds away from zero like SQL Server. One more reason to do money calculations with DECIMAL/numeric on both systems.

SELECT CEILING(2.1) AS ceil_pos, CEILING(-2.1) AS ceil_neg, FLOOR(-2.1) AS floor_neg, ABS(-7) AS abs_val,
       POWER(2, 0.5) AS pow_int_base, POWER(2.0, 0.5) AS pow_dec_base, SQRT(2) AS sqrt_val, SIGN(-3.5) AS sign_val, 17 % 5 AS modulo;

ceil_pos  ceil_neg  floor_neg  abs_val  pow_int_base  pow_dec_base  sqrt_val            sign_val  modulo
--------  --------  ---------  -------  ------------  ------------  ------------------  --------  ------
3         -2        -3         7        1             1.4           1.4142135623730951  -1.0      2

CEILING rounds up and FLOOR rounds down; for negative numbers "up" means toward zero: CEILING(-2.1) is -2. A typical use is "how many boxes do we need" (CEILING(quantity / 1.0 / box_capacity)). The trap of POWER is that the result type comes from the first argument: POWER(2, 0.5) returned 1, because 2 is an INT and √2 = 1.414 was cut to INT. POWER(2.0, 0.5) returned 1.4, because 2.0 is DECIMAL(2,1): one decimal place. If you want the right result, make the base FLOAT or a sufficiently wide DECIMAL. Functions such as SQRT, LOG and EXP always return FLOAT.

Integer Division and Decimal Precision

SELECT 7 / 2 AS int_div, 7 / 2.0 AS dec_div, 7 % 2 AS remainder, CAST(7 AS DECIMAL(10,2)) / 3 AS dec_by_int;

int_div  dec_div   remainder  dec_by_int
-------  --------  ---------  ----------
3        3.500000  1          2.333333

The quotient of two INT values is an INT and is truncated: 7 / 2 = 3. The most common case in Bookstore is the average review rating, because reviews.rating is a TINYINT and AVG returns its result in the input's type:

SELECT AVG(rating) AS avg_int, AVG(CAST(rating AS DECIMAL(5,2))) AS avg_dec, AVG(rating * 1.0) AS avg_times_one
FROM dbo.reviews
WHERE book_id = 26;

avg_int  avg_dec   avg_times_one
-------  --------  -------------
4        4.400000  4.400000

Dune's average is 4.4, but AVG(rating) said 4; that is the difference between "4 stars" and "4.4 stars" on the book card. In PostgreSQL avg returns numeric for integer input, so this trap does not exist there. For DECIMAL operations, SQL Server computes the result's precision and scale with rules:

DECLARE @a DECIMAL(10,2) = 1234.56, @b DECIMAL(10,2) = 3;
SELECT @a * @b AS product, @a / @b AS quotient,
       SQL_VARIANT_PROPERTY(@a * @b, 'Precision') AS p_prec, SQL_VARIANT_PROPERTY(@a * @b, 'Scale') AS p_scale,
       SQL_VARIANT_PROPERTY(@a / @b, 'Precision') AS q_prec, SQL_VARIANT_PROPERTY(@a / @b, 'Scale') AS q_scale;

product    quotient           p_prec  p_scale  q_prec  q_scale
---------  -----------------  ------  -------  ------  -------
3703.6800  411.5200000000000  21      4        23      13

In multiplication, precisions and scales add up: DECIMAL(10,2) * DECIMAL(10,2) → DECIMAL(21,4). In division the scale grows: DECIMAL(23,13). If the result would exceed 38 digits, SQL Server cuts from the scale and keeps at least 6 digits:

DECLARE @x DECIMAL(38,10) = 1, @y DECIMAL(38,10) = 3;
SELECT @x / @y AS big_div, SQL_VARIANT_PROPERTY(@x / @y, 'Scale') AS scale;

big_div  scale
-------  -----
.333333  6

The quotient of two numbers with 10 decimal places stopped at 6 digits. That is why making everything DECIMAL(38, x) on the idea that "wider is better" backfires in precise calculations such as exchange rates or unit prices; choose the precision you need and CAST intermediate results explicitly.

RAND, NEWID and Random Selection

RAND() is evaluated once per query; every row gets the same value. For a random number per row you need to derive a seed from NEWID(), which changes on every row:

SELECT TOP (3) book_id, RAND() AS rand_value, RAND(CHECKSUM(NEWID())) AS rand_per_row
FROM dbo.books
ORDER BY book_id;

book_id  rand_value           rand_per_row
-------  -------------------  -------------------
1        0.57200777273679648  0.70284360746822694
2        0.57200777273679648  0.33864401226013674
3        0.57200777273679648  0.77666590896452226

The same seed gives the same value: RAND(42) called twice returned 0.71435594503450972 twice. Useful for generating repeatable data in tests.

The most recommended way to get "one random book" is ORDER BY NEWID(). The plan shows what it does:

SET SHOWPLAN_TEXT ON;
GO
SELECT TOP (1) book_id, title FROM dbo.books WHERE is_active = 1 ORDER BY NEWID();
GO
SET SHOWPLAN_TEXT OFF;

  |--Sort(TOP 1, ORDER BY:([Expr1002] ASC))
       |--Compute Scalar(DEFINE:([Expr1002]=newid()))
            |--Clustered Index Scan(OBJECT:([sqlseries_ext_22].[dbo].[books].[PK_books]), WHERE:([sqlseries_ext_22].[dbo].[books].[is_active]=(1)))

The whole table is scanned, a GUID is generated for every row and the rows are sorted; all for a single row. With 77 books that is fine; with a few million rows it means a full scan on every call. Bookstore's home page "book of the day" also has a business rule: the same book should appear all day. Instead of randomness, we derive a deterministic position from the date:

DECLARE @day DATE = '2026-06-20';
SELECT b.book_id, b.title
FROM dbo.books AS b
WHERE b.is_active = 1
ORDER BY b.book_id
OFFSET ABS(CHECKSUM(@day)) % (SELECT COUNT(*) FROM dbo.books WHERE is_active = 1) ROWS FETCH NEXT 1 ROWS ONLY;

book_id  title
-------  ---------
69       Peter Pan

Peter Pan on June 20; the same query with '2026-06-21' returned Charlotte's Web (70). The result is stable for the whole day, can be cached, and the query does not sort the whole table. OFFSET still moves forward by skipping rows on large tables; in a very large catalog it is cheaper to pick a random value from the id range and take the first row with WHERE book_id >= @x, although gaps in the ids skew the distribution.

There are two ways to generate a GUID. NEWID() generates a completely random GUID. NEWSEQUENTIALID() generates increasing values on the same machine and can only be used in a column's DEFAULT:

DROP TABLE IF EXISTS dbo.guid_test;
CREATE TABLE dbo.guid_test (
    id_random     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    id_sequential UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
    n INT NOT NULL
);
INSERT INTO dbo.guid_test (n) VALUES (1), (2), (3);
SELECT n, LEFT(CAST(id_random AS CHAR(36)), 18) AS random_prefix, LEFT(CAST(id_sequential AS CHAR(36)), 18) AS sequential_prefix
FROM dbo.guid_test
ORDER BY n;

n  random_prefix       sequential_prefix
-  ------------------  ------------------
1  18FFEA6F-2B21-4400  A7554E3E-F36B-1410
2  058C7260-5D93-48C9  A8554E3E-F36B-1410
3  AACBA6DD-1960-4374  A9554E3E-F36B-1410

There is no pattern among the random GUIDs; the sequential ones increase one by one in the first byte (A7, A8, A9). If a clustered index key is a GUID, this difference matters for page splits and fragmentation; the details are in the SQL Server Derinlemesine series. Microsoft Learn gives two warnings: since the next value can be guessed, NEWSEQUENTIALID should not be used where privacy matters (for example an id visible in a URL), and after Windows restarts the values can start from a lower range. If you try to call the function directly in a query, you get "The newsequentialid() built-in function can only be used in a DEFAULT expression ..." (Msg 302). PostgreSQL 18 added uuidv7() for this job: a sortable UUID that starts with a timestamp; uuid_extract_version(uuidv7()) returns 7.

SCOPE_IDENTITY, @@IDENTITY, IDENT_CURRENT and OUTPUT

In post 15 we got the ids of newly inserted rows with OUTPUT. There are three more functions commonly used when inserting a single row, and their differences show up once a trigger is added. Let us create a trigger that writes an audit row on every insert into orders; the audit table's identity starts at 5000:

DROP TABLE IF EXISTS dbo.order_audit;
CREATE TABLE dbo.order_audit (
    audit_id   INT IDENTITY(5000, 1) PRIMARY KEY,
    order_id   INT          NOT NULL,
    action     VARCHAR(10)  NOT NULL,
    audited_at DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
CREATE OR ALTER TRIGGER dbo.tr_orders_audit ON dbo.orders AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.order_audit (order_id, action) SELECT order_id, 'insert' FROM inserted;
END;
GO
BEGIN TRAN;
INSERT INTO dbo.orders (customer_id, ordered_at, status) VALUES (1, '2026-06-30 10:00', 'Pending');
SELECT SCOPE_IDENTITY() AS scope_identity, @@IDENTITY AS at_at_identity, IDENT_CURRENT('dbo.orders') AS ident_current_orders;
ROLLBACK;

scope_identity  at_at_identity  ident_current_orders
--------------  --------------  --------------------
380             5000            380

SCOPE_IDENTITY() returns the last identity value in the same scope (the same batch, procedure or trigger): the order's id, 380. @@IDENTITY returns the last identity value in the session regardless of scope: the 5000 that the trigger wrote to the audit table. Before the trigger was added, both functions returned the same value; so code that uses @@IDENTITY silently starts returning the wrong id on the day someone adds a trigger to the table. IDENT_CURRENT('dbo.orders') returns the table's last generated value regardless of session and scope; if another session is inserting orders at the same time, you may see its id. In application code, the right choice for a single row is SCOPE_IDENTITY(), and for multiple rows OUTPUT.

OUTPUT also has a trigger-related restriction. An OUTPUT returned directly to the client does not work on a table with a trigger:

BEGIN TRAN;
INSERT INTO dbo.orders (customer_id, ordered_at, status)
OUTPUT inserted.order_id, inserted.status
VALUES (1, '2026-06-30 10:00', 'Pending'), (3, '2026-06-30 10:05', 'Pending');
ROLLBACK;

Msg 334, Level 16, State 1, Line 2
The target table 'dbo.orders' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

The fix is to write the result into a table variable with OUTPUT ... INTO and read it from there:

DECLARE @new TABLE (order_id INT, customer_id INT);
BEGIN TRAN;
INSERT INTO dbo.orders (customer_id, ordered_at, status)
OUTPUT inserted.order_id, inserted.customer_id INTO @new (order_id, customer_id)
VALUES (1, '2026-06-30 10:00', 'Pending'), (3, '2026-06-30 10:05', 'Pending');
SELECT order_id, customer_id FROM @new ORDER BY order_id;
SELECT @@TRANCOUNT AS open_transactions;
ROLLBACK;
SELECT @@TRANCOUNT AS after_rollback, IDENT_CURRENT('dbo.orders') AS ident_after_rollback;

order_id  customer_id
--------  -----------
381       1
382       3

(2 rows affected)
open_transactions
-----------------
1

(1 rows affected)
after_rollback  ident_after_rollback
--------------  --------------------
0               382

This error is the reason for the HasTrigger setting we saw in the Entity Framework Core series post "What's New in EF Core 7: ExecuteUpdate, ExecuteDelete, JSON Columns and TPC": EF Core uses OUTPUT when saving, and you have to tell it about tables with triggers. Note the last line too: we rolled back both transactions, but IDENT_CURRENT stayed at 382. Identity values are not given back on rollback; the next order will get 383, and 380–382 will never be used. Why gaps in identity are normal, and how SEQUENCE differs, is the topic of this series' upcoming post on everyday DDL.

@@ROWCOUNT, @@TRANCOUNT, ERROR_ Functions and Session Information

@@ROWCOUNT returns the number of rows affected by the last statement; the words "last statement" matter:

UPDATE dbo.books SET list_price = list_price WHERE category_id = 4;
IF @@ROWCOUNT = 0 PRINT 'nothing updated';
SELECT @@ROWCOUNT AS rowcount_after_if;

(4 rows affected)
rowcount_after_if
-----------------
0

The UPDATE affected 4 rows and IF read that correctly, but IF is itself a statement and reset @@ROWCOUNT. PRINT does the same: @@ROWCOUNT read after a PRINT came out as 0. If you will use the value in more than one place, put it into a variable right after the statement: DECLARE @updated INT = @@ROWCOUNT; kept the value 4. The "first SELECT, then check @@ROWCOUNT" pattern from post 15 relies on this rule. @@TRANCOUNT returns the number of open transactions; above it was 1 after BEGIN TRAN and 0 after ROLLBACK. If @@TRANCOUNT is not 0 at the end of a script, a transaction was left open.

The ERROR_ family returns values only inside a CATCH block:

BEGIN TRY
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS err_no, ERROR_SEVERITY() AS severity, ERROR_LINE() AS err_line, ERROR_MESSAGE() AS err_message;
END CATCH;
SELECT ERROR_NUMBER() AS outside_catch;

err_no  severity  err_line  err_message
------  --------  --------  ---------------------------------
8134    16        2         Divide by zero error encountered.

(1 rows affected)
outside_catch
-------------
NULL

We will cover TRY...CATCH, THROW and error handling with transactions in detail in this series' upcoming post on writing scripts. Functions that tell who and where the session is are useful in log and audit records:

SELECT DB_NAME() AS db_name, SUSER_SNAME() AS login_name, USER_NAME() AS user_name, APP_NAME() AS app_name;

db_name           login_name  user_name  app_name
----------------  ----------  ---------  --------
sqlseries_ext_22  sa          dbo        SQLCMD

SUSER_SNAME() returns the login, USER_NAME() the user in the database; the sa login is dbo in every database. APP_NAME() comes from Application Name in the connection string; if you put a name like "Bookstore.Api" in the application's connection string, you can read from the DMVs which application runs slow queries or holds locks. This value is the client's own claim; do not use it for security decisions.

Metadata: OBJECT_ID, COL_LENGTH, Catalog Views and INFORMATION_SCHEMA

The fastest way to learn a schema is to query it. OBJECT_ID returns an object's id, or NULL if it does not exist, and checks the type with a second argument; COL_LENGTH returns a column's length in bytes:

SELECT OBJECT_NAME(OBJECT_ID('dbo.books', 'U')) AS as_table, OBJECT_ID('dbo.books', 'V') AS as_view,
       OBJECT_ID('dbo.no_such_table') AS missing, COL_LENGTH('dbo.books', 'title') AS title_bytes,
       COL_LENGTH('dbo.books', 'isbn') AS isbn_bytes, COL_LENGTH('dbo.books', 'no_such_column') AS missing_column;

as_table  as_view  missing  title_bytes  isbn_bytes  missing_column
--------  -------  -------  -----------  ----------  --------------
books     NULL     NULL     400          13          NULL

title is an NVARCHAR(200); COL_LENGTH counts bytes, not characters, hence 400. IF OBJECT_ID('dbo.order_audit', 'U') IS NULL and IF COL_LENGTH('dbo.books', 'subtitle') IS NULL are the basic checks of re-runnable DDL scripts; that topic also comes in the upcoming DDL post.

For details there are two sources: SQL Server-specific catalog views (sys.tables, sys.columns, sys.indexes, sys.foreign_keys…) and the standard INFORMATION_SCHEMA views. Let us read the same column information from the latter:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'books' AND COLUMN_NAME IN ('title', 'isbn', 'list_price')
ORDER BY ORDINAL_POSITION;

TABLE_NAME  COLUMN_NAME  DATA_TYPE  CHARACTER_MAXIMUM_LENGTH  IS_NULLABLE
----------  -----------  ---------  ------------------------  -----------
books       isbn         char       13                        NO
books       title        nvarchar   200                       NO
books       list_price   decimal    NULL                      NO

INFORMATION_SCHEMA gives the length in characters (200), while sys.columns.max_length gives it in bytes (400). INFORMATION_SCHEMA exists in PostgreSQL and MySQL too and is a good start for portable scripts; but it does not contain product-specific information such as indexes, partitions or compression. For those you need catalog views. The indexes of order_items and their key columns:

SELECT i.name AS index_name, i.type_desc, i.is_unique, i.is_primary_key,
       STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS key_columns
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID('dbo.order_items') AND ic.is_included_column = 0
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key
ORDER BY i.name;

index_name                 type_desc     is_unique  is_primary_key  key_columns
-------------------------  ------------  ---------  --------------  -----------------
PK_order_items             CLUSTERED     1          1               order_item_id
UQ_order_items_order_book  NONCLUSTERED  1          0               order_id, book_id

sp_help 'dbo.books' returns part of the same information in one command, but as eight separate result sets; handy for looking by hand, not for processing in a script.

A useful query for Bookstore: foreign keys without an index. SQL Server automatically creates an index for a primary key and a unique constraint, but not for a foreign key. With an unindexed foreign key, the child table is scanned end to end when a parent row is deleted or when joining:

SELECT OBJECT_NAME(fk.parent_object_id) AS table_name, fk.name AS fk_name, c.name AS fk_column,
       OBJECT_NAME(fk.referenced_object_id) AS referenced_table
FROM sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
JOIN sys.columns AS c ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
WHERE NOT EXISTS (
    SELECT 1
    FROM sys.index_columns AS ic
    WHERE ic.object_id = fkc.parent_object_id
      AND ic.column_id = fkc.parent_column_id
      AND ic.key_ordinal = 1
)
ORDER BY table_name, fk_name;

table_name    fk_name                  fk_column           referenced_table
------------  -----------------------  ------------------  ----------------
book_authors  FK_book_authors_authors  author_id           authors
books         FK_books_categories      category_id         categories
categories    FK_categories_parent     parent_category_id  categories
order_items   FK_order_items_books     book_id             books
orders        FK_orders_customers      customer_id         customers
payments      FK_payments_orders       order_id            orders
reviews       FK_reviews_customers     customer_id         customers

Of the 11 foreign keys in the database, the column of 7 is not the first key column of any index. order_items.order_id is not in the list because the UQ_order_items_order_book index starts with it; book_authors.book_id is not there either, because it is the first column of the primary key. The query was written for single-column foreign keys; for multi-column foreign keys you also need to compare the column order. Which indexes should actually be added is a design decision covered in the index posts of the SQL Server Derinlemesine series; this query only produces the candidate list.

Function Summary and PostgreSQL Counterparts

I ran the expressions in the PostgreSQL column in my own PostgreSQL 18 container:

In Production

In money calculations, the choice of type comes before the choice of function: use DECIMAL, recognize INT division and AVG(int), and check the precision of intermediate results with SQL_VARIANT_PROPERTY. Round in one place and according to the business rule; rounding per line and then summing gives a different result from summing and then rounding, and it shows up on the invoice as a difference of a few kuruş.

Always get the new id with SCOPE_IDENTITY() or OUTPUT ... INTO. Even if @@IDENTITY and IDENT_CURRENT work today, a trigger added tomorrow or a concurrent session makes them return the wrong value. Do not do random selection on large tables with ORDER BY NEWID(); a deterministic selection rule or a precomputed candidate list is cheaper. Put an Application Name in the application's connection string; seeing which application opened which session saves time during an incident.

Common Pitfalls

• Getting the new id with @@IDENTITY on a table with a trigger; you get the id of the row the trigger inserted.
• Using OUTPUT without INTO on a table with a trigger (Msg 334).
• Reading @@ROWCOUNT after an IF, a PRINT or another statement; put the value into a variable right away.
• Not noticing the decimals cut off by integer operations such as AVG(rating) and 7 / 2; POWER(2, 0.5) returning 1.
• The result of ROUND staying in the input's type and overflowing at the edge (Msg 8115).
• ORDER BY NEWID() on a large table; the whole table is scanned and sorted for a single row.
• Using RAND() for a random value per row; it is evaluated once per query.

A Small Experiment

★☆☆ Compute each book's average rating with both AVG(rating) and AVG(rating * 1.0), and list the books where the two results show a different number of stars when rounded to an integer.

★★☆ Add an audit trigger to the customers table and, in a script that inserts a new customer, compare the ids obtained with SCOPE_IDENTITY(), @@IDENTITY and OUTPUT ... INTO. Run the script inside a transaction, roll it back at the end and look at what IDENT_CURRENT shows.

★★★ Extend the unindexed foreign key query so that it also evaluates multi-column foreign keys correctly: all columns of the foreign key must appear, in the same order, as the first n key columns of an index. For testing, create a small pair of tables with a two-column foreign key.

With this post we have completed T-SQL's everyday function catalog: string, date and time, conversion and NULL, math, system and metadata. In the next post we move to where these functions meet JSON: turning query results into JSON with FOR JSON, opening JSON into rows with OPENJSON, and JSON_VALUE, JSON_QUERY and JSON_MODIFY.