Read table row count

This example demonstrates how to retrieve the total number of rows in a ServiceNow table using the get_table_row_count endpoint.

The row count is calculated server-side using sysparm_count=true, which is significantly faster and more efficient than downloading records and counting them client-side.

You can optionally apply a server-side filter using the Query parameter to count only matching rows (for example, high-priority incidents or active records).

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

-- Count ALL records in the Incident table
-- Uses server-side counting (no data rows are downloaded)
SELECT count
FROM get_table_row_count
WITH(
	TableName='incident'
)

-- Count only HIGH PRIORITY incidents (priority = 1)
-- The Query parameter applies an encoded ServiceNow filter before counting
SELECT count
FROM get_table_row_count
WITH(
	TableName='incident',
	Query='priority=1'
)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SERVICENOW_IN_GATEWAY], '-- Count ALL records in the Incident table
-- Uses server-side counting (no data rows are downloaded)
SELECT count
FROM get_table_row_count
WITH(
	TableName=''incident''
)

-- Count only HIGH PRIORITY incidents (priority = 1)
-- The Query parameter applies an encoded ServiceNow filter before counting
SELECT count
FROM get_table_row_count
WITH(
	TableName=''incident'',
	Query=''priority=1''
)')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_SERVICENOW_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = '-- Count ALL records in the Incident table
-- Uses server-side counting (no data rows are downloaded)
SELECT count
FROM get_table_row_count
WITH(
	TableName=''incident''
)

-- Count only HIGH PRIORITY incidents (priority = 1)
-- The Query parameter applies an encoded ServiceNow filter before counting
SELECT count
FROM get_table_row_count
WITH(
	TableName=''incident'',
	Query=''priority=1''
)'
EXEC (@MyQuery) AT [LS_TO_SERVICENOW_IN_GATEWAY]