Microsoft Business Intelligence: Row count statement in SQL Server

Tuesday, 4 November 2025

Row count statement in SQL Server

Recently we have worked on a SSIS package. This package is used to extract the data from the source system and load it into the destination stage table. Using this package we are loading around 100 table. It does not have any complex mapping or transformation.

My problem is that we are doing frequently changes in this package. Means while we are also doing same modification on the existing table like altering, dropping or adding some column in the table. Some time our some DFT got unmapped. It was very difficult to find which DFT has unmapped.

The best way to run the package and see the stage table data. Again if we will write the query like select * from tablename it will return the all data from this table or we need to write the Select top (n) * from tablename.

Like below

SELECT * From Sales.SalesTaxRate

SELECT * From Sales.PersonCreditCard

SELECT * From Person.PersonPhone

SELECT * From Sales.SalesTerritory

SELECT * From Person.PhoneNumberType

SELECT * From Production.Product

SELECT * From Sales.SalesTerritoryHistory

SELECT * From Production.ScrapReason

SELECT * From HumanResources.Shift

SELECT * From Production.ProductCategory

SELECT * From Purchasing.ShipMethod

Out moto is to just get the data populated or not for that we need to use top clause in every script. Instead of using top we will use SET ROWCOUNT statement.

Syntex : SET ROWCOUNT

See the example

SET ROWCOUNT 5

From each table it will return the 5 records.

It limits the number of rows returned by all subsequent SELECT statements within the current session by using the keyword SET ROWCOUNT.

The SQL Server ROWCOUNT Set Function causes the server to stop the query processing after the specified numbers are returned. This setting is only applicable for the current session. If we set ROWCOUNT 0 then it will return all rows.

We can set this using the Tools menu bar see below