Why do I need to configure the SQL dialect of a data source?
Dialect means "the variant of a language". Hibernate, as we know, is database agnostic. It can work with different databases. However, databases have proprietary extensions/native SQL variations, and set/sub-set of SQL standard implementations. Therefore at some point hibernate has to use database specific SQL. Hibernate uses "dialect" configuration to know which database you are using so that it can switch to the database specific SQL generator code wherever/whenever necessary.
Short answer
"The irony of JDBC is that, although the programming interfaces are portable, the SQL language is not. Despite the many attempts to standardize it, it is still rare to write SQL of any complexity that will run unchanged on two major database platforms. Even where the SQL dialects are similar, each database performs differently depending on the structure of the query, necessitating vendor-specific tuning in most cases."
..stolen from Pro JPA 2 Mastering the Java Persistence API, chapter 1, page 9
So, we might think of JDBC as the ultimate specification that abstracts away everything related to databases, but it isn't.
A quote from the JDBC specification, chapter 4.4, page 20:
The driver layer may mask differences between standard SQL:2003 syntax and the native dialect supported by the data source.
May is no guarantee that the driver will, and therefore we should provide the dialect in order to have a working application. In a best-case scenario, the application will work but might not run as effectively as it could if the persistence provider knew which dialect to use. In the case of Hibernate he will refuse to deploy your application unless you feed him the dialect.
What about JPQL then?
The JDBC specification does not mention the word JPQL. JDBC is a standardized way of database access. Go read this JavaDoc and you will find that once the application can access the database, what must be fed into the JDBC compliant driver is vanilla = undecorated SQL.
It is worth noting that JPQL is a query language, not a data definition language (DDL). So even if we could feed the JDBC driver with JPQL, that would be of no use for the persistence provider during the phase of parsing the persistence.xml file and setting up tables.
Closer look at the property
For your reference, here is an example for Hibernate and EclipseLink on how to specify a Java DB dialect in the persistence.xml file:
<property name="hibernate.dialect" value="org.hibernate.dialect.DerbyTenSevenDialect"/>
<property name="eclipselink.target-database" value="JavaDB"/>
Is the property mandatory?
In theory, the property has not been standardized and the JPA 2.1 specification says not a word about SQL dialects. So we're out of luck and must turn to vendor specific empirical studies and documentation thereof.
Hibernate refuse to accept a deployment archive that hasn't specified the property rendering the archive undeployable. Hibernate documentation says:
Always set the hibernate.dialect property to the correct org.hibernate.dialect.Dialect subclass for your database.
So that is pretty clear. Do note that the dialects listed in the documentation are specifically targeting one or the other vendor. There is no "generic" dialect or anything like that. Given then that the property is an absolute requirement for a successful deployment, you would expect that the documentation of the WildFly application server which bundles Hibernate should say something, but it doesn't.
EclipseLink on the other hand is a bit more forgiving. If you don't provide the property, the deployment deploys (without warning too). EclipseLink documentation says:
Use the eclipselink.target-database property to specify the database to use, controlling custom operations and SQL generation for the specified database.
The talk is about "custom operations and SQL generation", meaning it is bit vague if you ask me. But one thing is clear: They don't say that the property is mandatory. Also note that one of the available values is "Database" which represent "a generic database" target. Hmm, what "dialect" would that be? SQL 2.0?? But then again, the property is called "target-database" and not "dialect" so maybe "Database" translates to no SQL at all lol. Moving on to the GlassFish server which bundles EclipseLink. Documentation (page "6-3") says:
You can specify the optional eclipselink.target-database property to guarantee that the database type is correct.
So GlassFish argues that the property is "optional" and the value added is a "guarantee" that I am actually using Java DB - in case I didn't know.
Conclusion
Copy-paste whatever you can find on google and pray to God.
Hibernate.dialect property tells Hibernate to generate the appropriate SQL statements for the chosen database.
A list of available dialects can be found here: http://javamanikandan.blogspot.in/2014/05/sql-dialects-in-hibernate.html
RDBMS Dialect
DB2 org.hibernate.dialect.DB2Dialect
DB2 AS/400 org.hibernate.dialect.DB2400Dialect
DB2 OS390 org.hibernate.dialect.DB2390Dialect
PostgreSQL org.hibernate.dialect.PostgreSQLDialect
MySQL org.hibernate.dialect.MySQLDialect
MySQL with InnoDB org.hibernate.dialect.MySQLInnoDBDialect
MySQL with MyISAM org.hibernate.dialect.MySQLMyISAMDialect
Oracle (any version) org.hibernate.dialect.OracleDialect
Oracle 9i/10g org.hibernate.dialect.Oracle9Dialect
Sybase org.hibernate.dialect.SybaseDialect
Sybase Anywhere org.hibernate.dialect.SybaseAnywhereDialect
Microsoft SQL Server org.hibernate.dialect.SQLServerDialect
SAP DB org.hibernate.dialect.SAPDBDialect
Informix org.hibernate.dialect.InformixDialect
HypersonicSQL org.hibernate.dialect.HSQLDialect
Ingres org.hibernate.dialect.IngresDialect
Progress org.hibernate.dialect.ProgressDialect
Mckoi SQL org.hibernate.dialect.MckoiDialect
Interbase org.hibernate.dialect.InterbaseDialect
Pointbase org.hibernate.dialect.PointbaseDialect
FrontBase org.hibernate.dialect.FrontbaseDialect
Firebird org.hibernate.dialect.FirebirdDialect