I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.
In this part I’m talking about a really useful feature for the SQL developer: the query history. In SQL Server, you can retrieve queries executed against a database by querying the data management views (DMVs). This is my favorite StackOverflow answer: Recover unsaved SQL query scripts. Because how many times has it happened that you closed a query tab (or SSMS crashed) and you needed to retrieve that query? Too many times. To be fair, the latest versions of SSMS keep history of your open tabs in case SSMS crashes and they will show the recovered tabs once you re-open SSMS. But if you closed a tab yourself, you have to resort to the DMV. The only problem is that the DMVs are reset every time the SQL Server instance is restarted. So if you’re wondering what that ad-hoc query was you wrote last week? You might not find it again.
That being said, there are also 3rd party vendors that can help you with this. For example, SQL Toolbelt by Redgate keeps an excellent history of your tabs, even the ones you closed yourself. A lifesaver. Keep in mind there’s a difference between tab history and query history. If you overwrite your query in a tab with a new query, even Redgate can’t save you.
How does Snowflake handle all this? They keep a query history for all queries issued against the account. It’s right there in the menu bar:
In the history, you can view all the queries, alongside with other information such as user, rows/bytes read, the warehouse used, start and end times, and the duration. You can filter on most fields to search in the history:
You can scroll down to go back further in time, but eventually this stops. However, you can also query an “account usage view” (similar to the DMVs in SQL Server) to get all the history (up to 1 year back):
Even better, every tab has its own little history window:
If you open it, you get a condensed view of the history of all the queries you executed in that tab:
If you want to retrieve the SQL query from the history, you click on the query ID, which will take you to this view:
Nice.