1. Overview

Importing an SQL file is a basic operation for any MySQL user to restore a database from a backup, transfer data, share a database schema or configure a new environment. Although Graphical User Interface (GUI) applications such as phpMyAdmin are available, command line usage provides more control, efficiency, and reliability, especially for big files or scripted operations.

In this tutorial, we’ll discuss commonly used command-line approaches to import a .sql file.

2. Using Shell Redirection

The most straightforward method is employing the redirection operator of the shell (<). This method redirects the contents of the SQL file directly to the mysql client program from the command line. Below is a one-line command to import the SQL file:

$ mysql -u [username] -p [database_name] < /path/to/file.sql

The above command prompts to enter the password for the specified MySQL user. It’s crucial to have the target database already created before running this command, or else, the import fails.

The primary advantage of this method is its simplicity. It’s a quick, single-line command that is easy to use in scripts. For small to medium-sized files, it’s highly efficient. But, if we’re importing a large file over a network, this method can sometimes be slower due to network latency, as it streams each query to the server one by one rather than as a single block.

3. Using the source Command

The second method is to log in to the MySQL command-line client first and then use the source command. It has the advantage of offering an interactive session, with more control prior to starting the import:

$ mysql -u [username] -p [database_name]

When we’re logged in, we’ll get the mysql> prompt. Using this prompt, we can execute the source command using the path to the SQL file:

mysql> source /path/to/file.sql;

Again, we should check that the database [database_name] already exists before we start the process. The source command offers an immediate feedback, which generally displays the output of each command being run from the SQL file. This can be very reassuring to see during a large import.

MySQL, by default, executes with autocommit turned on. That is, every individual SQL statement, like an INSERT or an UPDATE, is executed as its own transaction and is automatically committed to the database right away. For a big SQL file containing thousands of statements, this leads to an enormous number of individual transactions, each of which takes a log flush to disk. This constant disk I/O is one of the major performance bottlenecks.

The below command snippet first selects the appropriate database and then imports the SQL file by treating it as a single transaction:

mysql> use db_name;
mysql> SET autocommit=0 ; source file.sql ; COMMIT ;

In the above command, we first disable the autocommit. By doing so, we ensure that all these statements are buffered and not written to disk immediately. All the updates from the SQL file are committed to the database in a single, bulk operation with the COMMIT statement. It needs only one log flush to disk, not one for each individual statement, which is where the huge performance improvement comes from.

One of the big drawbacks to turning off autocommit is that the import is now an “all-or-nothing” process. If one of the statements in the SQL file fails, the whole transaction will be rolled back.

4. Conclusion

In this article, we compared various means of importing an SQL file from the command line in MySQL. For imports of small to medium-sized files, the redirection method is a great option for its simplicity. For large files, imports from a less stable network, or when we need more interactive feedback and control, the source command is the more secure and powerful choice.

     

Related Stories

• How to Configure Time Zone Settings in MySQL?
• Introduction to Apache Doris
• Introduction to CrateDB

 

1. Introduction

In this article, we’ll talk about CrateDB. CrateDB is an open-source, distributed SQL database designed for storing and running high-speed analytics on large volumes of data.

CrateDB combines the benefits of a SQL database and the scalability and flexibility that NoSQL databases provide.

In the following sections, we’ll install and run CrateDB. We’ll also delve into the features that make CrateDB a viable and lucrative database option.

2. Installing CrateDB

We can install and run CrateDB locally. It supports different operating systems, including Linux-based distributions, Mac OS, Windows, and even Docker.

Additionally, there is a cloud-based offering called CrateDB Cloud, a fully managed service that does not require any installation.

2.1. Installation in Linux

Let’s install CrateDB on Debian or Ubuntu machines:

# Install prerequisites.
sudo apt update
sudo apt install --yes apt-transport-https apt-utils curl gnupg lsb-release
# Import the public GPG key for verifying the package signatures.
curl -sS https://cdn.crate.io/downloads/debian/DEB-GPG-KEY-crate | \
    sudo tee /etc/apt/trusted.gpg.d/cratedb.asc
# Add CrateDB repository to Apt
echo "deb https://cdn.crate.io/downloads/debian/stable/ default main" | \
    sudo tee /etc/apt/sources.list.d/crate-stable.list
## Install
sudo apt install crate

Once the installation is complete, we need to restart the service:

sudo systemctl start crate

2.2. Installation in Windows

For Windows machines, we need to download the release archive. Then we extract the archive using a tool like 7-Zip. Now, let’s open PowerShell and run:

unzip -o crate-*.zip
cd crate-*
./bin/crate

When the installation is complete, the web Admin UI of CrateDB will be accessible at http://localhost:4200.

3. Running CrateDB

Now that CrateDB is installed, let’s get started with using it. CrateDB has two main components, the Admin UI and the CrateDB Shell CLI(Crash).

3.1. Admin UI

The Admin UI is a great interactive way to use CrateDB and comes with several useful tabs and a status bar.

The Status bar shows information essential to the running CrateDB node, including the Cluster name, the version of the CrateDB that is running, and the total number of nodes in the cluster.

This also contains additional health checks for Data and Cluster status.

On the left panel, there are several tabs of the following:

• Overview screen
• SQL console
• Table browser
• Views browser
• Shards browser
• Cluster browser
• Monitoring

Let’s check how the Admin UI looks with zero configurations done:

3.2. Crash(Crate DB Shell)

The Crate DB shell is an interactive CLI application that works with CrateDB, and we can use it like a SQL terminal and node manager.

Crash needs to connect to the CrateDB cluster to work:

crash --hosts node1.example.com \
                node2.example.com \
        -c "SELECT * FROM sys.nodes" \
        --format json \
    > output.json

Crash supports several built-in commands, and a comprehensive list of such is present in the documentation page.

4. Dynamic Data Handling and SQL Support

In this section, let’s talk about how CrateDB handles data internally.

4.1. Data in CrateDB

CrateDB is columnar. It therefore supports dynamic schemas and data structures that change on the fly without downtimes. This allows for storing and querying structured, semi-structured data, and unstructured data, such as raw JSONs, very easily.

While traditional object-relational databases allow us to store and process JSON data only opaquely, CrateDB handles objects as first-level citizens.

With such dynamic objects, when no strict schema is defined, CrateDB indexes all attributes by default, therefore allowing for efficient querying.

CrateDB uses Apache Lucene as its underlying storage layer. Therefore, it inherits its concepts about storage entities and units similar to Elasticsearch.

Let’s consider the following example. We create a table for storing weather information with the following:

CREATE TABLE weather_reading ( "timestamp" TIMESTAMP, "readings" OBJECT(DYNAMIC), "fields" OBJECT(DYNAMIC) );

Let’s perform a DML query on the table:

INSERT INTO weather_reading ( timestamp, readings, fields ) VALUES ( '2025-07-16T00:00:00', { "site_id" = 007 }, { "temperature" = 21.92, "humidity" = 100 } );

Now, we can leverage the power of CrateDB to efficiently query the dynamic data:

SELECT
  *
FROM
  reading
WHERE
  readings['site_id'] = 
  '007';

4.2. SQL Support

There is native support for standard SQL baked into CrateDB. This includes joins, subqueries, indexing, and aggregations. This makes it very easy to integrate with existing SQL-based clients. CrateDB uses the PostgreSQL wire protocol and is therefore compatible with many PostgreSQL tools and clients.

An automatic advantage that comes out of this is that integration with systems using traditional database access interfaces like ODBC or JDBC is much simpler.

4.3. Search in CrateDB

CrateDB offers native BM25 term search and vector search, all while using SQL.

This enables powerful single-query hybrid search over massive volumes of data.

Additionally, there is full-text search functionality with an inverted index and Okapi BM25 search ranking at scale.

CrateDB also natively supports Geo-search and semantic search on Vector embeddings through HNSQ data retrieval.

5. Distributed Architecture

CrateDB is natively distributed. It’s well-suited for large-scale distributed systems and employs a shared-nothing architecture. Therefore, data is automatically sharded across all available nodes, which makes it highly available and fault-tolerant.

With support for horizontal scaling out of the box, new nodes can be easily added and removed.

Additionally, a distributed query execution engine powers CrateDB that supports running ad-hoc SQL queries on billions of records in milliseconds. This makes CrateDB ideal for real-time aggregations and operational analytics.

6. Performance and Scalability

CrateDB provides scalability through partitioning, sharding, and replication.

CrateDB automatically splits tables into shards, which are self-contained chunks that hold a portion of the data. These shards are then distributed across nodes in the cluster, each backed by a dedicated Lucene index.

It should be noted that choosing the right number of shards depends on our data volume, query patterns, and hardware setup.

We can further organise data using partitioning, which splits tables based on defined columns. Each partition is essentially a collection of shards, making queries faster by narrowing the data scope.

To ensure fault tolerance and improve performance, CrateDB lets us replicate table shards across nodes. Replication not only provides redundancy but also boosts read performance by enabling more parallel query execution.

7. Conclusion

In this article, we discussed CrateDB and its powerful features and dynamic data handling capabilities, which make it a great alternative to traditional databases. It combines the benefits of SQL and NoSQL databases. We also discussed the distributed nature of CrateDB, making it suitable for efficiently handling large volumes of data with ease.

Finally, we saw the advanced search capabilities across multiple domains that come out of the box with CrateDB. CrateDB also provides excellent performance and scaling benchmarks.

     

Related Stories

• Import an SQL File Using the Command Line in MySQL
• How to Configure Time Zone Settings in MySQL?
• Introduction to Apache Doris

 

1. Introduction

Unlike table size checks, monitoring the size of a database is an essential task for administrators and developers to ensure efficient storage management and optimal performance.

In this tutorial, we’ll explore how to use the built-in tools and system views of SQL Server and PostgreSQL, which enable us to quickly get a snapshot of the size of a single database or all databases in an instance.

2. How to Check Database Size in PostgreSQL?

PostgreSQL provides built-in functions that make retrieving the database size fairly trivial.

Here, the query retrieves the size of the specific PostgreSQL database named university, showing it as 294 MB:

# SELECT pg_database.datname AS "Database", size_pretty(pg_database_size(pg_database.datname)) AS "Size" FROM pg_database WHERE datname = 'university';
   Database    |  Size   
---------------+---------
 university    | 294 MB
(1 row)

Also, the pg_database_size function returns the size in bytes, whilst pg_size_pretty makes it human-readable (e.g., in MB, GB).

Furthermore, we can list the sizes of all databases on the server, ordered from largest to smallest via ORDER BY:

# SELECT datname AS "Database", pg_size_pretty(pg_database_size(datname)) AS "Size" FROM pg_database ORDER BY pg_database_size(datname) DESC;
   Database    |  Size   
---------------+---------
 gateway       | 543 MB
 university    | 294 MB
 apiserver     | 288 MB
 ...
 ...
 ...
 data_archival | 8436 kB
(11 rows)

From the output, we can see that gateway is the largest database at 543 MB, followed by university and apiserver. On the other hand, the smallest database is data_archival with 8436 kB in size.

3. How to Check Database Size in SQL Server?

Similarly, SQL Server provides built-in functions to check database sizes.

To begin with, we can use the sp_spaceused procedure, which returns the total size and unallocated space of the current database:

# EXEC sp_spaceused;
 database_name   |  database_size  | unallocated space
-----------------+-----------------+-------------------
  university     |    294 MB       |    121 MB

Alternatively, the sys.master_files can show the university database size and report its total size in MB:

# SELECT DB_NAME(database_id) AS [Database], CAST(SUM(size) * 8 / 1024 AS VARCHAR(20)) + ' MB' AS [Size] FROM sys.master_files WHERE DB_NAME(database_id) = 'university' GROUP BY database_id;
   Database    |  Size   
---------------+---------
 university    | 294 MB

Next, to see the sizes of all databases in the SQL Server instance, we can join sys.databases with sys.master_files:

# SELECT d.name AS [Database], CAST(SUM(mf.size) * 8 / 1024 AS VARCHAR(20)) + ' MB' AS [Size] FROM sys.databases d JOIN sys.master_files mf ON d.database_id = mf.database_id GROUP BY d.name ORDER BY SUM(mf.size) DESC;
   Database    |  Size
---------------+---------
 gateway       | 543 MB
 university    | 294 MB
 apiserver     | 288 MB
 ...
 ...
 ...
 data_archival | 8436 kB

Typically, built-in functions like sp_spaceused provide the easiest way to get the current database size.

Additionally, for more detailed insights, we can query sys.master_files and check the sizes of a specific database or all databases.

4. How to Check Database Size in MySQL?

MySQL doesn’t provide any built-in function like PostgreSQL or SQL Server to check database size, but it offers a straightforward way through the information_schema.tables system view.

With that in mind, we can calculate the size of a specific database (e.g., university) by summing up the data and index lengths for all tables under that schema:

> SELECT table_schema AS "Database", FLOOR(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.tables WHERE table_schema = 'university' GROUP BY table_schema;
+------------+------------+
| Database   | Size (MB)  |
+------------+------------+
| university | 294        |
+------------+------------+

Here, the query returns the total size of the university database as 294 MB.

Furthermore, to view the sizes of all databases on the server and sort them from largest to smallest, we can leverage a fairly simple query:

> SELECT table_schema AS "Database", FLOOR(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables GROUP BY table_schema ORDER BY SUM(data_length + index_length) DESC;
+----------------+------------+
| Database       | Size (MB)  |
+----------------+------------+
| gateway        | 543        |
| university     | 294        |
| applicationdbs | 121        |
| data_archival  | 8          |
+----------------+------------+

Thus, the output shows the total size of each database by summing data_length (table data) and index_length (indexes) from all tables in the schema. As such, it reflects the actual on-disk storage in megabytes, excluding logs or temporary files.

5. Conclusion

In this article, we got into database size checks.

In summary, monitoring database sizes is essential for efficient storage management and optimal performance. SQL Server and PostgreSQL provide built-in functions to quickly check the size of single or multiple databases, while MySQL offers system views like information_schema that enable similar insights through custom queries.

These tools can be leveraged to plan storage, optimize performance, and avoid unexpected space issues.

     

Related Stories

• How to Exclude a Column Using SELECT EXCEPT in SQL
• What Is Sargability in SQL?
• Updating Rows With a Subquery Referencing the Same Table in SQL

 

1. Introduction

Roles and permissions are essential to securing Microsoft Azure SQL Server databases. Providing users with the appropriate level of access is crucial for compliance and security as businesses grow.

To query and examine the server-level, database-level, and custom roles that Azure SQL Database offers, we can use the Azure Portal, the Azure Active Directory integration, or custom T-SQL statements.

In this tutorial, we’ll present the different types of roles, how to view user permissions, and the best practices for auditing access in Azure SQL Server.

2. Understanding the Roles in Azure SQL Server

Roles are predefined or user-defined security groupings. We can assign users to roles rather than giving them permissions on a case-by-case basis.

We differentiate between server-level and database-level roles:

Additionally, organizations can establish custom roles to comply with the least privilege principle. With user-defined custom roles, administrators can customize permissions to meet particular job functions or operational requirements.

This ensures that users only have access to the resources they need for their tasks, reducing the possibility of unintentional or malicious data exposure.

3. Methods for Viewing Roles Using a Query

Azure SQL supports querying system views to retrieve roles and permissions.

3.1. View Database Roles Assigned to Users

Here’s an SQL query that retrieves a list of database roles and their members from the current SQL Server database:

SELECT dp.name AS DatabaseRole,
mp.name AS MemberName,
mp.type_desc AS MemberType
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.role_principal_id = dp.principal_id
JOIN sys.database_principals mp ON drm.member_principal_id = mp.principal_id
ORDER BY dp.name;

The sys.database_role_members view maps database roles to their members, showing which users belong to which roles. The sys.database_role_principals view contains information about all database-level security principals, including users, roles, and application roles.

Here’s what the output typically looks like:

The actual output depends on our database’s current role assignments. We’ll see all users and roles that are members of other roles, sorted by the role name.

This query is a great way to get a clear snapshot of role memberships. It’s particularly useful when auditing permissions and managing access.

3.2. View Server-Level Roles

Getting server-level role memberships in SQL Server is useful for auditing administrative privileges and understanding access control at the instance level.

Here’s an SQL query that retrieves a list of server roles and their members:

SELECT spr.name AS ServerRole,
spm.name AS MemberName
FROM sys.server_role_members srm
JOIN sys.server_principals spr ON srm.role_principal_id = spr.principal_id
JOIN sys.server_principals spm ON srm.member_principal_id = spm.principal_id
ORDER BY spr.name;

The sys.server_role_members view shows the relationships between server-level roles and their members. The sys.server_principals view lists all server-level security principals, such as logins and server roles, providing metadata about each.

Here’s what the output typically looks like:

The actual output will reflect the logins configured on the SQL Server instance and their assigned server roles. These roles govern various capabilities, such as managing logins, configuring server settings, or executing high-level tasks.

3.3. View Explicit Permissions Granted to a User

In contrast to the query showing role memberships, we’ll now focus on explicit permissions granted directly to users or roles.

When auditing who has access to what, and under whose authority, a detailed view of explicit database-level permissions comes in handy:

SELECT dp1.name AS Grantee,
dp2.name AS Grantor,
perm.permission_name,
perm.state_desc,
obj.name AS ObjectName
FROM sys.database_permissions perm
JOIN sys.database_principals dp1 ON perm.grantee_principal_id = dp1.principal_id
JOIN sys.database_principals dp2 ON perm.grantor_principal_id = dp2.principal_id
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
ORDER BY dp1.name, perm.permission_name;

This query serves as an excellent tool to demonstrate granular access control within relational databases.

The following table provide details about the output columns in the query.

Here’s how the output typically looks like:

Furthermore, this highlight security best practices in enterprise data environments, and clarify the distinctions between role based vs explicit permission assignments.

4. Viewing Roles Using the Azure Portal

The Azure Portal provides a graphical interface for viewing and managing user roles:

The steps to get there are:

• We’ll navigate to the Azure Portal and open the SQL databases section
• Select the target database, then configure the server firewall and set the Active Directory admin for identity mapping
• Within the database view, we’ll access the Access control (IAM) panel to review user roles and permissions.

If the SQL Server is integrated with Azure AD, permissions can also be reviewed through the Azure Active Directory portal. Further, we’ll navigate to enterprise applications, then select users and groups to audit group-based assignments across multiple users.

This is especially useful when auditing group-based assignments across multiple users.

5. Best Practices for Managing and Reviewing Permissions

To ensure secure and scalable access management, it’s essential to follow the principle of least privilege. This is done by assigning only the permissions that are strictly necessary.

For routine audits, we need to use Azure Automation or SQL queries. As a result of these audits, we can detect excessive or out-of-date permissions, ensure adherence to internal policies, and identify potential security threats before they become a problem.

We should also implement role-based access control through Azure AD groups for better scalability, and enable logging via Azure Monitor and SQL Auditing to effectively track access changes.

6. Conclusion

In this article, we discussed about how Azure SQL Server roles and permissions ensures secure and legal database access. T-SQL queries, the Azure Portal, and Azure AD help administrators keep track of who has access and what they can do with it. 

The ability to manage roles and permissions in Azure SQL Server is essential for preserving data security, operational integrity, and regulatory compliance.

When onboarding new users, distributing access among teams, or getting ready for an audit, knowing who can do what and why is crucial.

     

Related Stories

• How to Check Blocking Queries
• Fixing MySQL ‘Host Not Allowed’ Error: A Simple Guide
• Change a PostgreSQL User Password

 

1. Overview

In SQL, we commonly use the SELECT * statement to retrieve all columns from the table. However, in certain scenarios, we sometimes want to display all columns except one or even a few. Unfortunately, standard SQL doesn’t support the direct  SELECT * EXCEPT syntax in most database management systems. Instead, the implementations vary depending on the database we’re using.

In this tutorial, we’ll explore various techniques for excluding specific columns in SQL Server, PostgreSQL, and MySQL.

To demonstrate, we’ll use the Baeldung University database schema as the working example.

2. Listing Specific Columns in SQL

Generally, we can always explicitly list the required columns instead of using a SELECT * query. This approach works in every database management system and is ideal for small tables that rarely change their schema.

To start with, we can use the following syntax to display only the selected columns:

SELECT column_1, column_2, column 3, column_4...
FROM table_name;

Above, we specify the required columns and table_name.

While this method is straightforward, it requires that we know and enumerate all required columns. Especially with big tables, this can be very tedious.

3. SQL Server sys.columns

In SQL Server, we can use the sys.columns view system to dynamically build the query that excludes specific columns. In particular, this view contains metadata about each column, including its name, data type, and position.

To illustrate, let’s exclude the gpa column in the Student table. For this purpose, we can execute a specific query:

DECLARE @sql NVARCHAR(MAX);
SELECT @sql =
    'SELECT ' + STRING_AGG(QUOTENAME(name), ', ')
    + ' FROM dbo.student'
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.student')
  AND name <> 'gpa';
EXEC sp_executesql @sql;

The important part here, and the one we can build upon, is AND name <> ‘gpa’. Adding AND statements with respective names enables the exclusion of multiple columns.

Consequently, we see the respective output:

In this output, all columns are listed except the gpa.

4. PostgreSQL string_agg

In PostgreSQL, we can exclude a column using the INFORMATION_SCHEMA view and concatenating the column list with string_agg. This approach uses the system catalog view to build flexible queries without manually listing every column.

For instance, let’s exclude the code column from the department table:

university=# DO $$
university$# DECLARE
university$#     sql_query text;
university$# BEGIN
university$#     SELECT 'SELECT ' ||
university$#            string_agg(quote_ident(column_name), ', ' ORDER BY ordinal_position) ||
university$#            ' FROM Department'
university$#     INTO sql_query
university$#     FROM information_schema.columns
university$#     WHERE table_schema = 'public'
university$#       AND table_name   = 'department'
university$#       AND column_name <> 'code';
university$#
university$#     RAISE NOTICE 'Generated SQL: %', sql_query;
university$#     EXECUTE sql_query;
university$# END $$;
NOTICE:  Generated SQL: SELECT id, name FROM department
DO

In return, we see the generated query SELECT id, name FROM department.

Now, let’s run the above-generated query to see the results:

university=# SELECT id, name FROM department;
 id |              name
----+--------------------------------
  1 | Computer Science
  2 | Electronics and Communications
  3 | Mechanical Engineering
  4 | Civil Engineering
  5 | Mathematics
(5 rows)

Thus, the output only displays the id and name columns.

5. MySQL GROUP_CONCAT

In MySQL, we can achieve a functionality similar to that in PostgreSQL using the GROUP_CONCAT function, along with the INFORMATION_SCHEMA view. This approach enables the dynamic elimination of specific columns in the database.

For example, to eliminate the textbook column from the course table, we can use the COLUMN_NAME field:

SET @sql = NULL;
SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY ORDINAL_POSITION SEPARATOR ', ')
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'university'
  AND TABLE_NAME = 'course'
  AND COLUMN_NAME <> 'textbook';
SET @sql = CONCAT('SELECT ', @sql, ' FROM course');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Below, we can see all columns from the course table except textbook:

While the approach uses similar concepts, the syntax isn’t interchangeable with that of PostgreSQL.

6. Conclusion

In this article, we learned various techniques for excluding a particular column in SQL Server, PostgreSQL, and MySQL.

First, we discussed explicitly listing the required columns as a universal and reliable approach. Then, we saw how to dynamically generate column lists using sys.columns in SQL Server, INFORMATION_SCHEMA with string_agg in PostgreSQL, and GROUP_CONCAT in MySQL to exclude unwanted columns.

     

Related Stories

• Most Efficient Way to Get Table Row Count in SQL
• How to Retrieve the Database Size?
• What Is Sargability in SQL?

 

1. Introduction

In this tutorial, we’ll look at SQL blocking queries in-depth. We’ll use our University schema and SQL Server 2022 as our main SQL dialect.

SQL Server offers two ways to monitor blocking behavior:

1. Using the SP_WHO2 stored procedure
2. Via an SQL query

2. Blocking in Databases

All SQL queries run under a session. We define a blocking session as holding a lock on a particular resource, which prevents other sessions from acquiring a conflicting lock type on that same resource.

Any blocked session must wait for the corresponding blocking session to complete its execution and release its lock on the resource.

Blocking queries can significantly impair database performance and business operations in many ways. For example, they can decrease throughput as queries take more than the normal time to complete. As a result, we see non-realtime user experience, application slowdowns, and poor business flow. Also, as database usage increases, a higher volume of concurrent transactions means longer execution times and increased disk I/O.

Thus, all these reasons motivate us to minimize blocking behavior in our database systems.

3. Causes

Many things can contribute to blocking behavior.

First, long-running queries cause a significant chunk of blocking of a database session. A long-running query holds its locks for the entire duration. Thus, it blocks other processes.

Second, we block a DB session when we cancel or kill a query. This is so because a process gets in a rollback state when we cancel or kill a query (say, a client restart). In such a case, the database must undergo a rollback process to undo any data modifications. During this (potentially time-consuming) rollback, the query’s statements continue to hold locks, which block other processes.

Another cause of blocking is when we set isolation levels for our transactions without thorough consideration. Any transaction isolation level other than Read Uncommitted also adds to blocking. For example, the database internally uses the default Read Committed level during read operations to prevent dirty reads. However, this leads to blocking if multiple transactions attempt to access the same data concurrently.

Higher isolation levels, such as Repeatable Read and Serializable, hold shared locks for the entire transaction duration. Thus, it increases their potential to cause blocking.

4. SP_WHO2

Let’s check how SP_WHO2 helps detect blocking.

In the first session (spid=56), we run an UPDATE query on the Course table to set the credits of a particular course:

BEGIN TRAN;
UPDATE Course
SET credits = 8
WHERE id = 'CS111';

In our second session (spid=58), we run the same query with a different credit value. Since we didn’t commit or rollback the previous query, our second session blocks:

BEGIN TRAN;
UPDATE Course
SET credits = 9
WHERE id = 'CS111';

We now open a third session to execute this stored procedure to determine the source of blocking:

EXEC sp_who2;

And we can see that the second session is in the SUSPENDED state:

SPID  Status          BlkBy   Command         
----- -----------     ------- -----------------
56    sleeping        -        AWAITING COMMAND
58    SUSPENDED       56       UPDATE

The field blkby shows that the first session (spid=56) is its blocker.

5. DMV Query

Now we’ll explore how to solve the same problem using queries.

SQL Server offers a framework called DMV, or dynamic management view. It provides granular information about the sessions, locks, and resources.

Let’s open a fourth session to run this query to know the reason and source of blocking of our second session:

SELECT
    blocked.session_id,
    blocked.status,
    blocked.wait_type,
    blocking.session_id AS blocking_session_id,
    blocking.status AS blocking_status,
    blocked.command
FROM sys.dm_exec_requests AS blocked
INNER JOIN sys.dm_exec_sessions AS blocking ON blocked.blocking_session_id = blocking.session_id
LEFT JOIN sys.dm_exec_connections AS conn_blocked ON blocked.session_id = conn_blocked.session_id
LEFT JOIN sys.dm_exec_connections AS conn_blocking ON blocked.blocking_session_id = conn_blocking.session_id
OUTER APPLY sys.dm_exec_sql_text(conn_blocked.most_recent_sql_handle) AS blocked_sql
OUTER APPLY sys.dm_exec_sql_text(conn_blocking.most_recent_sql_handle) AS blocking_sql
WHERE blocked.blocking_session_id <> 0;

The output shows that our second session (spid=58) is suspended by our first session (56), which holds a lock due to the update command:

session_id  status       wait_type   blocking_session_id   blocking_status   command
----------  -----------  ----------- --------------------  ----------------  --------
58          suspended    LCK_M_X     56                    sleeping          UPDATE

We can find the user who has blocked the session via attribute blocking.login_name AS blocking_login. Further, we can find the blocking command using the construct blocking_sql.text AS blocking_query_text.

6. Blocking Termination

We can resolve the blocking behavior by either:

1. Rolling back the blocking session
2. Committing the blocking session, or
3. Terminating the blocking session

To terminate our blocking session (spid=56), we use the KILL command:

KILL 56;

The SQL Server immediately terminates the specified session and initiates a rollback of any open transactions the killed session was running. As part of the rollback, all locks held by the session will be released, unblocking our second session.

7. Comparison

Let’s compare both solutions:

DMVs are more flexible than SP_WHO2 because of two main reasons.

First, DMVs have customizable queries offering relational views. We can filter, join, and aggregate data in DMVs. On the other hand, SP_WHO2 offers a fixed output with making modifications very difficult.

Second, DMVs offer high level of detail such as such as blocking SQL query text, specific locks held (for example, LCK_M_S and LCK_M_X), lock duration, lock mode (S, X, U) and locked resource type (key, page, object). In contrast, SP_WHO2 offers minimal details (session id of blocker and blocker command).

8. Best Practices

The most effective strategy for managing blocking in SQL Server is proactive prevention. Although identifying and addressing existing blocking is crucial, implementing measures to minimize the occurrence and duration of locks is much more impactful for maintaining a healthy, high-concurrency SQL Server environment.

8.1. Transaction Management

We should minimize the duration of each transaction by keeping them short and simple. We can do this by splitting every long transaction into smaller units. This approach significantly reduces the lock held duration there by decreasing the blocking window.

Another useful practice is to avoid taking user input within a transaction. User can delay and this often increases lock held duration leading to blocking.  

8.2. Indexing Strategies

Our SQL Server should have properly defined indexes.

We should handle related indexes (clustering) and non-related indexes (singleton) indexes separately. This separation reduces logical reads, and minimizes the lock held duration.

All this leads to efficient data access by reducing the need for resource-intensive full table scans that acquire broader, more restrictive locks.

8.3. Query Optimization

We can use the construct sys.dm_exec_query_plan to identify performance bottlenecks and optimize inefficient queries. This reduces the execution time of resource-intensive queries.

Second, we should avoid SELECT * when we don’t really need all columns of a table. This way, we reduce the amount of data read from disk and transferred over the network, thereby minimizing I/O and memory usage.

8.4. Application Design

A good practice for making our application robust and deterministic is to implement retry logic to overcome transient errors, such as deadlocks or lock timeouts.

This allows the application to gracefully handle temporary blocking situations without crashing, improving overall system resilience.

9. Conclusion

In this article, we studied blocking queries in depth by looking at the SP_WHO2 and SQL queries. We further showed that via queries, we can trace complex blocking chains, pinpoint head blockers, and uncover the precise root causes of concurrency issues. Then, we gave best practices to avoid blocking.

By adopting these best practices and maintaining a proactive stance, we can get minimal blocking and maximal concurrency of our database system. Thus, all this can offer a seamless user experience.

     

Related Stories

• Types of Locks in SQL Server
• Viewing Roles and Permissions Granted to Users of Azure SQL Server Instance
• Fixing MySQL ‘Host Not Allowed’ Error: A Simple Guide

 

1. Introduction

In this tutorial, we’ll explore the correct usage of single quotes, double quotes, and backticks in MySQL.

Although appearing similar, these three types of quotation marks serve very different purposes in MySQL. Understanding the correct use of each is essential for writing clear, error-free SQL queries and avoiding common pitfalls, especially when dealing with strings, identifiers, and reserved keywords.

2. Single Quotes in MySQL

We primarily use single quotes (‘…’) for string literals in MySQL. Whenever we need to specify a fixed string value, such as a name, date, or text data, we should use single quotes:

SELECT * FROM users WHERE name = 'Lee Xin';

MySQL interprets everything inside single quotes as a literal string. If our string contains a single quote character, then we must escape it with another single quote:

SELECT 'It''s a sunny day';

This escaping prevents syntax errors and ensures that the string is correctly processed. We can test this query with one and two quotes using online MySQL compiler. The results will look like this:

> SELECT 'It's a sunny day';
ERROR 1064 ...
> SELECT 'It''s a sunny day';
+-----------+
+ It's a sunny day

As we can see, it outputs an error when we use only a single quote inside single quotes that define our string.

3. Double Quotes in MySQL

Double quotes (“…”) have a special role depending on the SQL mode.

By default, in MySQL’s ANSI_QUOTES mode, double quotes are the same as backticks. It means they identify column or table names. In a non-ANSI_QUOTES mode, double quotes can sometimes act like single quotes for string literals, but relying on this behavior can cause compatibility issues across environments.

For example, lets assume we have the following query:

SELECT "column" FROM table WHERE foo = "bar";

Here we can have two outcomes:

• If ANSI_QUOTES is enabled, the query will select the values from column named column where column foo is equal to column bar.
• Otherwise, the query will select the string literal “column” where column foo is equal to string “bar”.

The best practice is to avoid using double quotes so that our code becomes independent of SQL modes.

4. Backticks in MySQL

We use backticks (`…`) for delimiting identifiers such as column names, table names, and database names that might otherwise cause problems because they are reserved keywords, contain spaces, or special characters:

SELECT `order`, `user name` FROM `sales data`;

Without backticks, “order” would be interpreted as a reserved keyword, and “user name” would be invalid due to the space. Backticks ensure that MySQL treats the enclosed text as an identifier exactly as written.

5. Conclusion

In this article, we clarified when to use single quotes, double quotes, and backticks in MySQL.

Single quotes are the standard for string literals. Double quotes are best avoided, as their meaning shifts depending on the SQL mode. Backticks remain the safest and most reliable choice for column, table, and database names that include reserved keywords, spaces, or special characters.

     

Related Stories

• What’s the Ideal Data Type to Use When Storing Latitude / Longitude in a MySQL Database?
• Convert Integer to String in PostgreSQL
• Import an SQL File Using the Command Line in MySQL

 

1. Overview

Storing Boolean values is a basic requirement in most database-driven applications. We often use them to track user preferences, feature toggles, or permissions in a table. So, learning how to reliably store binary logic in MySQL can be critical.

This might seem obvious, but MySQL doesn’t offer a true Boolean data type in the traditional sense. Instead, it uses workarounds that may be confusing and lead to unexpected behavior if misunderstood. Yet, choosing the right data type ensures efficiency and compatibility across applications and environments.

In this tutorial, we’ll explore how MySQL represents Boolean values, what data types we can use instead, and which option makes the most sense for storing true/false data.

2. MySQL Boolean Type Support

In short, yes – we can define columns as BOOLEAN or BOOL. However, unlike other database management systems (DBMS), MySQL considers Boolean an alias for TINYINT(1).

This means, when we define a column as BOOLEAN, MySQL actually creates a TINYINT(1) column that can store values from -128 to 127 (if signed) or from 0 to 255  (if unsigned). Under the hood, MySQL converts FALSE to 0 and TRUE to 1.

Considering this, let’s create a sample users table and insert some data:

CREATE TABLE Users (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  is_active BOOL not null
);
-- insert
INSERT INTO Users VALUES (1, 'Zack', 'Sales', TRUE);
INSERT INTO Users VALUES (2, 'Mark', 'Marketing', FALSE);

Next, we query this table using SELECT and WHERE to retrieve the active users:

SELECT * FROM USERS WHERE is_active is true;
Output:
+-------+------+-------+-----------+
| empId | name | dept  | is_active |
+-------+------+-------+-----------+
|     1 | Zack | Sales |         1 |
+-------+------+-------+-----------+

Thus, we can notice that MySQL indeed stores 1 instead of TRUE.

Of course, we can also do the opposite, and instead of using the Boolean values TRUE and FALSE, we can just type their respective numeric values 1 or 0:

INSERT INTO Users VALUES (1, 'Zack', 'Sales', 1);
     -- OR
SELECT * FROM Users WHERE is_active = 1;

This returns the same result. Both approaches work the same way, so the choice often comes down to code readability.

3. Use TINYINT Directly

Now that we understand how MySQL handles the BOOLEAN data type, we can simply use TINYINT(1) directly instead.

Let’s employ ALTER TABLE to update the USERS table to redefine the is_active column as TINYINT(1) instead of BOOL:

ALTER TABLE Users
MODIFY COLUMN is_active TINYINT(1);

Now, if we rerun the same tests from the previous example, we get identical results. This confirms that we can use BOOLEAN and TINYINT(1) interchangeably in MySQL.

Notably, MySQL can convert Boolean values to and from other integer types like INT, SMALLINT, or BIGINT in the same way. That said, TINYINT(1) remains the most space-efficient choice.

4. Use BIT With 0 or 1

A Boolean represents two possible values (true or false), and MySQL offers a way to store this binary logic with BIT. Technically, BIT(1) can store a single binary value (0 or 1), making it a natural fit for representing Boolean values. However, it’s often less practical in real-world use cases.

Let’s start by updating the Users table to define the is_active column as BIT(1):

ALTER TABLE Users
MODIFY COLUMN is_active BIT(1) NOT NULL;

Next, we add some records:

INSERT INTO USERS VALUES (1, 'Zack', 'Sales', b'1');
INSERT INTO USERS VALUES (2, 'Mike', 'HR', b'0');

Here, the b’value’ notation explicitly tells MySQL we’re inserting a binary literal with a value that contains only 0 and 1.

However, there’s another catch with BIT: by default, MySQL may display BIT values as non-printable characters or binary blob:

SELECT empId, name, dept, is_active FROM Users;
+-------+------+-------+-----------+
| empId | name | dept  | is_active |
+-------+------+-------+-----------+
|     1 | Zack | Sales |           |
|     2 | Mike | HR    |           |
+-------+------+-------+-----------+

So, if we want to retrieve BIT values as visible zeroes and ones, we need to explicitly convert them into a readable format:

SELECT empId, name, dept, BIN(is_active) AS is_active_bin
FROM Users;
+-------+------+-------+---------------+
| empId | name | dept  | is_active_bin |
+-------+------+-------+---------------+
|     1 | Zack | Sales |             1 |
+-------+------+-------+---------------+

In the above query, we use the BIN() function to see the binary representation as a string.

Alternatively, another option is to use CAST():

SELECT empId, name, dept, CAST(is_active AS UNSIGNED) AS is_active_num FROM Users;

The CAST function displays BIT(1) values as numeric 0 or 1. It converts the binary data into unsigned integers.

In practice, BIT can be tricky and even inconsistent across platforms. For example, many developers report normal behavior on Windows but issues on Linux systems. Furthermore, BIT(1) still takes up a full byte, just like TINYINT(1). Considering that, it’s simpler to just use TINYINT(1).

5. Use ENUM With Custom Values

What if we want to use custom Boolean-like values similar to what’s possible in other DBMSs? We can rely on ENUM for that. It enables us to define a specific set of values for a column, like yes and no.

As usual, let’s update the Users table and change the type of the is_active column to ENUM:

ALTER TABLE Users 
MODIFY COLUMN is_active ENUM('yes', 'no') NOT NULL;

After this change, the is_active column accepts either yes or no values. Additionally, MySQL can also take these values as numeric indexes (yes as 1, no as 2).

Let’s insert two rows of data and query the table:

INSERT INTO Users VALUES (1, 'Nick', 'Accounting', yes);
INSERT INTO Users VALUES (2, 'John', 'Marketing', 2);
SELECT * FROM Users
+-------+------+-----------+------------+
| empId | name | dept       | is_active
+-------+------+-----------+------------+
|     1 | Nick | Accounting | yes       |
|     2 | John | Marketing  | no        |
+-------+------+------------+-----------+
Here, the first row uses the defined ENUM value directly (yes). The second row uses its numeric index (2), which MySQL automatically maps to the corresponding label (no). Basically, this shows that we can query ENUM values either by label or by index.

6. Conclusion

In this article, we explored how MySQL interprets Boolean values and the most common data types available to represent true and false logic. Specifically, we found that MySQL uses BOOL and TINYINT(1) interchangeably, learned how to work with the BIT type, and applied ENUM for custom true and false labels.

In conclusion, although we can use multiple data types to represent Boolean values, TINYINT(1) remains the most reliable, space-efficient choice.

     

Related Stories

• Import an SQL File Using the Command Line in MySQL
• When to Use Single Quotes, Double Quotes, and Backticks in MySQL
• What’s the Ideal Data Type to Use When Storing Latitude / Longitude in a MySQL Database?

 

1. Introduction

Performance optimization is very crucial in SQL for database applications, especially when it involves large datasets. A key concept that impacts query speed is sargability (Search ARGument ABILITY).

Sargability refers to the ability of an SQL query to utilize database indexes to speed up data retrieval operations. It is when the conditions within a WHERE, HAVING, and ORDER BY clause scan, but also JOINs and HAVING, are leveraged by the database engine to efficiently retrieve rows via indexing. When a query is sargable, the optimizer can determine whether to use index seeks (for small subsets of rows) or index scans for accessing larger data.

In this tutorial, we’ll explain sargability, what makes a query sargable, tools for identifying sargability, how it affects performance, and how to write sargable SQL queries. This tutorial uses the table from Baeldung’s simple University database schema.

2. What Makes an SQL Statement Sargable?

The core concept of sargability revolves around search arguments that can efficiently leverage database indexes to optimize query execution.

The difference between sargable and non-sargable queries is this: sargable queries enable the optimizer to determine when to use index seek and index scans. Non-sargable queries often force the database to perform full table scans, as they can’t utilize indexes due to their search conditions.

For a query to efficiently use an index, certain conditions must be met in the WHERE, JOIN, or ORDER BY clauses:

• Direct column comparisons using operators like =, <, >, <=, >=, or BETWEEN
• No functions or calculations applied to the indexed column (e.g., avoid LOWER(column) or column + 10)
• Consistent data types between the column and the comparison value to avoid implicit type conversion
• Leading column usage in multi-column indexes (e.g., use the first column in the index)
• Pattern matching with a trailing wildcard, such as LIKE ‘abc%’, rather than LIKE ‘%abc’
• Use of IN with specific values (e.g., WHERE id IN (1, 2, 3)), depending on the database engine

These conditions help the database perform index seeks rather than full scans, improving performance and reducing resource usage.

3. Tools to Identify Non-Sargable Queries

Detecting a non-sargable query at the early stage helps to optimize performance. It allows database admins, analysts, or developers to refactor the queries rather than executing inefficient queries with cost and computational implications. The table below shows the tools for identifying sargability in different SQL versions:

In addition, it is important to note these observations:

• Check execution plans for table scans instead of index seeks
• Pay attention to warnings regarding “implicit conversions” or “cardinality estimate errors”
• Monitor and compare query read times relative to the rows returned

Examples in the next section use the EXPLAIN command to detect non-sargable queries.

4. Examples of Sargable vs. Non-Sargable Queries

Understanding the difference between sargable and non-sargable queries becomes easier with practical examples. In this section, we aim to highlight the common query patterns that prevent index usage and understand the best way to rewrite them. Rather than focusing on the query result, we use EXPLAIN to analyze if a query is sargable or not.

4.1. Function on a Column

Wrapping a column in a function like CAST(), YEAR(), or CONVERT() makes the query non-sargable, as it prevents the use of an index even if applicable to the specified column:

-- Non-Sargable
EXPLAIN SELECT * FROM Student 
WHERE YEAR(birth_date) = 2001;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | Student | NULL | ALL | NULL | NULL | NULL | NULL | 25 | 100 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

Here, the YEAR() function is applied to birth_date, so the index on the birth_date column is ignored. Let’s look at a sargable alternative:

-- Sargable
EXPLAIN SELECT * FROM Student 
WHERE birth_date >= '2001-01-01' AND birth_date < '2002-01-01';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | Student | NULL       | range | year_idx      | year_idx | 4       | NULL |   10 |      100 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

This query is sargable because it allows the optimizer to search the columns in the WHERE clause using index scans.

4.2. Leading Wildcard in LIKE

Using a preceding “%” when performing a text search prevents the use of an index on the column to be searched:

-- non-sargable
EXPLAIN SELECT * FROM Student WHERE name LIKE '%Po';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered           | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 | 11.110000610351562 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------+-------------+

The better approach to make the query sargable is to use the wildcard at the end of the search, just like this:

-- Sargable
EXPLAIN SELECT * FROM Student WHERE name LIKE 'Po%';
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | index_name    | index_name | 243     | NULL |    2 |      100 | Using index condition |

The above table shows that the query will utilize the “index_name” index, reducing the search to two rows.

4.3. Data Type Mismatch

If the id in the column is numerical (INT), using a quote in the search can make the query non-sargable, because the engine first converts the data type from VARCHAR to INT before executing the query:

-- Non-Sargable (if id is INT)
SELECT * FROM customers WHERE id = '1001';

A better alternative is to run the search without the quote. It shows the significant difference between the two queries:

-- Sargable
SELECT * FROM customers WHERE id = 1001;

The above query won’t need a data type conversion. Hence, it’s considered searchable.

4.4. OR Conditions Across Different Columns

The OR condition can be sargable if the columns in the conditions have appropriate indexes. However, the columns become non-sargable when neither of them has an index, the columns do not share a composite index, or the query combines indexed and non-indexed columns.

-- non-sargable 
EXPLAIN 
SELECT * FROM Student 
WHERE national_id = 123345566 OR name = 'John Liu';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered           | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------+-------------+
|  1 | SIMPLE      | Student | NULL       | ALL  | index_name    | NULL | NULL    | NULL |   25 | 13.600000381469727 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+--------------------+-------------+

We can fix this by using a UNION statement, which ensures that each column is independently indexed:

-- Sargable
EXPLAIN 
SELECT * FROM Student WHERE national_id = 123345566
UNION 
SELECT * FROM Student WHERE name='John Liu';
+----+--------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | Student    | NULL       | ALL  | NULL          | NULL       | NULL    | NULL  |   25 |       10 | Using where     |
|  2 | UNION        | student    | NULL       | ref  | index_name    | index_name | 243     | const |    1 |      100 | NULL            |
|  3 | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL       | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-----------------+

While the first part uses a full table scan due to the lack of an index on the national_id column, the second part uses an index and a “ref“. Since a part of the query benefits from using an index, it helps to retain sargability.

4.5. Sargability in JOINs

What makes JOINs and subqueries sargable is if predicates or conditions can use indexes. Avoiding functions when trying to match case-sensitive composite columns makes a significant difference:

-- non-sargable JOIN
EXPLAIN SELECT c.name, e.semester, e.grade 
FROM Course c 
JOIN Exam e 
       ON LOWER(c.id) = LOWER(e.course_id) 
WHERE c.credits > 4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered          | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------+--------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   81 | 33.32999801635742 | Using where                                |
|  1 | SIMPLE      | e     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  143 |               100 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------------+--------------------------------------------+

From the output, we can see why this is non-sargable. Many of the conditions listed in section 2 also apply to optimizing JOINs. It is important to ensure that filtering happens in the execution plan rather than after joining large datasets. Let’s now look at a sargable version.

-- Sargable
EXPLAIN SELECT c.name, e.semester, e.grade 
FROM Course c 
JOIN Exam e 
        ON c.id = e.course_id 
WHERE c.credits > 4;
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-----------------+------+-------------------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref             | rows | filtered          | Extra       |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-----------------+------+-------------------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | PRIMARY,id          | NULL                | NULL    | NULL            |   81 | 33.32999801635742 | Using where |
|  1 | SIMPLE      | e     | NULL       | ref  | exam_course_id_fkey | exam_course_id_fkey | 43      | university.c.id |    1 |               100 | NULL        |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-----------------+------+-------------------+-------------+

Removing the functions and sticking to direct column comparison with the “>” operator solves the optimization issue from the first query.

5. How Non-Sargable Queries Affect Performance

Non-sargable queries force the database engine to ignore indexes and perform full table scans, thereby degrading performance. This results in higher CPU usage, increased disk I/O, and longer execution time as the dataset gets larger.

When indexes do not apply to queries, the engine examines every row to determine if it matches the computationally expensive condition. For non-sargable JOINs, or subqueries, the query optimizer may be forced to use inefficient join strategies or process larger results than necessary.

Additionally, non-sargable queries create scaling issues. A query that performs acceptably on small datasets (e.g., 10,000 rows) may become unusable on larger tables (e.g., 10 million rows) due to the non-linear growth in resource requirements.

6. Difference in Sargability Across SQL Engines

The table below shows details on how different database engines interpret and optimize queries for sargability. It is important to make this distinction in cases when we’re tuning for specific environments:

7. Conclusion

Sargability isn’t just about having indexes. It’s about ensuring that queries can use indexes more efficiently. However, this concept of SQL query optimization is often overlooked due to more focus on functionality rather than performance. Understanding this concept can improve the scalability of database systems.

In this article, we explored what sargability is and why it matters, common patterns that prevent sargability, sargable and non-sargable examples, and tools and techniques to identify non-sargable conditions.

     

Related Stories

• How to Retrieve the Database Size?
• How to Exclude a Column Using SELECT EXCEPT in SQL
• Updating Rows With a Subquery Referencing the Same Table in SQL

 

1. Overview

Subqueries are SELECT statements nested inside other SQL statements. Such nested queries execute first and return a result, which is then used by the outer query.

In this tutorial, we’ll demonstrate how to update rows using a subquery that references the same table in a MySQL relational database. Then, we’ll demonstrate that we can update rows via an UPDATE … FROM statement referencing the same table in both the PostgreSQL and the SQL Server relational databases.

Notably, we’ll employ the Program table from the Baeldung University database.

2. MySQL Database

Let’s suppose we want to update the end_date in the Program table to equal the start_date + 3 weeks for all rows. To achieve this, we have two main options.

At the outset, we can’t use a simple UPDATE … FROM statement in MySQL to access the outer query table within a subquery in an UPDATE statement. In fact, when we try to do this, we encounter ERROR 1093 (HY000): You can’t specify the target table ‘x’ for update in the FROM clause.

2.1. Using a Subquery Within a Self-Join

We can update rows using a subquery that references the same table through a self-join. This method is especially useful if parts of the updated data come from within the same table, e.g., updating the end_date column based on the start_date column:

UPDATE Program AS P1
JOIN (
    SELECT id, DATE_ADD(start_date, INTERVAL 3 WEEK) AS new_end_date
    FROM Program
) AS P2
ON P1.id = P2.id
SET P1.end_date = P2.new_end_date;

In this case, the statement above joins the Program table with a temporary table (P2) that contains the calculated new_end_date for every row. The UPDATE then sets the end_date of each row in P1 to the corresponding value from P2.

Thus, we update all 24 rows in the table. Let’s verify this with a SELECT query on the table:

SELECT start_date,end_date from Program;
+------------+------------+
| start_date | end_date   |
+------------+------------+
| 2012-01-01 | 2012-01-22 |
| 2012-01-01 | 2012-01-22 |
| 2011-01-01 | 2011-01-22 |
| 2011-01-01 | 2011-01-22 |
| 2010-01-01 | 2010-01-22 |
| 2010-01-01 | 2010-01-22 |
| 2018-07-01 | 2018-07-22 |
| 2018-07-01 | 2018-07-22 |
| 2011-01-11 | 2011-02-01 |
| 2011-01-11 | 2011-02-01 |
| 2010-07-11 | 2010-08-01 |
| 2010-07-11 | 2010-08-01 |
| 2010-07-01 | 2010-07-22 |
| 2010-07-01 | 2010-07-22 |
| 2010-07-01 | 2010-07-22 |
| 2010-07-01 | 2010-07-22 |
| 2010-01-11 | 2010-02-01 |
| 2010-01-11 | 2010-02-01 |
| 2011-07-01 | 2011-07-22 |
| 2011-07-01 | 2011-07-22 |
| 2011-01-11 | 2011-02-01 |
| 2011-01-11 | 2011-02-01 |
| 2010-07-11 | 2010-08-01 |
| 2010-07-11 | 2010-08-01 |
+------------+------------+

As expected, all values are updated properly.

2.2. Using a Subquery Within a Derived Table

As another option, we can use a derived table for updating with data within the same table. In particular, the derived table Temp_table acts as an intermediate result set that the outer UPDATE query depends upon. Therefore, the UPDATE is performed only after the result set from the derived table is materialized:

UPDATE Program
SET end_date = (
    SELECT new_end_date FROM (
        SELECT DATE_ADD(start_date, INTERVAL 3 WEEK) AS new_end_date
        FROM Program AS Temp
        WHERE Temp.id = Program.id
    ) AS Temp_table
);

In this example, the subquery calculates the end_date for each row individually by joining the Temp_table to the Program table on the id column.

3. PostgreSQL Database

The PostgreSQL UPDATE syntax is more flexible and doesn’t require a nested subquery to avoid a self-referencing error. Again, we have two options for the example task of updating the end_date column in the Program table to equal the start_date + 3 weeks for all rows.

3.1. Using a Self-Join

Even in the case of PostgreSQL, we can update all 24 rows using a self-join, but this time we don’t need a SELECT subquery:

UPDATE Program AS P1
SET end_date = P2.start_date + INTERVAL '3 weeks'
FROM Program AS P2
WHERE P1.id = p2.id;

In fact, this is perhaps the most common method for performing such an update in PostgreSQL. Specifically, it joins the Program table (aliased as P1) with itself (aliased as P2) on the id field and sets the P1 end_date to the calculated value from P2.

Let’s verify all 24 rows are updated:

postgres=> SELECT start_date, end_date from Program;
 2012-01-01 | 2012-01-22
 2011-01-01 | 2011-01-22
 2011-01-01 | 2011-01-22
 2018-07-01 | 2018-07-22
 2018-07-01 | 2018-07-22
 2010-01-11 | 2010-02-01
 2010-01-11 | 2010-02-01
 2011-07-01 | 2011-07-22
 2011-07-01 | 2011-07-22
 2010-07-01 | 2010-07-22
 2010-07-01 | 2010-07-22
 2010-07-01 | 2010-07-22
 2010-07-01 | 2010-07-22
 2011-01-11 | 2011-02-01
 2011-01-11 | 2011-02-01
 2010-07-11 | 2010-08-01
 2010-07-11 | 2010-08-01
 2011-01-11 | 2011-02-01
 2011-01-11 | 2011-02-01
 2010-07-11 | 2010-08-01
 2010-07-11 | 2010-08-01
(24 rows)

Similar to the previous example, the self-join works in practice.

3.2. Using a Subquery Within an UPDATE … SET Statement

We can use a subquery within the SET clause of the UPDATE statement:

UPDATE Program
SET end_date = (
    SELECT start_date + INTERVAL '3 weeks'
    FROM Program AS Temp
    WHERE Temp.id = Program.id
);

This query updates each row by correlating it with a subquery on the same table. Furthermore, the subquery fetches the start_date for the corresponding id and adds three weeks. Again, we can verify that the end_date is set to start_date + 3 weeks using a simple, unnested SELECT query.

4. SQL Server Database

The SQL Server UPDATE…JOIN syntax is very powerful, enabling us to join tables directly within the UPDATE statement. In this case, we use the DATEADD function for date calculations. So, let’s discuss two options to update the end_date in the Program table.

4.1. Using a Self-Join

Similar to PostgreSQL, we can use a self-join without a nested subquery:

UPDATE P1
SET P1.end_date = DATEADD(week, 3, P2.start_date)
FROM Program AS P1
JOIN Program AS P2
ON P1.id = P2.id;

This statement updates P1 using a JOIN to P2 on the id column. Specifically, the FROM clause defines the tables being used and their aliases, and the SET clause performs the update using the joined data.

4.2. Using a Subquery Within an UPDATE … SET Statement

Lastly, as an alternative, we can use a subquery within an UPDATE … SET statement:

UPDATE Program
SET end_date = (
    SELECT DATEADD(week, 3, start_date)
    FROM Program AS Temp
    WHERE Temp.id = Program.id
);

Similar to PostgreSQL, SQL Server can employ a correlated subquery to update each row based on its own data without a complex nested structure.

5. Conclusion

In this article, we learned about updating rows in a table using a subquery on the same table.

MySQL doesn’t support a straightforward UPDATE … FROM statement; therefore, we usually need a nested subquery using a self-join or a derived table. However, PostgreSQL and SQL Server can both use an UPDATE … FROM syntax to update rows using data obtained from the same table; therefore, we don’t always need a subquery.

     

Related Stories

• How to UPDATE a Record Using a SELECT in SQL Server
• Combining DISTINCT and COUNT In a SQL Query
• How to Use Columns From a Subselect in WHERE Clause in SQL