20 tips on converting MySQL views to MS SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

20 tips on converting MySQL views to MS SQL

Author

Topic

hans_belgim
Starting Member

2 Posts

Posted - 2012-03-09 : 04:34:13

Hi ALL,

I found interesting post about converting MySQL queries to MS SQL and want to share it with everyone:

http://advenet.com/a_kuprianov/blog/default.aspx

Hans

Kristen
Test

22859 Posts

Posted - 2012-03-09 : 09:04:47

I think that article is as good as useless.

50% of the suggestions contain "BLOCKED EXPRESSION" so I have no idea what they were, and whether they would be useful. The author either hasn't noticed or can't be bothered to fix them.

1 = "if the query contains 'ORDER BY...' clause, it is necessary to insert 'TOP 100 PERCENT' right after 'SELECT' keyword"

Bad idea, and won't work reliably

3. "replace 'JOIN ... WHERE' by 'CROSS JOIN ... WHERE'"

Hmmm

8. "replace 'timediff(%expr1%, %expr2%)' by 'CAST(%expr1% - %expr2% AS TIME)'"

Dubious?

10. "replace 'conact(%expr1%, %expr2%, ..., %exprN%)' by '%expr1% + %expr2% + ... + %exprN%'"

Useless advice. I presume this is a typo and was intended to refer to MySQL's conCat() function? if so that does a variety of type conversions that just using "+" in SQL Server won't do.

12. "replace 'RAND()' by 'newID()' - it works in MS SQL 2005 and higher"

Yeah, for an ORDER BY that would work, but there is no mention made of that. Anyway, shouldn't be using ORDER BY in a VIEW, and elsewhere NewID() won't give the same datatype as RAND() which will probably break existing code

A brand new blog, with only 2 articles, gets posted here a couple of days later, by a user with only two posts. Maybe its just a coincidence ...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-09 : 10:04:19

quote:
1 = "if the query contains 'ORDER BY...' clause, it is necessary to insert 'TOP 100 PERCENT' right after 'SELECT' keyword"

If it's a view or subquery, adding TOP (100) PERCENT will have the same effect as removing the Order By entirely, a resultset ordered however the last query operator left it. Removing the Order By entirely is preferred.

quote:
MySQL '... like %template%' is equal to 'CONTAINS(..., 'template')' in MS SQL

No, actually SQL server does have a LIKE operator, and just blindly converting to Contains will result in the following error:
Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view <object name> because it is not full-text indexed.

quote:
replace '%table1% cross join %table2% on %condition%' by '%table1% cross join %table2% where %condition%'

Err, no. CROSS JOIN .. ON ... would convert to INNER JOIN ... ON or LEFT|RIGHT OUTER JOIN ... ON. Cross join is for the times when there is no join predicate and a Cartesian product is desired.

quote:
replace 'timediff(%expr1%, %expr2%)' by 'CAST(%expr1% - %expr2% AS TIME)'

I can think of very few data types that you can just subtract one from the other, cast as time and get something meaningful.

--
Gail Shaw
SQL Server MVP

dm_
Starting Member

1 Post

Posted - 2012-08-11 : 11:24:22

I'd like just to add a comment regarding the tool from http://dbconvert.com , mentioned in the article.
Due to the limitation, views can't be converted in trial version. It is possible to do that after purchase. Unfortunately, author of this article never requested providing him a full functional copy to make a competent review.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources