Test a Percentage Of Rows

There are times when working with a large data set that you may not have space or time to test every row you have processed. I use a limiting table that contains a sleect set of key values you will inner join your test query to.This throttles the test to just that set of key values, as a percentage of the total data.

DECLARE
--set the percent of the rows, as a whole number, you want to run these tests against
    @PercentRows INT = 10,
    @RowsToProcess INT,
    @SQL NVARCHAR(MAX)

DECLARE @LoanTbl TABLE (Loan_ID INT)

SET @RowsToProcess = (SELECT COUNT (DISTINCT Loan_ID)FROM TableOne (NOLOCK))*(@PercentRows*.01)
SET @SQL = 'SELECT DISTINCT TOP '+CONVERT(VARCHAR(20),@RowsToProcess)+' Loan_ID FROM TableOne (NOLOCK) ORDER BY Loan_ID DESC';

INSERT INTO @LoanTbl
EXECUTE sp_executesql @SQL