title: What best practices do you use for testing database queries?
body: <p>I'm currently in the process of testing our solution that has the whole "gamut" of layers: UI, Middle, and the omnipresent Database.</p>
  
  <p>Before my arrival on my current team, query testing was done by the testers manually crafting queries that would theoretically return a result set that the stored procedure should return based on various relevancy rules, sorting, what have you.</p>
  
  <p>This had the side effect of bugs being filed against the tester's query more often than against the actual query in question.</p>
  
  <p>I proposed actually working with a known result set that you could just infer how it should return since you control the data present -- previously, data was pulled from production, sanitized, and then populated in our test databases.</p>
  
  <p>People were still insistent on creating their own queries to test what the developers have created.  I suspect that many still are.  I have it in my mind that this isn't ideal at all, and just increases our testing footprint needlessly.</p>
  
  <p>So, I'm curious, which practices do you use to test scenarios like this, and what would be considered ideal for the best end-to-end coverage you can get, without introducing chaotic data?</p>
  
  <p>The issue I have is where's the best place to do what testing.  Do I just poke the service directly, and compare that dataset to that which I can pull from the stored procedure?  I have a rough idea, and have been successful enough so far, but I feel like we're still missing something important here, so I'm looking to the community to see if they have any valuable insights that might help formulate my testing approach better.</p>