Writing SQL Queries

Some best practices...

SQL Queries used within Databoxes and Dynamic Lists are provide you with very powerful features and gives read-only access to a wealth of information that you may wish to use within your scripts. However, using all this power requires some thought and care!

Example SQL Query Databox

Always insert a comment and maintain the revision history.

At some point someone will need to know more about this query, what is does, it's origins, any changes, why it's broken (who broke it!) etc and having adequate comments can save a lot of head scratching.

Never embed a database name in any SQL Queries, i.e. the above query should read:

Don't include the database name itself!

Embedding the database name in the query is unnecessary and will cause problems when moving between environments, e.g. consider what happens when your system is copied into a Test environment and needs to access a database named SQLActiveH_Test; all queries that point at SQLActiveH will either fail or retrieve incorrect data! You may experience other problems related to permissions. So, take heed of the following point...

Always select the Database you wish to use from the dropdown list.

If this doesn't show the required database, please do not insert a database name in the query, rather, contact Support and they will make the necessary arrangeents to add to the list.