Efficient SQL test query or validation query that will work across all (or most) databases

Question

Many database connection pooling libraries provide the ability to test their SQL connections for idleness. For example, the JDBC pooling library c3p0 has a property called preferredTestQuery, which gets executed on the connection at configured intervals. Similarly, Apache Commons DBCP has validationQuery.

Many example queries I've seen are for MySQL and recommend using SELECT 1; as the value for the test query. However, this query doesn't work on some databases (e.g. HSQLDB, for which SELECT 1 expects a FROM clause).

Is there a database-agnostic query that's equivalently efficient but will work for all SQL databases?

Edit:

If there's not (which seems to be the case), can somebody suggest a set of SQL queries that will work for various database providers? My intention would be to programmatically determine a statement I can use based on my database provider configuration.

See also [Simple DB2 Query for connection validation](http://stackoverflow.com/questions/2775184/simple-db2-query-for-connection-validation). — dma_k, Oct 04 '12 at 11:08
Note: configuring a test query is not needed anymore, see [my answer](http://stackoverflow.com/a/35270023/60518) below — Tim Büthe, Feb 08 '16 at 12:46

score 347 · Answer 1 · edited Jun 15 '21 at 16:51

347

After a little bit of research along with help from some of the answers here:

SELECT 1

H2
MySQL
Microsoft SQL Server (according to NimChimpsky)
PostgreSQL
SQLite
Hive

SELECT 1 FROM DUAL

Oracle

SELECT 1 FROM any_existing_table WHERE 1=0

or

SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS

or

CALL NOW()

HSQLDB (tested with version 1.8.0.10)

Note: I tried using a WHERE 1=0 clause on the second query, but it didn't work as a value for Apache Commons DBCP's validationQuery, since the query doesn't return any rows

VALUES 1 or SELECT 1 FROM SYSIBM.SYSDUMMY1

Apache Derby (via daiscog)

SELECT 1 FROM SYSIBM.SYSDUMMY1

DB2

select count(*) from systables

Informix

edited Jun 15 '21 at 16:51

msangel

9,895
3
50
69

answered Sep 08 '10 at 16:45

Rob Hruska

118,520
32
167
192

That should be "SELECT 1 FROM any_existing_table WHERE 1=0" - otherwise the call might be very slow. By the way, both SELECT 1 and SELECT 1 FROM DUAL also work with H2. – Thomas Mueller Sep 09 '10 at 14:01
Assuming OP wants a Java answer: I believe that with Java 6 this answer is now outdated. See my answer elsewhere on this page. – peterh Jan 21 '14 at 11:32
@RobHruska, your answer helped to solve my problem "Failed to validate a newly established connection" too. http://stackoverflow.com/questions/33100423/failed-to-validate-a-newly-established-connection/33106358#33106358 – SparX Oct 14 '15 at 05:27
For oracle, Use `SELECT 1 from DUAL;` – Viswanath Lekshmanan Mar 30 '16 at 07:17
@RobHruska I had to use `SELECT 1 FROM SYSIBM/SYSDUMMY1` for DB2. [source](http://newsolutions.de/forum-systemi-as400-i5-iseries/threads/13648-SQL-zumThema-Arbeitstage-des-Monats-ermitteln) (sorry it's in german). The error message I got when using the dot notation was something like: [SQL5016] Qualified Objectname SYSDUMMY1 not valid – JackLeEmmerdeur May 22 '17 at 14:18
How about SQL-99 "VALUES constructor" : `VALUES (CURRENT_TIMESTAMP)` ? Works with HSQLDB 2.x . – Jarek Przygódzki Nov 04 '19 at 13:11

score 28 · Answer 2 · answered Feb 08 '16 at 12:45

If your driver is JDBC 4 compliant, there is no need for a dedicated query to test connections. Instead, there is Connection.isValid to test the connection.

JDBC 4 is part of Java 6 from 2006 and you driver should support this by now!

Famous connection pools, like HikariCP, still have a config parameter for specifying a test query but strongly discourage to use it:

connectionTestQuery

If your driver supports JDBC4 we strongly recommend not setting this property. This is for "legacy" databases that do not support the JDBC4 Connection.isValid() API. This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. Again, try running the pool without this property, HikariCP will log an error if your driver is not JDBC4 compliant to let you know. Default: none

Mark Byers · Answer 3 · 2010-09-08T14:40:15.903

12

Unfortunately there is no SELECT statement that will always work regardless of database.

Most databases support:

SELECT 1

Some databases don't support this but have a table called DUAL that you can use when you don't need a table:

SELECT 1 FROM DUAL

MySQL also supports this for compatibility reasons, but not all databases do. A workaround for databases that don't support either of the above is to create a table called DUAL that contains a single row, then the above will work.

HSQLDB supports neither of the above, so you can either create the DUAL table or else use:

SELECT 1 FROM any_table_that_you_know_exists_in_your_database

edited Sep 08 '10 at 14:40

answered Sep 08 '10 at 14:07

Mark Byers

811,555
193
1,581
1,452

Thanks for the answer. I've updated my question slightly due to your "there is no SELECT statement that will always work" statement. `SELECT 1 FROM DUAL` also doesn't work with HSQLDB. – Rob Hruska Sep 08 '10 at 14:18
1

+1, this is about where I've come with my research as well, particularly for the HSQLDB case. – Rob Hruska Sep 08 '10 at 14:26
which ones don't support "select 1" ? Select from dual only works oracle doesn't it ? Not sql server, or mysql at least – NimChimpsky Sep 08 '10 at 14:34
+1 I've given up trying to think of an RDBMS independent way! – Martin Smith Sep 08 '10 at 14:34

score 12 · Accepted Answer · answered Aug 23 '21 at 18:25

The jOOQ manual's section about the DUAL table lists the following for jOOQ's select(inline(1)) query:

-- Access
SELECT 1 FROM (SELECT count(*) dual FROM MSysResources) AS dual

-- BigQuery, CockroachDB, Exasol, H2, Ignite, MariaDB, MySQL, PostgreSQL, 
-- Redshift, Snowflake, SQLite, SQL Server, Sybase ASE, Vertica
SELECT 1

-- MemSQL, Oracle
SELECT 1 FROM DUAL

-- CUBRID
SELECT 1 FROM db_root

-- Db2
SELECT 1 FROM SYSIBM.DUAL

-- Derby
SELECT 1 FROM SYSIBM.SYSDUMMY1

-- Firebird
SELECT 1 FROM RDB$DATABASE

-- HANA, Sybase SQL Anywhere
SELECT 1 FROM SYS.DUMMY

-- HSQLDB
SELECT 1 FROM (VALUES(1)) AS dual(dual)

-- Informix
SELECT 1 FROM (SELECT 1 AS dual FROM systables WHERE (tabid = 1)) AS dual

-- Ingres, Teradata
SELECT 1 FROM (SELECT 1 AS "dual") AS "dual"

score 2 · Answer 5 · answered Apr 19 '13 at 18:45

2

I use

Select COUNT(*) As X From INFORMATION_SCHEMA.SYSTEM_USERS Where 1=0

for hsqldb 1.8.0

answered Apr 19 '13 at 18:45

thinkbase

141
1
2

score 2 · Answer 6 · answered Feb 04 '16 at 20:08

2

For tests using select count(*), it should be more efficient to use select count(1) because * can cause it to read all the column data.

answered Feb 04 '16 at 20:08

Nathan Niesen

375
4
11

The asterisk in `COUNT(*)` has no such effect. If the optimiser isn't too weird, the two should actually behave exactly the same way, [see this blog post](https://blog.jooq.org/whats-faster-count-or-count1/) – Lukas Eder Aug 23 '21 at 18:30

score 2 · Answer 7 · edited Dec 28 '11 at 10:05

2

I use this one:

select max(table_catalog) as x from information_schema.tables

to check connection and ability to run queries (with 1 row as result) for postgreSQL, MySQL and MSSQL.

edited Dec 28 '11 at 10:05

Shadow The GPT Wizard

66,030
26
140
208

answered Dec 28 '11 at 09:57

Wojciechk

99
1
10

score 1 · Answer 8 · answered Jan 21 '14 at 11:31

1

Assuming the OP wants a Java answer:

As of JDBC3 / Java 6 there's the isValid() method which should be used rather than inventing one's own method.

The implementer of the driver is required to execute some sort of query against the database when this method id called. You - as a mere JDBC user - do not have to know or understand what this query is. All you have to do is to trust that the creator of the JDBC driver has done his/her work properly.

answered Jan 21 '14 at 11:31

peterh

18,404
12
87
115

2

I believe the OP is talking about a validation query for a Container's connection pool configuration, not programmatically. For example in Tomcat's context.xml, where you setup Resources, it takes a validationQuery which Tomcat uses to validate a connection. Tomcat itself would have to be changed to take advantage of isValid(). That isn't something the OP can control. – Michael Mar 07 '14 at 22:07
It's also worth noting that "the creator of the JDBC driver has done his/her work properly" is not really guaranteed. I just found that neither Postgres, HSQLDB, nor H2 bothered to implement the method, so it'll always raise an exception there. – Cannoliopsida Jun 12 '14 at 18:28

score 1 · Answer 9 · answered Nov 21 '14 at 08:27

1

How about

SELECT user()

I use this before.MySQL, H2 is OK, I don't know others.

answered Nov 21 '14 at 08:27

wener

7,191
6
54
78

score 1 · Answer 10 · answered Sep 08 '10 at 14:21

1

select 1 would work in sql server, not sure about the others.

Use standard ansi sql to create a table and then query from that table.

answered Sep 08 '10 at 14:21

NimChimpsky

46,453
60
198
311

Does ansi SQL cover `create table`? – Martin Smith Sep 08 '10 at 14:24
yes it does. If you use ansi data types. I'd be surprised if "select 1" didn't work though. – NimChimpsky Sep 08 '10 at 14:30

score 1 · Answer 11 · answered Oct 25 '16 at 12:56

1

Just found out the hard way that it is

SELECT 1 FROM DUAL

for MaxDB as well.

answered Oct 25 '16 at 12:56

Lars Decker

11
2

This does not provide an answer to the question. Once you have sufficient [reputation](http://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](http://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/low-quality-posts/14092840) – Peter Brittain Oct 25 '16 at 21:13
I don't get it, it adds value to the accepted answer, so where is the problem? – Lars Decker Oct 27 '16 at 08:27
1

And as you mentioned: as i cannot comment the accepted answer, so I put it as an answer here. So better not write a post although it might be helpful just because of missing reputation? – Lars Decker Oct 27 '16 at 08:29
TBH, it's a close call... Rather than duplicating an answer, this _should_ have been a comment on the original answer. Failing that, you could have made a suggested edit to the original. – Peter Brittain Oct 27 '16 at 09:18

score 1 · Answer 12 · edited Nov 19 '18 at 22:41

1

For Oracle the high performing query will be

select 'X' from <your_small_table> where <primay_key_coulmn> = <some_value>

This is from a performance perspective.

edited Nov 19 '18 at 22:41

pushkin

9,575
15
51
95

answered Nov 19 '18 at 14:59

Joby Kurian

11
1

score 0 · Answer 13 · answered Jan 20 '17 at 12:30

0

I use this for Firebird

select 1 from RDB$RELATION_FIELDS rows 1

answered Jan 20 '17 at 12:30

claudsan

195
1
2
8

DeFlanko · Answer 14 · 2017-04-12T18:12:59.680

For MSSQL.

This helped me determine if linked servers were alive. Using an Open Query connection and a TRY CATCH to put the results of the error to something useful.

IF OBJECT_ID('TEMPDB..#TEST_CONNECTION') IS NOT NULL DROP TABLE #TEST_CONNECTION
IF OBJECT_ID('TEMPDB..#RESULTSERROR') IS NOT NULL DROP TABLE #RESULTSERROR
IF OBJECT_ID('TEMPDB..#RESULTSGOOD') IS NOT NULL DROP TABLE #RESULTSGOOD

DECLARE @LINKEDSERVER AS VARCHAR(25)    SET @LINKEDSERVER = 'SERVER NAME GOES HERE'
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @OPENQUERY AS VARCHAR(MAX)

--IF OBJECT_ID ('dbo.usp_GetErrorInfo', 'P' ) IS NOT NULL DROP PROCEDURE usp_GetErrorInfo;  
--GO  

---- Create procedure to retrieve error information.  
--CREATE PROCEDURE dbo.usp_GetErrorInfo  
--AS  
--SELECT     
--    ERROR_NUMBER() AS ErrorNumber  
--    ,ERROR_SEVERITY() AS ErrorSeverity  
--    ,ERROR_STATE() AS ErrorState  
--    ,ERROR_PROCEDURE() AS ErrorProcedure  
--    ,ERROR_LINE() AS ErrorLine  
--    ,ERROR_MESSAGE() AS Message;  
--GO  


BEGIN TRY
SET @SQL='
SELECT 1 
'''
--SELECT @SQL
SET @OPENQUERY = 'SELECT * INTO ##TEST_CONNECTION FROM OPENQUERY(['+ @LINKEDSERVER +'],''' + @SQL + ')'
--SELECT @OPENQUERY
EXEC(@OPENQUERY)
SELECT * INTO #TEST_CONNECTION FROM ##TEST_CONNECTION
DROP TABLE ##TEST_CONNECTION
--SELECT * FROM #TEST_CONNECTION
END TRY

BEGIN CATCH
-- Execute error retrieval routine.
IF OBJECT_ID('dbo.usp_GetErrorInfo') IS NOT NULL -- IT WILL ALWAYS HAVE SOMTHING... 
    BEGIN
        CREATE TABLE #RESULTSERROR (
        [ErrorNumber]       INT
        ,[ErrorSeverity]    INT
        ,[ErrorState]       INT
        ,[ErrorProcedure]   INT
        ,[ErrorLine]        INT
        ,[Message]          NVARCHAR(MAX) 
        )
        INSERT INTO #RESULTSERROR
        EXECUTE dbo.usp_GetErrorInfo
    END
END CATCH

BEGIN 
    IF (Select ERRORNUMBER FROM #RESULTSERROR WHERE ERRORNUMBER = '1038') IS NOT NULL --'1038' FOR ME SHOWED A CONNECTION ATLEAST. 
        SELECT
        '0' AS [ErrorNumber]        
        ,'0'AS [ErrorSeverity]  
        ,'0'AS [ErrorState]     
        ,'0'AS [ErrorProcedure] 
        ,'0'AS [ErrorLine]      
        , CONCAT('CONNECTION IS UP ON ', @LINKEDSERVER) AS [Message]            
    ELSE 
        SELECT * FROM #RESULTSERROR
END

learn.microsoft.com

Efficient SQL test query or validation query that will work across all (or most) databases

14 Answers14

Linked