Pagination - SQL Server Vs MySQL‏

Continuing my series on how same things can be done differently in SQL Server and MySQL, this week we will see how Pagination works in SQL Server and MySQL. Pagination is a method of showing result in small batches if the results return a large set of data.

Consider the following set of data

create table testing
(
    sales_id int,
    product_id char(7),
    sales_date datetime,
    sales_amount decimal(12,2)
)
 
insert into testing(sales_id,product_id,sales_date,sales_amount)
select 1,'PR00001','2001-01-01',1200.00 union all
select 2,'PR00002','2003-01-21',3000.50 union all
select 3,'PR00002','2003-01-21',2500.00 union all
select 4,'PR00001','2002-02-15',1000.00 union all
select 5,'PR00003','2005-12-19',3000.50 union all
select 6,'PR00003','2006-11-01',8000.00 union all
select 7,'PR00004','2007-04-22',350.00 union all
select 8,'PR00004','2007-04-22',590.00 union all
select 9,'PR00004','2007-04-22',590.00 union all
select 10,'PR00001','2008-05-27',4800.50


Let us assume that you want to return the first 5 rows from the results ordered by sales_date

SQL Server

Version 2012 onwards you can use OFFSET and FETCH NEXT Clauses

select * from testing
order by sales_date
offset 0 rows
fetch next 5 rows only


The above code orders the results by ascending order of sales_date and fetches 5 rows. The next 5 rows can be returned using the following code

select * from testing
order by sales_date
offset 5 rows
fetch next 5 rows only


The FETCH clause skips the first 5 rows and returns the next five rows

result1
MySQL

The same functionality can be done in MySQL using the LIMIT clause

select * from testing
order by sales_date
LIMIT 0,5


The above code returns first five rows in ascending order of sales_date. To get next five rows, use the following code

select * from testing
order by sales_date
LIMIT 5,10

result2

Arithmetic Operations - SQL Server Vs MySQL

Arithmetic operations done in SQL Server and MySQL may not give the same result. Integer divisions always result in an integer in SQL Server whereas in MySQL, it results to a decimal. Let’s see this with examples.

SQL Server

Run the following code

SELECT 5/2

The result is 2 and not 2.5 This is because both 5 and 2 are Integers and the end result is also converted to the data type of integer. So the actual value 2.5 becomes 2 when implicitly converted to an integer datatype.

SELECT 1/0

This results to the error Divide by zero error because any number divided by zero is infinity.

SELECT 'a'/10

The above throws an error "Conversion failed when converting the varchar value 'a' to data type int."
Now let’s observe arithmetic operations in MySQL, given the same set of data.

MySQL

Run the following code

SELECT 5/2

The result is 2.5 Although both 5 and 2 are of integer datatypes, MySQL results to decimal datatype during the division

SELECT 1/0

MySQL returns NULL for the above select statement. It won't give Divide by Zero error

SELECT 'a'/10

The above returns 0 and not an error.

Hope these tips were useful and you should keep them in mind when doing arithmetic calculations in MySQL

Row set Concatenation - SQL Server vs MySQL

Row set concatenation is a frequently required feature. Based on identical value, other unique values should be concatenated.

Consider the following set of data

create table test(id int, names varchar(100))
insert into test(id,names)
select 1,'Suresh' union all
select 1,'Kumar' union all
select 1,'Nithil' union all
select 2,'John' union all
select 2,'Murugan'


SQL Server

We can use FOR XML PATH in SQL Server as shown below

declare @names varchar(8000)
set @names=''
select distinct id,
    stuff((select (','+@names+names) from test as t2 where t1.id=t2.id for xml path('')),1,1,'') as names
from
    test as t1


The FOR XML PATH concatenates the names for each id and the STUFF function removes the first comma from a list

MySQL

MySQL has a built-in function named GROUP_CONCAT()

select id,group_concat(names) from test
group by id


This built-in function concatenates the names for each id. Simple!

row-concat-sql

Stored Procedure - SQL Server vs MySQL

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how Stored Procedures are supported in both SQL Server and MySQL. We also explore the difference in calling them.

Consider the following code assuming that you have a database named test

SQL Server

Create procedure proc_test
as
select 'Hello World from SQL Server'

GO

The above code creates a stored procedure named proc_test in your database. To execute the procedure, you need to use either EXEC or EXECUTE keywords

EXEC proc_test

This statement will display the result Hello World in SQL Server

MySQL

DELIMITER $$;
DROP PROCEDURE IF EXISTS `test`.`proc_test`$$
CREATE PROCEDURE `test`.`proc_test` ()

BEGIN   
    select 'Hello World from MySQL' as message;

END$$

DELIMITER ;$$


The above code creates a stored procedure named proc_test in the database named test. To execute the procedure, you need to use the keyword CALL

CALL proc_test()

which will display the result Hello World in MySQL.

Note that in MySQL, delimiters are important for creating a stored procedure and the procedure name should be succeed by empty brackets () if there are no parameters.