Code Comparison for SQL Server vs. MariaDB

By:   |   Comments (4)   |   Related: > 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 '+@name)
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:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




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

Much appreciated, thanks


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

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 (36281)

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 9, 2015 - 11:10:39 AM - Jacque Back To Top (36186)

Thanks!  This really helps.  Kudos!















get free sql tips
agree to terms