20160815

MySQL vs T-SQL Syntax Differences

I'm working on a web app project that uses a SQL backend. I wrote it originally using Ruby and MySQL, and I've been porting it to Azure and Microsoft SQL Server. You'd hope that creating the database schema would be the same for both, because it's all SQL, but nope, that's just wishful thinking. SQL has many flavors, and each of them support certain different things. T-SQL (which is what's used on Microsoft SQL Server) seems to lack some nice features MySQL had, which makes me sad.

Here are the changes I had to make in the script to create the DB schema on T-SQL compared to MySQL:

  • MySQL used backquotes (`) for table names in a lot of places. T-SQL either doesn't use any at all or uses regular double quotes.
  • DROP TABLE silently ignores failures for nonexistent tables on MySQL, but with T-SQL you have to specify IF EXISTS. Thanks.
  • T-SQL, maddeningly, does not support unsigned integers. Everything is signed. Ughhh, whyyyy?
  • varchar or other string-type fields should specify their collation parameters, i.e. when comparing two values from this column, should they compare as case-sensitive and so on. The collation values in T-SQL are more granular and specific (and hence look different) than those on MySQL. For example, on MySQL I had latin1_general_ci but with T-SQL I had to use SQL_latin1_general_cp1_ci_ai. The ci means "case insensitive", and ai means "accent insensitive", i.e. "ΓΌ" is the same as "u".
  • On MySQL you call the primary key a UNIQUE KEY, but with T-SQL you call it a PRIMARY KEY and have to specify IDENTITY on it. Ok, thanks again.
  • Enum type values use different syntax. On MySQL, if you want to represent a value that can only be "a", "b", or "t", you do `valuename` ENUM('a', 'b', 't'). With T-SQL it is "valuename" CHAR(1) CHECK("valuename" IN ('a', 'b', 't'))
  • The worst offender is that ON DELETE CASCADE doesn't work in T-SQL. When you have two related tables in a SQL database, associated by a foreign key, what happens if the row in the foreign table is deleted? Maybe you'd like all the rows referring to that foreign key to also be deleted, because they're now invalid.

    In MySQL, ON DELETE CASCADE does exactly that, but T-SQL basically doesn't support it. Instead, I'd have to make some kind of trigger--a stored procedure that executes automatically when it detects it needs to do this kind of cascading deletion. I haven't done that yet.

There is some decent documentation about T-SQL syntax, but I still had to use trial and error to figure out most of the stuff above, peeling the onion on syntax errors, one at a time. Maybe this pain, codified in the table above, will help some other poor soul.

0 comments:

Post a Comment