Tags: MSSQL , MySQL

I covered few points in my previous article MSSQL to MySQL Guide to Migration Part - 1. We'll cover few more points for migration. So far we covered important points for basic migration in this article we'll discuss some extended migration points.

1. DENSE_RANK function
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. - Microsoft (http://technet.microsoft.com/en-us/library/ms173825.aspx)

You all may not aware of this function but it exists in MSSQL. This function doesn't exists in MySQL. There are many ways available on the internet but word to wise eliminate duplicate records if possible. Don't use this function if your product support multiple dialect.

2. Database INDEX migration
CLUSTERED INDEX Primary key will do the job in both MSSQL and MySQL.
NON-CLUSTERED INDEX There ain't clause "Non-Clustered" in MySQL. Yet you can assign multiple index in MySQL. Its just matter of syntax.

/* MSSQL */
CREATE NONCLUSTERED INDEX [IndxNc_user_master__MultiCol01] ON [dbo].[user_master] 
(
 [column1] ASC,
 [column2] ASC
);
/* -------------------------------------------------- */
/* MySQL */
ALTER TABLE user_master
ADD INDEX IndxNc_user_master__MultiCol01 
(
column1 ASC,
column2 ASC
);

3. JOIN(concat) two string in MSSQL and MySQL
MSSQL is designed in a way that it will identify your input.

• If you are using string with `+` then it'll append and prepend for you. 
• If you are using numeric value with `+` then it'll do summation. 

MySQL you've to use different methods to perform the same.

• If you are using string use concat('string', column_value, 'string', column_value).
• If you are using numeric value, its same as MSSQL. Use `+` sign

/* MSSQL numeric value */
SELECT 100 + id FROM user_master;
/* MSSQL String value */
SELECT ':'+Firstname+':' FROM user_master;
/* -------------------------------------------------- */
/* MySQL numeric value */
SELECT 100 + id FROM user_master;
/* MySQL String value */
SELECT concat(':', Firstname, ':') FROM user_master;

4. PRINT message in MSSQL and MySQL
MSSQL provide clause called PRINT to print run time custom message. In MySQL SELECT clause does the job for you.

Warning: PRINT in MSSQL won't effect your output result while you are dealing with hibernate, where in MySQL SELECT clause print message in tabular format so hibernate will use this table as a result. You may get wrong result in hibernate. Read more about hibernate + stored procedure How To Call Stored Procedure In Hibernate

/* MSSQL */
PRINT 'javaQuery';
/* -------------------------------------------------- */
/* MySQL */
SELECT 'javaQuery';

5. Comments
This is not much important however for your consideration.

• MSSQL: It supports comment block /* comment */ and hyphen --.
• MySQL: It supports comment block /* comment */ and hyphen following one space -- .

/* MSSQL */
/* MSSQL comments */
/* --SELECT * FROM user_master */
/* -------------------------------------------------- */
/* MySQL */
/* MySQL comments */
/* -- SELECT * FROM user_master */

6. SELECT values into variables
Selecting values in multiple values differ in both the database language. Here sample code is available for both database.

• In MSSQL you can use `=` operator.
• In MySQL you can use `INTO` phrase.

/* MSSQL */
DECLARE @VARIABLE1 VARCHAR(100);
DECLARE @VARIABLE2 VARCHAR(100);

SELECT @VARIABLE1 = Firstname, @VARIABLE2 = Lastname
FROM user_master
WHERE id = 1;
/* -------------------------------------------------- */
/* MySQL */
DECLARE VARIABLE1 VARCHAR(100);
DECLARE VARIABLE2 VARCHAR(100);
 
SELECT Firstname, Lastname INTO VARIABLE1, VARIABLE2 
FROM user_master
WHERE id = 1;

Tags: MSSQL , MySQL