Primarily I've used Database.query (Dynamic SOQL) when:
- I'm creating a managed package and don't want to take an explicit dependency on a feature. E.g.
List<sobject> revenueSchedules = Database.query('Select Id from OpportunityLineItemSchedule where OpportunityLineItemId in :oliIds'); If I did this in static SOQL all the installers of the managed package would need Product Schedules enabled.
- I want to dynamically include an optional field or filter in the SOQL query. A custom setting may define the name of a field that should be used to populate something else. As it isn't know at "compile" time it needs to be dynamic.
Otherwise I'd generally use static SOQL to cut down on ceremony code.
As the other answers and comments point out, dynamic SOQL limits your binding options. You can do simple bindings, such as checking if an Id is in a Set. However, you can't do more complex bindings that traverse properties. Instead you will need to convert these to strings. See Salesforce Ideas: Dynamic SOQL binding expression support to match static SOQL
Note that you need to be more careful with dynamic SOQL that you don't open yourself up to SOQL Injection.