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

Find A Date Range Gap In A Daily Loaded Table

:SETVAR DatabaseName "NAME"
:SETVAR SourceTable "dbo.TABLE"
:SETVAR ControlColumn "etl_date"
USE [$(DatabaseName)]

DECLARE @Tbl1 TABLE (ID INT IDENTITY(1,1) , Dater1 DateTime)DECLARE @Tbl2 TABLE (ID INT IDENTITY(1,1) , Dater2 DateTime)DECLARE @DateFilter DATETIME = '2013-01-05 00:00:00.000'
INSERT INTO @Tbl1SELECT DISTINCT eff_start_date FROM $(SourceTable)WHERE $(ControlColumn) >= @DateFilterORDER BY eff_start_date
INSERT INTO @Tbl2SELECT DISTINCT eff_start_date FROM $(SourceTable)WHERE $(ControlColumn) >= @DateFilterORDER BY eff_start_date
SELECT '$(SourceTable)' SourceTable,t.Dater2,CASE
WHEN (CAST(o.Dater1 AS DATE) <> CAST(DATEADD(D,-1,t.Dater2) AS DATE)) THEN 'FAIL'
ELSE 'PASS' END AS 'Skipped Day',CASEWHEN (DATEPART(weekday,t.Dater2) = 2) THEN 'Monday'WHEN (DATEPART(weekday,t.Dater2) = 3) THEN 'Tuesday'
WHEN (DATEPART(weekday,t.Dater2) = 4) THEN 'Wednesday'
WHEN (DATEPART(weekday,t.Dater2) = 5) THEN 'Thurday'WHEN (DATEPART(weekday,t.Dater2) = 6) THEN 'Friday'WHEN (DATEPART(weekday,t.Dater2) = 7) THEN 'Saturday'WHEN (DATEPART(weekday,t.Dater2) = 1) THEN 'Sunday'ELSE 'Nomads!' END DayOfWeeker FROM @Tbl1 o JOIN @Tbl2 t ON o.ID = (t.ID-1)WHERE o.ID <> 1ORDER BY Dater1 DESC

Sample Regex Patterns

Used for test data generation

US Phone [0-9]{3}(-)[0-9]{3}(-)[0-9]{4}
US Phone Optional dash [0-9]{3}(-|)[0-9]{3}(-|)[0-9]{4}
US Phone Extension [X][ ][0-9]{3,4}
US SSN [0-9]{3}-[0-9]{2}-[0-9]{4}
US SSN no dash [0-9]{3}[0-9]{2}[0-9]{4}
Basic Email [a-z]{4,8}([0-9]{0,3}|\.[a-z]{4,10})@([a-z]{4,9}\.)?([a-z-]{6})\.(org|com|net|biz|cx)

Company Name
(Re|Ad|Par|Tru|Thru|In|Bar|Cip|Dop|End|Em|Fro|Gro|Hap|Kli|Lom|Mon|Qwi|Rap|Sup|Sur|Tip|Tup|Un|Up|Var|Win|Zee)(ban|cad|dud|dim|er|frop|glib|hup|jub|kil|mun|nip|peb|pick|quest|rob|sap|sip|tan|tin|tum|ven|wer|werp|zap)(il|ic|im|in|up|ad|ack|am|on|ep|ed|ef|eg|aqu|ef|edg|op|oll|omm|ew|an|ex|pl)?(icator|or|ar|ax|an|ex|istor|entor|antor|in|over|ower|azz)(([ -]?Pro|Duplex|Multi){0}) (Direct|WorldWide|Holdings|International||) (|Inc|Company|Group|Corp.)?