How to Run SQL Queries: A Complete Guide for Database Developers and DBAs

The process of writing and running SQL queries is the cornerstone of database development. A wide range of users, from software developers and DBAs to analysts and managers, need to retrieve, update, and analyze data on a regular basis. What's more, they need to do it safely and effectively, with minimum effort and drastically reduced risks of mishaps like accidental data loss.

In this article, you'll learn all about writing and executing SQL queries in the easiest possible way. Methods, troubleshooting tips, and best practices await you. That said, let's get started with the basics.

Different methods to run SQL queries

There are several methods, or, should we say, tools that will help you write and run queries against your databases. Regardless of the database system you are dealing with (e.g., Microsoft SQL Server, MySQL, MariaDB, PostgreSQL, or Oracle Database), you generally have three options: to employ a stock GUI tool for a given database system (such as SSMS for SQL Server or Workbench for MySQL); to empower yourself with a more advanced alternative (such as dbForge Edge); or to use the command line, if that's what you prefer.

The pros of using GUI tools are obvious. You don't need to have any technical background or experience with command-line tools. Instead, you have an intuitive interface and a bunch of wizards ready to walk you through every possible use case and show you every possible option.

But still, there is a major difference between using a free stock tool and a paid commercial one. It's all in the functionality. Surely, SQL Server Management Studio is a reliable, regularly updated piece of software that gives you the essentials for writing a query, running it, and exploring the output. However, it will not provide you with some of the most advanced context-aware code completion, versatile formatting, and smart debugging features offered by the competing dbForge Edge. Stock tools will always leave you wanting more - that's why we have so many SSMS add-ins on the market.

On the other hand, commercial database solutions are your optimal investment to ensure the ease, convenience, and safety of your work. For instance, here are some of the features that have the goal of making your work with queries as quick and painless as possible.

• You will code twice as fast with the already mentioned code completion. You can even relegate the initial coding to an AI copilot and then adjust its output, if necessary.
• Alternatively, you can build queries involving multiple tables, JOINs, conditions, and subqueries on visual diagrams, with no manual coding whatsoever.
• You will format your SQL code in just a couple of clicks. After all, clean SQL is easy to read, debug, and maintain, which is great news for long-term database management.
• You will be able to debug stored procedures, triggers, functions, and scripts with next to no effort.
• You will get everything to analyze and optimize the performance of your queries, either manually or via AI.
• If there's an unwanted deletion or modification of your data, there's always a backup that can be restored in a matter of moments.

You'd like to have all of these at hand, wouldn't you? But when it comes to free tools, chances are that you'll either find these features limited in some way - or simply absent. Meanwhile, commercial tools can't allow themselves to lag behind the competition and thus provide you with the best firepower you can get.

The third method involves the command line, and it's mostly favored by seasoned developers, DBAs, engineers, and other enthusiasts who are well-acquainted with all the advantages and limitations of this method.

Now, let's take a closer look at each of these methods.

Running SQL queries with dbForge tools

We'll start with the simplest method, which happens to be the most convenient and advanced at the same time. It involves dbForge Edge, our multidatabase solution that comprises four IDEs called Studios, each dedicated to a particular database system - SQL Server, MySQL, Oracle, and PostgreSQL.

For our example, we'll take dbForge Studio for SQL Server and show you how easy it can be to manage your queries. The general workflow is as follows.

Looks easy, doesn't it? It's even easier in practice. Launch the Studio and open SQL Editor. You can do it either from the SQL Development tab of the Start Page or using the New SQL button on the toolbar.

SQL Editor is your primary workspace for writing and running queries. Context-aware code completion will make smart suggestions to help you do the job as fast as possible.

You will also be able to apply instant formatting with a predefined or custom profile.

After you enter your query, you can hit Execute, and the Studio will give you the output that can be explored further.

That said, we can't help but invite you to give it a go yourself. Download dbForge Edge for a free 30-day trial, pick the Studio for the database system of your choice, and feel free to explore its vast capabilities, which include a rich variety of additional query management tools we have previously mentioned, including, but not limited to visual query building, query analysis and optimization, and debugging. It is also worth noting that each Studio is available in a completely free Express edition that offers basic query execution and code completion.

Running SQL queries in SQL Server Management Studio (SSMS)

SSMS is the default tool for developing SQL code and managing SQL Server databases. The workflow will be similar to that of dbForge Edge, except you won't get the same level of code completion and formatting capabilities, let alone more advanced stuff like debugging or AI-powered context-aware generation and optimization of SQL code. Still, you get all it takes to write, run, and receive the output of your SQL queries in a similar way.

Running SQL queries using the command line

To run an SQL query against a SQL Server database from the command line, you can use the sqlcmd utility. It's a stock command-line tool by Microsoft that usually comes with SQL Server. The basic syntax for connecting to a SQL Server instance and running a query is as follows.

sqlcmd -S <server_name> -d <database_name> -U <username> -P <password> -Q "<SQL_query>"

Here's an example that lists all rows from a table.

sqlcmd -S localhost -d Sales -U sa -P your_password -Q "SELECT * FROM Customers;"

If you need to run a query from a .sql file, you can easily do it.

sqlcmd -S localhost -d SalesDB -U sa -P your_password -i C:\scripts\query.sql

You can find similar tools for other database systems, for instance, the MySQL Command-Line Client.

Running SQL queries with third-party tools

Finally, we should definitely mention other third-party GUI tools of varying functionality (such as DataGrip or DBeaver) with a roughly similar workflow for SQL coding and execution. You can check our feature-by-feature comparison of these tools with dbForge Edge to see which one is the best fit for your particular needs.

Best practices for running SQL queries

Now that you know the ways to handle your SQL queries, we can proceed to best practices that will help you in your daily work with queries.

Optimizing SQL queries for performance

Here are the ways to achieve maximum performance for your queries.

• Create indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses. Use covering indexes when possible to include all columns needed by a query. Yet, at the same time, you should avoid over-indexing, which can slow down your INSERT, UPDATE, and DELETE operations.
• Avoid SELECT *. It's always better to specify only the columns you need. This reduces data transfer, memory usage, and improves index efficiency.
• Avoid complex and correlated subqueries. Use JOINs or common table expressions (CTEs) instead, where possible.
• Analyze execution plans to identify costly operations like table scans, hash joins, or missing indexes.
• Break large INSERT, UPDATE, or DELETE operations into smaller batches to reduce locking and log size.
• Apply filtering (WHERE clauses) as early as possible to reduce the dataset size.
• Use JOINs efficiently. Prefer INNER JOINs over OUTER JOINs when possible. Make sure joined columns are indexed. Reduce row counts before joining, if possible (you may use CTEs or subqueries to carry out filtering first).
• Periodically rebuild or reorganize fragmented indexes. This is one of the vital factors that helps improve query performance.
• Use dbForge AI Assistant to generate ideas for query optimization. The AI Assistant can analyze your SQL queries, suggest performance improvements, and even rewrite them to ensure optimal execution, saving you time and effort.

Using transactions and batch processing

Running SQL queries within transactions or batch processes is crucial for maintaining data consistency, integrity, and reliability, especially when executing multiple related operations.

The trick is simple. Transactions ensure that a group of SQL operations either all succeed or all fail. This prevents data corruption or partial updates and ensures adherence to the ACID principles. Here are several tips for working with transactions.

• Always handle errors or exceptions to decide whether to COMMIT or ROLLBACK.
• Keep transactions short and fast to minimize locking and blocking.
• Avoid user interaction inside transactions (e.g., waiting for input).
• Use TRY...CATCH (in SQL Server) or similar control structures for better error handling.

Avoiding long-running queries and deadlocks

Timely identification of long-running SQL queries is essential to maintaining fast, responsive applications and preventing locking, blocking, and performance degradation in your databases. Here's how you can detect, understand, and fix these problematic queries.

• Monitor the performance of your databases.
• Enable slow query logging to log queries that exceed a certain time threshold.
• Analyze query execution plans (EXPLAIN, EXPLAIN ANALYZE, or SHOW PLAN) to see how the database engine executes the query. Look for full table scans, missing indexes, and nested loop JOINs on large datasets.
• To avoid using long-running queries, you can optimize them.
• Set query timeouts to prevent runaway queries from consuming too many resources.

Using proper query formatting and comments

Well-formatted code is highly readable, maintainable, and generally easy to work with, which is a definite must in teamwork, when several people may work with the same code. After all, this query...

SELECT name,email FROM users WHERE status='active' AND created_at >= '2024-01-01' ORDER BY created_at DESC;

...looks much better like this:

SELECT
    name,
    email
FROM
    users
WHERE
    status = 'active'
    AND created_at >= '2024-01-01'
ORDER BY
    created_at DESC;

You will find a convenient formatter among the coding assistance tools of each individual Studio included in dbForge Edge. There are lots of useful goodies to be found there, including a library of predefined and custom formatting profiles, formatting on paste, and single-click formatting.

For the same purpose, you can as well use comments within your queries to explain complex logic, which makes your code easier to understand and modify. Here is an example.

SELECT
    c.customer_id,
    c.name,
    o.total_amount
FROM
    customers c
JOIN orders o
    ON c.customer_id = o.customer_id
    -- Only include completed orders
    AND o.status = 'completed'
WHERE
    c.signup_date >= '2023-01-01'
    -- Target customers acquired after new campaign launch
ORDER BY
    o.total_amount DESC;

Risks of running SQL queries: The dangers of accidental data deletion

Direct execution of SQL queries (especially on production or sensitive data) comes with quite tangible risks. If not written and reviewed carefully, even a single executed query can cause irreversible data loss, performance issues, or security breaches. For instance, if you run a DELETE query without a WHERE clause, you may delete all rows in your table. TRUNCATE or UPDATE queries should also be treated with precision.

We can suggest the following tips to help you avoid these troubles.

• Always double-check queries before execution. It is quite obvious but still worth reiterating.
• Wrap multi-step operations in transactions for better safety.
• Avoid running queries without having set up regular backups (dbForge Edge has that feature firmly in place).
• Give read-only access to users who don't need write permissions.
• Don't hesitate to write clear and strict WHERE clauses.
• Enable query logging and audit trails.

By the way, if you are using dbForge Edge, you will be assisted by execution warnings, thrown whenever you attempt to execute DELETE, DROP, UPDATE, and TRUNCATE statements without the WHERE clause. Features like this add an extra layer of security that shouldn't be neglected.

Troubleshooting common issues when running SQL queries

So, are there any other issues that we haven't covered yet? Let's have a look.

Common SQL syntax errors

First and foremost, there can be syntax errors that can prevent queries from running or lead to unexpected results. These may include the following:

• Missing commas between columns
• Missing semicolons in multi-statement scripts
• Mismatched or missing parentheses
• Incorrect keyword usage or order
• Incorrect string quotes
• Ambiguous, missing, or incorrectly used table aliases

Still, none of these is a problem with dbForge Edge at hand. Its coding assistance tools deliver instant syntax validation that highlights syntax issues. Additionally, you can use dbForge AI Assistant to pinpoint and explain errors in your queries.

Resolving connection issues

Next, we should say a few words about connection issues, ranging from configuration errors to network-level restrictions.

• Incorrect credentials, which come with error messages like Access denied for user, Login failed for user, or Authentication failed, are typically fixed by checking whether your username and password have been entered correctly. You may also need to make sure the database name is correct as well, since some systems validate access at the database level. Additionally, make sure you have chosen the correct authentication method (e.g., password vs. integrated security).
• Wrong hostname/port is typically associated with connection timeouts, inability to reach the host, and DNS resolution errors. To fix this, make sure your hostname/IP is correct, your port matches the configuration of your database server (e.g., 5432 for PostgreSQL, 3306 for MySQL), and you are using the correct connection string format.
• Firewall/network block may show up when database access works locally but fails remotely. To address this issue, check local firewall rules (e.g., Windows Defender, ufw), cloud security groups (e.g., AWS SGs, Azure NSGs), or your corporate VPN restrictions. You may also use ping to test host reachability.
• Database server not running is yet another problem distinguished by refused connection. To fix this, make sure your database service is running (e.g., sudo systemctl status postgresql for PostgreSQL and sudo systemctl status mysql for MySQL).
• Insufficient privileges manifest themselves when your login succeeds, but your queries fail with the permission denied error. Here, everything is simple: you need to either log in under a user with sufficient privileges or get those privileges for your current user from an admin.
• SSL or encryption issues are handled by selecting the correct SSL mode and retrieving valid certificates (especially with managed databases like AWS RDS).

Handling query timeouts

SQL queries can take a long time to execute, especially on large volumes of data. This may happen due to inefficient logic, missing or inefficient indexes, or high resource usage caused by large dataset scans. These slow queries can result in timeouts, causing applications to fail or hang.

To handle these issues, feel free to use the best practices described above.

Identifying and fixing data type mismatches

Data type mismatches in SQL occur when the type of a column doesn't align with the type of the value used in a query. These mismatches can lead to errors, unexpected results, or performance issues, especially in filtering, joining, or inserting data.

The most common examples of mismatches include the following:

• Comparison of strings with numbers
• Comparison of strings with dates
• Attempt to join columns of different types
• Insertion of mismatching data types

The solutions for handling data type mismatches are quite simple.

• Verify data types before writing and executing queries
• Use explicit type casting
• Avoid implicit casting in filters and JOINs, since implicit casts prevent index usage and slow down your queries

Conclusion

Now that you know all about running and optimizing SQL queries, we can gladly invite you to get the best toolset to accompany you in this journey. Download dbForge Edge for a free 30-day trial, give it a go, and see how easy it is to manage multiple database systems with the ultimate development and management suite at hand.

FAQ