is there standard sql that works in all database

Question

As seen below the syntax is different for different Database .Isnt there a standard way that works in all Databases. Is there any tool to convert any sql to any sql

SqlServer2005:

 CREATE TABLE Table01 (  
 Field01  int  primary key identity(1,1)  
 )

Sqlite:

CREATE TABLE Table01 (
  Field01  integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE
);

Answer 1

No, there's no standard SQL for this. As a shameless plug, I can recommend trying Wizardby if you're into anything related to database schema migration/continuous integration.

Answer 2

There are several SQL standards out there, and SQL 1999 is probably the closest you will get, as each DB adopted a different standard (if any).

Answer 3

The "query" part of SQL (commonly called DML - Data Manipulation Language) is reasonably standardized, and queries written on one database will often run on another database if no "vendor enhancement" features are used. The "create database bits" parts of SQL (often known as DDL - Data Definition Language) is not standardized, and every database out there does it a little differently. So, as you've discovered, statements such as CREATE TABLE written for one database will not work without some tweaking on another database.

<soapbox>
In my opinion this is a Good Thing. DDL effectively defines what can be created in the database. If all vendors use exactly the same DDL, then all products are going to be exactly the same in terms of the features they support. For example, in order to allow for table inheritance in PostgreSQL there must be some way to define how one table inherits from another, and this definition must be part of how a table is defined, either as part of the CREATE TABLE statement or in some other fashion, but it has to be a DDL statement. Thus, for purely functional reasons (because PostgreSQL supports a feature which most databases do not) DDL in PostgreSQL must be different than in other databases. A similar situation arises in MySQL because it allows the use of different ISAM engines with different capabilities. Similar situations arise in Oracle...and in SQL Server...and <your favorite database here>.

Standards are a double-edged sword. One one hand they're a great thing, because they provide a "standard" way of doing something. On the other hand they're a terrible thing because the very purpose of a standard is to provide a "standard" way of doing something, which is another way of saying "create stagnation and inhibit innovation".
</soapbox>

Share and enjoy.