Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
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... –  Aaron Bertrand Apr 30 '12 at 19:05
add comment

3 Answers

You can do this pretty easily using SUM and CASE:

SELECT 
    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.

share|improve this answer
    
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 '12 at 9:12
add comment

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:

SELECT T.*, 
       CASE 
          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, 
       CASE 
          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;

Alternatively:

SELECT T.*, 
       CASE 
          WHEN NOT ( LEFT(Barcode, 1) <> 0 ) THEN '{{violates constraint}}'
          ELSE '{{satisfies constraint}}'
       END AS result__Barcode__first_char_cannot_be_0, 
       CASE 
          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!

share|improve this answer
add comment

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...

share|improve this answer
    
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 '12 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 '12 at 18:37
2  
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. –  Aaron Bertrand Apr 30 '12 at 18:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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