If you are making the transition from MariaDB to SQL Server or vice versa, it is helpful to have a comparison of similar commands between the two platforms. This article provides a list of common tasks and the equivalent SQL code for both database platforms.
Requirements and Setup
In order to follow along with the examples in this article, you will need:
- SQL Server 2014 (or earlier versions)
- MariaDB installed on the same machine
- Windows OS
You can find the MariaDB installer here.
How to Start the Command Line
In SQL Server, you can use the sqlcmd command line tool to administer the database. To start the command line, open the command prompt and type sqlcmd.
In MariaDB, you can use the MySQL Client to start the command line. You will need to enter the password that was requested during the installation of the software.
Comparison of SQL Server vs. MariaDB Commands
Here is a comparison of some common tasks and their equivalent SQL code in SQL Server and MariaDB:
How to Create a Database
SQL Server:
CREATE DATABASE TestDB;
MariaDB:
CREATE DATABASE TestDB2;
How to Switch to a Different Database
SQL Server:
USE TestDB;
MariaDB:
USE TestDB;
How to Verify that the Database was Created
SQL Server:
EXEC sp_databases;
MariaDB:
SHOW DATABASES;
How to Create a Simple Table
SQL Server:
CREATE TABLE customer (
id INT,
name VARCHAR(30)
);
MariaDB:
CREATE TABLE customer (
id INT,
name VARCHAR(30)
);
How to Insert Data into a Table
SQL Server:
INSERT INTO customer VALUES (1, 'John');
MariaDB:
INSERT INTO customer VALUES (1, 'John');
How to Create a Simple Stored Procedure
SQL Server:
CREATE PROCEDURE showcustomers
AS
SELECT * FROM customer;
MariaDB:
CREATE PROCEDURE showcustomers()
SELECT * FROM customer;
How to Call a Stored Procedure
SQL Server:
EXEC showcustomers;
MariaDB:
CALL showcustomers;
How to Get the Current Date
SQL Server:
SELECT CONVERT(DATE, GETDATE());
MariaDB:
SELECT CURRENT_DATE;
How to Get the Current Time
SQL Server:
SELECT CONVERT(TIME, GETDATE());
MariaDB:
SELECT CURRENT_TIME;
How to Get the Current Date and Time
SQL Server:
SELECT GETDATE();
MariaDB:
SELECT CURRENT_DATE, CURRENT_TIME;
How to Modify the Date Format
SQL Server:
SELECT FORMAT(GETDATE(), 'MM-dd-yyyy') AS date;
MariaDB:
SELECT DATE_FORMAT(CURRENT_DATE, '%m%d%Y');
How to Assign a Value to a Variable
SQL Server:
DECLARE @var INT = 1;
SELECT @var;
MariaDB:
SET @var = 1;
SELECT @var;
How to Return the List of Tables of the Current Database
SQL Server:
SELECT * FROM information_schema.tables;
MariaDB:
SHOW TABLES;
How to Return the List of Views of the Current Database
SQL Server:
SELECT * FROM information_schema.views;
MariaDB:
SHOW TABLES;
These are just a few examples of the comparison between SQL Server and MariaDB commands. By understanding the differences and similarities between the two platforms, you can easily transition from one to the other.
For more information about SQL Server and MariaDB commands, refer to their respective documentation.