SQL Compatibility Chart (esp data types)

So...happens I'm working on some code which...will end up being used on different sql servers at the same time.

Although the SQL code is different depending on the server, the data types and columns are not.

Therefor, I need to know which are the data types common to (at least most) sql server types.

As a starting point, I have the following types:

byte, char, float, int, text, varchar, blob

Please note that spelling is quite important, since the data type name will end in the query as is (eg: although both int and integer are supported, I need the common one).

So, the question is, does anyone know of a chart comparing compatibility between sql servers? Or perhaps someone which did some research in the field?

As far as bias goes, I'm obviously biased to a particular RDBMS, so no need for answers on which RDBM开发者_如何转开发S happens to be better. Let's keep this focused and on topic, ok?

---

I think you will end up writing specific, casy by case SQL statements for each type of database server. Certainly I did.

I've been in your situation, including having the intention to write database agnostic code, but in the long run it just does not work. One database will not, for example, handle multi-byte strings while another will demand them (ie, SQL Server CE), this will force you to use either Varchar vs NVarchar on columns, for example. Some databses will support multi byte strings, but with awful performance. One will use VARCHAR2 (Oracle), and everyone else will use VARCHAR. One will handle BLOBs one way while another will do so differently. Don't get me started on date data types, either.

Rather than find the magic subset of the SQL language and data types that works in all databases, you would be wiser to look for a data access method/library that can hide the differences for you (maybe some ORM library that lets you create DB objects as well as access them?)

Like I said, I have been (and still am) in your situation of having to support multiple databases and the best solution for me is to write optimal code for each database, rather that trying to find SQL data types and code that works in all of them (I wasn't able to, not to a satisfactory level).

Also, you will be able to squeeze more performance out of each DB if you create separate SQL text for each database (ie, the performance-related parameters you can specify while creating an Oracle table that do not apply at all when creating a table in any other database).

I say, do not fight the syntax differences in the different databases, you will not win. It's a better idea to put up with and use those differences to your advantage as much as possible.

---

I'd look into the SQL ANSI standard specification and use the data types specified there. A book like this may help you.

They all have good documentation, so I would just read up on their data types. Would probably have all the info you need. The only other information I could find before is pretty old.

Hope that helps.

Edit: Just another thought... you could use the strategy pattern for your SQL, that way it wouldn't matter if it was different, you could use the more advanced features. Though this way you'd have more work to do and more to maintain :/