Here’s a straightforward problem: I have a table with a lot of fields in it (in this case, several tables — new Fact and Dimension tables in a star schema data warehouse, but, you know, any wide table will do).<\/p>\n
I want to extract a few real world test records that exercise the entire table… a “covering set” of test cases… so if I have 100 columns, and record A has non-zero, non-null values in columns 1-50 and record B has good values in columns 51-100, then I only need to test those two records. How great is that?!<\/p>\n
Ok, I should probably BUILD test cases, but I like using real data since there are always unseen business rules lurking about. Anyway, this is a pretty basic math problem: Select the minimal number of objects from the set of rows where the union of the viable (non-null, non-zero) columns across the subset covers all possible columns.<\/p>\n
There’s some code below. \u00c2\u00a0Note that it is very bad code. \u00c2\u00a0I use the wrong scope on global temporary tables, I don’t do lots of checking of things, I generate SQL and execute it, I debug with print statements. \u00c2\u00a0It is also formatted poorly, but that’s actually more of a wordpress\/plugin issue than anything else.<\/p>\n
But it’s mine, and I love it…<\/p>\n
<\/p>\n
To use, do something like:<\/p>\n
\r\n\r\nexec etl.DQFindCompleteTestCases 'Facts', 'MyFactTable', 'MyFactTablePrimaryKey'\r\n\r\n<\/pre>\nWhich will output records with the MyFactTablePrimaryKey ID and the number of new columns handled by that corresponding record.<\/p>\n
Note that the procedure will not go into an infinite loop if there is a column that has all NULLs or all zeros or something. \u00c2\u00a0Found out I needed that the hard way. \u00c2\u00a0Oops!<\/p>\n
\r\nALTER PROCEDURE [ETL].[DQFindCompleteTestCases](@SchemaName [nvarchar](128),\r\n@TableName [nvarchar](128),\r\n@IDFieldName [nvarchar](128))\r\n\/*\r\nCreated 2017\/02\/22 by Chip Lynch\r\n\r\nInputs: @DatabaseName.@SchemaName.@TableName identifies a table for which\r\nall numeric type columns will be searched for non-zero non-null values.\r\nA short (not provably minimal) list of records, identified by @IDFieldName,\r\nwill be returned so that the collection of those records contains non-zero or non-null\r\nvalues in ALL of the available columns.\r\n\r\nToDo: Add more robust schedule to rules when runtimes become onerous.\r\n*\/\r\nAS\r\nBEGIN\r\n\r\nSET NOCOUNT ON\r\n\r\n\/* Validate inputs *\/\r\n\r\nIF (@SchemaName IS NULL OR\r\n@TableName IS NULL OR\r\n@IDFieldName IS NULL)\r\nBEGIN\r\nRAISERROR ('Incorrect parameters.', 15, 1)\r\nEND\r\n\/* Create tables and indexes as needed *\/\r\n\r\ncreate table #TempGoodIDs (\r\nCandidateID varchar(4000)\r\n);\r\n\r\nselect\r\nsac.name as column_name, sac.system_type_id, sac.user_type_id\r\ninto #TempMyColumns\r\nfrom sys.all_columns sac\r\njoin sys.all_objects sao on\r\nsac.object_id = sao.object_id\r\njoin sys.schemas ss on\r\nsao.schema_id = ss.schema_id\r\njoin sys.types st on\r\nsac.system_type_id = st.system_type_id\r\nwhere\r\nss.name = @SchemaName and\r\nsao.name = @TableName and\r\nst.name in ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'decimal', 'numeric', 'smallmoney', 'bigint')\r\nand st.name != @IDFieldName -- Just in case\r\n;\r\n\r\nDECLARE @ColumnsRemaining int;\r\nselect @ColumnsRemaining = count(1) from #TempMyColumns;\r\n\r\nDECLARE @MySQL nvarchar(max);\r\nDECLARE @MyNotNullList varchar(max);\r\nDECLARE @MyNEZeroList varchar(max);\r\nDECLARE @MySumList varchar(max);\r\nDECLARE @MyGoodColumnList varchar(max);\r\nDECLARE @MyCandidateColumn varchar(128);\r\nDECLARE @MyGoodCount int;\r\nDECLARE @MyRecordCount int;\r\n\r\nWHILE(@ColumnsRemaining > 0)\r\nBEGIN\r\n-- Build SQL to select the record with the most <> 0 fields from the list:\r\n\r\n--select @MyNotNullList = stuff( (\r\n-- select ' IS NOT NULL OR ' + tmc.column_name\r\n-- from #TempMyColumns tmc\r\n-- for XML PATH('')\r\n-- ), 1, 17, '') + ' IS NOT NULL'\r\n\r\nselect @MyNEZeroList = stuff( (\r\nselect ' <> 0 OR ' + tmc.column_name\r\nfrom #TempMyColumns tmc\r\nfor XML PATH(''), type\r\n).value('.','nvarchar(max)'), 1, 9, '') + ' <> 0'\r\n\r\nprint @MyNEZeroList\r\n\r\nselect @MySumList = stuff( (\r\nselect '+ CASE WHEN ' + tmc.column_name + ' <> 0 THEN 1 ELSE 0 END '\r\nfrom #TempMyColumns tmc\r\nfor XML PATH(''), type\r\n).value('.','nvarchar(max)'), 1, 1, '')\r\n\r\nif object_id('tempdb..##TempBestID') IS NOT NULL\r\nDROP TABLE ##TempBestID\r\n\r\nset @MySQL = 'select top 1 ' + @IDFieldName + ' as MyId, ' + @MySumList + ' as MyGoodColCount ' +\r\n' into ##TempBestID ' +\r\n' from ' + @SchemaName + '.' + @TableName +\r\n' where (' + @MyNEZeroList + ') ' +\r\n--' AND ' + @MyNotNullList +\r\n' order by 2 desc;'\r\n\r\nprint len(@MySQL)\r\nprint 'select top 1 ' + @IDFieldName + ' as MyId, ' + @MySumList + ' as MyGoodColCount '\r\nprint ' into ##TempBestID '\r\nprint ' from ' + @SchemaName + '.' + @TableName\r\nprint ' where (' + @MyNEZeroList + ') '\r\n--' AND ' + @MyNotNullList +\r\nprint ' order by 2 desc;'\r\n\r\nexec sp_executesql @MySQL\r\n\r\nselect * from ##TempBestID\r\n\r\nSELECT @MyRecordCount = @@ROWCOUNT\r\n\r\nif @MyRecordCount = 0\r\nBEGIN\r\nprint 'No new useful records found.'\r\nset @ColumnsRemaining = 0\r\nEND\r\n\r\n-- insert into #TempGoodIDs select cast(MyId as varchar) from ##TempBestID\r\n\r\nDECLARE MyColumnCursor CURSOR FOR\r\nselect tmc.column_name from #TempMyColumns tmc\r\nOPEN MyColumnCursor\r\n\r\nFETCH NEXT FROM MyColumnCursor into @MyCandidateColumn\r\nWHILE (@@FETCH_STATUS = 0) AND (@ColumnsRemaining > 0)\r\nBEGIN\r\n-- print @MyCandidateColumn\r\n\r\nif object_id('tempdb..##TempColumnFound') IS NOT NULL\r\nDROP TABLE ##TempColumnFound\r\n\r\n-- select * from tempdb.sys.tables\r\n\r\nselect @MySQL = 'select count(1) as MyCount into ##TempColumnFound from ' + @SchemaName + '.' + @TableName +\r\n' where ' + @IDFieldName + ' = ' + cast(MyID as varchar) +\r\n' and ' + @MyCandidateColumn + ' <> 0 ' +\r\n' and ' + @MyCandidateColumn + ' IS NOT NULL ;'\r\nfrom ##TempBestID; -- This will exist at runtime\r\n\r\nexec sp_executesql @MySQL\r\n\r\nselect @MyGoodCount = MyCount from ##TempColumnFound;\r\n\r\nif @MyGoodCount > 0\r\nBEGIN\r\nselect @ColumnsRemaining = @ColumnsRemaining - @MyGoodCount; -- Which should be an if statement...\r\ndelete from #TempMyColumns where #TempMyColumns.column_name = @MyCandidateColumn\r\n-- print 'Deleting ' + @MyCandidateColumn + ' - Columns Remaining = ' + cast(@ColumnsRemaining as varchar);\r\nEND\r\n\r\nFETCH NEXT FROM MyColumnCursor into @MyCandidateColumn\r\n\r\nEND\r\n\r\nCLOSE MyColumnCursor\r\nDEALLOCATE MyColumnCursor\r\nEND\r\n\r\nEND\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"