Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Compare MariaDB vs SQL Server SQL Commands


By:   |   Updated: 2015-01-15   |   Comments (2)   |   Related: More > Other Database Platforms

Problem

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. Sure you can do some searches to find the answer, but it would be helpful to have a list of common tasks and the equivalent SQL code for both database platforms.

Solution

The following tip will show a comparison of the T-SQL commands for SQL Server versus the SQL commands for MariaDB.

Requirements and setup

  1. SQL Server 2014, but 95% of the T-SQL commands will work on earlier versions as well
  2. The MariaDB should be installed. For this tip, I installed MariaDB and SQL Server on the same machine using the Windows OS. You can find the MariaDB installer here.

There are several ways to issue commands, but we will use the command line tools for both database systems.

How to start the command line in SQL Server

  1. SQL Server uses the sqlcmd as the command line to administer in SQL Server. In order to start, in the cmd write sqlcmd.

    Tabular properties

How to start the command line in MariaDB

  1. You can use the MySQL Client (MariaDB) to start the command line.

    Deploy tabular project

  2. You will need to enter a password. This password was requested when installing the software.

    Deploy tabular project

Comparison of SQL Server vs. MariaDB Commands

How to create a database

SQL Server

MariaDB

CREATE DATABASE TestDB
GO

Where TestDB is the new database created. In SQL Server when you create the database like this, you inherit the Database characteristics from the model database. The model database is a system database used as a template to create other databases. If you change the characteristics of the model database, the new databases created will inherit the changes. For more information about creating databases, review the CREATE DATABASES link.

CREATE DATABASE TestDB2;

For more information about creating Databases, review the CREATE DATABASES link,

How to switch to a different database

SQL Server

MariaDB

USE TestDB
GO

USE TestDB;

Note that only the ; and go are different

How to verify that the database was created

SQL Server

MariaDB

exec sp_databases
GO

show databases;

How to create a simple table

SQL Server

MariaDB

create table customer(id int,name varchar(30))
GO

For more information about creating tables and about SQL Server data types, refer to these links:

create table customer(id int,name varchar(30));

For more information about creating tables and about MariaDB data types, refer to these links:

How to insert data in a table

SQL Server

MariaDB

insert into customer values (1,'John')
GO

More information:

insert into customer values (1,'John');

How to create a simple stored procedure

SQL Server

MariaDB

create procedure showcustomers
as
select * from customer
GO

More information:

create procedure showcustomers()
select * from customer;

How to call a stored procedure

SQL Server

MariaDB

exec showcustomers
GO

call showcustomers;

How to get the current date

SQL Server

MariaDB

select CONVERT(date, GETDATE())
GO

More information:

select current_date;

How to get the current time

SQL Server

MariaDB

select CONVERT(time, GETDATE())
GO

More information:

select current_time;

How to get the current date and time

SQL Server

MariaDB

select getdate()

More information:

select current_date, current_time;

How to modify the date format

SQL Server

MariaDB

select FORMAT(getdate(),'MM-dd-yyyy') as date 
GO

More information:

select date_format(current_date,'%m%d%Y');

For more information, refer to this link:

How to assign a value to a variable

SQL Server

MariaDB

declare @var int=1
select @var
GO

set @var=1;
select @var;

How to return the list of tables of the current database

SQL Server

MariaDB

SELECT * FROM information_schema.tables
GO

SHOW TABLES;

How to return the list of views of the current database

SQL Server

MariaDB

SELECT * FROM information_schema.views
GO

SHOW TABLES;

It shows tables and views.

Next Steps
  • For more information, refer to the following links:
  • Stay tuned for more tips where we will cover functions and more queries


Last Updated: 2015-01-15


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.



    



Thursday, January 15, 2015 - 6:50:08 AM - Greg Robidoux Back To Top

Thanks Thomas for pointing that out. The code has been updated.


Thursday, January 15, 2015 - 5:25:42 AM - Thomas Franz Back To Top

Minor copy-paste-mistake: you should use

select CONVERT(time, GETDATE())

on MS SQL to get the current time (and not CONVERT(date ...))


Learn more about SQL Server tools