Home > Documentation > MySQL to MS SQL

Convert Views

One of the important steps of MySQL to SQL Server database migration is queries conversion. This article gives 20 tips on how to modify MySQL views to make it compatible with Microsoft SQL Server format. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.

First, you need to get list of all views in database as follows:

SELECT table_name, view_definition
 FROM information_schema.views
   WHERE table_schema='0atabase name%';

And then start to modify each MS SQL view query according to the following rules:

• if the query contains ORDER BY... clause, it is necessary to insert TOP 100 PERCENT right after SELECT keyword
• replace all occurrenced of now() by getdate()
• replace JOIN ... WHERE by CROSS JOIN ... WHERE
• replace isnull(expression) by expression is null
• replace all occurrenced of from_unixtime(expression) by DateAdd(ss, expression, '01-01-1970')
• cut off codepage conversions (like '_cp1252') if any, since SQL Server does not support this syntax
• replace curdate() by getdate()
• replace 'timediff(expr1, expr2)' by 'CAST(expr1 - expr2 AS TIME)'
• replace 'table1 cross join table2 on condition' by 'table1 cross join table2 where condition'
• replace 'conact(expr1, expr2, ..., exprN)' by 'expr1 + expr2 + ... + exprN'
• replace 'SELECT ... LIMIT number_of_rows' by 'SELECT TOP number_of_rows ...'
• replace 'RAND()' by 'newID()' - it works in MS SQL 2005 and higher
• replace 'DAY(expression)' or 'DAYOFMONTH(expression)' by 'DATENAME(d, expression)'
• replace 'DAYOFYEAR(expression)' by 'DATENAME(dy, expression)'
• replace 'DAYNAME(expression)' by 'DATENAME(dw, expression)'
• replace 'HOUR(expression)' by 'DATENAME(hh, expression)'
• MySQL '... like template' is equal to 'CONTAINS(..., 'template')' in MS SQL
• MySQL 'expression - INTERVAL 1 DAY' is equal to MS SQL 'dateAdd(day, -1, expression)'
• MySQL 'expression - INTERVAL 1 MONTH' is equal to MS SQL 'dateAdd(month, -1, expression)'
• MySQL 'expression - INTERVAL 1 YEAR' is equal to MS SQL 'dateAdd(year, -1, expression)'

Also you should remember that unlike MySQL SQL Server requires all selected columns in SELECT ... GROUP BY ... queries to be either under GROUP BY clause or in aggregation functions.

Of course, there are a lot of newances staying outside of this article, it just covers the most frequent issues in migrating views from MySQL to MS SQL. If you need a solution for complete migration of MySQL database to MS SQL server, take a look at MySQL-to-MSSQL converter.

Have any questions? Contact us