SQL: Is it possible to do TDD when working on SQL queries/views/etc.?

Being quite used to TDD in a Java environment I was wondering if TDD was also used when building SQL queries, creating views, etc.

If it is also used, how do people do it? You would need to create a lot of tiny DBs (for each unit test basically)?

I don't know if my question makes much sense but still: TDD is considered by quite some as the thing that chan开发者_JAVA百科ged the way they develop software and there definitely is development going on at the "SQL" level. So how does TDD and DB / SQL mix?

In the Rails world, you have a dedicated test database which you build each time you run your test suite through migrations, and fixtures for prepopulating your database prior to running tests.

This makes TDD against a database entirely possible.

I think the main reason TDD is not used by database developers is more cultural than anything else.

I don't think it is as common in the Database world.

The question to ask is: "what am I testing here?"

It can be done as you suggest, or by starting a transaction, populating tables with guaranteed initial known states, running your code under test and then rollback. But ultimately these tests tend to be data centric and brittle to DB changes: one schema change could break alot of tests and create alot of work.