I use MySQL coming from TSQL because MySQL is supported by the more affordable express licence.. Couple of lessons I had to learn when coming from TSQL:
- MySQL doesn't support active directory authentication. I invoke 'cmd /c whoami > %WorkingDir%User.txt' i the main program and read the results from the file. Then crete users each time using UserDel, User Add, and RightAdd based on matching table values. It's a pain but it works great once you set it up.
- MySQL doesn't support cross apply in SQL. You have to join to expressions or CTEs. It does the same thing just not as elegantly.
- You can't rename databases. You have to copy data to a new database and drop the old one.
- You can't delete records from a table using a subquery using the same table in the where or from claus (for example when trying to remove duplicate records based on top record subqueries) It's a pain, and I'm still struggling with the best way to do this.
- You can't start a use Begin Tran, Commit, or Rollback on a single transaction. You have to use the workbench interface and it effects the entire database until the transaction is completed.
- There is no Top X clause (select top 10 from table).. You have to use the workbench and the effect is also global
Other than that most of the syntax between MySQL and TSQL is the same,