I am have a table with about 100,000 rows.

Lets suppose the primary key is some sequence of numbers like a barcode for example.

I am doing checks like

SELECT count(*) FROM TABLE WHERE LEFT(Barcode, 1)  is 0

I have about a 100 more rules to implement. They should ALL return 0 rows. But how do I ensure that they are returning 0 rows because the data is in fact correct OR that it is returning 0 rows because of a poorly written query.

I know that all my queries should return 0 since there are constraints that prevent bad data from getting in the first place but this is still needed by the company.

  • 4
    How do you expect to verify it? If the original query is "poorly written", couldn't any second query also introduce similar problems? Same goes with manual inspection methods... Apr 30, 2012 at 19:05

3 Answers 3


You can do this pretty easily using SUM and CASE:

    COUNT(*) as 'Total Rowcount',
    SUM(CASE WHEN LEFT(BARCODE, 1) = 0 THEN 1 ELSE 0 END) as 'Leading Zero Barcode',
    SUM(CASE WHEN LEFT(Foo, 1) = 'X' THEN 1 ELSE 0 END) as 'Leading X in Foo'

Then you get a number for the WHOLE table/query, and a breakdown of each category, all on the same line. It'll be easy to see if you have a bad query since you'll get a low/high rowcount or something else.

This has the added advantage or processing more efficiently than a bunch of separate queries, since you just evaluate the whole table once.

  • I don't think this approach is very useful. Say the results of the query is equivalent to ROW ( 2, 1, 1 ) -- either both rows violate one constraint each or one row violates both constraints but you can't say which is the case. And you know that once you tell someone there are two failures across two failure modes the next task will be, show me the bad data!
    – onedaywhen
    May 1, 2012 at 9:12

Take care with nulls and never forget that SQL's three-valued logic is treats constraints differently i.e.

P(row)  | WHERE P(row) | CHECK(P(row))
TRUE    | Row accepted | Row satisfies constraint
FALSE   | Row rejected | Row violates constraint   
UNKNOWN | Row rejected | Row satisfies constraint   <-- careful here!  

My personal approach is the write a SELECT * FROM query and add columns returning a ternary value for each constraint:

          WHEN     ( LEFT(Barcode, 1) <> 0 ) THEN 'TRUE'
          WHEN NOT ( LEFT(Barcode, 1) <> 0 ) THEN 'FALSE'
          ELSE 'UNKNOWN'
       END AS result__Barcode__first_char_cannot_be_0, 
          WHEN     ( LEFT(Foo, 1) <> 'X' ) THEN 'TRUE'
          WHEN NOT ( LEFT(Foo, 1) <> 'X' ) THEN 'FALSE'
          ELSE 'UNKNOWN'
       END AS result__Foo__first_char_cannot_be_X
 FROM YourTable AS T;


          WHEN NOT ( LEFT(Barcode, 1) <> 0 ) THEN '{{violates constraint}}'
          ELSE '{{satisfies constraint}}'
       END AS result__Barcode__first_char_cannot_be_0, 
          WHEN NOT ( LEFT(Foo, 1) <> 'X' ) THEN '{{violates constraint}}'
          ELSE '{{satisfies constraint}}'
       END AS result__Foo__first_char_cannot_be_X
 FROM YourTable AS T;

These results can be used for further analysis e.g. tally failure modes, tally failures per failure mode, (tally of) rows that satisify all constraints, (tally of) rows that fail at least one constraint, etc.

Consider using a unit test framework such as tSQLt to add data that would otherwise violate constraints that are actually in force (FakeTable command in tSQLt): if your query doesn't highlight this data then you need to rewrite your query!

p.s. ensure you do not waste time testing the DBMS itself. If the constraint is declared and in force (not deferred, not in NO CHECK mode, etc) then you should trust the DBMS. By all means query the INFORMATION SCHEMA to ensure an expected key exists on the required columns but there is simply no point in testing that the values comprising the key are actually unique: if they weren't then the DBMS would have a serious bug and we'd all know about it!


Your question is a little vague, but would two queries per rule get you there? For your example, perhaps make sure the count returned without the WHERE criteria is greater than 0?

Also, the code you have provided is incorrect. Not sure if you meant = instead of is or quotes around the 0 or what...

  • It was somewhat pseudocode. I prefer to do it in one query but can create a seperate script to do another set. Or maybe at the bottom. Is there any other way
    – masfenix
    Apr 30, 2012 at 18:34
  • You can pack whatever you want into a where statement to do multiple "tests" in a single query. ie select 'this passes' where (select count(/*) from table where left(barcode,1) = 0) and (select count(/*) from table > 0) (pseudocode ;) ) (had to use slash star instead of star)
    – JHFB
    Apr 30, 2012 at 18:37
  • 3
    Use the back-tick ` to show code samples, including select * - though in general code snippets shouldn't be in comments. Even with mini-markdown they're very cumbersome to read. Apr 30, 2012 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.