When using the Query Data activity in Workflow, does it use SELECT * instead of specifying each column?
Yes, it uses SELECT *
Do you know why it doesn't list out each column instead?
The "Query Data" activity just retrieves all columns from the specified table. You only configure the criteria that determines which rows it returns, i.e. the "where" part of the SQL statement. Therefore, it just uses SELECT *
Is there a reason you're thinking it should do it by using SELECT col1, col2, col3... listing all columns in the table instead of just SELECT * ?
Our DBA's and most articles I've read say that it is best practice to not use SELECT * and list out all of the columns instead. There are a lot of reasons to not use SELECT *, but in the specific case of the Query Data activity it makes it harder to use SQL Profiler and can mess with the SQL optimizer to help troubleshoot and improve queries.
Knowing that the Query Data activity uses SELECT * we will probably work on switching all query activities to use the Custom Query activity instead, so we have more control. Thank you for the confirmation Alex.
You're welcome. Keep in mind the use cases for these activities as well. If SQL query optimization is going to be a concern, then that's a good reason to use the "Custom Query" activity where you can tune your SQL query exactly how you want. The "Query Data" activity is supposed to be an easy to use activity where you just select a table and configure some basic criteria for what rows you want back.
Yep, that all makes sense. Just needed confirmation that's how it worked.