mssqltips logo

Code Comparison for SQL Server vs. MariaDB

By:   |   Updated: 2015-02-06   |   Comments (4)   |   Related: More > Other Database Platforms

Problem

I have a need to work with both SQL Server and MariaDB.  I need to know how to complete some common coding best practices.  Can you provide a code comparison for MariaDB vs. SQL Server as it pertains to how to comment, how to create functions and procedures with parameters, how to store query results in a text file, how to show the top n rows in a query, how to use loops and more?

Solution

This tip demonstrates the code differences between SQL Server and MariaDB for a few common coding scenarios.  Also check out my first tip that demonstrates how to Compare MariaDB vs SQL Server SQL Commands.

Requirements and Setup

  1. SQL Server 2014 or earlier versions.
  2. The MariaDB should be installed. In this tip, I installed in the same machine the MariaDB and the SQL Server in the Windows OS. You can find the MariaDB installer here.

Code Comparison for SQL Server vs. MariaDB

How to comment 1 line of code in MariaDB vs. SQL Server

SQL Server

MariaDB

--This is a comment for 1 in SQL Server line

#This is the way to comment 1 line in MariaDB

 

How to comment multiple lines of code in MariaDB vs. SQL Server

SQL Server

MariaDB

/*This is a comment in
SQL Server multiple lines
of T-SQL code*/

/*MariaDB is equal to
SQL Server in comments for
multiple lines of code*/

 

How to SELECT the TOP 5 Rows in a table in MariaDB vs. SQL Server

SQL Server

MariaDB

SELECT TOP 5 *
FROM dbo.customer;
GO

SELECT from customer
LIMIT 5;

 

How to create a function in MariaDB vs. SQL Server

SQL Server

MariaDB

CREATE FUNCTION dbo.hello_mssqltips()
RETURNS varchar(30)

AS
BEGIN

RETURN ('Hello mssqltips')
END
GO

DELIMITER $$
CREATE FUNCTION hello_mssqltips()
RETURNS varchar(30)
LANGUAGE SQL
BEGIN
RETURN 'Hello mssqltips';
END;
$$
DELIMITER ;

 

How to create a function with parameters in MariaDB vs. SQL Server

SQL Server

MariaDB

CREATE FUNCTION dbo.hello_withparameter(@name varchar(30))
RETURNS varchar(30)

AS
BEGIN

RETURN ('Hello '[email protected])
END
GO

DELIMITER $$
CREATE FUNCTION hello_withparameter(name varchar(30))
RETURNS TEXT
LANGUAGE SQL
BEGIN
RETURN CONCAT('Hello ', name);
END;
$$
DELIMITER ;

 

How to call a function with parameters in MariaDB vs. SQL Server

SQL Server

MariaDB

select dbo.hello_withparameter('John')
GO

select hello_withparameter('John');

 

How to get information about a table in MariaDB vs. SQL Server

SQL Server

MariaDB

sp_help customer;
GO

describe customer;

 

How to get the code definition in MariaDB vs. SQL Server

SQL Server

MariaDB

sp_helptext object_name

Returns the code of existing procedures, functions and views. It is not applicable to tables.

help create table

help create procedure

help create function

 

How to create a procedure with a loop in MariaDB vs. SQL Server

SQL Server

MariaDB

create procedure dbo.repeatsample
@l INT
as
declare @value int=0
while @l>@value-1
BEGIN
SET @value = @value + 1
END
SELECT @value
GO

delimiter //
CREATE PROCEDURE repeatsample(l INT)
BEGIN
SET @value = 0;
REPEAT SET @value = @value + 1;
UNTIL @value > @l END REPEAT;
END
//

 

How to execute a procedure with a loop in MariaDB vs. SQL Server

SQL Server

MariaDB

exec repeatsample 10

CALL repeatsample(10)//

SELECT @value//

Note that we are calling the @value variable later after iterating in the loop in the procedure.

 

How to save the query results in a text file in MariaDB vs. SQL Server

SQL Server

MariaDB

Save this query in a file named customerquery.sql and save it at c:\.

USE Test;
GO
SELECT * FROM dbo.customer
GO

Now at the cmd prompt run this command:

sqlcmd -i c:\scripts\customerquery.sql -o c:\scripts\results.txt

/*First update the user privileges, in this case the root User to increase the privileges to write files.*/

UPDATE user
SET File_priv = 'Y'
WHERE User = 'root';
FLUSH PRIVILEGES;

/*Now you can save the results of a query in the customer.csv file.*/

select * from customer
into outfile 'c:\\customer.csv'

 

 

How to use IF, ELSEIF and ELSE clauses in MariaDB vs. SQL Server

SQL Server

MariaDB

/*This sample shows the water state according to the temperature*/

CREATE FUNCTION dbo.WaterState(@temperature INT )
RETURNS varchar(10)
as
BEGIN
declare @state varchar(10)
IF @temperature = 100
SET @state = 'Boiled'

IF @temperature between 1 and 99
SET @state = 'Liquid'

ELSE
SET @state = 'Frozen'

RETURN @state

END
GO

DELIMITER //

CREATE FUNCTION WaterState ( temperature INT )
RETURNS varchar(10)

BEGIN
declare state varchar(10) ;
IF temperature = 100 THEN
SET state = 'Boiled';

ELSEIF temperature between 1 and 99 then
SET state = 'Liquid';

ELSE
SET state = 'Frozen';

END IF;

RETURN state;

END; //

DELIMITER ;

 

Next Steps
For more information about T-SQL and Mariadb, refer to the following links:

Last Updated: 2015-02-06


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, June 15, 2015 - 10:25:30 AM - Radames Back To Top

Much appreciated, thanks


Thursday, February 19, 2015 - 8:46:39 AM - Dave Back To Top

Nice comparison of the two DBMS's.

The IF, ELSEIF, ELSE example serves its purpose but the logic may be flawed.  It look like a temperature > 100 will return 'Forzen'


Thursday, February 19, 2015 - 1:31:51 AM - R. M. Joseph Back To Top

Mr. Daniel Calbimonte I appreciate your help to understand the basic programming difference between T-SQL and MariaDN.

Recently I came to know about MariaDB.  Thanks.


Monday, February 09, 2015 - 11:10:39 AM - Jacque Back To Top

Thanks!  This really helps.  Kudos!



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools